目錄

• MySQL需要關(guān)注的參數(shù)及狀態(tài)變量
• 總結(jié)

MySQL需要關(guān)注的參數(shù)及狀態(tài)變量

• open_files_limit

操作系統(tǒng)允許mysqld打開(kāi)的文件數(shù)量。

這個(gè)值可以設(shè)置得比較大，比如50000，最好在系統(tǒng)初始化安裝時(shí)就設(shè)置了一個(gè)較大的值。

可修改文件/etc/security/limits.conf來(lái)實(shí)現(xiàn)，

命令如下：

vi /etc/security/limits.conf
* -nofile 50000

• max_connect_errors

此值應(yīng)設(shè)置得比較大，如大于5000，以避免因?yàn)檫B接出錯(cuò)而超過(guò)出錯(cuò)閾值，導(dǎo)致MySQL阻止該主機(jī)連接，如被阻塞，則須手動(dòng)執(zhí)行flush-hosts進(jìn)行復(fù)位。

• max_connections

允許并行的客戶端連接數(shù)目。默認(rèn)值為100太小，一般會(huì)不夠用。

生產(chǎn)環(huán)境中建議設(shè)置為2000~5000.注意，對(duì)于32位的MySQL由于有內(nèi)存限制，連接數(shù)不能過(guò)大(建議小于800)，否則可能會(huì)由于連接過(guò)多，造成MySQL實(shí)例崩潰。

• max_used_connections

MySQL Server啟動(dòng)后曾經(jīng)到達(dá)的最大連接數(shù)。

如果該值達(dá)到max_connections，那么某個(gè)時(shí)刻存在突然的高峰連接時(shí)，可能會(huì)有性能問(wèn)題。

• threads_connected

當(dāng)前打開(kāi)的連接數(shù)量。這個(gè)值不能超過(guò)設(shè)置的max_connections*80%。需要注意及時(shí)調(diào)整max_connections的值。一旦連接數(shù)超過(guò)了max_connections，就會(huì)出現(xiàn)客戶端連接不上的錯(cuò)誤。

• aborted_connects

試圖連接到MySQL服務(wù)器而失敗的連接數(shù)。正常情況下，該值不會(huì)持續(xù)增加，出現(xiàn)連接失敗的原因主要有如下幾點(diǎn)：

• 1) 客戶端程序在退出之前未調(diào)用mysql_close()。
• 2) 客戶端的空閑時(shí)間超過(guò)了wait_timeout或interactive_timeout秒，未向服務(wù)器發(fā)出任何請(qǐng)求。
• 3) 客戶端在數(shù)據(jù)傳輸中途突然結(jié)束。
• Aborted_clients

由于客戶端沒(méi)有正確關(guān)閉連接導(dǎo)致客戶端終止而中斷的連接數(shù)。

出現(xiàn)下述情況時(shí)，服務(wù)器將增加”Aborted_clients“(放棄客戶端)的狀態(tài)變量。

• 1) 客戶端不具有連接至數(shù)據(jù)庫(kù)的權(quán)限。
• 2) 客戶端采用了不正確的密碼。
• 3) 連接信息包含不正確的信息。
• 4) 獲取連接信息包的時(shí)間超過(guò)了connect_timeout秒。

我們可以使用如下命令發(fā)現(xiàn)異常：

mysqladmin -uroot -p -S /path/to/tmp//3306/mysql.sock ext | grep Abort

也可以使用tcpdump來(lái)判斷是什么原因?qū)е铝水惓＃?/p>

tcpdump -s 1500 -w tcp.out port 3306
strings tcpdump.out

• thread_cache_size

服務(wù)器應(yīng)緩存多少線程以便重新使用？

當(dāng)客戶端斷開(kāi)連接時(shí)，如果線程少于thread_cache_size，則客戶端的線程將被放入緩存。

如果有新連接請(qǐng)求分配線程則可以從緩存中重新利用線程，只有當(dāng)緩存空了時(shí)才會(huì)創(chuàng)建新線程。如果新連接很多，則可以增加該變量以提高性能。

如果是大量并發(fā)的短連接，則可能會(huì)因?yàn)閠hread_cache_size不夠而導(dǎo)致性能問(wèn)題。生產(chǎn)環(huán)境中一般將其設(shè)置為100~200。

由于線程可以緩存，所以線程持有的內(nèi)存不會(huì)被輕易釋放。

• Threads_created

創(chuàng)建用來(lái)處理連接的線程數(shù)。應(yīng)該監(jiān)視Thread_created的增量，如果較多，則需要增加thread_cache_size的值。

以上對(duì)thread_cache_size的設(shè)置在高并發(fā)的時(shí)候會(huì)很有效。高并發(fā)時(shí)大量并發(fā)短連接對(duì)CPU的沖擊不容忽視。

• treads_running

指同時(shí)運(yùn)行的線程數(shù)目。這個(gè)值一般不會(huì)大于邏輯CPU的個(gè)數(shù)，如果經(jīng)常有過(guò)多的線程同時(shí)運(yùn)行，那么可能就意味著有性能的問(wèn)題。

