MySQL Bugs: #27323: Query cache

Bug #27323	Query cache
Submitted:	21 Mar 2007 10:54	Modified:	23 Apr 2007 13:07
Reporter:	Tordjman Yohan	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.32 / 5.0.37	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any
Tags:	cache, query

Description:
The mysql server serves at 99% for selects on one table of 10 000 000 rows.

CREATE TABLE `passwd` (
  `numero` int(10) unsigned NOT NULL auto_increment,
  `login` varchar(64) NOT NULL default '',
  `password` varchar(10) character set latin1 collate latin1_bin NOT NULL default '',
  PRIMARY KEY  (`login`),
  UNIQUE KEY `numero` (`numero`)
) ENGINE=MyISAM AUTO_INCREMENT=49002156 DEFAULT CHARSET=latin1 PACK_KEYS=1

cache parameters are:

query_cache_limit       = 65536 # 64K 
query_cache_size        = 256M

reporting:

mysqladmin exten | grep Qcache
| Qcache_free_blocks         | 1         |
| Qcache_free_memory         | 119439032 |
| Qcache_hits                | 61006     |
| Qcache_inserts             | 145495    |
| Qcache_lowmem_prunes       | 0         |
| Qcache_not_cached          | 22        |
| Qcache_queries_in_cache    | 145495    |
| Qcache_total_blocks        | 290992    |

~10 mn

mysqladmin exten | grep Qcache
| Qcache_free_blocks         | 1        |
| Qcache_free_memory         | 1596088  |
| Qcache_hits                | 245522   |
| Qcache_inserts             | 260576   |
| Qcache_lowmem_prunes       | 0        |
| Qcache_not_cached          | 24       |
| Qcache_queries_in_cache    | 260576   |
| Qcache_total_blocks        | 521154   |

( now ~ 20 seconds for every checks )

mysqladmin exten | grep Qcache
| Qcache_free_blocks         | 1        |
| Qcache_free_memory         | 259768   |
| Qcache_hits                | 248399   |
| Qcache_inserts             | 261881   |
| Qcache_lowmem_prunes       | 0        |
| Qcache_not_cached          | 25       |
| Qcache_queries_in_cache    | 261881   |
| Qcache_total_blocks        | 523764   |
mysqladmin exten | grep Qcache
| Qcache_free_blocks         | 24841    |
| Qcache_free_memory         | 44314296 |
| Qcache_hits                | 262525   |
| Qcache_inserts             | 268716   |
| Qcache_lowmem_prunes       | 49857    |
| Qcache_not_cached          | 26       |
| Qcache_queries_in_cache    | 218859   |
| Qcache_total_blocks        | 462560   |
mysqladmin exten | grep Qcache
| Qcache_free_blocks         | 25472    |
| Qcache_free_memory         | 47479480 |
| Qcache_hits                | 266774   |
| Qcache_inserts             | 271569   |
| Qcache_lowmem_prunes       | 55801    |
| Qcache_not_cached          | 27       |
| Qcache_queries_in_cache    | 215768   |
| Qcache_total_blocks        | 457009   |

and now (~45mn after)

mysqladmin exten | grep Qcache
| Qcache_free_blocks         | 39714     |
| Qcache_free_memory         | 130604728 |
| Qcache_hits                | 726022    |
| Qcache_inserts             | 710216    |
| Qcache_lowmem_prunes       | 575625    |
| Qcache_not_cached          | 43        |
| Qcache_queries_in_cache    | 134591    |
| Qcache_total_blocks        | 308897    |

So, i am going to think that at one time, the server badly decides that there are not enought free cache and does some cleaning.
After, i go to think that it never go to cache anything...it seems that it do not reset something after cleaning... ?

The mysql is 5.0.37 that i have compiled myself.

How to repeat:
don't know !
it's the 1st time that i see that after an update

Thank you for the report.

From which version did you update? Please also provide queries you think should be cached.

i have upgraded from an old debian sid version to the last debian sid version (5.0.32).

the queries are very simple: select login from passwd where password = 'xxxx';

=> the server take 90% cpu in ~1h of work. It do ~600 resquet/s

i compiled/installed mysql 5.0.37 myself and same thing.

i tuned some parameters(1) and now it seems good for the cpu... 

but i really don't understand that:
| Questions                  | 89934873   |
| Qcache_free_blocks         | 41567      |
*| Qcache_free_memory         | 137077432  |*
| Qcache_hits                | 45458858   |
| Qcache_inserts             | 44315836   |
*| Qcache_lowmem_prunes       | 40438036   |*
| Qcache_not_cached          | 2870       |
| Qcache_queries_in_cache    | 128270     |
| Qcache_total_blocks        | 298108     |

how can be Qcache lowmem_prunes and 130Mo of Qcache free memory ?

(1)
key_buffer              = 384M
thread_stack            = 256K
query_cache_limit       = 65536
query_cache_size        = 256M
query_cache_type        = 1

Please read why Qcache_lowmem_prunes increases starting at http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

Also note we need repeatable test case from you showing why you consider it is our bug.

Ok so i'm trying with smaller query_cache_min_res_unit and do a report if i see some problems...

thanks

Thank you.

Will wait feedback from you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".