MySQL Bugs: #2715: key_blocks_used are not counted properly with --enable-locking

Bug #2715	key_blocks_used are not counted properly with --enable-locking
Submitted:	11 Feb 2004 13:49	Modified:	29 Mar 2004 8:05
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
It seems like they are just forgotten to be decremented, being flushed then table is modified by other peer, so next index read increases them further.

In the example below servers are configured to share the same datadir, running in --enable-locking mode 

How to repeat:
CREATE TABLE `lockingtest` (
  `i` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`i`)
) TYPE=MyISAM

insert into lockingtest values (1);
insert into lockingtest values (2);

MySQLServer1:

mysql> show status like "key_blocks_used";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Key_blocks_used | 13    |
+-----------------+-------+
1 row in set (0.00 sec)

MySQLServer2:

mysql> insert into lockingtest values (5);
Query OK, 1 row affected (0.01 sec)

MySQLServer1:

mysql> select * from lockingtest where i=1;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> show status like "key_blocks_used";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Key_blocks_used | 14    |
+-----------------+-------+
1 row in set (0.00 sec)

MySQLServer2:

mysql> insert into lockingtest values (5);
ERROR 1062: Duplicate entry '5' for key 1

MySQLServer1:

mysql> select * from lockingtest where i=1;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

mysql> show status like "key_blocks_used";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Key_blocks_used | 15    |
+-----------------+-------+
1 row in set (0.00 sec)

And so on.

Hm, Actually it is not really due to --enable-locking. It looks like then table is flushed and even dropped this variable is not decremented for some reason. 

mysql> CREATE TABLE `lockingtest` (
    ->   `i` int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`i`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.56 sec)

mysql> show status like "key_blocks_used";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Key_blocks_used | 15    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> insert into lockingtest values(1);
Query OK, 1 row affected (0.00 sec)

mysql> show status like "key_blocks_used";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Key_blocks_used | 16    |
+-----------------+-------+
1 row in set (0.00 sec)

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

mysql> show status like "key_blocks_used";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Key_blocks_used | 16    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> drop table lockingtest;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like "key_blocks_used";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Key_blocks_used | 16    |
+-----------------+-------+
1 row in set (0.00 sec)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Whenever the key cache needs a block, it first tries to use a block, which has never been used before. After all blocks of the key cache have been used once, the last recently used block will be re-used. The size of the key cache derives from the system variable key_buffer_size. Since each key cache block needs some administrational headers, you cannot simply divide key_buffer_size by key_cache_block_size. For 1024 byte blocks, I found about 1153 total bytes per key block on my system.

That means for an 8 MB key cache I could run your commands in a loop until the key_blocks_used reached 7274. After that they didn't grow further.
key_blocks_used tells how many key cache blocks have ever been used before.
It does not tell how many blocks are actually filled with current data.

So this behaviour is as it was designed. It is not a bug.
The only question is, how valuable information does key_blocks_used provide?
Not that much I would say...