PostgreSQLパフォーマンス最適化入門

データベース統計の表示

pg_stat_databaseテーブルには、各データベースに関する統計が含まれています。

SELECT * FROM pg_stat_database \gx

. . .
-[ RECORD 2 ]------------+------------------------------
datid                    | 13921
datname                  | postgres
numbackends              | 1
xact_commit              | 266
xact_rollback            | 9
blks_read                | 229
blks_hit                 | 11263
tup_returned             | 118708
tup_fetched              | 3563
tup_inserted             | 0
tup_updated              | 0
tup_deleted              | 0
conflicts                | 0
temp_files               | 0
temp_bytes               | 0
deadlocks                | 0
checksum_failures        |
checksum_last_failure    |
blk_read_time            | 0
blk_write_time           | 0
session_time             | 5303626.534
active_time              | 200.906
idle_in_transaction_time | 0
sessions                 | 2
sessions_abandoned       | 0
sessions_fatal           | 0
sessions_killed          | 0
stats_reset              | 2022-11-06 20:20:18.279798+01
. . .

私たちの目的のために興味深い列には、次のようなものがあります

• blks_read：データベース内で読み取られたディスクブロックの数。
• blks_hit：代わりにバッファキャッシュ内でディスクブロックが見つかった回数（ディスクからの遅い読み取りを回避）。
• xact_commit：コミットされたトランザクションの数。
• xact_rollback：ロールバックされたトランザクションの数。

Data Egretチームがブログで示しているように、これらの生の値を使用してキャッシュヒット率などの興味深い統計を計算できます。

SELECT
    datname,
    100 * blks_hit / (blks_hit + blks_read) as cache_hit_ratio
FROM
    pg_stat_database
WHERE
    (blks_hit + blks_read) > 0;

  datname  | cache_hit_ratio
-----------+-----------------
           |              99
 postgres  |              98
 template1 |              99
(3 rows)

これは、最も一般的なクエリを効果的にキャッシュできるように、データベースクラスターにRAMを追加することでメリットが得られるかどうかを評価するのに役立つ貴重な情報です。

テーブル統計の表示

他に役立つビュー群として、pg_stat_all_tables、pg_stat_user_tables、およびpg_stat_sys_tablesがあります。pg_stat_all_tablesビューはすべてのデータベースのアクセス統計を表示し、他の2つのビューはテーブルがユーザーテーブルかシステムテーブルかに基づいてフィルタリングします。

SELECT * FROM pg_stat_all_tables \gx

. . .
-[ RECORD 104 ]-----+------------------------
relid               | 1262
schemaname          | pg_catalog
relname             | pg_database
seq_scan            | 5168
seq_tup_read        | 20655
idx_scan            | 20539
idx_tup_fetch       | 20539
n_tup_ins           | 0
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

これらのビューにおける興味深い列には、次のようなものがあります

• seq_scan：テーブルに対して実行されたシーケンシャルスキャンの数。
• seq_tup_read：シーケンシャルスキャンから返された行の数。
• idx_scan：テーブルに対して実行されたインデックススキャンの数。
• idx_tup_fetch：インデックスを介して取得された行の数。

これらの列の数値は、インデックスのパフォーマンスと、実行しているクエリによって効果的に使用されているかどうかを評価するのに役立ちます。テーブルに多数のシーケンシャルスキャンがある場合は、最も一般的なクエリで使用できる追加のインデックスを作成するとメリットが得られるでしょう。

インデックスヒットの表示

現在持っているインデックスに関する詳細情報が必要な場合は、pg_stat_all_indexes、pg_stat_user_indexes、およびpg_stat_sys_indexesビューを参照できます。

SELECT * FROM pg_stat_all_indexes \gx

. . .
-[ RECORD 6 ]-+----------------------------------------------
relid         | 1249
indexrelid    | 2659
schemaname    | pg_catalog
relname       | pg_attribute
indexrelname  | pg_attribute_relid_attnum_index
idx_scan      | 822
idx_tup_read  | 1670
idx_tup_fetch | 1670
. . .

これらは、各インデックスがどれくらいの頻度で使用されているかに関する情報を提供します。idx_scan列はインデックスがスキャンされた回数を示します。idx_tup_read列はスキャンによって返されたエントリの数を示し、idx_tup_fetchはインデックススキャンによって返された行の総数を示します。

この情報は、クエリで使われていないインデックスがあるかどうかを理解するのに役立ちます。それらのインデックスを特定したら、インデックスを活用するようにクエリを書き換えるか、未使用のインデックスを削除して書き込みパフォーマンスを向上させることができます。

ロック情報の表示

収集した遅いクエリに関する情報の一部は、ロックの問題を示しているかもしれません。pg_locksビューをクエリすることで、現在保持されているすべてのロックに関する詳細情報を確認できます。

SELECT * FROM pg_locks \gx

