I have two databases setup for testing (so no live data or connections).
The first is a MySQL
5.5.57 Database acting as the master. The second is a MySQL
5.7.23 Database acting as a slave.
It is running
Statement Based Replication on MySQL 5.5 and
Row Based Replication on MySQL 5.7.
For some reason larger queries seem to take significantly longer on MySQL 5.7. For example I have the following table:
CREATE TABLE `test1` ( `col0` int(11) DEFAULT NULL, `col1` int(11) DEFAULT NULL, `col2` datetime DEFAULT NULL, `col3 datetime DEFAULT NULL, `col4` int(11) DEFAULT NULL, `col5` int(11) DEFAULT NULL, `col6` int(11) DEFAULT NULL, `col7` int(4) DEFAULT NULL, `col8` int(11) DEFAULT NULL, `col9` datetime DEFAULT NULL, `col10` datetime DEFAULT NULL, `UniqRef` int(10) unsigned NOT NULL AUTO_INCREMENT, `col11` tinyint(1) unsigned NOT NULL DEFAULT '0', `col12` tinyint(1) unsigned zerofill DEFAULT NULL, `col13` tinyint(1) unsigned DEFAULT NULL, PRIMARY KEY (`UniqRef`), KEY `col0` (`col0`), KEY `col3` (`col3`), KEY `col4` (`col4`), KEY `col8` (`col8`), KEY `col5` (`col5`), KEY `col10` (`Deleted`), KEY `col2` (`col2`), KEY `col12` (`col12`) USING BTREE, KEY `col13` (`col13`) ) ENGINE=InnoDB AUTO_INCREMENT=400046649 DEFAULT CHARSET=latin1;
The table is 21GB in size, and holds 83261829 rows (using
If I run the query:
UPDATE test.test1 SET col9 = NOW() WHERE UniqRef IN ('397958600','397940686','397940704','397940678','397. . . . .
The query contains 6945 uniqref entries. It runs almost instantly on MySQL 5.5, but then it replicates to MySQL 5.7 and takes 90 seconds to run.
I have changed various settings, but they have so far made no difference:
SET @@global.slave_compressed_protocol = 0; SET @@global.sync_binlog = 0; set @@global.range_optimizer_max_mem_size = 0;
I have also tried changing
write_io_threads to various values, as well as changing the number of
buffer pool instances, and disabling the binary logs.
Are there any other variables that may make a difference to try and speed this up that I have missed?
I managed to speed the queries up when run locally (directly on MySQL 5.7) by setting
SET @@global.range_optimizer_max_mem_size = 16777216;. After the this the queries went from 90+ seconds to 0 seconds.
However, when the same query was run on MySQL 5.5 and replicated across to MySQL 5.7, it still takes 90+ seconds to run.