はじめに
今更ながら2019/12/7(土)に開催されたPostgreSQLアンカンファレンスの発表内容をまとめました。
当日の資料はこちらから見れます。
本当はもっと早くまとめる予定でしたが、年末年始ゴロゴロとしていたら気づけばこんな時期になってしまいました。
13の新機能検証
検証に使用したPostgreSQL 13はgitからソースを直接取得し、ビルドしました(当時2019/12/6)。
現在(2020/1/6)はα版より劣るクラッシュテスト版(※)とやらのRPMがリリースされているため、そちらをつかったほうが導入は簡単です。
※原語だと使用用途は「CRASH TESTING ONLY」と書かれています。α版ではないようです。
- PostgreSQL RPM Repository (with Yum)
https://yum.postgresql.org/rpmchart.php
log_statement_sample_rate
スロークエリのログ出力を抑制するパラメータです。
従来のPostgreSQLは指定時間以上のスロークエリを全て出力するため、ログの肥大化と可読性の低下がありました。
しかし本パラメータを使うことで、スロークエリの出力量を調整できます。
具体的に関係するパラメータは以下の通りです。
| パラメータ名 | 説明 |
|---|---|
| log_min_duration_statement | 従来からあるパラメータ。 指定した時間(ミリ秒)以上かかったクエリを全てログに記録する。 |
| log_min_duration_sample |
追加されたパラメータ。 指定した時間(ミリ秒)以上かかったクエリを一部ログに記録する。 出力量は"log_statement_sample_rate"によって調整される。 優先度は"log_min_duration_statement"より低い。 よって"log_min_duration_statement"より高い値にした場合、無視される。 |
| log_statement_sample_rate |
追加されたパラメータ。 log_min_duration_sampleの出力量を割合で調整する。 1.0で全て記録し、0の場合、何も記録しない。 |
log_min_duration_sampleによるログ出力抑制の検証
パラメータを以下のように設定し、実際にスロークエリの出力量が抑制されるか検証してみました。
| パラメータ名 | 設定値 | 説明 |
|---|---|---|
| log_min_duration_statement | -1 | 無効化。ログ出力量はlog_min_duration_sampleでのみ管理させる。 |
| log_min_duration_sample | 500ms | 0.5秒以上かかったクエリを記録する。 |
| log_min_duration_statement | 0.5 | 出力されるスロークエリのうち50%を記録する。 |
スロークエリ10個で検証
以下の意図的なスロークエリを実行し、ログの結果を見てみる。
SELECT pg_sleep(1) AS label01;
SELECT pg_sleep(1) AS label02;
SELECT pg_sleep(1) AS label03;
SELECT pg_sleep(1) AS label04;
SELECT pg_sleep(1) AS label05;
SELECT pg_sleep(1) AS label06;
SELECT pg_sleep(1) AS label07;
SELECT pg_sleep(1) AS label08;
SELECT pg_sleep(1) AS label09;
SELECT pg_sleep(1) AS label10;
※pg_sleep(1)により1秒待機するため、必ず0.5秒以上かかる=スロークエリと判定される。
※labelXXと付けることで、どのSQLが記録されたか確認する。
結果は以下の通りです。
LOG: duration: 1001.541 ms statement: SELECT pg_sleep(1) AS label02;
LOG: duration: 1002.212 ms statement: SELECT pg_sleep(1) AS label03;
LOG: duration: 1001.598 ms statement: SELECT pg_sleep(1) AS label04;
LOG: duration: 1001.119 ms statement: SELECT pg_sleep(1) AS label05;
LOG: duration: 1001.363 ms statement: SELECT pg_sleep(1) AS label06;
LOG: duration: 1002.117 ms statement: SELECT pg_sleep(1) AS label08;
LOG: duration: 1001.350 ms statement: SELECT pg_sleep(1) AS label10;
10個では試行回数が少ないためか、7個のスロークエリが記録されています。
また何番目のスロークエリが記録されるかなどに、特に規則性はないように見えます。
(何度か実行したが、結果はあまり変わらなかった)
スロークエリ1000個で検証
今度はスロークエリの数を増やして検証してみました。
そのためにまず、スロークエリ1000個を生成するシェルスクリプトを書きました。
rm -rf sample.sql
for i in {1..1000}; do
echo "SELECT ${i}, pg_sleep(1) AS test;" >> sample.sql
done
結果、次のようなsqlファイルが生成されます。
SELECT 1, pg_sleep(1) AS test;
SELECT 2, pg_sleep(1) AS test;
SELECT 3, pg_sleep(1) AS test;
…
SELECT 1000, pg_sleep(1) AS test;
このsqlファイルを次のように実行しました。
$ psql -f sample.sql
今回のスロークエリには「test」というラベルが付いています。
よって以下のようにログから「test」という文字列を含む行数をカウントすることで、取得されたスロークエリ数が分かります。
$ grep -ic "test" postgresql-2019-12-06_200041.log
509
上記の通り、509行がヒットしました。
そのため1000個の50%である500に近い数が、ちゃんと取得されていることが分かります。
1000接続の各々1スロークエリで検証
上記の検証は1接続で1000個のスロークエリを実行しました。
そのため、今度は1000接続で各々1つのスロークエリを実行した場合の結果を見てみます。
今回は以下のように毎回psqlコマンドでスロークエリを実行するシェルスクリプトを書き、検証しました。
for i in {1..1000}; do
psql -c "SELECT ${i}, pg_sleep(1) AS test2;"
done
このスロークエリには「test2」というラベルが付いています。
よって前の検証と同様に「test2」の行数をカウントしてみます。
$ grep -ic "test" postgresql-2019-12-06_202546.log
512
上記の通り、512行がヒットしました。
そのため接続が異なる場合でも、1000個の50%である500に近い数が、ちゃんと取得されていることが分かります。
pgbenchでパーティション
13からpgbenchでパーティションテーブルを生成できるようになりました。
最近、PostgreSQLのパーティションそのものが機能強化されており、お手軽に検証テーブルを作りたい、という場合に役立ちます。
具体的にはpgbenchに次のオプションが追加されています。
| パラメータ名 | 説明 |
|---|---|
| --partitions= | pgbench_accountsテーブルに生成するパーティション数を指定する。 デフォルトは0で、その場合パーティションは生成されない。 |
| --partition-method= | 使用するパーティションの手法を指定する。 現在は"range"と"hash"が選べる。 デフォルトでは"range"。 |
pgbenchによるパーティションテーブルの作成と結果の確認
以下のコマンドにより100万行のpgbench_accountsテーブルを10パーティションで区切るられるよう作成します。
$ pgbench -i -s 10 --partitions=10 --partition-method=range
結果は次の通りです。pgbench_accountsテーブルが10個のパーティションに分かれています。
=# \d+ pgbench_accounts
Partitioned table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+----------+--------------+-------------
aid | integer | | not null | | plain | |
bid | integer | | | | plain | |
abalance | integer | | | | plain | |
filler | character(84) | | | | extended | |
Partition key: RANGE (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Partitions: pgbench_accounts_1 FOR VALUES FROM (MINVALUE) TO (100001),
pgbench_accounts_10 FOR VALUES FROM (900001) TO (MAXVALUE),
pgbench_accounts_2 FOR VALUES FROM (100001) TO (200001),
pgbench_accounts_3 FOR VALUES FROM (200001) TO (300001),
pgbench_accounts_4 FOR VALUES FROM (300001) TO (400001),
pgbench_accounts_5 FOR VALUES FROM (400001) TO (500001),
pgbench_accounts_6 FOR VALUES FROM (500001) TO (600001),
pgbench_accounts_7 FOR VALUES FROM (600001) TO (700001),
pgbench_accounts_8 FOR VALUES FROM (700001) TO (800001),
pgbench_accounts_9 FOR VALUES FROM (800001) TO (900001)
SQLをさらにコピペしやすくなった
PostgreSQLの「\SET PROMPT2」コマンドに指定できる値に「%w」が追加されました。
「\SET PROMPT2」はpsql画面の2行目以降のプロンプトの書式を指定する機能です。
例えば次のように2行目以降のプロンプトを空白にし、SQLをコピペしやすくすることが可能です。
postgres=# \set PROMPT2 ''
postgres=# SELECT
'2019-12-01'::date + g
FROM
generate_series(0, 3) AS g;
しかし上記はコピペはしやすくなりますが、可読性が低いのが欠点です。
PostgreSQL 13から追加された「\SET PROMPT2 '%w'」を実行することで、一行目との間隔を保持するため可読性が上がります。
postgres=# \set PROMPT2 '%w'
postgres=# SELECT
'2019-12-01'::date + g
FROM
generate_series(0, 3) AS g;
ただし「%w」単体で1行目と同じ幅の空白を追加するため、次のように余分な文字をいれるとズレてしまう点に注意が必要です。
postgres=# \set PROMPT2 '#%w>'
postgres=# SELECT
# > '2019-12-01'::date + g
# >FROM
# > generate_series(0, 3) AS g;
参考リンク
-
select * from depesz;
https://www.depesz.com/
PostgreSQLの新機能検証や役立つ機能の紹介などが載っているブログです。
今回の元ネタはここで紹介されていたものをそのまま参考にしました。
以下がそれぞれ参考にした具体的な記事です。
- Waiting for PostgreSQL 13 – Allow sampling of statements depending on duration
https://www.depesz.com/2019/11/16/waiting-for-postgresql-13-allow-sampling-of-statements-depending-on-duration/
log_statement_sample_rateの記事です。
- Waiting for PostgreSQL 13 – pgbench: add –partitions and –partition-method options.
https://www.depesz.com/2019/10/28/waiting-for-postgresql-13-pgbench-add-partitions-and-partition-method-options/
pgbenchによるパーティションテーブル作成機能追加の記事です。
- Waiting for PostgreSQL 13 – Allow invisible PROMPT2 in psql.
https://www.depesz.com/2019/11/26/waiting-for-postgresql-13-allow-invisible-prompt2-in-psql/
「\SET PROMPT2 '%w'」の記事です。
-
idea: log_statement_sample_rate - bottom limit for sampling
https://www.postgresql.org/message-id/flat/CAFj8pRDS8tQ3Wviw9%3DAvODyUciPSrGeMhJi_WPE%2BEB8%2B4gLL-Q%40mail.gmail.com
log_statement_sample_rateに関するPostgreSQLコミュニティのメーリングリストでの議論です。