dexter で PostgreSQL テーブルのインデックスすべきカラムを推定する

はじめに

dexterはPostgreSQL(以下pgと略します)のSQLを解析して必要なインデックスの推定を行なってくれるツールです。 今回はこのツールを試してみるために、CentOS上にpgをセットアップして、dexterをインストールしてみようと思います。

環境とバージョン

今回使用した環境とバージョンは下記の通りです。

OS

CentOS Linux release 7.3.1611 (Core)

Ruby関連

$ ruby -v
ruby 2.0.0p648 (2015-12-16) [x86_64-linux]
$ gem list --local
pg (0.21.0)
pg_query (0.11.4)
pgdexter (0.1.4)
(関連するgemだけ抜粋)

pg関連

$ yum info postgresql96-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.vodien.com
* epel: epel.mirror.angkasa.id
* extras: mirror.vodien.com
* updates: mirror.vodien.com
Installed Packages
Name : postgresql96-server
Arch : x86_64
Version : 9.6.3
Release : 1PGDG.rhel7
Size : 18 M
Repo : installed
From repo : pgdg96
Summary : The programs needed to create and run a PostgreSQL server
URL : http://www.postgresql.org/
License : PostgreSQL
Description : PostgreSQL is an advanced Object-Relational database management system (DBMS).
: The postgresql96-server package contains the programs needed to create
: and run a PostgreSQL server, which will in turn allow you to create
: and maintain PostgreSQL databases.

必要なパッケージのインストール

まずはパッケージマネージャで必要なものをインストールします。 pgはPostgreSQL公式サイトで配布されているパッケージを使います。

pgのリポジトリを追加

$ wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
$ rpm -ivh pgdg-centos96-9.6-3.noarch.rpm

pgインストール

$ yum install -y postgresql96-server postgresql96-devel postgresql96-contrib

-develはdexterが依存するpggemのビルドに、-contribはスロークエリログを有効にするために必要です。

gem インストールに必要なパッケージをインストール

$ yum install -y gcc ruby ruby-devel

pgのセットアップ

pgのデータ保存ディレクトリを初期化します。この操作はpgの実行ユーザーで行う必要があリます

$ sudo su - postgres
$ /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data/

自動起動を有効化して、起動します。

$ sudo systemctl enable postgresql-9.6
$ sudo systemctl start postgresql-9.6

この後データベースの作成やユーザーの設定などがありますが、詳しい説明はpgの素晴らしい公式日本語ドキュメントに譲ります。

dexterはpgのログファイルを監視してスロークエリを検出して解析を行うことができます。 pgでスロークエリをログに出力するためには、下記のスロークエリログの有効化を行ってください。

スロークエリログの有効化

スロークエリの出力のためにはpgの設定ファイルを編集します。クエリをログ出力する実行時間のしきい値をミリ秒単位で指定します。

/var/lib/pgsql/9.6/data/postgresql.conf

log_min_duration_statement = 10 # ms

設定ファイルを変更したら再起動が必要です。

$ sudo systemctl restart postgresql-9.6

dexterをインストール

dexterをインストールします。下記の手順はほとんどREADMEと同じですが、いくつか注意点があるので説明します。

HypoPGをビルド

ビルドスクリプトがpgのツールを使うのでパスを通しておく必要があります。

$ export PATH=$PATH:/usr/pgsql-9.6/bin/

以下はREADMEと同じです。

$ wget https://github.com/dalibo/hypopg/archive/1.0.0.tar.gz
$ tar xf 1.0.0.tar.gz
$ cd hypopg-1.0.0
$ make
$ make install # may need sudo

gemでdexterをインストール

$ gem install pgdexter

使ってみる

READMEにはログを監視する使い方が説明されていますが、とりあえずコマンドラインからクエリを指定するには下記のように--stateオプションを使います。

$ dexter <database-url> --state "SELECT * FROM ..."

ここでdatabase-urlはlibpqのドキュメントに詳細が記述されていますが、ざっくり下記のような文字列です。

postgresql://user:password@localhost/mydb

mydbは解析対象のテーブルが存在するDBを指定します(省略したり、他のDBを指定しても起動はしますが解析が行われないので注意してください)

--state オプションでクエリを指定する以外にも一定間隔で標準入力からクエリを読みこんで解析を行うモードもあります。この場合は下記のようにログファイルの内容をパイプします。

$ tail -F -n +1 <log-file> | dexter <database-url>

詳しいオプション

引数なしで実行すると下記のように使用方法が表示されます。

$ dexter
Usage:
    dexter <database-url> [options]

Options:
    --create         create indexes
    --exclude        prevent specific tables from being indexed
    --interval       time to wait between processing queries, in seconds
    --min-time       only process queries that have consumed a certain amount of DB time, in minutes
    --log-level      log level
    --log-sql        log sql
    -s, --statement  process a single statement
    -v, --version    print the version
    -h, --help       prints help

主要なオプションを確認してみましょう。

• --create 推定に続いてインデックスの作成を行います
• --statement 解析するクエリを指定します
• --interval --statement クエリの解析間隔を秒で指定します。オプションなしで起動した場合は--intervalで指定する間隔で標準入力からクエリを読みこんで解析を行います。

解析例

今回はtpc-hに含まれているクエリを解析してみました。下記のように作成すべきインデックスの候補が表示されています。

$ dexter postgresql://postgres:password@localhost/dextor_test -s "select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date '1994-09-01' and o_orderdate < date '1994-09-01' + interval '3' month and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;"
2017-07-06T16:01:55+00:00 Running analyze: ANALYZE "lineitem"
2017-07-06T16:02:06+00:00 Running analyze: ANALYZE "orders"
2017-07-06T16:02:14+00:00 Index found: lineitem (l_orderkey)
2017-07-06T16:02:14+00:00 Index found: orders (o_orderdate)

この結果のうちインデックスの推定結果は次の部分です。

2017-07-06T16:02:14+00:00 Index found: lineitem (l_orderkey)
2017-07-06T16:02:14+00:00 Index found: orders (o_orderdate)

lineitemテーブルのl_orderkeyカラムおよびordersテーブルのo_orderdateカラムにインデックスを作成すべきという結果になっています。 ここでは掲載していませんが、pgのログを確認すると、実際にはdexterはexplainを行なってその結果から必要なインデックスを推定しているようです。

まとめ

PostgreSQLでクエリを実行する際に必要なインデックスの推定を行うツールdexterをインストールして、 実際にインデックスの推定を行なってみました。

次回はインデックスの妥当性の検証や動作原理について調べてみたいです。