-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 13921
relation           | 12290
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/3920
pid                | 967262
mode               | AccessShareLock
granted            | t
fastpath           | t
waitstart          |
-[ RECORD 2 ]------+----------------
locktype           | virtualxid
database           |
relation           |
page               |
tuple              |
virtualxid         | 3/3920
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/3920
pid                | 967262
mode               | ExclusiveLock
granted            | t
fastpath           | t
waitstart          |

この出力は、PostgreSQL内のすべてのロックに関する情報を提供します。これは、個別のプロセスが同じオブジェクトの制御を要求したときに発生する競合問題を診断するのに役立ちます。

問題のあるロックを調査するのに役立つ可能性のある列には、次のようなものがあります

• locktype：ロック可能なオブジェクトの種類
• database/relation/page/tuple：ロックされたアイテムのオブジェクトID。データベースとリレーションについては、pg_databaseとpg_classで相互参照できます。
• mode：実装または要求されたロックモード。
• granted：ロックが付与されたかどうかを示すブール値。

PostgreSQLがすでにスロークエリをログに記録しているか確認する

最初にすべきことは、スロークエリロギングの現在の状態を確認することです。スロークエリロギングがすでに有効になっている場合は、何もする必要はありません。

スロークエリロギングが有効になっているかどうかは、次のように入力して確認できます

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | -1
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | default
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | -1
sourcefile      |
sourceline      |
pending_restart | f

short_descおよびextra_desc列の値を確認すると、ロギングが現在有効になっているかどうかを評価するための情報が見つかります。setting列が現在-1に設定されているため、スロークエリロギングは現在有効になっていないことがわかります。

現在の状態がわかったので、必要に応じて変更できます。

PostgreSQLを設定してスロークエリをログに記録する

次に進む前に、スロークエリロギングは非常に便利ですが、追加のパフォーマンス影響をもたらす可能性があることに注意することが重要です。PostgreSQLは、各クエリの時間を測定し、結果をログに記録するために追加の操作を実行する必要があります。これはパフォーマンスに影響を与え、予期せずハードドライブのスペースを使い果たす可能性があります。

常にスロークエリをログに記録することは良いアイデアではないかもしれません。代わりに、問題を積極的に調査しているときに機能を有効にし、完了したら無効にしてください。

SHOW config_file;

               config_file
-----------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

. . .
# Query logging configuration

#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
                                 # and their durations, > 0 logs only
                                 # statements running at least this number
                                 # of milliseconds

#log_min_duration_sample = -1    # -1 is disabled, 0 logs a sample of statements
                                 # and their durations, > 0 logs only a sample of
                                 # statements running at least this number
                                 # of milliseconds;
                                 # sample fraction is determined by log_statement_sample_rate

#log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
                                 # log_min_duration_sample to be logged;
                                 # 1.0 logs all such statements, 0.0 never logs

#log_transaction_sample_rate = 0.0 # fraction of transactions whose statements
                                   # are logged regardless of their duration; 1.0 logs all
                                   # statements from all transactions, 0.0 never logs
. . .

log_min_duration_statement = 5s

SELECT pg_reload_conf();

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | 5000
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | configuration file
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | 5000
sourcefile      | /etc/postgresql/14/main/postgresql.conf
sourceline      | 506
pending_restart | f

ALTER DATABASE helloprisma SET 'log_min_duration_statement' = 2000;

ALTER DATABASE

\drds

                  List of settings
 Role |  Database   |           Settings
------+-------------+-------------------------------
      | helloprisma | log_min_duration_statement=2000
(1 row)

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | 5000
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | configuration file
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | 5000
sourcefile      | /etc/postgresql/14/main/postgresql.conf
sourceline      | 506
pending_restart | f

スロークエリロギングのテスト

最小ロギング期間を超えるステートメントを発行して、設定をテストします。

SELECT pg_sleep(10);

 pg_sleep
----------

(1 row)

ログを確認すると、長時間実行されたクエリが発生したことを示すステートメントが見つかるはずです。

2022-11-11 17:58:04.719 CET [1121088] postgres@postgres STATEMENT:  select sleep(10);
2022-11-11 17:58:42.635 CET [1121088] postgres@postgres LOG:  duration: 10017.171 ms  statement: select pg_sleep(10);

グローバル制限と特定のテーブルで異なるしきい値を設定しているため、一方のみをトリガーするはずのクエリ時間を使用することで、それぞれが正しく適用されていることをテストできます。

たとえば、しきい値が低いデータベースに接続し、4秒間スリープさせると、ログ行がトリガーされるはずです。

\c helloprisma
SELECT pg_sleep(4);

ログには以下が表示されます

2022-11-13 14:46:07.361 CET [1252789] postgres@helloprisma STATEMENT:  alter database helloprisma set log_min_duration_statement=2s;
2022-11-13 14:53:05.027 CET [1309069] postgres@helloprisma LOG:  duration: 4022.546 ms  statement: select pg_sleep(4);

次に、グローバル設定のみの影響を受けるはずの別のデータベースに切り替えることができます。同じスリープステートメントではログ行がトリガーされるべきではありません。

\c postgres
SELECT pg_sleep(4);

新しいログ行は記録されないはずです。