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

目錄

• 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太小了。

總結

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