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
Triage: D2 (Serious)

[18 Dec 2006 10:04] Yahoo Serious
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
[19 Dec 2006 22:48] Valeriy Kravchuk
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)
[6 Mar 2008 11:34] Bugs System
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.
[26 Mar 2008 14:13] Bugs System
Pushed into 5.1.24-rc
[26 Mar 2008 17:36] Paul Dubois
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.
[27 Mar 2008 17:51] Bugs System
Pushed into 6.0.5-alpha
[30 Mar 2008 0:17] Jon Stephens
Fix also noted in the 5.1.23-ndb-6.3.11 and 6.0.5 changelogs.