MySQL需要關注的引數及狀態變數解讀

MySQL需要關注的引數及狀態變數

• open_files_limit

作業系統允許mysqld開啟的檔案數量。

這個值可以設定得比較大，比如50000，最好在系統初始化安裝時就設定了一個較大的值。

可修改檔案/etc/security/limits.conf來實現，

命令如下：

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

• max_connect_errors

此值應設定得比較大，如大於5000，以避免因為連線出錯而超過出錯閾值，導致MySQL阻止該主機連線，如被阻塞，則須手動執行flush-hosts進行復位。

• max_connections

允許並行的使用者端連線數目。預設值為100太小，一般會不夠用。

生產環境中建議設定為2000~5000.注意，對於32位元的MySQL由於有記憶體限制，連線數不能過大(建議小於800)，否則可能會由於連線過多，造成MySQL範例崩潰。

• max_used_connections

MySQL Server啟動後曾經到達的最大連線數。

如果該值達到max_connections，那麼某個時刻存在突然的高峰連線時，可能會有效能問題。

• threads_connected

當前開啟的連線數量。這個值不能超過設定的max_connections*80%。需要注意及時調整max_connections的值。一旦連線數超過了max_connections，就會出現使用者端連線不上的錯誤。

• aborted_connects

試圖連線到MySQL伺服器而失敗的連線數。正常情況下，該值不會持續增加，出現連線失敗的原因主要有如下幾點：

• 1) 使用者端程式在退出之前未呼叫mysql_close()。
• 2) 使用者端的空閒時間超過了wait_timeout或interactive_timeout秒，未向伺服器發出任何請求。
• 3) 使用者端在資料傳輸中途突然結束。
• Aborted_clients

由於使用者端沒有正確關閉連線導致使用者端終止而中斷的連線數。

出現下述情況時，伺服器將增加”Aborted_clients“(放棄使用者端)的狀態變數。

• 1) 使用者端不具有連線至資料庫的許可權。
• 2) 使用者端採用了不正確的密碼。
• 3) 連線資訊包含不正確的資訊。
• 4) 獲取連線資訊包的時間超過了connect_timeout秒。

我們可以使用如下命令發現異常：

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

也可以使用tcpdump來判斷是什麼原因導致了異常：

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

• thread_cache_size

伺服器應快取多少執行緒以便重新使用？

當用戶端斷開連線時，如果執行緒少於thread_cache_size，則使用者端的執行緒將被放入快取。

如果有新連線請求分配執行緒則可以從快取中重新利用執行緒，只有當快取空了時才會建立新執行緒。如果新連線很多，則可以增加該變數以提高效能。

如果是大量並行的短連線，則可能會因為thread_cache_size不夠而導致效能問題。生產環境中一般將其設定為100~200。

由於執行緒可以快取，所以執行緒持有的記憶體不會被輕易釋放。

• Threads_created

建立用來處理連線的執行緒數。應該監視Thread_created的增量，如果較多，則需要增加thread_cache_size的值。

以上對thread_cache_size的設定在高並行的時候會很有效。高並行時大量並行短連線對CPU的衝擊不容忽視。

• treads_running

指同時執行的執行緒數目。這個值一般不會大於邏輯CPU的個數，如果經常有過多的執行緒同時執行，那麼可能就意味著有效能的問題。

這個指標很重要往往表明一個系統繁忙程度，它在系統爆發性效能問題之前，會有一個上升的趨勢，此時收集的效能資訊，將有助於我們診斷複雜的效能問題。

• slow_launch_chreads

如果這個值比較大，則意味著建立執行緒太慢了，可能是系統出現了效能問題，存在資源瓶頸，從而導致作業系統沒有安排足夠的CPU時間給新建立的執行緒。

• query_cache_size

為了快取查詢結果分配的記憶體大小。一般設定為256MB。注意不要設定得太大。

可監控查詢快取命中率：Qcache_hits / (Qcache_hits+Com_select)。

更改這個值，會清空所有的快取結果集，對於非常繁忙的系統，可能會很耗時，導致服務停頓，因為MySQL在刪除所有的快取查詢時是逐個進行的。

• Qchache_lowmem_prunes

該變數記錄了由於查詢快取出現記憶體不足，而需要從快取中刪除的查詢數量，可通過監控Qcache_lowmem_prunes的增量，來衡量是否需要增大query_cache_size。

Qcache_lowmem_prunes狀態變數提供的資訊能夠幫助你調整查詢快取的大小。

它可計算為了快取新的查詢而從查詢快取區移出到自由記憶體中的查詢數目。

查詢快取區使用最近最少使用(LRU)策略來確定哪些查詢需要從快取區中移出。

• InnoDB_buffer_pool_wait_free

一般情況下，是通過後臺向InnoDB緩衝池中寫入資料的。

但是，如果需要讀或建立頁，並且沒有 乾淨的頁可用，那麼它還需要先等待頁面清空。

如果已經適當設定了緩衝池的大小，那麼該值應該會很小。

• Slow_queries

查詢時間超過long_query_time秒的查詢個數。應該監控此變數的增量變化，一般1秒內不要超過5~10個，否則可能是有效能問題。

• Select_full_join

沒有 使用索引的連線數量。如果該值較大，則應該仔細檢查一下表的索引。

• Created_tmp_tables

建立記憶體臨時表的數量，如果Created_tmp_disk_tables比較大，則應該考慮增加tmp_table_size的大小。

注：應該將tmp_table_size和max_heap_table_size簡單調整到大小一樣。32MB一般足夠了。對這兩個引數的控制通常基於記憶體引擎的臨時表可以增長的閾值，若超過了這個閾值，就會轉化成 On-disk MyISAM表。

• Created_tmp_disk_tables

伺服器執行語句時在硬碟上自動建立的臨時表的數量。

• Bytes_received 和 Bytes_sent

可用來監控MySQL的流量。

• key_buffer_size

MyISAM索引緩衝，實際用到多少就分配多少。不一定需要分配很大的空間，可參考實際觀察到的值，不要大於實際值。

如下命令可用於評估索引空間的大小。

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

• Open_tables

當前開啟的表的數量。

• Opened_tables

已經開啟的表的數量。

檢視Open_tables及Opened_tables的增量時，如果後者的增量比較大，那麼可能table_open_cache（或者table_cache）不夠用了。

如果Open_tables對比table_cache_size並不大，但Opened_tables還在持續增長，那麼也可能是顯式臨時表被不斷開啟而導致的。

• table_open_cache（table_cache 5.1.3之前的引數名）

預設的設定太小了，生產環境中應該將其設定得足夠大，數千到一萬是比較合理的值。

檢查Opened_tables status變數，如果該值比較大，而我們不經常執行 FLUSH TABLES命令，那麼應該增加table_open_cache的變數值。

• table_definition_cache

一般可以將其設定為足夠高的值來快取表定義，比如4096，這並不會耗費什麼資源。預設的256太小了。

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。