稼動統計情報を活用しよう(2)
NTT オープンソースソフトウェアセンタ 笠原 辰仁
稼動統計情報を取得してみよう
では、実際に稼動統計情報を取得してみましょう。稼動統計情報は、PostgreSQLのテーブルやビューの形で提供されています。pg_stat_* という名称のテーブル/ビューがそれらに当たります。そのため、取得にはSQLを用います。なお、psqlの\dコマンドなどでpg_stat_*のビュー定義を見てみると、pg_stat_get_*() 関数で各種情報が取得されていることが分かると思います。稼動統計情報を直接取得するには関数を使うのですが、それをビュー経由でユーザが閲覧できるようになっています。本項では、pg_stat_*で提供されているビューを読み解いていくことにします。
それでは、前ページの冒頭で紹介した情報について具体的に解説していきます。下記は稼動統計情報の中でも多用される情報ですので、覚えておくと便利です。
DBレベルのスループット/エラー/キャッシュヒット率の確認
DBレベルでのアクティビティ状況を把握しておくことで、マクロな視点での問題把握が可能です。問題の発生有無をざっくりとチェックするのに有用です。また、管理しているDBがどれくらいの処理を行っているかの統計的な情報を得るのにも便利です。
- スループット/エラーの確認
- pg_stat_database の xact_commit (コミット数) と xact_rollback (ロールバック数) を利用します。定期的にこれらの情報を取得し、(差分÷情報取得時間間隔) として「単位時間あたりのコミット数」を計算しましょう。システム全体のスループットを把握できます。
=# SELECT datname, xact_commit, xact_rollback FROM pg_stat_database; datname | xact_commit | xact_rollback -----------+-------------+--------------- template1 | 0 | 0 template0 | 0 | 0 postgres | 101216 | 1
- キャッシュヒット率の確認
- pg_stat_databaseのblks_readとblks_hitを利用します。blks_hitは共有メモリにあった(キャッシュヒットした)ブロックの読み込み回数、blks_readは共有メモリに無かった(キャッシュヒットしなかった)ブロックの読み込み回数です。ここでいうブロックとは、PostgreSQLの最小のI/O単位である8kB(デフォルトの場合)の塊を指します。ほぼ同義で"ページ"とも言われます。blks_hit*100/(blks_hit+blks_read) の計算でキャッシュヒット率を算出します。メモリ等のチューニングでは、これをなるべく100に近づけていくことを目指します。
-- division by zero エラー (0割り) に注意しましょう
=# SELECT datname,
round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio
FROM pg_stat_database WHERE blks_read > 0;
datname | cache_hit_ratio
----------+-----------------
postgres | 99.00
なお、キャッシュヒット率についてですが、blks_readの数値はディスクからブロックをリードしたケースの他、「PostgreSQLの共有メモリにはなかったが、カーネルのキャッシュやディスクのバッファにはデータがあった」ケースも含みます。そのため、メモリ上だけで処理が行えており、キャッシュヒット率が低くてもI/O負荷が低く、性能に問題が無いケースもあると思います。そのため、このキャッシュヒット率はPostgreSQLの共有メモリに、使用頻度の高いデータが十分に乗っているか？という目安として使うと良いでしょう。これは、下記のテーブルやインデックスにも同じことが言えます。
テーブル/インデックスのキャッシュヒット率の確認
テーブル/インデックス単位でのキャッシュヒット率を確認します。特に、インデックスやアクセス頻度の高いテーブルのキャッシュヒット率は100に近いことが理想です。極端に低いキャッシュヒット率のテーブルやインデックスがいないかをチェックしましょう。
- 各テーブルのキャッシュヒット率の確認
- pg_statio_user_tables の heap_blks_hit と heap_blks_read を利用します。キャッシュヒット率の算出方法、及び意味合いは pg_stat_database と同じです。テーブルによっては、TOASTテーブル(各テーブルが持つ、長大なレコードを格納するための別テーブル)を持つものがあり、その場合はTOASTテーブルについてのキャッシュヒット率を toast_blks_hit と toast_blks_read から同様に求められます。
=# SELECT relname,
round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2)
AS cache_hit_ratio FROM pg_statio_user_tables
WHERE heap_blks_read > 0 ORDER BY cache_hit_ratio;
relname | cache_hit_ratio
------------------+-----------------
pgbench_accounts | 97.00
pgbench_tellers | 99.00
pgbench_history | 99.00
pgbench_branches | 99.00
- インデックスのキャッシュヒット率の確認
- pg_statio_user_indexesのidx_blks_hitとidx_blks_readを利用します。キャッシュヒット率の算出方法、及び意味合いはpg_stat_databaseと同じです。
=# SELECT relname, indexrelname,
round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2)
AS cache_hit_ratio FROM pg_statio_user_indexes
WHERE idx_blks_read > 0 ORDER BY cache_hit_ratio;
relname | indexrelname | cache_hit_ratio
------------------+-----------------------+------------------
pgbench_tellers | pgbench_tellers_pkey | 90.00
pgbench_branches | pgbench_branches_pkey | 99.00
pgbench_accounts | pgbench_accounts_pkey | 99.00
大量の行を読み取っている表スキャンの確認
表スキャンは、しばしば性能に大きな影響を与える処理です。特に、SQLの実行計画の変化や、インデックスの張り忘れによる意図しない表スキャンの発生は、早期に発見して対策を行う必要があります。1回のスキャンで少量(数10～数100件)の行を読み取っているのであれば問題はありませんが、数万～数十万の行を読み取っているものがあれば注意が必要です。
- 表スキャンあたりの読み取り行数の確認
- pg_stat_user_tables の seq_tup_read (表スキャンで読み取られた総行数) と seq_scan (表スキャン回数) を利用します。seq_tup_read/seq_scan とすることで、表スキャン1回あたりにどれだけの行を読んでいるかが確認できます。
=# SELECT relname, seq_scan, seq_tup_read,
seq_tup_read/seq_scan AS tup_per_read FROM pg_stat_user_tables
WHERE seq_scan > 0 ORDER BY tup_per_read DESC;
relname | seq_scan | seq_tup_read | tup_per_read
------------------+----------+--------------+--------------
pgbench_accounts | 1 | 100000 | 100000
pgbench_tellers | 153613 | 1000010 | 6
pgbench_branches | 35659 | 16461 | 0
ガベージを大量に抱えているテーブルの確認 (ver8.3～)
ver8.3 から、テーブル内の有効な行数と、ガベージとなった行数をモニタリングできるようになりました。VACUUM や HOT が適切に機能しているか、またガベージによるDBの肥大化が進行していないかをチェックする目安として有用です。(参考: HOTの効果(4))
- ガベージの量の確認方法
- pg_stat_user_tablesのn_live_tup(有効な行数)とn_dead_tup(ガベージとなった行数)を利用します。下記の様にガベージの行数を割合とテーブルのサイズを併せて出力すると、ガベージがより具体的な量として把握できます。
=# SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup*100/(n_dead_tup+n_live_tup), 2) AS dead_ratio,
pg_size_pretty(pg_relation_size(relid)) FROM pg_stat_user_tables
WHERE n_live_tup > 0 ORDER BY dead_ratio DESC;
relname | n_live_tup | n_dead_tup | dead_ratio | pg_size_pretty
------------------+------------+------------+------------+----------------
pgbench_accounts | 100000 | 6097 | 5.00 | 14 MB
pgbench_tellers | 10 | 0 | 0.00 | 64 kB
pgbench_history | 20000 | 0 | 0.00 | 1048 kB
pgbench_branches | 1 | 0 | 0.00 | 136 kB
HOT更新の効き具合の確認 (ver8.3～)
ver8.3で実装されたHOT機能は、更新処理速度の向上とガベージのリアルタイムな回収を可能とするとても有用な機能です (参考: HOTの効果)。HOTが予想どおり、しっかりと機能しているか確認をしておきましょう。
- HOT更新の比率の確認
- pg_stat_user_tablesのn_tup_upd(更新された行数)とn_tup_hot_upd(HOTが機能した更新行数)を利用します。(n_tup_hot_upd/n_tup_upd)の計算により、更新された行数のどれくらいの割合でHOTが有効に働いたかを確認できます。この値が低いようであれば、ロングトランザクションの影響や、不要なインデックスの存在を疑いましょう。十分にHOTが機能する環境であれば、以下の例の様に100に近い値になります。
=# SELECT relname, n_tup_upd, n_tup_hot_upd,
round(n_tup_hot_upd*100/n_tup_upd, 2) AS hot_upd_ratio
FROM pg_stat_user_tables WHERE n_tup_upd > 0 ORDER BY hot_upd_ratio;
relname | n_tup_upd | n_tup_hot_upd | hot_upd_ratio
------------------+-----------+---------------+---------------
pgbench_accounts | 100000 | 96079 | 96.00
pgbench_tellers | 100000 | 99921 | 99.00
pgbench_branches | 100000 | 99548 | 99.00
ロングトランザクションの確認
ロングトランザクションは、VACUUMやHOTによるガベージ回収処理を妨げ、DBの肥大化を起こす要因の一つです (参考: HOTの上手な使い方)。ロングトランザクションとは、BEGINを発行してからCOMMITするまで、長時間かかっている処理を指します。BEGINを発行してそのまま放置状態になっている処理などが無いか、定期的にチェックすると良いでしょう。
- トングトランザクションの処理と経過時間の確認 (ver8.3～)
- 主に、pg_stat_activity の current_query (実施中の処理内容) とxact_start (現在実施中のトランザクションの開始時間) を利用します。now() などの現在時刻を返す関数を利用して、どれくらいの時間が経過しているかを確認します。バッチ処理でもないのに、数分～数時間経過している処理があれば要注意です。
=# SELECT procpid, waiting, (current_timestamp - xact_start)::interval(3)
AS duration, current_query FROM pg_stat_activity
WHERE procpid <> pg_backend_pid();
procpid | waiting | duration | current_query
---------+---------+--------------+-----------------------------
10434 | f | 00:04:52.846 | <IDLE> in transaction
10440 | t | 00:04:48.974 | SELECT * FROM t FOR UPDATE;
- トングトランザクションの処理と経過トランザクション数の確認 (～ver8.2)
- ver8.2以前では、pg_stat_activityにxact_startが無いため、代わりにどれくらい古いトランザクションなのかを目安とします。pg_stat_activityのprocpid(処理を実施しているプロセスID)とpg_locks の pid を利用してJOINを行い、処理中のトランザクションの age (どれくらい昔のトランザクションか？)を算出します。1分あたり100トランザクションが実施されるシステムで、ageの項が10000だったならば、約100分前に開始された処理と推測することができます。
=# SELECT procpid, age(transaction), current_query
FROM pg_stat_activity a, pg_locks l WHERE a.procpid = l.pid
AND locktype = 'transactionid' AND pid <> pg_backend_pid();
procpid | age | current_query
---------+-------+-----------------------------
10434 | 10016 | <IDLE> in transaction
10440 | 10016 | SELECT * FROM t FOR UPDATE;
ロック待ち処理の確認
更新処理が多発するシステムでは、ロック待ちの発生により想定した性能が出ない、ということがあります。また、アドホックに実施された単発のSQLやメンテナンス処理による、長期ロック保持によって他の処理がロックを待つ状態になることもあります。そのような状況になっていないかを定期的にチェックすると良いでしょう。もちろん、性能検証時などの監視としても便利です。
- ロック待ちとなっている処理内容と対象のテーブルを確認する
- 主にpg_locks と pg_stat_activity を利用します。また、pg_locks を処理対象のテーブルをOIDとして管理しているので、具体的なテーブル名を出力するためpg_classも利用します。下記のSQLで大量の行が出力された場合、ロック待ちが頻発しています。locktypeと処理対象のテーブルに注意して、何か特定の処理がネックとなっていないか注意しましょう。(下記のSQLのduration(ロック待ち時間)の計算はpg_stat_activity.xact_start情報を持つver8.3以降で可能です。8.2以前では、ロングトランザクションの確認で行ったようなどれくらい古いトランザクションなのかを調べる age() 関数で代替情報を取得できます。)
-- 表示の関係でqueryを6文字で切っています
=# SELECT l.locktype, c.relname, l.pid, l.mode,
substring(a.current_query, 1, 6) AS query,
(current_timestamp - xact_start)::interval(3) AS duration
FROM pg_locks l LEFT OUTER JOIN pg_stat_activity a
ON l.pid = a. procpid
LEFT OUTER JOIN pg_class c ON l.relation = c.oid
WHERE NOT l.granted ORDER BY l.pid;
locktype | relname | pid | mode | query | duration
---------------+----------+------+---------------+--------+--------------
tuple | tellers | 2700 | ExclusiveLock | UPDATE | 00:00:00.013
transactionid | | 2701 | ShareLock | INSERT | 00:00:00.004
transactionid | | 2702 | ShareLock | UPDATE | 00:00:00.014
tuple | tellers | 2703 | ExclusiveLock | UPDATE | 00:00:00.004
tuple | tellers | 2704 | ExclusiveLock | UPDATE | 00:00:00.009
tuple | branches | 2705 | ExclusiveLock | UPDATE | 00:00:00.001
transactionid | | 2706 | ShareLock | UPDATE | 00:00:00.001
transactionid | | 2707 | ShareLock | UPDATE | 00:00:00.017
transactionid | | 2708 | ShareLock | UPDATE | 00:00:00.007
(9 rows)
-- ver8.2以前の場合(時間ではなく、どれくらい古いトランザクションかを表示)
# SELECT l.locktype, c.relname, l.pid, l.mode,
substring(a.current_query, 1, 6) AS query,
age(l.transaction) AS txn_age
FROM pg_locks l LEFT OUTER JOIN pg_stat_activity a
ON l.pid = a. procpid
LEFT OUTER JOIN pg_class c ON l.relation = c.oid
WHERE NOT l.granted ORDER BY l.pid;
locktype | relname | pid | mode | query | txn_age
---------------+----------+-------+---------------+--------+---------
transactionid | | 13910 | ShareLock | UPDATE | 3
tuple | tellers | 13911 | ExclusiveLock | UPDATE | 9
transactionid | | 13913 | ShareLock | UPDATE | 5
transactionid | | 13914 | ShareLock | UPDATE | 13
tuple | tellers | 13916 | ExclusiveLock | UPDATE | 12
tuple | branches | 13917 | ExclusiveLock | UPDATE | 1
tuple | tellers | 13918 | ExclusiveLock | UPDATE | 7
transactionid | | 13919 | ShareLock | UPDATE | 8
(8 rows)
pg_locksでロック待ちの判別をする際には、上記SQLの結果で得られる、locktypeに着目して下さい。
- locktype=relation の場合
- 表ロックの競合が起こっています。ロックを持っている処理としては、ALTER TABLE や CLUSTER などが疑われます。
- locktype = transactionid or tupleの場合
- 行ロックの競合が起こっています。なお、ある行について最初にロックを保持したトランザクション処理はpg_locksには表れません(ver8.2以降で使用可能な contrib/pgrowlocks で参照可能です)。次にロックを取りにきたトランザクションはlocktype=transactionidとしてロック待ちとなり、pg_locks に表れます。その次にロックを取りに来たトランザクションは locktype=tuple としてロック待ちとなり、pg_locks に表れます。
- locktype=extendの場合
- テーブル拡張のためのロック競合が起こっています。特定のテーブルへのINSERTが集中していることが推測できます。
次ページでは、稼動統計情報を格納しているテーブルやビューを一部紹介します。