はじめに
PostgreSQL12で使いたい以下のモジュールのインストール・設定方法についてのメモです。
- pg_prewarm
- pg_stat_statements
- pg_repack
- auto_explain
- pg_store_plans
- pgstattuple
※pg_store_plansを入れれば、auto_explainは必要ないかもしれません。
以下については使用したかったのですが、PostgreSQL12がまだリリースされていなかったため、リリースした後に追記します。
- pg_hint_plan（REL12-1.3.5で対応済み）
- pg_statsinfo（2020/2/5に対応済み）
- pg_stats_reporter（2020/2/5に対応済み）
auto_explain
auto_explainモジュールは、自動的に遅いSQLの実行計画をログへ出力することができます。
使用するためにはpostgresql.confを以下のように変更します。設定パラメータは他にもあるので公式サイトを参照してください。
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration=10000
auto_explain.log_min_durationに設定した値（ms）よりも時間がかかったSQLの実行計画を出力します。大量のログが出力されないように10秒などを設定します。
ログの出力は以下のとおりです。
2019-10-25 14:56:30 CEST [10147]: [3-1] user=postgres, db=testdbLOG: duration: 0.069 ms plan:
Query Text: SELECT abalance FROM pgbench_accounts WHERE aid = 900347;
Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..8.44 rows=1 width=4)
Index Cond: (aid = 900347)
参考
pg_store_plans
pg_store_plansは実行計画ごとに実行統計を取得する機能拡張です。実行計画に関連する統計情報を表示するビュー(pg_store_plansビュー)が利用できます。
また、pg_store_plansを入れておくとpg_stats_reporterのレポート機能で実行計画の統計を出力することができます。
コンパイル
CentOS7.5でコンパイルしたときのメモです。
モジュールは以下からダウンロードしておきます。
# tar xvzf pg_store_plans-1.4.tar.gz
# cd pg_store_plans-1.4
# make USE_PGXS=1
# sudo make USE_PGXS=1 install
使用するためにはpostgresql.confを以下のように変更します。変更後、PostgreSQLを再起動します。
shared_preload_libraries = 'pg_store_plans'
モジュールを使用するためには、以下を実行します。
# CREATE EXTENSION pg_repack;
CREATE EXTENSION
参考
pg_stat_statements
pg_stat_statementsモジュールは、SQLの実行時間や回数などを統計情報をデータベースに記録するモジュールです。
使用するためにはpostgresql.confを以下のように変更します。
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all # またはtop
pg_stat_statements.save = on
pg_stat_statements.maxは記録されるSQLの最大数を指定します。デフォルトは5000で、5000を超えると実行回数が少ないSQLが削除されます。
モジュールを使用するためには、以下を実行します。
CREATE EXTENSION pg_stat_statements;
統計情報が保存されたpg_stat_statementsビューから、実行回数、実行時間等を検索すると以下のようになります。
select substr(query, 0, 80) as query, calls
,(total_time / 1000)::numeric(10,3) as total_time_sec
,(mean_time / 1000)::numeric(10,3) as avg_time_sec
,(min_time / 1000)::numeric(10,3) as min_time_sec
,(max_time / 1000)::numeric(10,3) as max_time_sec
from pg_stat_statements
order by total_time desc
limit 10;
query | calls | total_time_sec | avg_time_sec | min_time_sec | max_time_sec
---------------------------------------------------------------------------------+-------+----------------+--------------+--------------+--------------
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 | 20000 | 69.178 | 0.003 | 0.000 | 0.034
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 | 20000 | 55.097 | 0.003 | 0.000 | 0.045
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 | 20000 | 0.632 | 0.000 | 0.000 | 0.005
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 20000 | 0.199 | 0.000 | 0.000 | 0.000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $ | 20000 | 0.161 | 0.000 | 0.000 | 0.002
copy pgbench_accounts from stdin | 1 | 0.128 | 0.128 | 0.128 | 0.128
vacuum analyze pgbench_accounts | 1 | 0.063 | 0.063 | 0.063 | 0.063
alter table pgbench_accounts add primary key (aid) | 1 | 0.058 | 0.058 | 0.058 | 0.058
CREATE EXTENSION pg_stat_statements | 1 | 0.030 | 0.030 | 0.030 | 0.030
vacuum pgbench_branches | 2 | 0.025 | 0.013 | 0.012 | 0.013
(10 rows)
蓄積されたデータをいったんリセットする場合は以下のSQLを実行します。
select pg_stat_statements_reset();
参考
pg_repack
pg_repackモジュールは、テーブル・インデックスの再編成をオンラインで実行するためのモジュールです。テーブル・インデックスの再編成は表ロックが長時間かかってしまいますが、pg_repackを利用することにより表ロックがかかる時間が短くなります。
コンパイル
CentOS7.5でコンパイルしたときのメモです。
モジュールは以下からダウンロードしておきます。
# yum install -y postgresql12-devel
# yum install zlib-devel
# yum install readline-devel
# yum install openssl-devel
# yum install -y cmake
# yum install -y clang
# yum install -y centos-release-scl
# yum install -y llvm-toolset-7 llvm5.0
# mkdir -p /opt/rh/llvm-toolset-7/root/usr/bin
# ln -s /usr/bin/clang /opt/rh/llvm-toolset-7/root/usr/bin/clang
# mkdir -p /usr/lib64/llvm5.0/bin/
# ln -s /opt/rh/llvm-toolset-7/root/usr/bin/llvm-lto /usr/lib64/llvm5.0/bin/llvm-lto
$ export PATH=$PATH:/usr/pgsql-12/bin
$ cd pg_repack
$ make
$ sudo make install
-bash-4.2$ psql testdb
psql (12.0)
Type "help" for help.
testdb=# CREATE EXTENSION pg_repack;
CREATE EXTENSION
データベースtestdbのpgbench_accountsテーブルを再編成する場合は以下のように実行します。
$ pg_repack -n -t pgbench_accounts -d testdb
INFO: repacking table "public.pgbench_accounts"
参考
- pg_repack 1.4.5
- pg_repack (オンラインテーブル再編成ツール)
- 運用で役立つツールpg_repackのご紹介
- RDS Postgresqlのディスク使用量を削減する(pg_repack)
- pg_repack のインストール
pg_hint_plan
インストールしようとしましたが、11までしか対応していませんでした。リリースされてから記載します。
参考
pg_prewarm/pg_buffercache
pg_prewarmでは、ユーザが手動でテーブルやインデックスを指定することで共有バッファにキャッシュさせることができpg_buffercacheは共有バッファの状態を表示できます。
使用するためにはpostgresql.confを以下のように変更します。
shared_preload_libraries = 'pg_prewarm'
$ psql -U postgres testdb
testdb=# create extension pg_prewarm;
CREATE EXTENSION
testdb=# create extension pg_buffercache;
CREATE EXTENSION
pgbench_accountsテーブルを共有バッファにキャッシュさせる場合は以下のように実行します。
testdb=# select pg_prewarm('pgbench_accounts', 'buffer');
pg_prewarm
------------
16693
(1 row)
共有バッファの状態を表示するためには、以下のようなSQLを実行します。
testdb=# select c.relname, count(*) as buffers
from pg_buffercache as b
inner join pg_class as c on b.relfilenode = pg_relation_filenode(c.oid) and b.reldatabase in (0, (select oid from pg_database where datname = current_database()))
group by c.relname
order by 2 desc;
relname | buffers
-----------------------------------------+---------
pgbench_accounts | 16693
pg_proc | 83
pg_attribute | 31
pg_class | 16
pg_depend_reference_index | 12
参考
pgstattuple
pgstattupleモジュールは、タプルレベルの統計情報を取得することができます。
モジュールを使用するためには、以下を実行します。
CREATE EXTENSION pgstattuple;
テーブル情報を取得するためには以下のように実行します。
testdb=# select * from pgstattuple('pgbench_accounts');
-[ RECORD 1 ]------+----------
table_len | 134381568
tuple_count | 1000000
tuple_len | 121000000
tuple_percent | 90.04
dead_tuple_count | 207
dead_tuple_len | 25047
dead_tuple_percent | 0.02
free_space | 1894920
free_percent | 1.41
インデックス情報を取得するためには以下のように実行します。
testdb=# select * from pgstatindex('pgbench_accounts_pkey');
-[ RECORD 1 ]------+---------
version | 4
tree_level | 2
index_size | 22487040
root_block_no | 290
internal_pages | 11
leaf_pages | 2733
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 90.08
leaf_fragmentation | 0
avg_leaf_densityはリーフページの平均密度（%）です。
leaf_fragmentationはリーフページの断片化の状況で、低い方が良いです。
参考