Sample Header Ad - 728x90

Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

9 votes
3 answers
14487 views
I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "insert" with only one record, the server takes about 0.2 ~ 0.3 secs with MySQL 8. The same "insert" in MySQL 5.7 takes just 0.003 secs or less. I already discarded Windows Defender or another Antivirus Program, the writing speed of the hard disk is not the problem because I have a Virtual Machine in the same server, the VM has MySQL 5.7 and the result is pretty good too: 0.003 secs or less. I've tested the same "insert" in different computers with different S.O. and the result is always the same: MySQL 5.7 (or 5.6) takes 0,003 secs or less, meanwhile MySQL 8 takes 0.2 ~ 0.3 secs in every insert with just one record. The "creates" are slower too. What seems strange to me is that simple "selects" or "selects with joins" are faster in MySQL 8 than previous versions. I think the problem is with the queries that write on disk, but not a limitation with the disk, maybe an assigned limitation in my.ini or in another MySQL settings. Here I leave you the scripts with the slow performance in MySQL 8: CREATE TABLE ciiu_test2 ( id INT(11) NULL DEFAULT NULL, codbut VARCHAR(11) NULL DEFAULT NULL, ciiu VARCHAR(8) NULL DEFAULT NULL ) COLLATE='utf8_general_ci' ENGINE=MyISAM ; **Execution time in MySQL 5.7: 0.016 sec** **Execution time in MySQL 8.0.12: 0.140 sec** INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (1, '18237', '2750'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (2, '18238', '9491'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (3, '18245', '9411'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (4, '18248', '2221'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (5, '18264', '3520'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (6, '18265', '4645'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (7, '18268', '6202'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (8, '18276', '6512'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (9, '18290', '4210'); **Execution time in MySQL 5.7: 0.002 sec** **Execution time in MySQL 8.0.12: 0.681 sec** Please, don't recommend me optimize the "inserts" with multiples records. Specifically, I need to get the same times in MySQL 8 with every "insert" that I had once in MySQL 5.7. I thank you in advance for the help you can give me with this issue.
Asked by Juan Carlos Poveda Escobar (111 rep)
Aug 30, 2018, 08:48 PM
Last activity: Sep 6, 2024, 07:10 PM