I spent some time going through the source the other day in order to try to understand the difference between these SHOW STATUS variables in 5.x:
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
Innodb_pages_read
Here's what I discovered that I'm not sure is 100% right, but I suspect is at least on the right track:
Innodb_buffer_pool_read_requests: Â requests to get something from the buffer pool. Â This isn't really that great of a mystery. Â I suspect these are 'direct' read requests, meaning those caused directly from a query of some kind. Â
Innodb_buffer_pool_reads: Â Direct read requests for a given page that had to go to disk to fetch it.
Innodb_page_reads:Â Â Including Innodb_buffer_pool_page reads, above, plus a few other indirect things like read aheads, merge pages, and recovery page reads. Â
So, essentially the Innodb_buffer_pool_reads is helpful to discover your buffer pool hit ratio based on direct read requests. Â Innodb_page_reads includes more stuff that Innodb does behind the scenes (like read-aheads, which should reduce the direct page reads from disk, I believe). Â
Please, please, please take this as from someone who spent just a bit of time trolling around the source, and not from someone more authoritative and knows what they're talking about. Â Unless, of course, someone who is authoritative kindly posts a comment saying I'm right. :)
1 Comment
I am not sure whether cache_hit percents can be get by (1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests);
The main problem is whether the Innodb_buffer_pool_read_requests unit is same as Innodb_buffer_pool_reads.
We belive the Innodb_buffer_pool_reads unit is page or block(16k),but don't kown the read_requests' unit.
when I selected a very simple table like below(mysql server had only a connection when testing),
I found the result is not what we want. After submit a select, the
read_requests increased by 36. If I immediatly show global status after
selected, the increased requests is 5.
I don't kown the reason.
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 348 |
| Innodb_buffer_pool_reads | 16 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 348 |
| Innodb_buffer_pool_reads | 16 |
+-----------------------------------+-------+
4 rows in set (0.01 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 348 |
| Innodb_buffer_pool_reads | 16 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 450 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 450 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 455 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 486 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 486 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 522 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 522 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 527 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 558 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.01 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 563 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 594 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 599 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 630 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.01 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 630 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.01 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.01 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 666 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 666 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 666 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 702 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 702 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from dist;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.01 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 707 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 738 |
| Innodb_buffer_pool_reads | 17 |
+-----------------------------------+-------+
4 rows in set (0.00 sec)