#author("2019-12-09T15:13:38+00:00","default:haikikyou","haikikyou")
[[PostgreSQL/調査検証]]
#contents
* パーティション性能 [#f665c62f]
PostgreSQLでは、バージョン10から宣言的パーティションが使えるようになっている。~
10以前はトリガを使用することで、別々のテーブルにアクセスする必要があった。~
またPostgreSQL11から12に変わり、パーティション性能の向上が図られた。~
ここでは、簡単なアプリケーションを使ってパーティション性能について測定した結果を参考までに記載する。
* 測定環境 [#u4794023]
個人用のデスクトップ環境で以下の通り。
|~項目|~スペック|h
|OS macOS High Sierra|10.13.6（17G9016）|
|Model|iMac (27-inch, Late 2012)|
|CPU|3.2 GHz Intel Core i5|
|メモリ|32 GB 1600 MHz DDR3|
|グラフィックス|NVIDIA GeForce GTX 680MX 2048 MB|
* 測定条件 [#u27250eb]
+ PostgreSQL 11.6 / 12.1
+ パーティション / パーティション子テーブルアクセス / 非パーティション
+ シングルプロセス / マルチプロセス
* 測定用資材 [#n8223f22]
測定に使ったアプリケーション一式は、以下の通り。
** アプリケーション [#oec28e2d]
psycopg2のインストールが必要
- &ref(bench.py);
#geshi{{{
$ pip install psycopg2
}}}
**セットアップ＆実行スクリプト [#mc2d153c]
- &ref(partition_test.sh);
- &ref(run_bench_test_suite.sh);
- &ref(run_bench_test_suite_multiproc.sh);
* 結果 [#p0b743d7]
- PostgreSQL11では、パーティション数が10、100と上がるにつれて、大幅に性能が落ちる。~
ただし、子テーブルに直接アクセスすれば、PostgreSQL12ともほとんど変わらない。
- 一方、PostgreSQL12では、パーティション数の増加につれほとんど性能の劣化がない。~
3000パーティションまで上げたが、パーティション数10の時と比較してもほとんど処理時間は変わらなかった。
** 1プロセス [#aed64c6c]
- 1プロセスで、10テーブル、テーブルあたり1000件更新（10000件更新）
- PostgreSQL11の1000パーティション以降は、測定マシン環境で長時間終わらず測定断念
#ref(./partition-1proc.png)
** 5プロセス [#c7d17821]
- 5プロセスで、10テーブル、テーブルあたり1000件更新（10000件更新）
- 5プロセスは、それぞれパーティションレンジの異なるテーブルにアクセス
- PostgreSQL11の1000パーティション以降は、測定マシン環境で長時間終わらず測定断念
#ref(./partition-5proc.png)
* PostgreSQL11と12でのパーティションのロック [#c4cde79f]
** PostgreSQL11 [#h4e5e85e]
PostgreSQL11では、親テーブルのロックを得ると、データレンジにない子テーブルのロックも獲得される。
#geshi(sql){{{
$ ./partition_test.sh --part 1000 20
$ psql -d postgres
psql (11.6)
Type "help" for help.
postgres=# \d+ part_*;
Table "public.part_tbl"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
msg | text | | | | extended | |
Partition key: RANGE (id)
Partitions: part_tbl_1 FOR VALUES FROM (1) TO (1001),
part_tbl_10 FOR VALUES FROM (9001) TO (10001),
part_tbl_11 FOR VALUES FROM (10001) TO (11001),
part_tbl_12 FOR VALUES FROM (11001) TO (12001),
part_tbl_13 FOR VALUES FROM (12001) TO (13001),
part_tbl_14 FOR VALUES FROM (13001) TO (14001),
part_tbl_15 FOR VALUES FROM (14001) TO (15001),
part_tbl_16 FOR VALUES FROM (15001) TO (16001),
part_tbl_17 FOR VALUES FROM (16001) TO (17001),
part_tbl_18 FOR VALUES FROM (17001) TO (18001),
part_tbl_19 FOR VALUES FROM (18001) TO (19001),
part_tbl_2 FOR VALUES FROM (1001) TO (2001),
part_tbl_20 FOR VALUES FROM (19001) TO (20001),
part_tbl_3 FOR VALUES FROM (2001) TO (3001),
part_tbl_4 FOR VALUES FROM (3001) TO (4001),
part_tbl_5 FOR VALUES FROM (4001) TO (5001),
part_tbl_6 FOR VALUES FROM (5001) TO (6001),
part_tbl_7 FOR VALUES FROM (6001) TO (7001),
part_tbl_8 FOR VALUES FROM (7001) TO (8001),
part_tbl_9 FOR VALUES FROM (8001) TO (9001)
Table "public.part_tbl_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
msg | text | | | | extended | |
Partition of: part_tbl FOR VALUES FROM (1) TO (1001)
Partition constraint: ((id IS NOT NULL) AND (id >= 1) AND (id < 1001))
-- ページ節約のため、残りのテーブル情報については省略
postgres=# begin;
BEGIN
postgres=# select id from part_tbl where id = 1;
id
----
1
(1 row)
postgres=# update part_tbl set msg = 'hoge' where id = 1;
UPDATE 1
}}}
#geshi(sql){{{
-- select実行後にpg_locksを参照
postgres=# select pl.pid, pc.relname, pl.mode from pg_locks pl inner join pg_class pc on (pl.relation = pc.oid) where pl.pid = 83066;
pid | relname | mode
-------+-------------+-----------------
83066 | part_tbl | AccessShareLock
83066 | part_tbl_17 | AccessShareLock
83066 | part_tbl_5 | AccessShareLock
83066 | part_tbl_18 | AccessShareLock
83066 | part_tbl_9 | AccessShareLock
83066 | part_tbl_13 | AccessShareLock
83066 | part_tbl_15 | AccessShareLock
83066 | part_tbl_16 | AccessShareLock
83066 | part_tbl_20 | AccessShareLock
83066 | part_tbl_1 | AccessShareLock
83066 | part_tbl_10 | AccessShareLock
83066 | part_tbl_2 | AccessShareLock
83066 | part_tbl_6 | AccessShareLock
83066 | part_tbl_3 | AccessShareLock
83066 | part_tbl_7 | AccessShareLock
83066 | part_tbl_11 | AccessShareLock
83066 | part_tbl_14 | AccessShareLock
83066 | part_tbl_4 | AccessShareLock
83066 | part_tbl_8 | AccessShareLock
83066 | part_tbl_12 | AccessShareLock
83066 | part_tbl_19 | AccessShareLock
(21 rows)
-- update実行後に再度pg_locksを参照
postgres=# select pl.pid, pc.relname, pl.mode from pg_locks pl inner join pg_class pc on (pl.relation = pc.oid) where pl.pid = 83066;
pid | relname | mode
-------+-------------+------------------
83066 | part_tbl | RowExclusiveLock
83066 | part_tbl | AccessShareLock
83066 | part_tbl_17 | RowExclusiveLock
83066 | part_tbl_17 | AccessShareLock
83066 | part_tbl_5 | RowExclusiveLock
83066 | part_tbl_5 | AccessShareLock
83066 | part_tbl_18 | RowExclusiveLock
83066 | part_tbl_18 | AccessShareLock
83066 | part_tbl_9 | RowExclusiveLock
83066 | part_tbl_9 | AccessShareLock
83066 | part_tbl_13 | RowExclusiveLock
83066 | part_tbl_13 | AccessShareLock
83066 | part_tbl_15 | RowExclusiveLock
83066 | part_tbl_15 | AccessShareLock
83066 | part_tbl_16 | RowExclusiveLock
83066 | part_tbl_16 | AccessShareLock
83066 | part_tbl_20 | RowExclusiveLock
83066 | part_tbl_20 | AccessShareLock
83066 | part_tbl_1 | RowExclusiveLock
83066 | part_tbl_1 | AccessShareLock
83066 | part_tbl_10 | RowExclusiveLock
83066 | part_tbl_10 | AccessShareLock
83066 | part_tbl_2 | RowExclusiveLock
83066 | part_tbl_2 | AccessShareLock
83066 | part_tbl_6 | RowExclusiveLock
83066 | part_tbl_6 | AccessShareLock
83066 | part_tbl_3 | RowExclusiveLock
83066 | part_tbl_3 | AccessShareLock
83066 | part_tbl_7 | RowExclusiveLock
83066 | part_tbl_7 | AccessShareLock
83066 | part_tbl_11 | RowExclusiveLock
83066 | part_tbl_11 | AccessShareLock
83066 | part_tbl_14 | RowExclusiveLock
83066 | part_tbl_14 | AccessShareLock
83066 | part_tbl_4 | RowExclusiveLock
83066 | part_tbl_4 | AccessShareLock
83066 | part_tbl_8 | RowExclusiveLock
83066 | part_tbl_8 | AccessShareLock
83066 | part_tbl_12 | RowExclusiveLock
83066 | part_tbl_12 | AccessShareLock
83066 | part_tbl_19 | RowExclusiveLock
83066 | part_tbl_19 | AccessShareLock
(42 rows)
}}}
** PostgreSQL12 [#fe0f57e7]
PostgreSQL12では、対象の子テーブルのみのロックが取られている。
#geshi(sql){{{
$ psql -d postgres
psql (12.1)
Type "help" for help.
postgres=# begin;
BEGIN
postgres=# select id from part_tbl where id = 1;
id
----
1
(1 row)
postgres=# update part_tbl set msg = 'hoge' where id = 1;
UPDATE 1
}}}
#geshi(sql){{{
postgres=# select pl.pid, pc.relname, pl.mode from pg_locks pl inner join pg_class pc on (pl.relation = pc.oid) where pl.pid = 86768;
pid | relname | mode
-------+------------+-----------------
86768 | part_tbl | AccessShareLock
86768 | part_tbl_1 | AccessShareLock
(2 rows)
postgres=# select pl.pid, pc.relname, pl.mode from pg_locks pl inner join pg_class pc on (pl.relation = pc.oid) where pl.pid = 86768;
pid | relname | mode
-------+------------+------------------
86768 | part_tbl | RowExclusiveLock
86768 | part_tbl | AccessShareLock
86768 | part_tbl_1 | RowExclusiveLock
86768 | part_tbl_1 | AccessShareLock
(4 rows)
}}}
* 参考リンク [#gcf14bec]
- [[PostgreSQL 12は ここがスゴイ！ ～性能改善やpluggable storage engineなどの新機能を徹底解説～ （NTTデータ テクノロジーカンファレンス 2019講演資料）>https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada]]- &size(11){&color(gray){on https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada};};
- [[PostgreSQL: パーティションし過ぎには気をつけろ！>https://qiita.com/KazuyaTomita/items/c50d47111150e9b8d503]] - &size(11){&color(gray){on https://qiita.com/KazuyaTomita/items/c50d47111150e9b8d503};};
