| Bug #61130 | Key_blocks_not_flushed values is not correct! | ||
|---|---|---|---|
| Submitted: | 11 May 2011 4:32 | Modified: | 17 Jan 2012 19:58 |
| Reporter: | kelvin chou | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Options | Severity: | S3 (Non-critical) |
| Version: | 5.0.77, 5.0.95, 5.1.62, 5.5.21, 5.6.5 | OS: | Linux (CenOS 5.5) |
| Assigned to: | CPU Architecture: | Any | |
[11 May 2011 5:38]
kelvin chou
After first time,I proform the insert statment the values of Key_blocks_not_flushed,Key_blocks_unused,Key_blocks_used don't change at all.
[11 May 2011 15:57]
MySQL Verification Team
kelvin, try adding some indexes to your table, then retest. preferably with a few hundred records.
[12 May 2011 2:06]
kelvin chou
Thanks for your response. follow you suggestion, Then I insert 100 records(pls see ex1) and the I perform flush status and tables the value of Key_blocks_not_flushed becomes 4294967290 ?? schema: | test2 | CREATE TABLE `test2` ( `id` int(11) default NULL, `name` varchar(50) default NULL, KEY `id_idx` (`id`), KEY `name_idx` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | ex1 mysql> show status like 'key%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 6 | | Key_blocks_unused | 14491 | | Key_blocks_used | 6 | | Key_read_requests | 255 | | Key_reads | 6 | | Key_write_requests | 212 | | Key_writes | 0 | +------------------------+-------+ 7 rows in set (0.00 sec) ex2: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'key%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 14491 | | Key_blocks_used | 6 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | +------------------------+-------+ 7 rows in set (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'key%'; +------------------------+------------+ | Variable_name | Value | +------------------------+------------+ | Key_blocks_not_flushed | 4294967290 | <== the value is not correct!! | Key_blocks_unused | 14497 | | Key_blocks_used | 6 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 6 | +------------------------+------------+ 7 rows in set (0.01 sec)
[12 May 2011 5:30]
MySQL Verification Team
Strange... I got a correct result on 5.0.77 and 5.5.12 using this testcase: ----------- flush tables; set global delay_key_write=all; drop table if exists `test2`; create table `test2` (`id` int(11) default null, `name` varchar(50) default null,key `id_idx` (`id`), key `name_idx` (`name`)) engine=myisam default charset=latin1 delay_key_write=1; flush status; show status like '%key_block%'; insert into test2 values (50,100),(100,200),(200,400); insert into test2 select rand()*100000,rand()*100000 from test2 a,test2 b,test2 c,test2 d,test2 e,test2 f,test2 g,test2 h; show status like '%key_block%'; ------ What is your result from the above?
[13 May 2011 3:08]
kelvin chou
This is my test resoult:
I use two Terminal one is for insert used,the other one is show status.
insert into test2 values (50,100),(100,200),(200,400);
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 2 |
| Key_blocks_unused | 14494 |
| Key_blocks_used | 19 |
| Key_read_requests | 47 |
| Key_reads | 22 |
| Key_write_requests | 12 |
| Key_writes | 6 |
+------------------------+-------+
7 rows in set (0.00 sec)
and then
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'key%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0 | <== The value become zero ?? I just flush
the status not flush tables
| Key_blocks_unused | 14494 |
| Key_blocks_used | 19 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 | <== no data insert into Disk
+------------------------+-------+
7 rows in set (0.00 sec)
then I perform flush tables
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'key%';
+------------------------+------------+
| Variable_name | Value |
+------------------------+------------+
| Key_blocks_not_flushed | 4294967294 | <== It happened again!!
| Key_blocks_unused | 14497 |
| Key_blocks_used | 19 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 2 |
+------------------------+------------+
7 rows in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.0.77-log |
+------------+
[13 May 2011 8:53]
MySQL Verification Team
the key_blocks_not_flush is going negative... same thing was reported in bug #17328
[16 May 2011 2:29]
kelvin chou
Thanks for your reply. Does this bug fix in version 5.0.91-community-log? Since my production(5.0.91-community-log) Key_blocks_unused=0 and Key_blocks_not_flushed=0,the values have no changed anymore. Does this mean the funcation "delay_key_write" not work anymore? or the status of Key_blocks_not_flushed=0 and Key_blocks_unused=0 is a bug ? Because the document indacate the key_buffer using the LRU strategy,so that the Key_blocks can be reused. If the key_block is considered "dirty" ,the value of Key_blocks_not_flushed will changed ??
[17 Jan 2012 19:58]
Sveta Smirnova
Thank you for the report.
Verified as described.
Test case for MTR:
set global delay_key_write=all;
create table `test2` (`id` int(11) default null,
`name` varchar(50) default null,key `id_idx` (`id`),
key `name_idx` (`name`)) engine=myisam default charset=latin1 delay_key_write=1;
insert into test2 values ('2343','test');
insert into test2 values ('23423','test');
insert into test2 values ('2330','test3');
insert into test2 values ('2334','test8');
connect (addconroot, localhost, root,,);
connection addconroot;
show status like 'key%';
connection default;
insert into test2 values (50,100),(100,200),(200,400);
insert into test2 select rand()*100000,rand()*100000 from test2 a,test2 b,test2 c,test2
d,test2 e,test2 f,test2 g,test2 h;
connection addconroot;
show status like 'key%';
flush status;
show status like 'key%';
flush tables;
show status like 'key%';
[18 Jan 2012 16:56]
Sveta Smirnova
Bug #17328 was marked as duplicate of this one.
Description: I want to test what's defferent if I use parameter "delay_key_write = ALL". When I insert first time, and show status like 'key%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 4 | | Key_blocks_unused | 14492 | | Key_blocks_used | 8 | | Key_read_requests | 8 | | Key_reads | 4 | | Key_write_requests | 8 | | Key_writes | 0 | and 2nd and 3rd and 4rd times insert,the resoult of show status like 'key%' is blow +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 5 | <== no changed | Key_blocks_unused | 14491 | <== no changed | Key_blocks_used | 8 | <== no changed | Key_read_requests | 14 | | Key_reads | 5 | | Key_write_requests | 15 | | Key_writes | 0 | Is this nomal?? I think the values should be changed! how could I know is it work or not ? How to repeat: CREATE TABLE `test2` ( `id` int(11) default NULL, `name` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into test2 values ('2343','test'); insert into test2 values ('23423','test'); insert into test2 values ('2330','test3'); insert into test2 values ('2334','test8'); Suggested fix: Should the values be changed when I proform the statment