快捷導(dǎo)航

從MySQL的源碼剖析Innodb buffer的命中率計算

更新時間：2015年05月09日 15:38:52 作者：吳炳錫

這篇文章主要介紹了從MySQL的源碼剖析Innodb buffer的命中率計算,作者結(jié)合C語言寫的算法來分析innodb buffer hit Ratios,需要的朋友可以參考下

按官方手冊推薦Innodb buffer Hit Ratios的計算是:

100-((iReads / iReadRequests)*100)
iReads : mysql-&gt;status-&gt;Innodb_buffer_pool_reads
iReadRequests: mysql-&gt;status-&gt;Innodb_buffer_pool_read_requests

出處: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graphref.html
搜”Hit Ratios”
推薦有興趣的同學(xué)把這個頁面都看一下應(yīng)該也會有很大收獲.
另外在hackmysql: www.hackmysql.com網(wǎng)站上的: mysqlsqlreport中關(guān)于buffer命中計算是:

$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);

即:

ib_bp_hit=100-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

另外我們知道查看Innodb Buffer Hit Ratios的地方是:

復(fù)制代碼代碼如下:

show engine innodb status\G;

Buffer pool hit rate : XXXX/1000;
那個XXX/1000即是buffer pool hit ratios的命中.
這樣也可以從代碼里看一下這個bp命中計算:

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

在buf0buf.c 中的buf_print_io函數(shù)中可以看到:

void
buf_print_io(
…
 
if (buf_pool-&gt;n_page_gets &gt; buf_pool-&gt;n_page_gets_old) {
fprintf(file, "Buffer pool hit rate %lu / 1000\n",
(ulong)
(1000 - ((1000 * (buf_pool-&gt;n_pages_read
- buf_pool-&gt;n_pages_read_old))
/ (buf_pool-&gt;n_page_gets
- buf_pool-&gt;n_page_gets_old))));
} else {
fputs("No buffer pool page gets since the last printout\n",
file);
}
 
buf_pool-&gt;n_page_gets_old = buf_pool-&gt;n_page_gets;
buf_pool-&gt;n_pages_read_old = buf_pool-&gt;n_pages_read;
…
}

結(jié)合:
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
is NOT protected by the buffer
pool mutex */
…
ulint n_page_gets_old;/* n_page_gets when buf_print was
last time called: used to calculate
hit rate */
…
ulint n_pages_read_old;/* n_pages_read when buf_print was
last time called */
…

從這個來看innodb buffer hit Ratios的命中計算需要本次取的值和上次值做一個減法公式應(yīng)該為

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

t(n): 時間點兩個時間間隔最少是30秒以上,在小意義不大.

iReads: Innodb_buffer_pool_reads
iReadRequest: Innodb_buffer_pool_read_requests

對innodb的輸出參數(shù)有興趣的可以關(guān)注: storage/innobase/buf/Srv0srv.c 中的:

void srv_export_innodb_status()

思考:
對于innodb_buffer_pool_read_requests, innodb_buffer_pool_reads這種累加值,當(dāng)很大時進行: innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 相來講只能得到從開始到現(xiàn)在的命中率的表現(xiàn)了. 如果想得到現(xiàn)在近五分鐘,近一分鐘或是8點到9點每分鐘的命中率情況,如果還是按著innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 進行計算,只能得到mysqld開起累計在8點-9點的每分鐘的累計平均命中情況.
所以如果想到每(五)分鐘的命中情況,就需要本次取得的值和一(五)分鐘前的值進行相減,然后進行運算.這樣才能得到一個當(dāng)下的bp命中情況.
兩種方法沒實質(zhì)的對錯的問題,但相對于源碼中的那種計算方式更容讓發(fā)現(xiàn)數(shù)據(jù)庫的抖動問題.

能解決的問題:
偶而的數(shù)據(jù)庫性能抖動能直觀的反應(yīng)出來.

您可能感興趣的文章: