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

[16 Apr 2010 19:49] Olaf van der Spek
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:
-
[16 Apr 2010 19:55] Olaf van der Spek
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)
[16 Apr 2010 20:46] Miguel Solorzano
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.
[16 Apr 2010 21:34] Olaf van der Spek
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
[20 Apr 2010 14:29] Sveta Smirnova
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?
[20 Apr 2010 14:46] Olaf van der Spek
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.
[24 Apr 2010 16:36] Sveta Smirnova
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)