如何殺掉pg資料庫正在運行的sql
- 2019 年 12 月 18 日
- 筆記
我們在生產環境可能經常遇到長sql，長sql對資料庫的影響還是挺大的，不僅可能對主機資源消耗較大，還可能會阻塞其他sql的正常執行，所以對於長sql我們要尤其注意。一般生產環境都會配置長sql告警，可以根據業務情況調整告警閾值。
那麼在postgresql資料庫如果遇到了長sql告警我們應該怎麼處理呢？我總結一下：一查二看三殺。
一查：
首先我們要定位到是哪條sql引起的告警。登錄pg資料庫查看pg_stat_activity（pgxc架構使用pgxc_stat_activity）視圖，通過query_start欄位查看sql的開始運行時間，state欄位表明了當前sql的狀態，一般有三種：active表示sql活躍正在執行；idle表示當前該連接空閑，上一條sql已經執行完畢，當前沒有sql在執行；idle in transaction表示當前事務還未結束，事務中上一條sql已經執行完畢，當前事務沒有sql在執行，事務中空閑。pg_stat_activity視圖中還有個pid欄位，這個欄位是當前連接的進程/執行緒號，這個我們後面處理的依據。
二看：
二看執行計劃，我們使用explain 命令查看當前sql的執行計劃，確認sql執行計劃是否正確，是否走到了索引，是否走到了正確的索引，如果沒有走正確索引，我們可以對錶進行分析，重新收集統計資訊。如果想得到更精確的資訊，可以使用explain performance命令來查看更詳細的執行計劃資訊，值得注意的是explain不會真正執行sql，而explain performance會真正執行sql。
三殺：
在定位sql後，我們往往會跟業務確認這條sql是否能夠殺掉，當業務確認後，我們就需要殺掉該sql。
pg資料庫殺掉某條sql有三種方法，也可以稱為三板斧：cancel->terminate->kill
①使用pg_cancel_backend(pid)殺掉某條sql，這個是溫柔的殺，向後台發送sigint訊號，關閉當前後台進程，用戶只能關閉自己的後台進程，事務回滾。
②使用pg_terminate_backend(pid)殺掉某條sql，這個是強殺，向後台發送sigterm訊號，關閉當前後台進程，需要有超級用戶許可權，超級用戶可以關閉所有後台進程，事務回滾。
③這裡為什麼會有第三種殺法呢？可能大家遇到過，使用pg_cancel_backend殺不掉的進程，但是其實pg_terminate_backend有時也無法殺掉某條sql，筆者在生產環境遇到過，這時我們可能會抓一下該連接的堆棧，然後我們可能想儘快殺掉該sql，問題原因後面再分析，這時我們就要從作業系統層面使用kill命令來殺掉連接了。通過上面查到的pid，在作業系統上ps -ef |grep pid查看當前連接的狀態，然後kill -9 pid殺掉該連接。
postgres=# select pid,query_start,state,query from pg_stat_activity where state='active'; pid | query_start | state | query -------+-------------------------------+--------+-------------------------------------------------------------------------------- 5001 | 2019-08-13 12:45:16.652909+08 | active | select * from perf_analyse; 18876 | 2019-08-13 12:45:19.019691+08 | active | select pid,query_start,state,query from pg_stat_activity where state='active'; (2 rows) postgres=# q postgres@xxx:~> ps -ef |grep 5001 postgres 5001 23550 64 12:45 ? 00:00:13 postgres: postgres postgres [local] SELECT postgres 7677 6228 0 12:45 pts/3 00:00:00 grep --color=auto 5001 postgres@xxx:~> kill -9 5001
上面三板斧在生產環境中還是建議從前到後執行，雖然第三種方法最直接有效，但是畢竟是生產環境，萬一觸發什麼bug也不知道，所以迫不得已再使用。
好吧，加油吧。
