這篇文章主要講解了“怎么解決MySQL too many connections問題”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“怎么解決MySQL too many connections問題”吧！
迪B哥在某個愜意的周末接到連續數據庫的告警，告警信息如下：
看到too many connection的報錯信息，基本上可以把問題定位在：
（1）機器負載飆升，導致SQL執行效率下降，導致連接推積；
（2）業務訪問量突增（或者有SQL注入現象），導致連接數打滿；
（3）出現“死鎖”或者鎖競爭嚴重，導致大量SQL堆積。
（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掉了這個進程，果然推積的請求瞬間就執行完成了，也之間印證了剛剛上述推論。
在與開發同學溝通過程中，開發同學說庫中是myisam表所以不會主動開啟事務，在代碼里也沒有設置autocommit=0的代碼，那么根本原因在哪？
當我們定位到這臺服務器上的請求都是來自python的定時腳本，使用python 操作mysql的時候，使用了其pymysql模塊，但是在進行插入操作的時候，必須使用受到提交事務。Python的pymysql模塊默認是會設置autocommit=0的。
讓我們來對比一下其他同樣使用python訪問的正常連接請求，再斷開前都會手動的commit。
找到原因后有思考了下，是不是可以在建連后就設置autocommit=1呢？這樣對于之后新變更的SQL就不要再考慮到手動commit的事情了，可以通過在初始化連接池的時候，對每一個連接進行設置，即
（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仍然會被堵住
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)
（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問題這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
