.jpg)
下面小編就為大家?guī)硪黄?a href="http://www.babyishan.com/?golink=aHR0cDovL3d3dy5waHAuY24vd2lraS8xMTYwLmh0bWw=" target="_blank" rel="noopener">mysql幾點(diǎn)重要的性能指標(biāo)計算和優(yōu)化方法總結(jié)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
1 QPS計算(每秒查詢數(shù))
針對MyISAM引擎為主的DB
MySQL>?show?GLOBAL?status?like?'questions'; +---------------+------------+ |?Variable_name?|?Value???| +---------------+------------+ |?Questions???|?2009191409?| +---------------+------------+ 1?row?in?set?(0.00?sec) mysql>?show?global?status?like?'uptime'; +---------------+--------+ |?Variable_name?|?Value?| +---------------+--------+ |?Uptime????|?388402?| +---------------+--------+ 1?row?in?set?(0.00?sec)
QPS=questions/uptime=5172，mysql自啟動以來的平均QPS，如果要計算某一時間段內(nèi)的QPS，可在高峰期間獲取間隔時間t2-t1，然后分別計算出t2和t1時刻的q值，QPS=(q2-q1)/(t2-t1)
針對InnnoDB引擎為主的DB
mysql>?show?global?status?like?'com_update'; +---------------+----------+ |?Variable_name?|?Value??| +---------------+----------+ |?Com_update??|?87094306?| +---------------+----------+ 1?row?in?set?(0.00?sec) mysql>?show?global?status?like?'com_select'; +---------------+------------+ |?Variable_name?|?Value???| +---------------+------------+ |?Com_select??|?1108143397?| +---------------+------------+ 1?row?in?set?(0.00?sec) mysql>?show?global?status?like?'com_delete'; +---------------+--------+ |?Variable_name?|?Value?| +---------------+--------+ |?Com_delete??|?379058?| +---------------+--------+ 1?row?in?set?(0.00?sec) mysql>?show?global?status?like?'uptime'; +---------------+--------+ |?Variable_name?|?Value?| +---------------+--------+ |?Uptime????|?388816?| +---------------+--------+ 1?row?in?set?(0.00?sec)
QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076,某一時間段內(nèi)的QPS查詢方法同上。
2 TPS計算(每秒事務(wù)數(shù))
mysql>?show?global?status?like?'com_commit'; +---------------+---------+ |?Variable_name?|?Value??| +---------------+---------+ |?Com_commit??|?7424815?| +---------------+---------+ 1?row?in?set?(0.00?sec) mysql>?show?global?status?like?'com_rollback'; +---------------+---------+ |?Variable_name?|?Value??| +---------------+---------+ |?Com_rollback?|?1073179?| +---------------+---------+ 1?row?in?set?(0.00?sec) mysql>?show?global?status?like?'uptime'; +---------------+--------+ |?Variable_name?|?Value?| +---------------+--------+ |?Uptime????|?389467?| +---------------+--------+ 1?row?in?set?(0.00?sec) TPS=(com_commit+com_rollback)/uptime=22
3 線程連接數(shù)和命中率
mysql>?show?global?status?like?'threads_%'; +-------------------+-------+ |?Variable_name???|?Value?| +-------------------+-------+ |?Threads_cached??|?480??|???//代表當(dāng)前此時此刻線程緩存中有多少空閑線程 |?Threads_connected?|?153??|??//代表當(dāng)前已建立連接的數(shù)量，因?yàn)橐粋€連接就需要一個線程，所以也可以看成當(dāng)前被使用的線程數(shù) |?Threads_created??|?20344?|??//代表從最近一次服務(wù)啟動，已創(chuàng)建線程的數(shù)量 |?Threads_running??|?2???|???//代表當(dāng)前激活的（非睡眠狀態(tài)）線程數(shù) +-------------------+-------+ 4?rows?in?set?(0.00?sec) mysql>?show?global?status?like?'Connections'; +---------------+-----------+ |?Variable_name?|?Value???| +---------------+-----------+ |?Connections??|?381487397?| +---------------+-----------+ 1?row?in?set?(0.00?sec) 線程緩存命中率=1-Threads_created/Connections??=?99.994% 我們設(shè)置的線程緩存?zhèn)€數(shù) mysql>?show?variables?like?'%thread_cache_size%'; +-------------------+-------+ |?Variable_name???|?Value?| +-------------------+-------+ |?thread_cache_size?|?500??| +-------------------+-------+ 1?row?in?set?(0.00?sec)
根據(jù)Threads_connected可預(yù)估thread_cache_size值應(yīng)該設(shè)置多大，一般來說250是一個不錯的上限值，如果內(nèi)存足夠大，也可以設(shè)置成thread_cache_size值和threaads_connected值相同；
或者通過觀察threads_created值，如果該值很大或一直在增長，可以適當(dāng)增加thread_cache_size的值；在休眠狀態(tài)下每個線程大概占用256KB左右的內(nèi)存，所以當(dāng)內(nèi)存足夠時，設(shè)置太小也不會節(jié)約太多內(nèi)存，除非該值已經(jīng)超過幾千。
4 表緩存
mysql>?show?global?status?like?'open_tables%'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?Open_tables??|?2228?| +---------------+-------+ 1?row?in?set?(0.00?sec)
我們設(shè)置的打開表的緩存和表定義緩存
mysql>?show?variables?like?'table_open_cache'; +------------------+-------+ |?Variable_name??|?Value?| +------------------+-------+ |?table_open_cache?|?16384?| +------------------+-------+ 1?row?in?set?(0.00?sec) mysql>?show?variables?like?'table_defi%'; +------------------------+-------+ |?Variable_name?????|?Value?| +------------------------+-------+ |?table_definition_cache?|?2000?| +------------------------+-------+ 1?row?in?set?(0.00?sec)
針對MyISAM：
mysql每打開一個表，都會讀入一些數(shù)據(jù)到table_open_cache 緩存 中，當(dāng)mysql在這個緩存中找不到相應(yīng)的信息時，才會去磁盤上直接讀取，所以該值要設(shè)置得足夠大以避免需要重新打開和重新解析表的定義，一般設(shè)置為max_connections的10倍，但最好保持在10000以內(nèi)。
還有種依據(jù)就是根據(jù)狀態(tài)open_tables的值進(jìn)行設(shè)置，如果發(fā)現(xiàn)open_tables的值每秒變化很大，那么可能需要增大table_open_cache的值。
table_definition_cache 通常簡單設(shè)置為服務(wù)器中存在的表的數(shù)量，除非有上萬張表。
針對InnoDB：
與MyISAM不同，InnoDB的open table和open file并無直接聯(lián)系，即打開frm表時其相應(yīng)的ibd文件可能處于關(guān)閉狀態(tài)；
故InnoDB只會用到table_definiton_cache，不會使用table_open_cache；
其frm文件保存于table_definition_cache中，而idb則由innodb_open_files決定(前提是開啟了innodb_file_per_table)，最好將innodb_open_files設(shè)置得足夠大，使得服務(wù)器可以保持所有的.ibd文件同時打開。
5 最大連接數(shù)
mysql>?show?global?status?like?'Max_used_connections'; +----------------------+-------+ |?Variable_name????|?Value?| +----------------------+-------+ |?Max_used_connections?|?1785?| +----------------------+-------+ 1?row?in?set?(0.00?sec)
我們設(shè)置的max_connections大小
mysql>?show?variables?like?'max_connections%'; +-----------------+-------+ |?Variable_name??|?Value?| +-----------------+-------+ |?max_connections?|?4000?| +-----------------+-------+ 1?row?in?set?(0.00?sec)
通常max_connections的大小應(yīng)該設(shè)置為比Max_used_connections狀態(tài)值大，Max_used_connections狀態(tài)值反映服務(wù)器連接在某個時間段是否有尖峰，如果該值大于max_connections值，代表客戶端至少被拒絕了一次，可以簡單地設(shè)置為符合以下條件：Max_used_connections/max_connections=0.8
6 Innodb 緩存命中率
mysql>?show?global?status?like?'innodb_buffer_pool_read%'; +---------------------------------------+--------------+ |?Variable_name?????????????|?Value????| +---------------------------------------+--------------+ |?Innodb_buffer_pool_read_ahead_rnd???|?0??????| |?Innodb_buffer_pool_read_ahead?????|?268720????|???//預(yù)讀的頁數(shù) |?Innodb_buffer_pool_read_ahead_evicted?|?0??????|??? |?Innodb_buffer_pool_read_requests???|?480291074970?|?//從緩沖池中讀取的次數(shù) |?Innodb_buffer_pool_reads???????|?29912739???|?????//表示從物理磁盤讀取的頁數(shù) +---------------------------------------+--------------+ 5?rows?in?set?(0.00?sec)
緩沖池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)=99.994%
如果該值小于99.9%，建議就應(yīng)該增大innodb_buffer_pool_size的值了，該值一般設(shè)置為內(nèi)存總大小的75%-85%，或者計算出操作系統(tǒng)所需緩存+mysql每個連接所需的內(nèi)存（例如排序緩沖和臨時表）+MyISAM鍵緩存，剩下的內(nèi)存都給innodb_buffer_pool_size，不過也不宜設(shè)置太大，會造成內(nèi)存的頻繁交換，預(yù)熱和關(guān)閉時間長等問題。
7 MyISAM Key Buffer命中率和緩沖區(qū)使用率
mysql>?show?global?status?like?'key_%'; +------------------------+-----------+ |?Variable_name?????|?Value???| +------------------------+-----------+ |?Key_blocks_not_flushed?|?0?????| |?Key_blocks_unused???|?106662??| |?Key_blocks_used????|?107171??| |?Key_read_requests???|?883825678?| |?Key_reads???????|?133294??| |?Key_write_requests???|?217310758?| |?Key_writes???????|?2061054??| +------------------------+-----------+ 7?rows?in?set?(0.00?sec) mysql>?show?variables?like?'%key_cache_block_size%'; +----------------------+-------+ |?Variable_name????|?Value?| +----------------------+-------+ |?key_cache_block_size?|?1024?| +----------------------+-------+ 1?row?in?set?(0.00?sec) mysql>?show?variables?like?'%key_buffer_size%'; +-----------------+-----------+ |?Variable_name??|?Value???| +-----------------+-----------+ |?key_buffer_size?|?134217728?| +-----------------+-----------+ 1?row?in?set?(0.00?sec)
緩沖區(qū)的使用率=1-(Key_blocks_unused*key_cache_block_size/ key_buffer_size)=18.6%
讀命中率=1-Key_reads /Key_read_requests=99.98%
寫命中率=1-Key_writes / Key_write_requests =99.05%
可看到緩沖區(qū)的使用率并不高，如果很長一段時間后還沒有使用完所有的鍵緩沖，可以考慮把緩沖區(qū)調(diào)小一點(diǎn)。
鍵緩存命中率可能意義不大，因?yàn)樗蛻?yīng)用相關(guān)，有些應(yīng)用在95%的命中率下就工作良好，有些則需要99.99%，所以從經(jīng)驗(yàn)上看，每秒的緩存未命中次數(shù)更重要，假設(shè)一個獨(dú)立磁盤每秒能做100個隨機(jī)讀，那么每秒有5個緩沖未命中可能不會導(dǎo)致I/O繁忙，但每秒80個就可能出現(xiàn)問題。
每秒緩存未命中=Key_reads/uptime=0.33
8 臨時表使用情況
mysql>?show?global?status?like?'Created_tmp%'; +-------------------------+----------+ |?Variable_name??????|?Value??| +-------------------------+----------+ |?Created_tmp_disk_tables?|?19226325?| |?Created_tmp_files????|?117???| |?Created_tmp_tables???|?56265812?| +-------------------------+----------+ 3?rows?in?set?(0.00?sec) mysql>?show?variables?like?'%tmp_table_size%'; +----------------+----------+ |?Variable_name?|?Value??| +----------------+----------+ |?tmp_table_size?|?67108864?| +----------------+----------+ 1?row?in?set?(0.00?sec)
可看到總共創(chuàng)建了56265812 張臨時表，其中有19226325 張涉及到了磁盤IO，大概比例占到了0.34，證明數(shù)據(jù)庫應(yīng)用中排序，join語句涉及的數(shù)據(jù)量太大，需要優(yōu)化SQL或者增大tmp_table_size的值，我設(shè)的是64M。該比值應(yīng)該控制在0.2以內(nèi)。
9 binlog cache使用情況
mysql>?show?status?like?'Binlog_cache%';? +-----------------------+----------+ |?Variable_name?????|?Value??| +-----------------------+----------+ |?Binlog_cache_disk_use?|?15????| |?Binlog_cache_use???|?95978256?| +-----------------------+----------+ 2?rows?in?set?(0.00?sec) mysql>?show?variables?like?'binlog_cache_size'; +-------------------+---------+ |?Variable_name???|?Value??| +-------------------+---------+ |?binlog_cache_size?|?1048576?| +-------------------+---------+ 1?row?in?set?(0.00?sec)
Binlog_cache_disk_use表示因?yàn)槲覀僢inlog_cache_size設(shè)計的內(nèi)存不足導(dǎo)致緩存二進(jìn)制日志用到了臨時文件的次數(shù)
Binlog_cache_use 表示 用binlog_cache_size緩存的次數(shù)
當(dāng)對應(yīng)的Binlog_cache_disk_use 值比較大的時候 我們可以考慮適當(dāng)?shù)恼{(diào)高 binlog_cache_size 對應(yīng)的值
10 Innodb log buffer size的大小設(shè)置
mysql>?show?variables?like?'%innodb_log_buffer_size%'; +------------------------+---------+ |?Variable_name?????|?Value??| +------------------------+---------+ |?innodb_log_buffer_size?|?8388608?| +------------------------+---------+ 1?row?in?set?(0.00?sec) mysql>?show?status?like?'innodb_log_waits'; +------------------+-------+ |?Variable_name??|?Value?| +------------------+-------+ |?Innodb_log_waits?|?0???| +------------------+-------+ 1?row?in?set?(0.00?sec)
innodb_log_buffer_size我設(shè)置了8M，應(yīng)該足夠大了；Innodb_log_waits表示因log buffer不足導(dǎo)致等待的次數(shù)，如果該值不為0，可以適當(dāng)增大innodb_log_buffer_size的值。
11 表掃描情況判斷
mysql>?show?global?status?like?'Handler_read%'; +-----------------------+--------------+ |?Variable_name?????|?Value????| +-----------------------+--------------+ |?Handler_read_first??|?19180695???| |?Handler_read_key???|?30303690598?| |?Handler_read_last???|?290721????| |?Handler_read_next???|?51169834260?| |?Handler_read_prev???|?1267528402??| |?Handler_read_rnd???|?219230406??| |?Handler_read_rnd_next?|?344713226172?| +-----------------------+--------------+ 7?rows?in?set?(0.00?sec)
Handler_read_first：使用索引掃描的次數(shù)，該值大小說不清系統(tǒng)性能是好是壞
Handler_read_key：通過key進(jìn)行查詢的次數(shù)，該值越大證明系統(tǒng)性能越好
Handler_read_next：使用索引進(jìn)行排序的次數(shù)
Handler_read_prev：此選項表明在進(jìn)行索引掃描時，按照索引倒序從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù)，一般就是ORDER BY … DESC
Handler_read_rnd：該值越大證明系統(tǒng)中有大量的沒有使用索引進(jìn)行排序的操作，或者join時沒有使用到index
Handler_read_rnd_next：使用數(shù)據(jù)文件進(jìn)行掃描的次數(shù)，該值越大證明有大量的全表掃描，或者合理地創(chuàng)建索引，沒有很好地利用已經(jīng)建立好的索引
12 Innodb_buffer_pool_wait_free
mysql>?show?global?status?like?'Innodb_buffer_pool_wait_free'; +------------------------------+-------+ |?Variable_name????????|?Value?| +------------------------------+-------+ |?Innodb_buffer_pool_wait_free?|?0???| +------------------------------+-------+ 1?row?in?set?(0.00?sec)
該值不為0表示buffer pool沒有空閑的空間了，可能原因是innodb_buffer_pool_size設(shè)置太大，可以適當(dāng)減少該值。
13 join操作信息
mysql>?show?global?status?like?'select_full_join'; +------------------+-------+ |?Variable_name??|?Value?| +------------------+-------+ |?Select_full_join?|?10403?| +------------------+-------+ 1?row?in?set?(0.00?sec)
該值表示在join操作中沒有使用到索引的次數(shù)，值很大說明join語句寫得很有問題
mysql>?show?global?status?like?'select_range'; +---------------+----------+ |?Variable_name?|?Value??| +---------------+----------+ |?Select_range?|?22450380?| +---------------+----------+ 1?row?in?set?(0.00?sec)
該值表示第一個表使用ranges的join數(shù)量，該值很大說明join寫得沒有問題，通常可查看select_full_join和select_range的比值來判斷系統(tǒng)中join語句的性能情況
mysql>?show?global?status?like?'Select_range_check'; +--------------------+-------+ |?Variable_name???|?Value?| +--------------------+-------+ |?Select_range_check?|?0???| +--------------------+-------+ 1?row?in?set?(0.00?sec)
如果該值不為0需要檢查表的索引是否合理，表示在表n+1中重新評估表n中的每一行的索引是否開銷最小所做的聯(lián)接數(shù)，意味著表n+1對該聯(lián)接而言并沒有有用的索引。
mysql>?show?GLOBAL?status?like?'select_scan'; +---------------+-----------+ |?Variable_name?|?Value???| +---------------+-----------+ |?Select_scan??|?116037811?| +---------------+-----------+ 1?row?in?set?(0.00?sec)
select_scan表示掃描第一張表的連接數(shù)目，如果第一張表中每行都參與聯(lián)接，這樣的結(jié)果并沒有問題；如果你并不想要返回所有行但又沒有使用到索引來查找到所需要的行，那么計數(shù)很大就很糟糕了。
14 慢查詢
mysql>?show?global?status?like?'Slow_queries'; +---------------+--------+ |?Variable_name?|?Value?| +---------------+--------+ |?Slow_queries?|?114111?| +---------------+--------+ 1?row?in?set?(0.00?sec)
該值表示mysql啟動以來的慢查詢個數(shù)，即執(zhí)行時間超過long_query_time的次數(shù)，可根據(jù)Slow_queries/uptime的比值判斷單位時間內(nèi)的慢查詢個數(shù)，進(jìn)而判斷系統(tǒng)的性能。
15表鎖信息
mysql>?show?global?status?like?'table_lock%'; +-----------------------+------------+ |?Variable_name?????|?Value???| +-----------------------+------------+ |?Table_locks_immediate?|?1644917567?| |?Table_locks_waited??|?53?????| +-----------------------+------------+ 2?rows?in?set?(0.00?sec)
這兩個值的比值：Table_locks_waited /Table_locks_immediate 趨向于0，如果值比較大則表示系統(tǒng)的鎖阻塞情況比較嚴(yán)重
.png)
