從MySQL的源碼分析Innodb buffer的射中率盤算。本站提示廣大學習愛好者:(從MySQL的源碼分析Innodb buffer的射中率盤算)文章只能為提供參考,不一定能成為您想要的結果。以下是從MySQL的源碼分析Innodb buffer的射中率盤算正文
按官方手冊推舉Innodb buffer Hit Ratios的盤算是:
100-((iReads / iReadRequests)*100) iReads : mysql->status->Innodb_buffer_pool_reads iReadRequests: mysql->status->Innodb_buffer_pool_read_requests
出處: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graphref.html
搜”Hit Ratios”
推舉有興致的同窗把這個頁面都看一下應當也會有很年夜收成.
別的在hackmysql: www.hackmysql.com網站上的: mysqlsqlreport中關於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的處所是:
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函數中可以看到:
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", (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; … }
聯合:
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的射中盤算須要本次取的值和前次值做一個減法公式應當為
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的輸入參數有興致的可以存眷: storage/innobase/buf/Srv0srv.c 中的:
void srv_export_innodb_status()
思慮:
關於innodb_buffer_pool_read_requests, innodb_buffer_pool_reads這類累加值,當很年夜時停止: innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 相來說只能獲得從開端到如今的射中率的表示了. 假如想獲得如今近五分鐘,近一分鐘或是8點到9點每分鐘的射中率情形,假如照樣按著innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 停止盤算,只能獲得mysqld開起累計在8點-9點的每分鐘的累計均勻射中情形.
所以假如想到每(五)分鐘的射中情形,就須要本次獲得的值和一(五)分鐘前的值停止相減,然落後交運算.如許能力獲得一個當下的bp射中情形.
兩種辦法沒本質的對錯的成績,但絕對於源碼中的那種盤算方法更容讓發明數據庫的發抖成績.
能處理的成績:
偶而的數據庫機能發抖能直不雅的反響出來.