MySQL Bugs: #48979: mysql query cache limit size

Bug #48979	mysql query cache limit size
Submitted:	23 Nov 2009 8:54	Modified:	24 Jan 2010 0:27
Reporter:	Benoit MOISAN	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S5 (Performance)
Version:	5.0.45	OS:	Linux (red hat 5)
Assigned to:		CPU Architecture:	Any
Tags:	query cache limit size

Description:
I put mysql query size on servers with 16 Gb of RAM.
I allocated 8 Go to the query cache size. And it seems that the query cache looks good and when he decreased at 4 Gb, it has been flush or something like that. I used only the half of the cache size.

here is the /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

max_connections=2000
skip-locking
key_buffer_size = 128M
max_allowed_packet = 1M
table_cache = 128
sort_buffer_size = 64M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
query_cache_size= 8192M
query_cache_min_res_unit= 1024

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 3

tmp_table_size=256M
max_heap_table_size=256M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

and here you have 2  show status. The second one is 10 minutes later.

Sun Nov 22 10:30:01 CET 2009
Variable_name   Value
Binlog_cache_disk_use   0
Binlog_cache_use        0
Qcache_free_blocks      1
Qcache_free_memory      4309918944
Qcache_hits     2088789 
Qcache_inserts  4150660
Qcache_lowmem_prunes    0
Qcache_not_cached       684
Qcache_queries_in_cache 4150660
Qcache_total_blocks     8301323
Ssl_callback_cache_hits 0
Ssl_session_cache_hits  0
Ssl_session_cache_misses        0
Ssl_session_cache_mode  NONE
Ssl_session_cache_overflows     0
Ssl_session_cache_size  0
Ssl_session_cache_timeouts      0
Ssl_used_session_cache_entries  0
Threads_cached  6

the second :

Sun Nov 22 10:40:01 CET 2009
Variable_name   Value
Binlog_cache_disk_use   0
Binlog_cache_use        0
Qcache_free_blocks      1
Qcache_free_memory      8586765072
Qcache_hits     807
Qcache_inserts  3044
Qcache_lowmem_prunes    0
Qcache_not_cached       2
Qcache_queries_in_cache 3044
Qcache_total_blocks     6091
Ssl_callback_cache_hits 0
Ssl_session_cache_hits  0
Ssl_session_cache_misses        0
Ssl_session_cache_mode  NONE
Ssl_session_cache_overflows     0
Ssl_session_cache_size  0
Ssl_session_cache_timeouts      0
Ssl_used_session_cache_entries  0
Threads_cached  2
 

How to repeat:
set query cache size à 8 Gb and see when you reach the middle of the size.

Thank you for the problem report. Please, try to repeat with a newer version, 5.0.88, and inform about the results. Note also that query cache bigger than 512M or so is rarely useful and often gives more problems than benefits.

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".

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".