MySQL Bugs: #52901: Key_blocks_unused counter incorrect

Bug #52901	Key_blocks_unused counter incorrect
Submitted:	16 Apr 2010 19:49	Modified:	24 Apr 2010 16:36
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.45-1 (Debian)	OS:	Linux
Assigned to:	Geir Høydalsvik	CPU Architecture:	Any
Tags:	qc

Description:
The Key_blocks_unused counter appears to be incorrect. It's exactly the same as Key_blocks_used. After a restart, it's back to normal.

key_buffer = 2024M

mysql> show status like 'Key%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 1694295   |
| Key_blocks_used        | 1694295   |
| Key_read_requests      | 345863459 |
| Key_reads              | 166618    |
| Key_write_requests     | 66240003  |
| Key_writes             | 300499    |
+------------------------+-----------+
7 rows in set (0.00 sec)

mysql> show status like 'Key%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    961
Current database: *** NONE ***

+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 1694295 |
| Key_blocks_used        | 0       |
| Key_read_requests      | 0       |
| Key_reads              | 0       |
| Key_write_requests     | 0       |
| Key_writes             | 0       |
+------------------------+---------+
7 rows in set (0.00 sec)

How to repeat:
-

This one is weird too, having Key_blocks_used == Key_blocks_not_flushed
I'm running a big select query, nothing else.

mysql> show status like 'Key%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Key_blocks_not_flushed | 782468    |
| Key_blocks_unused      | 911827    |
| Key_blocks_used        | 782468    |
| Key_read_requests      | 130083469 |
| Key_reads              | 0         |
| Key_write_requests     | 27798262  |
| Key_writes             | 0         |
+------------------------+-----------+
7 rows in set (0.00 sec)

Thank you for the bug report. Not clue how to repeat this issue? I tried on my side with current source server on FC 12 X86_64 without success. Thanks in advance.

It's a table with 82 m rows, 1.3 gb data, 0.8 gb index.

CREATE TABLE `t` (
  `s` binary(16) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

select unhex(s), count(*) c from t group by s having c > 1

Thank you for the report.

> It's a table with 82 m rows, 1.3 gb data, 0.8 gb index.

But table definition provided has no index. Is it real table you use?

Yes. The production server has:
CREATE TABLE `xwi_serials_valid` (
 `serial` binary(16) NOT NULL,
 KEY `serial` (`serial`(4))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I was actually trying to find non-unqiue rows on a test server, but that took way too long.

Thank you for the feedback.

Verified as described.

To repeat create table as described, then run loop in 1 connection:

insert into TABLE_NAME select * from TABLE_NAME 

and in another:

show processlist; show status like 'Key%'; select count(*) from TABLE_NAME;

Until Key_blocks_unused and Key_blocks_used become same. Value of key buffer size doesn't matter:

mysql> show status like 'Key%'; 
+------------------------+------------+
| Variable_name          | Value      |
+------------------------+------------+
| Key_blocks_not_flushed | 0          |
| Key_blocks_unused      | 13         |
| Key_blocks_used        | 13         |
| Key_read_requests      | 5743806337 |
| Key_reads              | 599335     |
| Key_write_requests     | 1097836579 |
| Key_writes             | 11360241   |
+------------------------+------------+
7 rows in set (0.00 sec)