MySQL Replication – Performance Comparison Between MySQL 5.7 and 5.5

MySQLmysql-5.5mysql-5.7

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.

Best Answer

I appear to have found the solution. Simply put I set MySQL 5.5 to also use ROW based Replication, using the following process:

SELECT @@GLOBAL.binlog_format;
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'row';
SET SESSION binlog_format = 'row';
FLUSH LOGS;
UNLOCK TABLES;  
START SLAVE;
SELECT @@GLOBAL.binlog_format;

After this, I ran the large query on MySQL 5.5 (ran instantly), and when it replicated to MySQL 5.7, it also ran instantly.

So there appears to be some problem with MySQL 5.7 processing statement based replication queries from a MySQL 5.5 server.

. . . But the plot thickens. Although the MySQL 5.5 database claimed to be using ROW Based Replication:

SELECT @@GLOBAL.binlog_format; = ROW

and the fact that this appears to have helped with replication to MySQL 5.7, when I grabbed the latest binary logs (an hour later or about 10 binary logs later) and ran mysqlbinlog against them, I was surprised to see that they were still populated with STATEMENTs. It was only after a full restart of the database that the binary logs started producing the new ROW based format ! ! !

Related Question