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.

  • 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.

  • 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. – Rick James Jan 27 '16 at 17:56

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.