MySQL最佳化之連線最佳化

上文MySQL最佳化之快取最佳化 這篇文章中提到了一個很重要的概念，就是show variables是用來表示系統編譯或者配置在my.cnf中的變數值。而show status則稱之為狀態值，顯示的是當前服務例項執行所具有的狀態資訊，是一個動態改變的值。因此常用來觀測當前MySQl的執行是否正常，如果不正常那麼依靠調整靜態引數來提高MySQL的效能。所以明白這兩個概念的不同，是後面調優的基礎。

MySQL 連線最佳化

記得有一次在公司內部連線MySQL的時候，總是連線不上。找到DBA後，經過排查原因，是當前MySQL連線數滿了，經過調整後，解決了問題。引發連線數過多的錯誤原因一般有兩個，第一的確是有很多人在連線MySQL，造成連線數用完。第二種是max_connections值過小。

1、連線引數（show variables）

?

mysql> show variables like '%connect%';+-----------------------------------------------+-----------------+| Variable_name                 | Value      |+-----------------------------------------------+-----------------+| character_set_connection           | utf8      || collation_connection             | utf8_general_ci || connect_timeout                | 10       || disconnect_on_expired_password        | ON       || init_connect                 |         || max_connect_errors              | 100       || max_connections                | 151       || max_user_connections             | 0        || performance_schema_session_connect_attrs_size | 512       |+-----------------------------------------------+-----------------+

max_connections是指MySQL服務例項能夠同時接受的的最大併發連線數。MySQL實際上支援最大連線數加一的演算法，保障當連線數用完的時候，超級管理員依然可以和服務端建立連線，進行管理。

max_user_connections設定指定賬號的最大併發連線數。

max_connect_errors 當某臺非法主機惡意連線MySQL服務端，遭到的錯誤達到設定值後，MySQL會解決來自該主機的所有連線。但執行flush hosts後會清零。

2、連線狀態(show status)

有一點需要注意的，變數值（show variables）是以小寫字母開頭的，而狀態值（show status）是以大寫字母開頭。這樣區分有助於記憶和分類

?

mysql> show status like '%connections%';+-----------------------------------+-------+| Variable_name           | Value |+-----------------------------------+-------+| Connection_errors_max_connections | 0   || Connections            | 197  || Max_used_connections       | 2   |+-----------------------------------+-------+

Connection_errors_max_connections 當MySQL的最大併發數大於系統變數（show variables）中max_connections的最大併發數，因此而被拒絕的次數，將會記錄在這個變數裡。如果Connection_error_max_connections值比較大，則說明當前系統併發比較高，要考慮調大max_connections的值。

Connections表示MySQL從啟動至今，成功建立連線的連線數，這個值是不斷累加的。

Max_used_connections表示MySQL從啟動至今，同一時刻併發的連線數，取得是最大值。如果這個值大於 max_connections則表明系統經常處於高併發的狀態，應該考慮調大最大併發連線數。

3、連線執行緒引數（thread variabls and status）

?

mysql> show variables like 'thread%';+--------------------+---------------------------+| Variable_name   | Value           |+--------------------+---------------------------+| thread_cache_size | 9             || thread_concurrency | 10            || thread_handling  | one-thread-per-connection || thread_stack    | 262144          |+--------------------+---------------------------+

thread_cache_size 設定連線執行緒快取的數目。這個快取相當於MySQL執行緒的快取池（thread cache pool）,將空閒的連線執行緒放入連線池中快取起來，而非立即銷燬。當有新的連線請求時，如果連線池中有空閒的連線，則直接使用。否則要重新建立執行緒。建立執行緒是一個不小的系統開銷。MySQL的這部分執行緒處理和Nginx 的執行緒處理有異曲同工之妙，以後介紹Nginx的執行緒處理時，會拿來做對比。

thread_handling 預設值是： one-thread-per-connection 表示為每個連線提供或者建立一個執行緒來處理請求，直至請求完畢，連線銷燬或者存入快取池。當值是no-threads 時，表示在始終只提供一個執行緒來處理連線，一般是單機做測試使用的。

thread_stack stack 是堆的意思，由PHP 程式詳解這篇部落格，知道程式和執行緒都是有唯一的ID的，程式的ID系統會維護，二執行緒的ID,則由具體的執行緒庫區維護，當程式或者執行緒休眠的時候，程式的上下文資訊要在記憶體中開闢出一塊區域，儲存程式的上下文資訊，以便於迅速喚醒程式。預設為MySQL的每個執行緒設定的堆疊大小為：262144/1024=256k

檢視執行緒狀態資訊

?

mysql> show status like 'Thread%';+-------------------+-------+| Variable_name   | Value |+-------------------+-------+| Threads_cached  | 1   || Threads_connected | 1   || Threads_created  | 2   || Threads_running  | 1   |+-------------------+-------+

Thread_cached 當前執行緒池的執行緒數

Thread_connected 當前的連線數

Thread_cached: 當前連線執行緒建立數, 如果這個值過高，可以調整threadcachesize 也就是調整執行緒快取池的大小。

Thred_runnint: 當前活躍的執行緒數。

連線請求堆疊

MySQL在很短的時間內，突然收到很多的連線請求時，MySQL會將不能來得及處理的連線請求儲存在堆疊中，以便MySQL後續處理。back_log引數設定了堆疊的大小，可以透過如下命令檢視：

?

mysql> show variables like 'back_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| back_log   | 80  |+---------------+-------+

連線異常

?

mysql> show status like 'Aborted%';+------------------+-------+| Variable_name  | Value |+------------------+-------+| Aborted_clients | 0   || Aborted_connects | 219  |+------------------+-------+

Aborted_clients MySQL 客戶機被異常關閉的次數。

Aborted_connects 試圖連線到MySQL伺服器而失敗的連線次數。

other

?

mysql> show status like 'Slow%';+---------------------+-------+| Variable_name    | Value |+---------------------+-------+| Slow_launch_threads | 0   || Slow_queries    | 0   |+---------------------+-------+

?

mysql> show variables like 'slow_launch_time';+------------------+-------+| Variable_name  | Value |+------------------+-------+| slow_launch_time | 2   |+------------------+-------+

Slow_lunch_threads 建立執行緒的時間過長，超過slow_launch_time的設定值，則會記錄。

可以透過使用 Connection_error%來檢視連線的錯誤狀態資訊：

?

mysql> show status like 'Connection_error%';+-----------------------------------+-------+| Variable_name           | Value |+-----------------------------------+-------+| Connection_errors_accept     | 0   || Connection_errors_internal    | 0   || Connection_errors_max_connections | 0   || Connection_errors_peer_address  | 0   || Connection_errors_select     | 0   || Connection_errors_tcpwrap     | 0   |+-----------------------------------+-------+

Connection_errors_peer_address 查詢MySQL客戶機IP地址是發生的錯誤數。