Invalidating query cache entries key mysql xxdatings com
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 makes it difficult to make a good decision without showing the concrete performance consequences to each decision.Currently nobody wants: 1) a blocking mutex 2) a client barrier on a write.For clarification, when I said "no internal mechanism," I meant exactly that. That is, when some write occurs, the whole 1GB will be scanned to find any references to the table that was modified. I recommend a size of no more than 50M, unless your data rarely changes. It takes out a global Mutex, and takes it out too soon.Defragmentation is needed and has to be done manually. If you do DML (INSERTs, UPDATEs, DELETEs) on Inno DB more often than on My ISAM, I would say remove the query cache altogether, which I said in the beginning. This mutex is one reason that My SQL cannot make effective use of more than about 8 cores.The current code actually locks/unlocks the query_cache lock twice when the query_cache_size is zero! This can all be bypassed if the check for query_cache_size is done without holding the QC lock as in Query_cache::store_query The CPU time wasted is very less, however, in benchmark situations, every cpu cycle counts.How to repeat: Run a sysbench read-write benchmark with lots of threads.
See my earlier posts on using My SQL 5.5 in conjunction with accessing multiple CPUs for Inno DB would be better.
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.
We do this and handle 4M calls per minute to the cluster during our load tests (not benchmark..real deal) as a result.
If you have a mix of Inno DB and My ISAM, you will have to find the right balance for your application based on on how high your cache misses are.
In fact, pages 209-210 of the same book point out reasons for cache misses : set to 104857600 (100M), this only allows for 100 cached results set in a perfect world.