MySQL Bugs: #46872: very high Key_blocks_unused after upgrade to 5.0.84

Bug #46872	very high Key_blocks_unused after upgrade to 5.0.84
Submitted:	23 Aug 2009 22:35	Modified:	24 Aug 2009 12:05
Reporter:	Tom Keyser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.0.84-log	OS:	Other (CentOS 5.3)
Assigned to:		CPU Architecture:	Any
Tags:	CentOS, innodb, Key_blocks_unused, Key_blocks_used

Description:
I have 2 servers running mysql both have 6000-7000 tables about 12GB-13GB of data. All table are Innodb.

Both servers are showing very high Key_blocks_unused after upgrade to 5.0.84-log from 5.0.82-log'

MYSQL1 after 393,719 queries
key_buffer_size = 32M
mysql> show status like '%key%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| Com_preload_keys       | 0      |
| Com_show_keys          | 0      |
| Handler_read_key       | 0      |
| Key_blocks_not_flushed | 0      |
| Key_blocks_unused      | 26777  |
| Key_blocks_used        | 29     |
| Key_read_requests      | 161990 |
| Key_reads              | 5486   |
| Key_write_requests     | 112444 |
| Key_writes             | 0      |
+------------------------+--------+
10 rows in set (0.00 sec)

MYSQL2 after 581,957 queries
key_buffer_size = 32M
mysql> show status like '%key%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Com_preload_keys       | 0       |
| Com_show_keys          | 0       |
| Handler_read_key       | 0       |
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 26777   |
| Key_blocks_used        | 150     |
| Key_read_requests      | 9292471 |
| Key_reads              | 34553   |
| Key_write_requests     | 2834953 |
| Key_writes             | 0       |
+------------------------+---------+
10 rows in set (0.00 sec)

How to repeat:
I have restated mysql, and the issue still exists.

Why do you think that this is an indication of any problem? Do you use any MyISAM tables in your databases? Are you sure that you had same/similar queries to the same tables in both cases? 

Please, send your entire my.cnf files from both servers also.

I was using 5.0.45 then 5.0.82 and now 5.0.84

All tables are Innodb except the default mysql tables and the schema views.

Under 5.0.45 I saw a lot of activity in the key cache, and if I am not mistake I saw the same in 5.0.82.

Are you suggesting that this is normal with all tables being Innodb and the default value for key cache is enough when all tables are Innodb?

my.cnf for MYSQL1

Attachment: my.cnf (application/octet-stream, text), 21.67 KiB.

my.cnf for MYSQL2

Attachment: my.cnf (application/octet-stream, text), 21.68 KiB.

Blocks in key cache are used when they are needed. With all the really big tables being InnoDB it is used for (seldom) queries to the tables in the mysql database and for temporary tables created as MyISAM and having indexes (if any). 

I do not see any problem with key_buffer_size (check key cache hit ratios, see http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Key_blocks_use... etc) or any bug in your case.