- バックアップ一覧
- 差分 を表示
- 現在との差分 を表示
- ソース を表示
- PostgreSQL/調査検証/パーティション へ行く。
パーティション性能 †
PostgreSQLでは、バージョン10から宣言的パーティションが使えるようになっている。
10以前はトリガを使用することで、別々のテーブルにアクセスする必要があった。
またPostgreSQL11から12に変わり、パーティション性能の向上が図られた。
ここでは、簡単なアプリケーションを使ってパーティション性能について測定した結果を参考までに記載する。
測定環境 †
個人用のデスクトップ環境で以下の通り。
項目 | スペック |
---|---|
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 |
測定条件 †
- PostgreSQL 11.6 / 12.1
- パーティション / パーティション子テーブルアクセス / 非パーティション
- シングルプロセス / マルチプロセス
- パーティション数 10 / 100 / 1000 / 2000
実行内容は以下の通り。
- 1パーティションで1000件のレコードを入れる。
- 10パーティションのレコード(10 x 1000件 = 10000件)のレコードをSELECT...FOR UPDATE、UPDATEする。
- 複数プロセス(5プロセス)の場合は、各プロセスが2パーティションを実行するよう配分する。
測定用資材 †
測定に使ったアプリケーション一式は、以下の通り。
アプリケーション †
psycopg2のインストールが必要
$ pip install psycopg2
セットアップ&実行スクリプト †
結果 †
- PostgreSQL11では、パーティション数が10、100と上がるにつれて、大幅に性能が落ちる。
ただし、子テーブルに直接アクセスすれば、PostgreSQL12ともほとんど変わらない。 - 一方、PostgreSQL12では、パーティション数の増加につれほとんど性能の劣化がない。
3000パーティションまで上げたが、パーティション数10の時と比較してもほとんど処理時間は変わらなかった。
1プロセス †
- 1プロセスで、10テーブル、テーブルあたり1000件更新(10000件更新)
- PostgreSQL11の1000パーティション以降は、測定マシン環境で長時間終わらず測定断念
5プロセス †
- 5プロセスで、10テーブル、テーブルあたり1000件更新(10000件更新)
- 5プロセスは、それぞれパーティションレンジの異なるテーブルにアクセス
- PostgreSQL11の1000パーティション以降は、測定マシン環境で長時間終わらず測定断念
PostgreSQL11と12でのパーティションのロック †
PostgreSQL11 †
PostgreSQL11では、親テーブルのロックを得ると、データレンジにない子テーブルのロックも獲得される。
$ ./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
-- 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 †
親テーブルアクセス †
PostgreSQL12では、親テーブル経由でアクセスしても、対象の子テーブルのみのロックが取られている。
SELECT ..
$ 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
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)
SELECT .. FOR UPDATE
$ psql -d postgres psql (12.1) TYPE "help" FOR help. postgres=# BEGIN; BEGIN postgres=# SELECT id FROM part_tbl WHERE id = 1 FOR UPDATE; id ---- 1 (1 行) postgres=# UPDATE part_tbl SET msg = 'hoge' WHERE id = 1; UPDATE 1
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 = 60079; pid | relname | mode -------+------------+-------------- 60079 | part_tbl | RowShareLock 60079 | part_tbl_1 | RowShareLock (2 行) 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 = 60079; pid | relname | mode -------+------------+------------------ 60079 | part_tbl | RowExclusiveLock 60079 | part_tbl | RowShareLock 60079 | part_tbl_1 | RowExclusiveLock 60079 | part_tbl_1 | RowShareLock (4 行)
子テーブルアクセス †
SELECT ..
$ psql -d postgres psql (12.1) TYPE "help" FOR help. postgres=# BEGIN; BEGIN postgres=# SELECT id FROM part_tbl_1 WHERE id = 1; id ---- 1 (1 ROW) postgres=# UPDATE part_tbl_1 SET msg = 'hoge' WHERE id = 1; UPDATE 1
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 = 60079; pid | relname | mode -------+------------+----------------- 60079 | part_tbl_1 | AccessShareLock (1 行) 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 = 60079; pid | relname | mode -------+------------+------------------ 60079 | part_tbl | AccessShareLock 60079 | part_tbl_1 | RowExclusiveLock 60079 | part_tbl_1 | AccessShareLock (3 行)
SELECT .. FOR UPDATE
$ psql -d postgres psql (12.1) TYPE "help" FOR help. postgres=# BEGIN; BEGIN postgres=# SELECT id FROM part_tbl_1 WHERE id = 1 FOR UPDATE; id ---- 1 (1 行) postgres=# UPDATE part_tbl_1 SET msg = 'hoge' WHERE id = 1; UPDATE 1
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 = 60079; pid | relname | mode -------+------------+-------------- 60079 | part_tbl_1 | RowShareLock (1 行) 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 = 60079; pid | relname | mode -------+------------+------------------ 60079 | part_tbl_1 | RowExclusiveLock 60079 | part_tbl_1 | RowShareLock (2 行)
子テーブルアクセス+親テーブルアクセス †
$ psql -d postgres psql (12.1) TYPE "help" FOR help. postgres=# BEGIN; BEGIN postgres=# SELECT id FROM part_tbl_1 WHERE id = 1; id ---- 1 (1 行) postgres=# UPDATE part_tbl SET msg = 'hoge' WHERE id = 1; UPDATE 1
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 = 60079; pid | relname | mode -------+------------+----------------- 60079 | part_tbl_1 | AccessShareLock (1 行) 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 = 60079; pid | relname | mode -------+------------+------------------ 60079 | part_tbl | RowExclusiveLock 60079 | part_tbl_1 | RowExclusiveLock 60079 | part_tbl_1 | AccessShareLock (3 行)
親テーブルアクセス+子テーブルアクセス †
$ 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 行) postgres=# UPDATE part_tbl_1 SET msg = 'hoge' WHERE id = 1; UPDATE 1
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 = 60079; pid | relname | mode -------+------------+----------------- 60079 | part_tbl | AccessShareLock 60079 | part_tbl_1 | AccessShareLock (2 行) 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 = 60079; pid | relname | mode -------+------------+------------------ 60079 | part_tbl | AccessShareLock 60079 | part_tbl_1 | RowExclusiveLock 60079 | part_tbl_1 | AccessShareLock (3 行)
ソースコードの調査 †
プランニング時のロック †
今回実行したクエリについての調査した内容である。
パーティションテーブルに関する情報の抽出 †
パーティションテーブルについては、以下で抽出されている。
参考 build_simple_rel() - https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;h=refs/heads/REL_12_STABLE
287 /* Check type of rtable entry */ 288 switch (rte->rtekind) 289 { 290 case RTE_RELATION: 291 /* Table --- retrieve statistics from the system catalogs */ 292 get_relation_info(root, rte->relid, rte->inh, rel); 293 break;
スタックトレースは以下の通り。
(lldb) bt * thread #1, queue = 'com.apple.main-thread', stop reason = step in * frame #0: 0x0000000103d98261 postgres`build_simple_rel(root=0x00007fb012033440, relid=1, parent=0x0000000000000000) at relnode.c:293 frame #1: 0x0000000103d53a2a postgres`add_base_rels_to_query(root=0x00007fb012033440, jtnode=0x00007fb011823818) at initsplan.c:114 frame #2: 0x0000000103d53a7a postgres`add_base_rels_to_query(root=0x00007fb012033440, jtnode=0x00007fb011823b60) at initsplan.c:122 frame #3: 0x0000000103d589ee postgres`query_planner(root=0x00007fb012033440, qp_callback=(postgres`standard_qp_callback at planner.c:3589), qp_extra=0x00007ffeec2a5948) at planmain.c:168 frame #4: 0x0000000103d5c8c8 postgres`grouping_planner(root=0x00007fb012033440, inheritance_update=false, tuple_fraction=0) at planner.c:2048 frame #5: 0x0000000103d5a298 postgres`subquery_planner(glob=0x00007fb011823d28, parse=0x00007fb011823388, parent_root=0x0000000000000000, hasRecursion=false, tuple_fraction=0) at planner.c:1012 frame #6: 0x0000000103d58e5b postgres`standard_planner(parse=0x00007fb011823388, cursorOptions=256, boundParams=0x0000000000000000) at planner.c:406 frame #7: 0x0000000103d58bee postgres`planner(parse=0x00007fb011823388, cursorOptions=256, boundParams=0x0000000000000000) at planner.c:275 frame #8: 0x0000000103e92879 postgres`pg_plan_query(querytree=0x00007fb011823388, cursorOptions=256, boundParams=0x0000000000000000) at postgres.c:878 frame #9: 0x0000000103e929e1 postgres`pg_plan_queries(querytrees=0x00007fb011823cf0, cursorOptions=256, boundParams=0x0000000000000000) at postgres.c:968 frame #10: 0x0000000103e957e1 postgres`exec_simple_query(query_string="SELECT id FROM part_tbl WHERE id = 1 FOR UPDATE;") at postgres.c:1143 frame #11: 0x0000000103e94b68 postgres`PostgresMain(argc=1, argv=0x00007fb011844d58, dbname="postgres", username="t-moriyasu") at postgres.c:4236 frame #12: 0x0000000103dca700 postgres`BackendRun(port=0x00007fb0116007c0) at postmaster.c:4437 frame #13: 0x0000000103dc9aea postgres`BackendStartup(port=0x00007fb0116007c0) at postmaster.c:4128 frame #14: 0x0000000103dc8a3a postgres`ServerLoop at postmaster.c:1704 frame #15: 0x0000000103dc626f postgres`PostmasterMain(argc=1, argv=0x00007fb011407260) at postmaster.c:1377 frame #16: 0x0000000103cc6149 postgres`main(argc=1, argv=0x00007fb011407260) at main.c:228 frame #17: 0x00007fff73b1b015 libdyld.dylib`start + 1 frame #18: 0x00007fff73b1b015 libdyld.dylib`start + 1 (lldb)
パーティション子テーブルに関するロック †
add_other_rels_to_query(root)で、パーティションの子テーブルもスキャン対象となり、ロックも獲得される。
子テーブルのロックモードは親テーブルと同じである。
参考 add_other_rels_to_query() - https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;h=refs/heads/REL_12_STABLE
(lldb) bt * thread #1, queue = 'com.apple.main-thread', stop reason = breakpoint 47.1 * frame #0: 0x0000000103e6ec75 postgres`LockAcquireExtended(locktag=0x00007ffeec2a54b8, lockmode=2, sessionLock=false, dontWait=false, reportMemoryError=true, locallockp=0x00007ffeec2a54b0) at lock.c:890 frame #1: 0x0000000103e6bf30 postgres`LockRelationOid(relid=16387, lockmode=2) at lmgr.c:116 frame #2: 0x000000010396fddb postgres`relation_open(relationId=16387, lockmode=2) at relation.c:56 frame #3: 0x0000000103a4b449 postgres`table_open(relationId=16387, lockmode=2) at table.c:43 frame #4: 0x0000000103d84ac7 postgres`expand_partitioned_rtentry(root=0x00007fb013000840, relinfo=0x00007fb011823dc0, parentrte=0x00007fb0118234a0, parentRTindex=1, parentrel=0x000000010490aa88, top_parentrc=0x00007fb013000c80, lockmode=2) at inherit.c:361 frame #5: 0x0000000103d8427a postgres`expand_inherited_rtentry(root=0x00007fb013000840, rel=0x00007fb011823dc0, rte=0x00007fb0118234a0, rti=1) at inherit.c:143 frame #6: 0x0000000103d53bf3 postgres`add_other_rels_to_query(root=0x00007fb013000840) at initsplan.c:163 frame #7: 0x0000000103d58a91 postgres`query_planner(root=0x00007fb013000840, qp_callback=(postgres`standard_qp_callback at planner.c:3589), qp_extra=0x00007ffeec2a5948) at planmain.c:266 frame #8: 0x0000000103d5c8c8 postgres`grouping_planner(root=0x00007fb013000840, inheritance_update=false, tuple_fraction=0) at planner.c:2048 frame #9: 0x0000000103d5a298 postgres`subquery_planner(glob=0x00007fb011823d28, parse=0x00007fb011823388, parent_root=0x0000000000000000, hasRecursion=false, tuple_fraction=0) at planner.c:1012 frame #10: 0x0000000103d58e5b postgres`standard_planner(parse=0x00007fb011823388, cursorOptions=256, boundParams=0x0000000000000000) at planner.c:406 frame #11: 0x0000000103d58bee postgres`planner(parse=0x00007fb011823388, cursorOptions=256, boundParams=0x0000000000000000) at planner.c:275 frame #12: 0x0000000103e92879 postgres`pg_plan_query(querytree=0x00007fb011823388, cursorOptions=256, boundParams=0x0000000000000000) at postgres.c:878 frame #13: 0x0000000103e929e1 postgres`pg_plan_queries(querytrees=0x00007fb011823cf0, cursorOptions=256, boundParams=0x0000000000000000) at postgres.c:968 frame #14: 0x0000000103e957e1 postgres`exec_simple_query(query_string="SELECT id FROM part_tbl WHERE id = 1 FOR UPDATE;") at postgres.c:1143 frame #15: 0x0000000103e94b68 postgres`PostgresMain(argc=1, argv=0x00007fb011844d58, dbname="postgres", username="t-moriyasu") at postgres.c:4236 frame #16: 0x0000000103dca700 postgres`BackendRun(port=0x00007fb0116007c0) at postmaster.c:4437 frame #17: 0x0000000103dc9aea postgres`BackendStartup(port=0x00007fb0116007c0) at postmaster.c:4128 frame #18: 0x0000000103dc8a3a postgres`ServerLoop at postmaster.c:1704 frame #19: 0x0000000103dc626f postgres`PostmasterMain(argc=1, argv=0x00007fb011407260) at postmaster.c:1377 frame #20: 0x0000000103cc6149 postgres`main(argc=1, argv=0x00007fb011407260) at main.c:228 frame #21: 0x00007fff73b1b015 libdyld.dylib`start + 1 frame #22: 0x00007fff73b1b015 libdyld.dylib`start + 1 (lldb)
パーティションテーブルの場合は継承フラグが立っており、子テーブルの展開が行われる。
参考 add_other_rels_to_query() - https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;h=refs/heads/REL_12_STABLE
161 /* If it's marked as inheritable, look for children. */ 162 if (rte->inh) 163 expand_inherited_rtentry(root, rel, rte, rti);
参考リンク †
- PostgreSQL 12は ここがスゴイ! ~性能改善やpluggable storage engineなどの新機能を徹底解説~ (NTTデータ テクノロジーカンファレンス 2019講演資料)- on https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada
- PostgreSQL: パーティションし過ぎには気をつけろ! - on https://qiita.com/KazuyaTomita/items/c50d47111150e9b8d503