Analysis of Innodbbuffer hit rate calculation from MySQL source code _ MySQL

This article mainly introduces how to analyze the Innodbbuffer hit rate calculation from the MySQL source code. The author analyzes innodbbufferhitRatios by using the C language algorithm, for more information about Innodb buffer Hit Ratios, see the following official manual:

100-((iReads / iReadRequests)*100)iReads : mysql->status->Innodb_buffer_pool_readsiReadRequests: mysql->status->Innodb_buffer_pool_read_requests

Source: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graphref.html
Search "Hit Ratios"
If you are interested in the recommendation, you should take a look at this page and it will be very rewarding.
In addition, on the hackmysql: www.hackmysql.com website: mysqlsqlreport, the buffer hit calculation is as follows:

$ib_bp_read_ratio = sprintf "%.2f",($stats{'Innodb_buffer_pool_read_requests'} ?100 - ($stats{'Innodb_buffer_pool_reads'} /$stats{'Innodb_buffer_pool_read_requests'}) * 100 :0);

That is:

ib_bp_hit=100-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

In addition, we know where to view Innodb Buffer Hit Ratios is:

The code is as follows:

Show engine innodb status \ G;

Buffer pool hit rate: XXXX/1000;
That XXX/1000 is the hit of buffer pool hit ratios.
In this way, we can also look at this bp hit calculation from the code:

storage/innobase/buf/buf0buf.c # void buf_print_iostorage/innodbase/include/buf0buf.h #struct buf_block_struct

In the buf_print_io function of buf0buf. c, you can see:

voidbuf_print_io(… if (buf_pool->n_page_gets > buf_pool->n_page_gets_old) {fprintf(file, "Buffer pool hit rate %lu / 1000\n",(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;…}

Combination:
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 throughthe adaptive hash index arecounted as page gets; this fieldis NOT protected by the bufferpool mutex */…ulint n_page_gets_old;/* n_page_gets when buf_print waslast time called: used to calculatehit rate */…ulint n_pages_read_old;/* n_pages_read when buf_print waslast time called */…

From this point of view, the hit calculation of innodb buffer hit Ratios requires a subtraction formula between the value obtained this time and the previous value.

ib_bp_hit=1000 – (t2.iReads – t1.iReads)/(t2.iReadRequest – t1.iReadRequest)*1000

T (n): the minimum interval between two time points is 30 seconds or more, which is of little significance.

iReads: Innodb_buffer_pool_readsiReadRequest: Innodb_buffer_pool_read_requests

If you are interested in the output parameters of innodb, see: in storage/innobase/buf/Srv0srv. c:

void srv_export_innodb_status()

Thoughts:
For the cumulative value of bytes, innodb_buffer_pool_reads, when it is very large: innodb_buffer_pool_reads/innodb_buffer_pool_read_requests, you can only get the hit rate from the beginning to the present. if you want to calculate the hit rate in the last five minutes, nearly one minute, or from to, if innodb_buffer_pool_reads/innodb_buffer_pool_read_requests is used, you can only obtain the cumulative average hits per minute from to when mysqld is enabled.
Therefore, if you think of the hit rate every (five) minutes, you need to subtract the value obtained this time from the one (five) minutes ago and perform the calculation. in this way, we can get the current bp hit situation.
The two methods have no real right or wrong problems, but compared with the calculation method in the source code, they are more likely to detect database jitter problems.

Issues that can be solved:
Occasional database performance jitter can be intuitively reflected.