MySQL replication - invalidating query cache entries (table)

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.

StackExchange Database Administrators Q#127324, answer score: 2