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 count(*)
).
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 read_io_threads
and 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?
UPDATE 2018-10-17
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.