MySQL連線數過多導致服務無法正常執行
【Mysql併發和連線數】
mysql併發數:netstat -ant |find /i "ESTABLISHED" |find /i ":3306 " /c
mysql連線數:select count(*) from information_schema.processlist;
或者:mysql -uroot -h127.0.0.1 -P3020 -e “show processlist”|wc -l >/tmp/1.log
或者: show full processlist;
show variables like 'max_connections';
./mysql -uroot -p1234.com -e 'show status' | grep -i Threads
show status like 'Threads%';
MySQL繁忙的時候執行show processlist,會發現有很多行輸出,每行輸出對應一個MySQL連線。怎麼診斷髮起連線的程式是哪個?它當前正在幹嘛呢?
首先,需要通過TCP Socket而不是Unix Socket連線MySQL,這樣在show processlist的輸出中就會有來源埠號。
然後殺掉執行緒
可以先授權某一個使用者,然後隔一定時間(30s)執行類似 show status 的命令,探針進行抓取,然後這些資料進行統計收集
【案例分析】遇到mysql超出最大連線數,相信不少人第一反應就是檢視mysql程式,看有沒有慢查詢,當然這個做法是完全正確的!
但是很多時候真正的問題不在這裡。今天有遇到同樣的問題,一味檢視mysql程式和慢查詢日誌,無果。
後來老大提點了一下,檢視一下nginx日誌,發現有一兩個訪問執行時候比較長,然後使用top命令檢視了一下伺服器負載,驚了,居然超高!
最後發現原來有一臺web分流主機掛了,導致另外幾臺web主機負載增高,從而導致了php-fpm的執行效率降低。
那麼這跟mysql有什麼關係呢?原因很簡單,因為php執行時間過長,mysql連線遲遲未釋放,就會導致連線數過多出現。
最後總結:其實很多時候,一個問題的根本原因並不是那麼直接的呈現出來,需要自己去跟蹤。
有一句很實用的話:遇到問題先查日誌(mysql、php、nginx、tomcat等)
===============================================================
排查連線數過多的方法
當使用者收到連結數告警時,意味著連線數即將達到該例項的上限。如果例項的連線數超過了例項規定的連線數,將無法建立新的連線,這個時候會影響使用者的業務;
Mysql 的連線通常是一個請求佔用一個連線,如果該請求(update,insert,delete,select)長時間沒有執行完畢,則會造成連線的堆積,迅速的消耗完資料庫的連線數,這個時候技術支援人員就要登入資料庫進行排序,看看到底是那些sql 佔用了連線;
問題排查步驟:
1 、檢視例項配置:
可登入RDS控制檯“詳情與配置”檢視例項額定連結數,我們假設最高支援1500個連結
2、 檢視當前的連線數:
1)可登入RDS控制檯“效能監控”檢視例項當前連結數。
2)或者登入資料庫查詢當前連線,可以使用同步賬號或者使用者的業務賬號登入資料庫,執行show processlist;
[root@r41d05036.xy2.aliyun.com ~]# mysql -uroot -h127.0.0.1 -P3020 -e “show processlist”|wc -l
1262
可以看到該例項已經有1262 個連線
3、排查是什麼動作佔用了這些連線:
[root@r41d05036.xy2.aliyun.com ~]# myql -uroot -h127.0.0.1 -P3018 -e “show full processlist”>/tmp/1.log
root@r14d11038.dg.aliyun.com # more /tmp/1.log
615083 my_db 223.4.49.212:54115 my_db Query 100 Sending data
INSERT INTO tmp_orders_modify (oid, tid, seller_id, status
, gmt_create, gmt_modified)
SELECT oid, tid, seller_id, status
, gmt_create, gmt_modified
FROM sys_info.orders WHERE
gmt_modified < NAME_CONST(‘v_last’,_binary’2012-12-24 10:33:00’ COLLATE ‘binary’) AN
D gmt_modified >= NAME_CONST(‘v_curr’,_binary’2012-12-24 10:32:00’ COLLATE ‘binary’)
621564 my_db 223.4.49.212:46596 my_db Query 3890 sorting result
insert into tmp_trades(sid, d, h, tc, tm, tp, ic, new_tp, old_tp)
select a.seller_id as sid,
…………..
from orders_1 as a where seller_id =1 and is_detail = ‘1’
and created < date_format(‘2012-12-24 10:35:00’, ‘%Y-%m-%d %H:00:00’)
and gmt_create < date_format(‘2012-12-24 10:40:00’, ‘%Y-%m-%d %H:%i:00’)
and gmt_create >= date_format(‘2012-12-24 10:35:00’, ‘%Y-%m-%d%H:%i:00’)
group by d, h
order by d
……………….此處省略其他sql
4、分析連線佔用的原因:
可以看到資料庫中有長時間沒有執行完成的sql,一直佔用著連線沒有釋放,而應用的請求一直持續不斷的湧入資料庫,這個時候資料庫的連線很快就被使用完;所以這個時候需要排查為什麼這些sql 為什麼長時間沒有執行完畢,是索引沒有建立好,還是sql執行耗時嚴重。
第一條sql:
INSERT INTO tmp_orders_modify (oid, tid, seller_id, status
, gmt_create, gmt_modified)
SELECT oid, tid, seller_id, status
, gmt_create, gmt_modified
FROM sys_info.orders WHERE
gmt_modified < NAME_CONST(‘v_last’,_binary’2012-12-24 10:33:00’ COLLATE ‘binary’) AN
D gmt_modified >= NAME_CONST(‘v_curr’,_binary’2012-12-24 10:32:00’ COLLATE ‘binary’)
是使用者從sys_info 資料庫中拉取訂單到自己的業務庫中那個,但是在orders 表上沒有gmt_modified 的索引,導致了全表掃描;(更加詳盡的排查方法可以參考:為什麼我的RDS慢了);
第二條sql:
看到這條sql 正在進行sorting 排序,為什麼導致sql 長時間sorting,通常情況下為排序的結果集太大導致排序不能在記憶體中完成,需要到磁碟上排序,進而導致了效能的下降;解決的辦法就是降低排序的結果集,常用的手段是利用索引的有序性,消除排序,或者建立適當的索引減小結果集;我們可以看到第二條sql 的排序欄位非常的複雜,但是我們可以看到查詢的時間範圍是很短,只有5 分鐘的時間間隔,這個時候就可以在gmt_create上建立一個索引,過濾掉大部分的記錄:
Alter tale order_1 add index ind_order_gmt_create(gmt_create);
(該使用者對orders 進行了分表,大概有50 多張分表需要新增gmt_create 欄位的索引);
5、經過上面兩步的優化後,使用者例項恢復正常:io 情況和connection 情況,可再次登入RDS控制檯檢視連線數。
【對於Windows作業系統的】原因:windows系統BUG,微軟官網有詳細介紹,系統併發過大,連線數過多,部分socket連線無法釋放關閉,而持續請求又導致無法釋放的socket連線不斷積壓,最終導致No buffer space available
1.對於windows環境,可通過修改登錄檔進行配置:
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
新增一個DWORD型別的值TcpTimedWaitDelay,值可以根據實際情況配置(可以配置十進位制30秒)。
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TCPIP\Parameters
新增一個DWORD型別的值MaxUserPort ,值可以根據實際情況配置(5000-65534)。
2.重啟伺服器
備註:
TcpTimedWaitDelay:確定 TCP/IP 可釋放已關閉連線並重用其資源前,必須經過的時間;預設為240秒,最低為30秒,最高為300秒。建議設為30秒。
MaxUserPort :確定在應用程式從系統請求可用使用者埠時,TCP/IP 可指定的最高階口號;預設值:無 建議值:至少十進位制 32768
----------------------------------------------------
也可以將以下兩行復制到空白txt中,儲存關閉txt,然後再修改txt為bat,這樣就變成了一個批處理檔案,雙擊批處理即可完成上面所說的登錄檔裡的新增
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters" /v "TcpTimedWaitDelay" /t REG_DWORD /d 30 /f
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters" /v "MaxUserPort" /t REG_DWORD /d 65534 /f
導致原因:
出現這種錯誤明顯就是 mysql_connect 之後忘記 mysql_close;
當大量的connect之後,就會出現Too many connections的錯誤,mysql預設的連線為100個,而什麼情況下會出現這種錯誤呢?
正常的mysql_connect 之後呼叫 mysql_close()關閉連線
但在連線錯誤時,會者mysql_real_query()出現錯誤退出時,可能忘記mysql_close();
所以在程式return 之前一定要判斷是否close(),最穩妥的方法就是在寫任何函式時都只有一個出口!
附錄模擬mysql連線增多的指令碼
#!/bin/bash
set j=2
while true
do
let "j=j+1"
/usr/local/mysql/bin/mysqlslap -a -c 500 -i 10 -uroot -p1234.com
done
1、監視資源使用情況(系統監視器) | Microsoft Docs https://docs.microsoft.com/zh-cn/sql/relational-databases/performance-monitor/monitor-resource-usage-system-monitor
2、Cloud Insight - IT綜合運維管理平臺|資料庫監控|IT基礎設施監控 - OneAPM http://www.oneapm.com/ci/feature.html
【參考資料】
1、RDS for MySQL 連線數滿情況的處理_MYSQL使用_技術運維問題_雲資料庫 RDS 版-阿里雲 https://help.aliyun.com/knowledge_detail/41714.html
2、【Mysql】連線數過多,應急處理方法-yhdmy-ITPUB部落格 http://blog.itpub.net/26148431/viewspace-2149247/
相關文章
- iptables導致無法遠端連線oracleOracle
- 統計資訊過舊導致SQL無法執行出來SQL
- 關於jboss最大執行緒數滿導致服務停止執行緒
- MySQL sleep連線過多 解決辦法MySql
- 解決Oracle 11gR2 空閒連線過多,導致連線數滿的問題Oracle
- Workstation服務無法啟動導致無法訪問檔案伺服器伺服器
- HttpClient引發的執行緒數過多導致應用崩潰HTTPclient執行緒應用崩潰
- NAMES.DIRECTORY_PATH配置不當導致無法連線oracleOracle
- 【線上等】關於tomcat最大執行緒數滿導致服務停止Tomcat執行緒
- 字元校驗集問題導致索引無法正常使用字元索引
- 大量"library cache lock"事件導致資料庫無法連線事件資料庫
- mysql 遠端無法連線MySql
- 【Mysql】連線數過多,應急處理方法MySql
- mysql怎麼連線服務MySql
- DDL觸發器設定導致DDL無法執行(二)觸發器
- DDL觸發器設定導致DDL無法執行(一)觸發器
- vim 編輯報錯導致無法正常退出和編輯
- MySQL 預插入的資料條數過多導致異常MySql
- MySQL時區導致無法產生表MySql
- OS臨時埠耗盡導致無法建立資料庫連線資料庫
- 本地無法連線Mysql的原因MySql
- Node.js 服務連線 MySQLNode.jsMySql
- ORA-16191 錯誤導致無法連線DATA GUARD環境
- oracle可執行檔案s位導致的Cluster資源組無法正常啟動的問題解決Oracle
- 執行wget命令,出錯:無法建立 SSL 連線。wget
- Nginx/Apache服務連線數梳理NginxApache
- docker-執行mysql服務DockerMySql
- 達夢資料庫dmap服務無法正常啟動資料庫
- 一個簡單的MySQL引數導致的連線問題解惑MySql
- CloseableHttpClient 連線超時導致XxlJob排程阻塞,影響排程任務的執行HTTPclient
- 應用使用JNDI,資料庫無法連線,導致的程序無法啟動問題處理資料庫
- [Windows無法連線到 System Event Notification Service服務]解決方案Windows
- WAMP無法連線mysql資料庫MySql資料庫
- [zt] oracle可執行檔案s位導致的Cluster資源組無法正常啟動的問題解決Oracle
- 記錄連線數導致警報失效,連線池少問題
- Oracle 12.1業務使用者使用序列時報ORA-600導致業務無法正常進行Oracle
- windows 7 /vista 內網正常,外網無法連線Windows內網
- 檢視mysql連線數 sql語句執行時間MySql