Invalidating query cache mysql speed dating skinny
Unfortunately, mysql has no internal mechanism for defragmenting the query cache.If you must have the query cache and you have so much RAM, you could execute the following: That leaves 7G. Keep in mind that the key buffer caches only My ISAM index pages, while the Inno DB Buffer Pool caches data and indexes.This ensures that you get the most up-to-date results; using the cache would return old table contents.Description: Since the Query cache hurts some workloads, we turn if off by setting query_cache_size=0.SQL_NO_CACHE is not noticed until clustering strategy: If you do read-write splitting with a proxy like Max Scale, or your application is capable, you can send some of the reads for those seldom invalidated tables only to slaves that have the query cache turned on, and the rest to other slaves with it turned off.
If all or the majority of your data is My ISAM, you could go with your original idea of using SQL_NO_CACHE.
Edit: At the request of the user @Rolando My SQLDBA below, here's the info on My ISAM and INNODB. It would be nice if Inno DB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Apparently, you have a lot of queries that get invalidated rather quickly and are probably not being reused.
For Inno DB under My SQL 4.0, the query cache was disabled for transactions.
I'm currently working on a My SQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that are being executed on many of the tables.
What I'm trying to determine is whether or not there is any benefit at all to allowing the query cache to be used for SELECT statements that are being run against these tables.
Search for invalidating query cache mysql:
id=38551 In short, in all My SQL versions before 5.5 , the query cache mutex is acquired even if query_cache_size=0 and query_cache_type=OFF : always!