MySQL replication - invalidating query cache entries (table)

Question

We lately moved to MySQL V5.5 on CentOS release 6.7 with master and 2 slaves. Since then the replication process lags a lot behind the master with the line in the process list of:

invalidating query cache entries (table).

The global query_cache_type & size were set to 0.

I have checked around and I can see it's a known old bug in MySQL but could not find a workaround for this issue.

Any advice?

• query_cache_type OFF in my.cnf before server restart.
• query_cache_wlock_invalidate set to OFF.
• using default % storage_engine as InnoDB.

We ran some more tests and we had an issue with disk IO - sar -u showed about 3-4 %iowait, disks infra changes help reduce this to about 1.5-2% but it still doesn't help the replication not lagging for 5000(!) seconds behind the master.

Answer 1

• Was query_cache_type OFF (0) in my.cnf, not merely turned off later? (This seems to be critical for having it really off.)

• Another possible common thread in the bugs -- MyISAM. If you are using MyISAM, you really should convert to InnoDB. There are many reasons for converting. Here are some tips on doing the conversion: http://mysql.rjweb.org/doc.php/myisam2innodb

• Make sure that query_cache_wlock_invalidate is OFF.

• Rick James: Well, I am stumped. You could consider writing a bug, or commenting on one of the existing bugs. But it might be considered just a dup.

Answer 2

_{Wiki answer generated from comments by AlonP and Rick James:}

After some more tuning what helped was to update innodb_flush_log_at_trx_commit = 0. When value was set to 1 with sync_binlog=1 this can cause the slave delay.

=2 is arguably slightly better than =0. =1 leads to a lot of I/O for security. However, the slave is not the only source of the data, so a crash that corrupts stuff is not the end of the world.

2 does an fsync once a second. 0 hopes that the OS eventually flushes things. fsync flushes pending writes to disk.