Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

Question

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.

Answer 1

A difference between 5.7 and 8.0 is that binary logging (to be used for replication and PITR) is on by default in MySQL 8.0. To run without binary logging in 8.0, start the MySQL server with --disable-log-bin.

More details The Binary Log

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

• For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 17.2, “Replication Implementation”.
• Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

Answer 2

From MySQL 8 , the transaction write-set extraction algorithm is introduced which is XXHASH64. The new default will make it easy for users to enable binary log write-set parallelization on master to speed up group replication.[1] If you are not using MySQL Group Replication, then you can change the settings from XXHASH64 to OFF. And you can enable binary logs if you are running any slave in parallel.

[mysqld]
#skip-log-bin 
transaction_write_set_extraction=OFF

  [1]: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

Answer 3

"A difference between 5.7 and 8.0 is that binary logging (to be used for replication) is on by default in MySQL 8.0. To run without binary logging in 8.0, start the MySQL server with --disable-log-bin."

In order to do that, I edited my.ini file this way:

[mysqld] skip-log-bin 

then I restarted the service and speed is the same or better than MySQL 5.7.

Source: https://forums.mysql.com/read.php?24,668698,668704#msg-668704