共計 3725 個字符，預計需要花費 10 分鐘才能閱讀完成。
這篇文章主要講解了“怎么解決 MySQL too many connections 問題”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“怎么解決 MySQL too many connections 問題”吧！
一、故障情況
迪 B 哥在某個愜意的周末接到連續數據庫的告警，告警信息如下：
二、艱難的探索過程 1、總體思路
看到 too many connection 的報錯信息，基本上可以把問題定位在：
（1）機器負載飆升，導致 SQL 執行效率下降，導致連接推積；
（2）業務訪問量突增（或者有 SQL 注入現象），導致連接數打滿；
（3）出現“死鎖”或者鎖競爭嚴重，導致大量 SQL 堆積。
2、排查過程
（1）機器的各項性能指標都顯示正常，沒有出現高負載現象，暫時先排除了這種原因；
（2）查看監控信息，發現在連接數打滿的時間點前并沒有訪問量突增的趨勢，同時通過檢查告警信息并沒有發現有注入工單；
（3）最后上到服務器上查看下 SQL 的執行情況：
3.1）查看 show full processlist；
大量的請求都是在“Waiting for table metadata lock”，可以分成三類請求：
A. Select 請求
B. Rename 請求
C. Sleep 請求
3.2）分析 Waiting for table metadata lock
一般來說常見的“Waiting for table metadata lock”會出現在 DDL 操作或者是有未提交的事務上，從 information_schema.processlist 表中，沒有發現有 DDL 操作，而能夠產生 MDL 鎖的操作也只剩下 rename，但是根據 SQL 執行的狀態，rename 操作也是在等待 MDL 鎖，所以 rename 操作應該是被阻塞的操作，而不是產生 MDL 鎖的操作。
接著我們來查看下死鎖和事務的相關指標：
A. show engine innodb status; 中沒有任何死鎖的信息
B. information_schema.innodb_trx、information_schema.innodb_locks、information_schema.innodb_lock_waits 的也沒有任何形式的鎖信息。
現在基本又排除了顯示的死鎖問題，那是從 show full processlist 中也抓不出任何請求，這里就比較疑惑了，當看了下表的結構式，發現這個表是 myisam 引擎的，所以上面的兩種統計信息里面沒有任何值就可以解釋了。
那么其實問題就集中在有未結束的事務上了，這里其實有一個誤區，當時跟開發溝通存在未關閉的事務時，開發一直認為不可能，因為 myisam 表是不支持事務的，只有 innodb 支持事務。但是對于 MDL 鎖來說，5.5 之后引入 MDL 事務級別的鎖不論對 myisam 還是 innodb 都是生效的。
3.3）查看未提交的事務
之后查看了下系統的事務自動提交的變量，autocommit 的值是 ON，那說明如果是事務未提交的話只可能是業務主動的開啟一個事務，而沒有 commit。
為了驗證這個猜想，打開了 general log，在 log 中果然發現，業務在開啟事務后，把 autocommit 的值設為 0 了，導致必須要顯示的 commit 才能提交事務。
這時候我們反過頭來看一下 host 為 10.49.84.70 的連接請求，由于 select 的執行速度很快，而且訪問并不頻繁，所以在抽樣的 show processlist 中，狀態值大部分時間是“Sleep”, 給問題的定位帶來了一些迷惑性的干擾。接著我們 kill 掉了這個進程，果然推積的請求瞬間就執行完成了，也之間印證了剛剛上述推論。
3、問題解決
在與開發同學溝通過程中，開發同學說庫中是 myisam 表所以不會主動開啟事務，在代碼里也沒有設置 autocommit= 0 的代碼，那么根本原因在哪？
當我們定位到這臺服務器上的請求都是來自 python 的定時腳本，使用 python 操作 mysql 的時候，使用了其 pymysql 模塊，但是在進行插入操作的時候，必須使用受到提交事務。Python 的 pymysql 模塊默認是會設置 autocommit= 0 的。
讓我們來對比一下其他同樣使用 python 訪問的正常連接請求，再斷開前都會手動的 commit。
找到原因后有思考了下，是不是可以在建連后就設置 autocommit= 1 呢？這樣對于之后新變更的 SQL 就不要再考慮到手動 commit 的事情了，可以通過在初始化連接池的時候，對每一個連接進行設置，即
三、延伸思考 1、metadata lock
（1）MDL 簡述
為了在并發環境下維護表元數據的數據一致性，在表上有活動事務（顯式或隱式）的時候，不可以對元數據進行寫入操作。因此從 MySQL5.5 版本開始引入了 MDL 鎖（metadata lock），來保護表的元數據信息，用于解決或者保證 DDL 操作與 DML 操作之間的一致性。
對于引入 MDL，其主要解決了 2 個問題，一個是事務隔離問題，比如在可重復隔離級別下，會話 A 在 2 次查詢期間，會話 B 對表結構做了修改，兩次查詢結果就會不一致，無法滿足可重復讀的要求；另外一個是數據復制的問題，比如會話 A 執行了多條更新語句期間，另外一個會話 B 做了表結構變更并且先提交，就會導致 slave 在重做時，先重做 alter，再重做 update 時就會出現復制錯誤的現象。所以在對表進行上述操作時，如果表上有活動事務（未提交或回滾），請求寫入的會話會等待在 Metadata lock wait。
支持事務的 InnoDB 引擎表和不支持事務的 MyISAM 引擎表，都會出現 Metadata Lock Wait 等待現象。一旦出現 Metadata Lock Wait 等待現象，后續所有對該表的訪問都會阻塞在該等待上，導致連接堆積，業務受影響。
（2）常見 MDL 鎖場景
2.1）當前有執行 DML 操作時執行 DDL 操作
2.2）當前有對表的長時間查詢或使用 mysqldump/mysqlpump 時，使用 alter 會被堵住
2.3）顯示或者隱式開啟事務后未提交或回滾，比如查詢完成后未提交或者回滾，DDL 會被堵住
2.4）表上有失敗的查詢事務，比如查詢不存在的列，語句失敗返回，但是事務沒有提交，此時 DDL 仍然會被堵住
2、myisam、innodb 對事務的支持
Myisam 是不支持事務的，innodb 是支持事務的，這個概念其實沒有任何問題，但是這里只的都是對于數據的事務性操作的支持，通過如下簡單的實驗可以很清楚的理解（關于事務的相關概念和解釋就不再贅述了，只是想區別一下 mysiam 不支持事務，但是主動開始事務中對 Myisam 的操作仍然會產生 MDL 鎖）：
在隔離級別為 RC 的情況下：
（1）myisam 表
1.1）CREATE TABLE `tb2` (`a` int(11) DEFAULT NULL ) ENGINE=MyISAM;
1.2）Session 1：
mysql begin ;
mysql insert into tb2(a) value(1);
（在 session2 的 update 之后）
mysql select * from tb2;
+——–+
| a |
+——–+
| 3 |
+——–+
Session 2：
mysql select * from tb2;
+———+
| a |
+———+
| 1 |
+———+
mysql update tb2 set a=3 where a=1;
mysql select * from tb2;
+——–+
| a |
+——–+
| 3 |
+——–+
mysql alter table tb2 add b int(11);
… hangs …
（2）innodb 表
2.1）CREATE TABLE `tb3` (`a` int(11) DEFAULT NULL ) ENGINE=INNODB;
2.2）Session 1：
mysql begin ;
mysql insert into tb3(a) value(1);
Session 2：
mysql select * from tb3;
Empty set (0.00 sec)
3、myisam 表的另一個 BUG
（1）場景
1.1）CREATE TABLE `tb2` (`a` int(11) DEFAULT NULL ) ENGINE=MyISAM;
1.2）Session 1：
mysql begin ;
mysql select * from tb2;
Session 2：
mysql create table if not exists tb2(a int);
… hangs …
1.3）查看 show processlist
Session 1：Sleep
Session 2：Waiting for table metadata lock
（2）解決方式
①session 1 上 commit 或者 rollback
②另外再開一個 session3，kill 掉可疑連接
感謝各位的閱讀，以上就是“怎么解決 MySQL too many connections 問題”的內容了，經過本文的學習后，相信大家對怎么解決 MySQL too many connections 問題這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！
