pg_squeezeを使ってみた



# はじめに
先日、ANNOUNCEのMLに[pg_squeeze](http://www.cybertec.at/en/products/pg_squeeze-postgresql-extension-to-auto-rebuild-bloated-tables/)という見慣れぬEXTENSIONのbetaリリースが流れていたので、ちょっと試してみることにした。

# pg_squeezeとは
ざっと見た感じでは、自動的にテーブルの再編成を行うツールのようだ。
要するに、自動CLUSTER、あるいは自動pg_repackという感じのツールか。
PostgreSQL 9.3から導入されたBackground Worker基盤の上で動くツールなので、EXTENSIONを組み込んでおけばPostgreSQL起動や終了と同期してくれるのがポイントか。

# インストール
## make
さっそく、ソースのアーカイブを入手してビルドしてみる。今回はPostgreSQL 9.6上にインストールしてみる。
何も考えずにアーカイブを展開してmakeしてみると・・・

```
[nuko@localhost pg_squeeze-1.0beta1]$ make USE_PGXS=1
make: --pgxs: コマンドが見つかりませんでした
make: *** ターゲットがありません.  中止.
```

(´・ω・`) しょぼーん

きちんとREADMEを見るか。
READMEを見ると、環境変数PG_CONFIGを設定しないといけないようだ。えー。
環境変数を設定して再度ビルドしてみる。

```
[nuko@localhost pg_squeeze-1.0beta1]$ export PG_CONFIG=/home/nuko/pgsql/pgsql-9.6.0/bin/pg_config
[nuko@localhost pg_squeeze-1.0beta1]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I./ -I/home/nuko/pgsql/pgsql-9.6.0/include/server -I/home/nuko/pgsql/pgsql-9.6.0/include/internal -D_GNU_SOURCE   -c -o pg_squeeze.o pg_squeeze.c
pg_squeeze.c: 関数 ‘squeeze_table’ 内:
pg_squeeze.c:1887:6: 警告: ‘tup_in’ はこの関数内初期化されずに使用されるかもしれません [-Wmaybe-uninitialized]
   if (tup_in == NULL)
      ^
pg_squeeze.c:1771:13: 備考: ‘tup_in’ はここで定義されています
   HeapTuple tup_in;
             ^
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I./ -I/home/nuko/pgsql/pgsql-9.6.0/include/server -I/home/nuko/pgsql/pgsql-9.6.0/include/internal -D_GNU_SOURCE   -c -o concurrent.o concurrent.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I./ -I/home/nuko/pgsql/pgsql-9.6.0/include/server -I/home/nuko/pgsql/pgsql-9.6.0/include/internal -D_GNU_SOURCE   -c -o worker.o worker.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I./ -I/home/nuko/pgsql/pgsql-9.6.0/include/server -I/home/nuko/pgsql/pgsql-9.6.0/include/internal -D_GNU_SOURCE   -c -o pgstatapprox.o pgstatapprox.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o pg_squeeze.so pg_squeeze.o concurrent.o worker.o pgstatapprox.o  -L/home/nuko/pgsql/pgsql-9.6.0/lib -Wl,--as-needed -Wl,-rpath,'/home/nuko/pgsql/pgsql-9.6.0/lib',--enable-new-dtags  
[nuko@localhost pg_squeeze-1.0beta1]$
```

なんか警告は出てるけど一応はビルドできた。

## make check

make checkはサポートしていない。

```
[nuko@localhost pg_squeeze-1.0beta1]$ make check
"make check" is not supported.
Do "make install", then "make installcheck" instead.
[nuko@localhost pg_squeeze-1.0beta1]$
```

## make install
なのでインストールしてからinstallcheckを動かすことにする。

```
[nuko@localhost pg_squeeze-1.0beta1]$ make install
/usr/bin/mkdir -p '/home/nuko/pgsql/pgsql-9.6.0/lib'
/usr/bin/mkdir -p '/home/nuko/pgsql/pgsql-9.6.0/share/extension'
/usr/bin/mkdir -p '/home/nuko/pgsql/pgsql-9.6.0/share/extension'
/usr/bin/install -c -m 755  pg_squeeze.so '/home/nuko/pgsql/pgsql-9.6.0/lib/pg_squeeze.so'
/usr/bin/install -c -m 644 .//pg_squeeze.control '/home/nuko/pgsql/pgsql-9.6.0/share/extension/'
/usr/bin/install -c -m 644 .//pg_squeeze--1.0.sql  '/home/nuko/pgsql/pgsql-9.6.0/share/extension/'
```

無事にインストール完了。

## make installcheck

インストールできたので、installcheckをかけてみる。

```
[nuko@localhost pg_squeeze-1.0beta1]$ make installcheck
/home/nuko/pgsql/pgsql-9.6.0/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/nuko/pgsql/pgsql-9.6.0/bin'    --dbname=contrib_regression squeeze
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
NOTICE:  database "contrib_regression" does not exist, skipping
DROP DATABASE
============== creating database "contrib_regression" ==============
ERROR:  permission denied to create database
command failed: "/home/nuko/pgsql/pgsql-9.6.0/bin/psql" -X -c "CREATE DATABASE \"contrib_regression\" TEMPLATE=template0" "postgres"
make: *** [installcheck] エラー 2
```

あー、うちの環境で動かすとユーザ名が```nuko```になっちゃうんだよな。仕方がない。
環境変数```PGUSER=postgres```を設定してリトライ。

```
[nuko@localhost pg_squeeze-1.0beta1]$ export PGUSER=postgres
[nuko@localhost pg_squeeze-1.0beta1]$ make installcheck
/home/nuko/pgsql/pgsql-9.6.0/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/nuko/pgsql/pgsql-9.6.0/bin'    --dbname=contrib_regression squeeze
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
NOTICE:  database "contrib_regression" does not exist, skipping
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test squeeze                  ... FAILED (test process exited with exit code 2)

======================
 1 of 1 tests failed. 
======================

The differences that caused some tests to fail can be viewed in the
file "/home/nuko/src/pg_squeeze-1.0beta1/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/home/nuko/src/pg_squeeze-1.0beta1/regression.out".

make: *** [installcheck] エラー 1
[nuko@localhost pg_squeeze-1.0beta1]$ 
```

えー、installcheckにカジュアルに失敗してるんだけど・・・。
regressionのdiffを見てみると、

```
---
> FATAL:  cannot create PGC_POSTMASTER variables after startup
> server closed the connection unexpectedly
> 	This probably means the server terminated abnormally
> 	before or while processing the request.
> connection to server was lost
```

えー、PGC_POSTMASTER変数って何よ・・・。そんなのREADMEに書いてないんだが。
まあ、いいや。とりあえずインストールはできたんだろうし、先に動かしてみよう。

# 使用条件
このツールもCLUSTERコマンドや、pg_repackと同様にPrimary Key/Unique Keyが存在するテーブルでないと動作しないっぽい。

> First, make sure that your table has either primary key or unique
> constraint. This is necessary to process changes other transactions might do
> while "pg_squeeze" is doing its work.

# PostgreSQL設定
READMEを読むと、以下のPostgreSQL設定が必要とな。

```
4. Apply the following settings to postgresql.conf:

   wal_level = logical

   max_replication_slots = 1 # ... or add 1 to the current value.

   shared_preload_libraries = 'pg_squeeze' # ... or add the library to the existing ones.
```

ほお。pg_squeezeはロジカルデコーディングの機能も使っているんだな。ふむ。
あ・・・ということは、UNLOGGED TABLEに対してはこのツールは使えないってことなのかな・・・。

とりあえず、postgresql.confに上記のような設定をしてPostgreSQLを再起動してみる。

```
shared_preload_libraries = 'pg_squeeze'
squeeze.worker_autostart = 'bench'
squeeze.worker_role = postgres

```

squeeze.* のパラメータはpg_squeeze専用のもの。この設定にしておくと、PostgreSQLサーバ起動時に自動的にbenchデータベースに対するpg_squeezeの処理をしてくれるものっぽい(たぶん)。


サーバログを見ると、とくにこのEXTENSIONを組み込みが正常に終了した等のメッセージは出ていない。pgauditみたいに組み込んだことが分かるメッセージくらい出してもいいのになあ。

# EXTENSIONの登録
pg_squeezeはデータベースへの登録が必要らしい。今回はpgbench用のデータベースbenchに組み込んで見る。

```
[nuko@localhost ~]$ psql -U postgres bench
psql (9.6.0)
Type "help" for help.

bench=# CREATE EXTENSION pg_squeeze ;
CREATE EXTENSION
bench=# 
```

# pg_squeeze用の設定
さて、このpg_squeezeだが、EXTENSIONを組み込んだだけで自動的によしなに再編成・・・ということはしてくれない。
いろいろ設定が必要っぽい。

## squeezeスキーマのテーブル群
pg_squeezeでは、管理用にいろんなテーブルをsqueezeスキーマ上に作ってみるみたい。

```
bench-# \d squeeze.
squeeze.errors                        squeeze.tables                        squeeze.tables_tabschema_tabname_key
squeeze.errors_id_seq                 squeeze.tables_id_seq                 squeeze.tasks
squeeze.errors_pkey                   squeeze.tables_internal               squeeze.tasks_active_idx
squeeze.log                           squeeze.tables_internal_pkey          squeeze.tasks_id_seq
squeeze.log_started_idx               squeeze.tables_pkey                   squeeze.tasks_pkey
bench-# \d squeeze.
```

pg_squeezeを使う場合には、```squeeze.table```に再編成処理対象のテーブルを登録する必要がある。めんどい。
今回は、pgbench_branchesテーブルを対象にする。このテーブルは、fillfactorを設定しないと、すぐに肥大化しそうだからだ。
(もっとも、レコード数自体は非常に少ないので、肥大化したとしても、それほど実害はないような気もするが)


squeeze.tableはこんなテーブルだ。

```
bench-# \d squeeze.tables
                                          Table "squeeze.tables"
      Column      |           Type           |                          Modifiers                          
------------------+--------------------------+-------------------------------------------------------------
 id               | integer                  | not null default nextval('squeeze.tables_id_seq'::regclass)
 tabschema        | name                     | not null
 tabname          | name                     | not null
 clustering_index | name                     | 
 rel_tablespace   | name                     | 
 ind_tablespaces  | name[]                   | 
 task_interval    | interval                 | not null default '01:00:00'::interval
 first_check      | timestamp with time zone | not null
 free_space_extra | integer                  | not null default 50
 min_size         | real                     | not null default 8
 vacuum_max_age   | interval                 | not null default '01:00:00'::interval
 max_retry        | integer                  | not null default 0
 skip_analyze     | boolean                  | not null default false
Indexes:
    "tables_pkey" PRIMARY KEY, btree (id)
    "tables_tabschema_tabname_key" UNIQUE CONSTRAINT, btree (tabschema, tabname)
Check constraints:
    "tables_free_space_extra_check" CHECK (free_space_extra >= 0 AND free_space_extra < 100)
    "tables_min_size_check" CHECK (min_size > 0.0::double precision)
    "tables_task_interval_check" CHECK (task_interval >= '00:01:00'::interval)
Referenced by:
    TABLE "squeeze.tables_internal" CONSTRAINT "tables_internal_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCAD
E
    TABLE "squeeze.tasks" CONSTRAINT "tasks_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
Triggers:
    tables_internal_trig AFTER INSERT ON squeeze.tables FOR EACH ROW EXECUTE PROCEDURE squeeze.tables_internal_trig_func()
```

トリガが設定されている。squeeze処理対象のテーブルをここに登録すると、他の管理用のテーブルにも何かしらの設定を伝播するのだろう。

とりあえず設定してみる。

```
bench=# INSERT INTO squeeze.tables (tabschema, tabname, first_check, task_interval, min_size) 
VALUES ('public','pgbench_branches',now(),'1 min', 0.1);
INSERT 0 1
bench=# INSERT INTO squeeze.tables (tabschema, tabname, first_check, task_interval, min_size) 
VALUES ('public','pgbench_tellers',now(),'1 min', 0.1);
INSERT 0 1
bench=# 
```

とりあえずINSERTはされた。たぶん、こういう設定。

* ターゲットはpublic.pgbench_accountsとpublic.pgbench_tellers
* 最初のsqueeze処理のためのチェックはINSERT文実行時
* 1分毎にチェック(デフォルトは1時間ぽい)
* 肥大化したかどうかの閾値(?)は 0.1 MB

# pgbenchによる動作確認
## 測定環境
今回はScale Factor = 10の状態でpgbench環境を初期生成した。

```
[nuko@localhost ~]$ pgbench -i -s 10 -U postgres bench 
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.15 s, remaining 1.38 s)
200000 of 1000000 tuples (20%) done (elapsed 0.42 s, remaining 1.68 s)
300000 of 1000000 tuples (30%) done (elapsed 0.70 s, remaining 1.63 s)
400000 of 1000000 tuples (40%) done (elapsed 0.83 s, remaining 1.25 s)
500000 of 1000000 tuples (50%) done (elapsed 1.04 s, remaining 1.04 s)
600000 of 1000000 tuples (60%) done (elapsed 1.27 s, remaining 0.85 s)
700000 of 1000000 tuples (70%) done (elapsed 1.44 s, remaining 0.62 s)
800000 of 1000000 tuples (80%) done (elapsed 1.65 s, remaining 0.41 s)
900000 of 1000000 tuples (90%) done (elapsed 1.86 s, remaining 0.21 s)
1000000 of 1000000 tuples (100%) done (elapsed 2.88 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
[nuko@localhost ~]$
```

pgbenchをデフォルトトランザクションで動かすと、pgbench_accounts, pgbench_branches, pgbench_tellersに対してUPDATE文が発行される。
現状はあえてfillfactorを100にしているので、どのテーブルもHOT更新がされにくく、肥大化しやすいはず。

```
bench=# \d+ pgbench_accounts
                       Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers | Storage  | Stats target | Description 
----------+---------------+-----------+----------+--------------+-------------
 aid      | integer       | not null  | plain    |              | 
 bid      | integer       |           | plain    |              | 
 abalance | integer       |           | plain    |              | 
 filler   | character(84) |           | extended |              | 
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Options: fillfactor=100
```

scale factorを10にして動かしているので、pgbench_branchesの初期件数は10件。リレーションサイズは8192バイト(1ページ)。pgbench_tellersの初期件数は100件、リレーションサイズは2ページ。
この状態でpg_squeezeを無効化/有効化して10万トランザクション実行してみる。

```
[nuko@localhost ~]$ pgbench -U postgres bench -c 4 -t 25000
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 4
number of threads: 1
number of transactions per client: 25000
number of transactions actually processed: 100000/100000
latency average = 3.371 ms
tps = 1186.728938 (including connections establishing)
tps = 1186.921844 (excluding connections establishing)
[nuko@localhost ~]$
```

## pgbench実行前/実行後のリレーションサイズ
10万回pgbenchのデフォルトトランザクション実行後のテーブルサイズをpg_relation_size()の値で比較してみた(サイズはbyte)。

|設定  |初期サイズ |pg_squeezeなし |pg_squeezeあり|
|:--|--:|--:|--:|
|pgbench_tellers  |16384     |204800               |49152|
|pgbench_branches |8192      |65536                |16384|

![20161104-pg_squeeze.png](https://qiita-image-store.s3.amazonaws.com/0/32626/1e9f8d26-a223-413b-6a9d-20015a51912c.png)


お、どちらのテーブルも25%程度のサイズに縮小されてますね。

# おわりに
まだβバージョンなので、使い倒すといろいろトラブルもあるかもしれないが(例えば、再度CREATE EXTENSIONするときにバックエンド異常が起きたりもした)、テーブルが肥大化しがちな案件であれば、試しに使ってみるのもありかな、という印象はもった。
あとは、再編成中にどのくらい性能に影響がでるか、どこかで測定しておきたいもの。