887
MySQL運維實戰（三）之 too many connection
too many connection 我們簡稱：TMC
一、什麼是too many connection
- 重要參數
max_connections : The maximum permitted number of simultaneous client connections
允許的最大的鏈接數，如果超過這個數值，則會報：ERROR 1040 (HY000): Too many connections
max_user_connections: The maximum number of simultaneous connections permitted to any given MySQL user account
允許的每個用戶最大鏈接數，如果超過這個數值，則會報： ERROR 1203 (42000): User dba already has more than 'max_user_connections' active connections
一般這樣的報錯隻會出現在業務機器上，並不會在DB server層報錯，這樣的話DBA就無法真正感知到錯誤，
MySQL也非常貼心的推出了一個status供DBA查看：Connection_errors_max_connections
Connection_errors_max_connections : The number of connections refused because the server max_connections limit was reached.
細心的同學就會發現：那如果出現'max_user_connections' 的報錯，就無法發現啦，這塊目前我還沒找到對應status
二、什麼情況下會發生too many connection
- slow query 引起
1. 真正的slow: 該query的確非常慢
2. 偽裝的slow: 該query本身並不慢，是受其它因素的影響導致
- sleep 空連接 引起
1. 沒有任何query，隻是sleep, 這種情況一般是代碼裏麵沒有主動及時釋放鏈接導致。
三、實戰案例
3.1 sleep 空鏈接引起的TMC
- 原因
由於代碼沒有主動及時的釋放鏈接，那麼在db server中存在大量的sleep鏈接，一旦超過max_connections則報錯。
- 解決方案
1. 遇到這樣的報錯，如果沒有及時解決，則會導致後麵的業務都一直連不上數據庫，影響麵很大。
2. 所以，我們第一件事情必須是保護數據庫，kill掉這些sleep鏈接。關於kill這件事，又有很多技巧可以談
2.1 如果是人工kill，這簡直無法完成這樣艱巨的任務，因為業務會時刻產生這樣的sleep鏈接，有無盡頭
2.2 如果自己寫腳本，沒秒去kill，當然可行。但是我們卻碰到過非常極端的情況，那就是MySQL無法響應你的kill請求。
2.3 所以，這裏還有一個更加靠譜的方案就是：設置wait_timeout, 它會自動幫你完成這項龐大且艱巨的任務，且一定可以kill掉
3. 完成上麵幾個步驟之後，隻能保證你的數據庫不會被壓到，且你有機會登陸進去做一些管理事情，但是要徹底解決還必須讓業務方處理這些sleep鏈接。
3.1 業務團隊排查沒有釋放鏈接的原因。
3.2 通常，如果可以，DBA協助業務方提供TMC期間top ip，讓業務方排查服務哪裏異常。
4. 啟用thread_pool功能可能可以解決這個問題，但是由於種種原因沒有使用
4.1 MySQL官方社區版不支持
4.2 無法解決slow query引起的TMC
4.3 可能因為該組件導致其本身的問題
3.2 slow query 引起的TMC
3.2.1 先來說說真正的slow query吧
一般這種情況，也非常清晰明了，找到它，優化它，當然前提是你的數據庫還活著。
我們通常有SQL防火牆保護，大大降低了這樣的風險。預知SQL防火牆為何物，且聽下回分享。
3.2.2 偽裝的slow query
好了，終於開始介紹這種最難的故障場景。
難點就是：因為它不是真正的slow，優化點難以尋找，所謂對症下藥，就是要找到對應的症狀是難點。
廢話不多說，這裏介紹下前一段時間遇到的一次真實的案例，一直想寫沒時間來著。
3.2.2.1 故障症狀
1. too many connection error
2. threads_runnig 非常多
3. 幾乎找不到有問題的query，沒有明顯慢的query
4. 幾乎任何語句都變得非常慢
5. 服務器io壓力並不大
3.2.2.2 故障分析
* show engine innodb status\G 統計的結果
427 not started sleeping before entering InnoDB
63 not started starting index read
27 not started committing
21 ACTIVE (PREPARED) 1 sec
14 ACTIVE 1 sec preparing
10 not started inserting
9 ACTIVE 1 sec inserting
5 not started estimating records in index range
4 ACTIVE 0 sec inserting
3 COMMITTED IN MEMORY committing
3 ACTIVE 0 sec committing
2 ACTIVE (PREPARED) 1 sec committing
2 ACTIVE 1 sec fetching rows
2 ACTIVE 1 sec committing
1 ACTIVE 1 sec updating or deleting
1 ACTIVE 1 sec starting index read
1 ACTIVE 0 sec starting index read
1 0 sec committing
3.2.2.3 重要參數詳解
官方文檔的解釋我不多說，這裏簡單介紹下自己的理解
innodb_thread_concurrency : 進入innodb存儲引擎的線程數量，如果數量滿了，就要排隊
innodb_thread_sleep_delay : 排隊等候進入innoDB的時候需要睡眠多長時間
innodb_adaptive_max_sleep_delay : 設置一個自適應的最大睡眠時間
innodb_concurrency_tickets: 一旦進入innoDB，就會獲取一個票據tickets，在票據期間可以隨意進入innoDB不需要排隊，如果用完了，理論上則要排隊（實測後發現並不是嚴格這套機製）
3.2.2.4 測試故障重現
- 表結構
dba:lc> show create table t_short;
+---------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------+
| t_short | CREATE TABLE `t_short` (
`id` int(11) DEFAULT NULL,
`name` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
dba:lc> show create table t_short_tmp;
+-------------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+--------------------------------------------------------------------------------------------------------------+
| t_short_tmp | CREATE TABLE `t_short_tmp` (
`id` int(11) DEFAULT NULL,
`name` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dba:lc> show create table t_long_tmp;
+------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------+
| t_long_tmp | CREATE TABLE `t_long_tmp` (
`id` int(11) DEFAULT NULL,
`name` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dba:lc> select count(*) from t_short;
+----------+
| count(*) |
+----------+
| 223133 |
+----------+
1 row in set (0.16 sec)
dba:lc> select count(*) from t_short_tmp;
+----------+
| count(*) |
+----------+
| 4462660 |
+----------+
1 row in set (3.06 sec)
dba:lc> select count(*) from t_long_tmp;
+----------+
| count(*) |
+----------+
| 4462660 |
+----------+
1 row in set (1.98 sec)
- 關鍵參數設置
set global innodb_thread_concurrency = 1; --方便模擬
- 測試用例, 三個語句開始執行時間不差1秒。
[session 1]
select * from t_long_tmp group by name
[session 2]
select * from t_short_tmp group by name
[session 3]
insert into t_short_tmp select * from t_short
- 跟蹤結果
dba:(none)> select trx_id,trx_mysql_thread_id,trx_state,trx_operation_state,trx_query,trx_concurrency_tickets,trx_weight,trx_started,now() from information_schema.innodb_trx;
+-----------------+---------------------+-----------+---------------------------------+-----------------------------------------------+-------------------------+------------+---------------------+---------------------+
| trx_id | trx_mysql_thread_id | trx_state | trx_operation_state | trx_query | trx_concurrency_tickets | trx_weight | trx_started | now() |
+-----------------+---------------------+-----------+---------------------------------+-----------------------------------------------+-------------------------+------------+---------------------+---------------------+
| 142200009 | 34 | RUNNING | inserting | insert into t_short_tmp select * from t_short | 3258 | 8374 | 2017-10-24 17:24:16 | 2017-10-24 17:24:17 |
| 421876372057712 | 18 | RUNNING | sleeping before entering InnoDB | select * from t_long_tmp group by name | 0 | 0 | 2017-10-24 17:22:20 | 2017-10-24 17:24:17 |
| 421876372056800 | 20 | RUNNING | sleeping before entering InnoDB | select * from t_short_tmp group by name | 0 | 0 | 2017-10-24 17:23:29 | 2017-10-24 17:24:17 |
+-----------------+---------------------+-----------+---------------------------------+-----------------------------------------------+-------------------------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)
- 總結
1. 通過以上測試和結果分析得出：當query超過innodb_thread_concurrency時，其餘query會等待，及時這樣的query非常快，也還是會等待，這就是所謂的偽裝的slow query
2. 通過trx_started，now（）分析得出：這些query直接的切換輪詢並不是真正意義上的平均公平分配，裏麵有一套自己的自適應算法，這裏麵我沒有深究下去，有興趣的同學可以繼續了解源碼。
3. 既然真正的原因找到，那麼解決方案也就很快出來，那就是讓並發線程少一點，通過我們的omega平台可以很方便的得出這段時間哪些query和connect最多，那麼協助業務一起溝通業務場景和優化方案，問題得到解決。
最後更新：2017-10-25 19:03:28