According to the official manual recommended InnoDB buffer Hit ratios calculation is:
100-((ireads/ireadrequests) *100)
ireads:mysql->status->innodb_buffer_pool_reads
iReadRequests: Mysql->status->innodb_buffer_pool_read_requests
Source: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graphref.html
Search "Hit ratios"
Recommend interested students to look at this page should also have a great harvest.
Also on the hackmysql:www.hackmysql.com website: Mysqlsqlreport about the buffer hit calculation is:
$ib _bp_read_ratio = sprintf "%.2f",
($stats {' innodb_buffer_pool_read_requests '}?
($stats {' innodb_buffer_pool_reads '}/
$stats {' innodb_buffer_pool_read_requests '}) * 100:0);
That
ib_bp_hit=100-(innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) *100
In addition, we know the place to view InnoDB Buffer Hit ratios is:
Copy Code code as follows:
Show Engine InnoDB status\g;
Buffer Pool hit rate:xxxx/1000;
That xxx/1000 is the hit of the buffer pool hit ratios.
This also allows you to look at the BP hit calculation from the code:
STORAGE/INNOBASE/BUF/BUF0BUF.C # void Buf_print_io
storage/innodbase/include/buf0buf.h #struct buf_block_struct
As you can see in the Buf_print_io function in BUF0BUF.C:
void
buf_print_io (
....
.) if (Buf_pool->n_page_gets > Buf_pool->n_page_gets_old) {
fprintf (file, "Buffer pool hit rate%lu/1000\n", c5/> (ULONG)
(1000-(1000 * (buf_pool->n_pages_read
-buf_pool->n_pages_read_old))
/(buf_pool- >n_page_gets
-Buf_pool->n_page_gets_old)));
else {
fputs ("No buffer pool page gets since the last printout\n",
file);
Buf_pool->n_page_gets_old = buf_pool->n_page_gets;
Buf_pool->n_pages_read_old = buf_pool->n_pages_read;
..
}
Combine:
In Storage\innobase\include\buf0buf.h
struct buf_block_struct{...
Ulint N_pages_read; /* Number read operations * ...
Ulint n_page_gets; /* Number of page gets performed;
Also successful searches through the
Adaptive Hash index are
counted as page gets; This field isn't
protected By the buffer
pool Mutex */
...
Ulint n_page_gets_old;/* N_page_gets When Buf_print is last time
called:used to calculate
hit ... rate
15/>ulint n_pages_read_old;/* N_pages_read When Buf_print is last time
called *
...
From this point of view InnoDB buffer hit Ratios's hit calculation requires the value of this fetch and the last value to do a subtraction formula should be
ib_bp_hit=1000– (t2.ireads–t1.ireads)/(t2.ireadrequest–t1.ireadrequest) *1000
T (N): Point two time interval is at least 30 seconds or more, in small significance.
Ireads:innodb_buffer_pool_reads
ireadrequest:innodb_buffer_pool_read_requests
Interested in the output parameters of InnoDB: storage/innobase/buf/srv0srv.c:
void Srv_export_innodb_status ()
Thinking:
for innodb_buffer_pool_read_requests, innodb_buffer_pool_reads This cumulative value, when it is very large: innodb_buffer_pool_reads/innodb_ Buffer_pool_read_requests can only get the performance from the beginning to the current hit rate. If you want to get nearly five minutes, nearly a minute or 8 to 9 points per minute of the hit rate, if you still follow the innodb_buffer_pool_reads/innodb_buffer_pool_read_requests to calculate, can only get the cumulative average hits per minute of the cumulative mysqld at 8 points-9 points.
So if you think of a hit for every (five) minutes, you need to subtract the value of this time and the value of one (five) minutes before you do the math. This will get a current BP hit.
There is no real problem of right and wrong in both methods, but the way to compute the database jitter problem is more than that in the source code.
The problem that can be solved:
Occasionally, the database performance jitter can be intuitively reflected.