MySQL Bugs: #25132: disabled query cache: Qcache_free

Bug #25132	disabled query cache: Qcache_free_blocks = 1
Submitted:	18 Dec 2006 10:04	Modified:	26 Mar 2008 17:36
Reporter:	Yahoo Serious (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.54, 5.0.34-\|BK, 4.1.21-community-nt, 5.0.27-community-nt	OS:	Linux (Linux, Windows 2000)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	qc

Description:
After enabling and disabling the query cache it reports 1 free memory block (Qcache_free_blocks = 1).  This suggests there is still one free memory block in the query cache, although the memory usage is zero (Qcache_free_memory = 0)?
So either MySQL is leaking or the variable is not set right.
(Notice: on start-up without query-cache the variable has value zero.)

How to repeat:
- start MySqlServer
- (enable query-cache if it is not)
- disable query-cache:
  .  SET GLOBAL query_cache_type = 0;
  .  SET GLOBAL query_cache_size = 0;
- check status
  .  SHOW STATUS LIKE 'Qcache%';
- notice: Qcache_free_blocks = 1, Qcache_free_memory = 0

Suggested fix:
Fix the error, probably the variable Qcache_free_blocks, or the query-cache-memory

Thank you for a problem report. Verified just as described with 5.0.34-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.01 sec)

mysql> set global query_cache_size=1000000;
Query OK, 0 rows affected (0.01 sec)

mysql> set global query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 990600 |
| Qcache_hits             | 0      |
| Qcache_inserts          | 0      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 1      |
| Qcache_queries_in_cache | 0      |
| Qcache_total_blocks     | 1      |
+-------------------------+--------+
8 rows in set (0.01 sec)

mysql> set global query_cache_type=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global query_cache_size=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 1     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 1     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 1     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.01 sec)

mysql> show global status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 1     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43513

ChangeSet@1.2594, 2008-03-06 12:40:46+01:00, thek@adventure.(none) +3 -0
  Bug#25132 disabled query cache: Qcache_free_blocks = 1
  
  The initial value of free memory blocks in 0. When the query cache is enabled 
  a new memory block gets allocated and is assigned number 1. The free memory
  block is later split each time query cache memory is allocated for new blocks.
  This means that the free memory block counter won't be reduced to zero when
  the number of allocated blocks are zero, but rather one. To avoid confusion
  this patch changes this behavior so that the free memory block counter is
  reset to zero when the query cache is disabled.
  Note that when the query cache is enabled and resized the free memory block
  counter was still calculated correctly.

Pushed into 5.1.24-rc

Noted in 5.1.24 changelog.

The Qcache_free_blocks status variable did not display a value of 0
if the query cache was disabled.

Pushed into 6.0.5-alpha

Fix also noted in the 5.1.23-ndb-6.3.11 and 6.0.5 changelogs.