Re: Queries slow down daily, optimizing helps -> why?
Posted by:
Rick James
Date: February 19, 2009 10:53PM
key_buffer is for MyISAM indexes only.
Query_cache is for statements. And you have it "ON" with 500MB of space (a lot). There are several caveats:
* When a table is modified (INSERT/...) all entries in the QC for that table are purged.
* When the _identical_ SELECT comes in (not even so much as a space different), the engine will first look in the QC to see if it is there. If so, it will return the result set from the QC.
* Result sets that are too big won't go into the QC.
So, another explanation for the slowdown...
1. Do the SELECT -- it gets into QC.
2. The table is modified -- QC flushed (for that table)
3. Do the SELECT again -- not found in QC, so slow.
4. Run SELECT again -- now blazingly fast because it is QC.
SHOW STATUS LIKE "Query%";
will give some numbers that (with some dividing) can give you the hit/miss rate, etc.
Subject
Views
Written By
Posted
4249
February 18, 2009 04:54AM
2222
February 18, 2009 09:04AM
2323
February 18, 2009 11:57AM
2263
February 19, 2009 01:00AM
2248
February 19, 2009 01:52AM
2274
February 19, 2009 03:32AM
1712
February 19, 2009 05:08AM
2152
February 19, 2009 12:24PM
Re: Queries slow down daily, optimizing helps -> why?
2232
February 19, 2009 10:53PM
2338
February 20, 2009 08:34AM
2086
March 18, 2009 03:58AM
2259
February 21, 2009 06:15AM
2181
February 24, 2009 10:02AM
2403
February 26, 2009 02:30AM
2042
February 26, 2009 09:56AM
2220
February 26, 2009 08:14PM
2192
February 27, 2009 01:38AM
2132
February 27, 2009 07:56PM
2111
February 28, 2009 06:20AM
2354
February 28, 2009 03:43PM
2248
February 27, 2009 04:21AM