From the source of Mysql analysis InnoDB buffer hit rate calculation _mysql

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.