PostgreSQLの負荷状況調査

システムの要となるRDBについて 高負荷になってしまい原因を探る時に使うクエリを備忘として残す いざというときにさくっとボトルネックの場所を抽出できるが 実際、本番でこれを使う自体が発生すると、きっと本番はまともに稼動できてないレベルの可能性が高いので 転職活動も同時に行った方がいいのかも...

環境について

・PostgreSQL 9.3以上 ・Linux, Windwosどちらでも同じ(と思う)

問題発生時の切り分け方

・特定のアプリケーションからのアクセスから高負荷になっていないか ・特定のテーブルやファンクションに高負荷になっていないか

というapp側から、DB側から、という２パターンのアプローチで 大体調査を進める事が多いと思うのでそのためのクエリがこちら

テーブル単位でアクセスしている回数を調べる

-- TABLE毎のアクセスを調べる方法
-- 統計なので運用が長いDBだと凄まじい数字が出るので事前に心の準備を行う事
-- トランザクションデータや履歴系の蓄積されがちなデータが普通は上位にくるのだが
-- 思いもよらぬテーブルが上位に来ていると、そこから糸口を探す
SELECT
relname
, coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0) as select_total
, coalesce(n_tup_ins,0) as insert_total
, coalesce(n_tup_upd,0) as update_total
, coalesce(n_tup_del,0) as delete_total
FROM
pg_stat_user_tables
ORDER BY total DESC;

クライアントの接続情報とクエリを調べる

-- PostgreSQL のクライアント接続情報
-- 特定のアプリケーションにbugがあり必要以上に接続したり切断忘れになっている場合
-- 顕著にselect結果に出てくるので、こちらはわかりやすい
-- またSQLの全文も見る事ができるので単純に汚いクエリを修正していくきっかけづくりもこの統計クエリから可能
SELECT
datid
,datname
,pid
,usesysid
,usename
,application_name
,client_addr
,client_hostname
,client_port
,backend_start
,xact_start
-- このquery_startの時間が過去であればあるほど激遅クエリであり
-- 数秒以上たっても終わって無いクエリであれば設計ミスか、実装ミス(呪術クエリ)の
-- どちらかである可能性が高い。またはその両方を兼ねているフルスタック不具合の可能性も高い。筆者は後者の地獄の不具合だった経験がある。
,query_start
,state_change
,waiting
,state
-- データ量が多いのでコメント
-- ,query
FROM
pg_stat_activity
WHERE
datname = '<target_database_name>'
AND pid <> pg_backend_pid();

-- PostgreSQL の特定のクエリをpid指定で停止させる方法
-- いわゆるkillコマンドのようなものと思っている
SELECT pg_cancel_backend(1111);

-- または

SELECT pg_terminate_backend(1111);

そもそもこれらのクエリにお世話になる時は、恐らくシステムはほぼまともに動いていないので SQLよりも辞表を先にかいた方がいいかも...

今日はとりあえずここまで...