Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

PostgreSQL12で使いたいモジュール(pg_prewarm/pg_stat_statements/auto_explain/pg_repack/pgstattuple)

はじめに

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_hint_plan

インストールしようとしましたが、11までしか対応していませんでした。リリースされてから記載します。

https://osdn.net/projects/pghintplan/releases/

参考

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はリーフページの断片化の状況で、低い方が良いです。

参考

mkyz08
SIer&バックエンドエンジニア&日曜プログラマー。 Apache Camel/VoltDB/Oracle/Apache karaf。 基本的に仕事外での自分用のメモ(興味があること)として記事を書いています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away