MySQL Bugs: #28384: SHOW SESSION STATUS increments Key_reads, but not Key_read

Bug #28384	SHOW SESSION STATUS increments Key_reads, but not Key_read_requests
Submitted:	11 May 2007 20:26	Modified:	2 Aug 2009 9:24
Reporter:	Jan Kneschke	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.28, 5.0.67	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
For monitoring they MyISAM Key Cache you use:

  Key_reads / Key_read_requests

to get the percentage of cache-requests which had to go to the disk to read a key-block. This expects that ALL key-reads are part of key-read-requests.

How to repeat:
> SHOW SESSION STATUS;

| Key_read_requests                 | 4674       |
| Key_reads                         | 32859      |
| Key_write_requests                | 36927      |
| Key_writes                        | 18         |

> SHOW SESSION STATUS;
| Key_read_requests                 | 4674       | diff = 0
| Key_reads                         | 32873      | diff = 14
| Key_write_requests                | 36941      | diff = 14
| Key_writes                        | 18         | diff = 0

> SHOW SESSION STATUS;
| Key_read_requests                 | 4714       | diff = 0
| Key_reads                         | 32989      | diff = 16
| Key_write_requests                | 37081      | diff = 140
| Key_writes                        | 18         | diff = 0

Nothing was executed in this session next to the SHOW SESSION STATUS;

Suggested fix:
Increment Key_read_requests whenever Key_reads is incremented.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.40/5.0.41. In case of the same result, please, send a complete test case, with all the steps that lead to the results presented.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

hi, my version is 5.0.67, i met this problem too
[root@ld_213_tj_60 ~]# mysqladmin ext|grep Key_
| Key_blocks_unused                 | 53579        |
| Key_blocks_used                   | 10           |
| Key_read_requests                 | 21857        |
| Key_reads                         | 804184       |

 [root@ld_213_tj_60 ~]# mysqladmin ext|grep Key_
| Key_blocks_unused                 | 53579        |
| Key_blocks_used                   | 10           |
| Key_read_requests                 | 21857        |
| Key_reads                         | 804218       |

 [root@ld_213_tj_60 ~]# mysqladmin ext|grep Key_
| Key_blocks_unused                 | 53579        |
| Key_blocks_used                   | 10           |
| Key_read_requests                 | 21857        |
| Key_reads                         | 804274       |

Key_read_requests was always 21857, but Key_reads changed from 804184 to 804274, and Key_reads was larger then Key_read_requests so many

my server enviroment was:

[root@ld_213_tj_60 ~]# uname -a
Linux ld_213_tj_60.28.211.11_cnc 2.6.9-78.0.17.ELsmp #1 SMP Thu Mar 5 04:58:34 EST 2009 x86_64 x86_64 x86_64 GNU/Linux

some status values
| Created_tmp_disk_tables      | 818890       |
| Created_tmp_files                    | 4            |
| Created_tmp_tables                | 1236024      |

and some variable values
Show global variables like ‘%tmp_table_size%’;
Show global variables like ‘%heap%’;
Show global variables like ‘%key_buffer_size%’;
| tmp_table_size                 | 100663296            |
| max_heap_table_size       | 100663296       |                              
| key_buffer_size                 | 67108864            |

JinRong,

thank you for the feedback, but you use mysqladmin command which creates new session each time and returns global values. So it is expected you have different results. Original report is about session values which should not be changed if you don't issue any query except SHOW STATUS

> Key_read_requests was always 21857, but Key_reads changed from 804184 to 804274, and
Key_reads was larger then Key_read_requests so many

Key_read_requests is the number of requests to read a key block from the cache. While key_reads is the number of physical reads of a key block from disk. So these 2 variables can increase independently.

(root:my_hostname:Thu Jul  2 15:52:01 2009)[ldjdb]> show status like 'key_read%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Key_read_requests | 24567   | 
| Key_reads         | 1092522 | 
+-------------------+---------+
2 rows in set (0.03 sec)

(root:my_hostname:Thu Jul  2 15:52:04 2009)[ldjdb]> show status like 'key_read%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Key_read_requests | 24567   | 
| Key_reads         | 1092570 | 
+-------------------+---------+
2 rows in set (0.03 sec)

(root:my_hostname:Thu Jul  2 15:52:27 2009)[ldjdb]> show status like 'key_read%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Key_read_requests | 24567   | 
| Key_reads         | 1092580 | 
+-------------------+---------+
2 rows in set (0.03 sec)

JinRong,

thank you for the feedback.

Just checked - Key_reads and Key_read_requests are GLOBAL variables, so my comment regarding to SESSION is not correct.

But Key_reads increases not only in key_cache_read, but in key_cache_write also. In this case variable Key_write_requests is increasing while Key_read_requests is not. This can be seen in Jan's output and this is not a bug to my understanding. Please check in your environment too.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".