MySQL replication – invalidating query cache entries (table)

MySQLmysql-5.5replication

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.

Best Answer

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

Related Question