迪B課堂 | 匪夷所思的「too many connections」案例解析

• 2019 年 11 月 20 日
• 筆記

【迪B課堂】為騰訊雲數據庫高級產品經理迪B哥開設的面向數據庫開發者、數據庫運維人員、雲端運維人員的系列培訓課程，旨在幫助大家從入門到精通學習和使用數據庫。《我說》為迪B課堂的答疑係列，3分鐘幫您解決數據庫日常運維過程中的小難題。

本期為迪B課堂特刊【MySQL經典案例解析系列】第一期。搜索關注「騰訊雲數據庫」官方微信，回復「迪B課堂」，即可查看歷史十期迪B課堂教程~

一、故障情況

迪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掉可疑連接

搜索關注「騰訊雲數據庫」官方微信，回復「迪B課堂」，即可查看歷史十期迪B課堂教程~

往期推薦

（點擊圖片即可跳轉閱讀）

瘋狂11.11

11月1日-12月2日， MySQL低至2.5折起，SQL Server 2折起，Redis2.5折起，參與每天5場秒殺，超低價格購買數據庫產品。企業新用戶及個人新用戶可領取千元代金券，企業版最高3200元代金券（滿8000可用）；個人最高1500元代金券（滿3750可用）。

↓↓點擊閱讀原文拼手速啦~