這個(gè)指標(biāo)很重要往往表明一個(gè)系統(tǒng)繁忙程度，它在系統(tǒng)爆發(fā)性性能問(wèn)題之前，會(huì)有一個(gè)上升的趨勢(shì)，此時(shí)收集的性能信息，將有助于我們?cè)\斷復(fù)雜的性能問(wèn)題。

• slow_launch_chreads

如果這個(gè)值比較大，則意味著創(chuàng)建線程太慢了，可能是系統(tǒng)出現(xiàn)了性能問(wèn)題，存在資源瓶頸，從而導(dǎo)致操作系統(tǒng)沒(méi)有安排足夠的CPU時(shí)間給新創(chuàng)建的線程。

• query_cache_size

為了緩存查詢結(jié)果分配的內(nèi)存大小。一般設(shè)置為256MB。注意不要設(shè)置得太大。

可監(jiān)控查詢緩存命中率：Qcache_hits / (Qcache_hits+Com_select)。

更改這個(gè)值，會(huì)清空所有的緩存結(jié)果集，對(duì)于非常繁忙的系統(tǒng)，可能會(huì)很耗時(shí)，導(dǎo)致服務(wù)停頓，因?yàn)镸ySQL在刪除所有的緩存查詢時(shí)是逐個(gè)進(jìn)行的。

• Qchache_lowmem_prunes

該變量記錄了由于查詢緩存出現(xiàn)內(nèi)存不足，而需要從緩存中刪除的查詢數(shù)量，可通過(guò)監(jiān)控Qcache_lowmem_prunes的增量，來(lái)衡量是否需要增大query_cache_size。

Qcache_lowmem_prunes狀態(tài)變量提供的信息能夠幫助你調(diào)整查詢緩存的大小。

它可計(jì)算為了緩存新的查詢而從查詢緩存區(qū)移出到自由內(nèi)存中的查詢數(shù)目。

查詢緩存區(qū)使用最近最少使用(LRU)策略來(lái)確定哪些查詢需要從緩存區(qū)中移出。

• InnoDB_buffer_pool_wait_free

一般情況下，是通過(guò)后臺(tái)向InnoDB緩沖池中寫(xiě)入數(shù)據(jù)的。

但是，如果需要讀或創(chuàng)建頁(yè)，并且沒(méi)有 干凈的頁(yè)可用，那么它還需要先等待頁(yè)面清空。

如果已經(jīng)適當(dāng)設(shè)置了緩沖池的大小，那么該值應(yīng)該會(huì)很小。

• Slow_queries

查詢時(shí)間超過(guò)long_query_time秒的查詢個(gè)數(shù)。應(yīng)該監(jiān)控此變量的增量變化，一般1秒內(nèi)不要超過(guò)5~10個(gè)，否則可能是有性能問(wèn)題。

• Select_full_join

沒(méi)有 使用索引的連接數(shù)量。如果該值較大，則應(yīng)該仔細(xì)檢查一下表的索引。

• Created_tmp_tables

創(chuàng)建內(nèi)存臨時(shí)表的數(shù)量，如果Created_tmp_disk_tables比較大，則應(yīng)該考慮增加tmp_table_size的大小。

注：應(yīng)該將tmp_table_size和max_heap_table_size簡(jiǎn)單調(diào)整到大小一樣。32MB一般足夠了。對(duì)這兩個(gè)參數(shù)的控制通常基于內(nèi)存引擎的臨時(shí)表可以增長(zhǎng)的閾值，若超過(guò)了這個(gè)閾值，就會(huì)轉(zhuǎn)化成 On-disk MyISAM表。

• Created_tmp_disk_tables

服務(wù)器執(zhí)行語(yǔ)句時(shí)在硬盤(pán)上自動(dòng)創(chuàng)建的臨時(shí)表的數(shù)量。

• Bytes_received 和 Bytes_sent

可用來(lái)監(jiān)控MySQL的流量。

• key_buffer_size

MyISAM索引緩沖，實(shí)際用到多少就分配多少。不一定需要分配很大的空間，可參考實(shí)際觀察到的值，不要大于實(shí)際值。

如下命令可用于評(píng)估索引空間的大小。

select sum(index_length) from information_schema.tables where engine="MYISAM";

• Open_tables

當(dāng)前打開(kāi)的表的數(shù)量。

• Opened_tables

已經(jīng)打開(kāi)的表的數(shù)量。

查看Open_tables及Opened_tables的增量時(shí)，如果后者的增量比較大，那么可能table_open_cache（或者table_cache）不夠用了。

如果Open_tables對(duì)比table_cache_size并不大，但Opened_tables還在持續(xù)增長(zhǎng)，那么也可能是顯式臨時(shí)表被不斷打開(kāi)而導(dǎo)致的。

• table_open_cache（table_cache 5.1.3之前的參數(shù)名）

默認(rèn)的設(shè)置太小了，生產(chǎn)環(huán)境中應(yīng)該將其設(shè)置得足夠大，數(shù)千到一萬(wàn)是比較合理的值。

檢查Opened_tables status變量，如果該值比較大，而我們不經(jīng)常運(yùn)行 FLUSH TABLES命令，那么應(yīng)該增加table_open_cache的變量值。

• table_definition_cache

一般可以將其設(shè)置為足夠高的值來(lái)緩存表定義，比如4096，這并不會(huì)耗費(fèi)什么資源。默認(rèn)的256太小了。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持。