PostgreSQL: パーティションし過ぎには気をつけろ！

バージョン

PostgreSQL 10.6

概要

DBのパフォーマンスをあげる上でパーティションは非常に有用ですが、PostgreSQLのパーティショニングは困った部分もあります。それはパーティション数が多くなると取得するロック数も大きくなる点です。

ちょっと1行で説明するのは大変で、具体的な例をあげて説明します。

以下のようなテーブルがあります。

CREATE TABLE public.hoge (
  id BIGSERIAL NOT NULL -- ID
  ,name TEXT NOT NULL DEFAULT '' -- 名前
  ,PRIMARY KEY(id)
);

このテーブルのidで分割されたhoge_childテーブルがあります。

CREATE TABLE public.hoge_child (
  id BIGSERIAL NOT NULL -- ID
  ,hoge_id BIGSERIAL NOT NULL -- hogeのID
) PARTITION BY LIST (hoge_id);

まず、hogeテーブルにレコードをいくつか入れます。

INSERT INTO public.hoge(
  name
  ) VALUES (
    'aaa'
)
;

INSERT INTO public.hoge(
  name
  ) VALUES (
    'bbb'
)
;

そして、hoge_childテーブルを作って、データを適当に入れます。

CREATE TABLE IF NOT EXISTS public.hoge_child_1 PARTITION OF public.hoge_child (PRIMARY KEY(id, hoge_id)) FOR VALUES  IN ('1');

INSERT INTO public.hoge_child(
  hoge_id
  ) VALUES (
    1
)
;

CREATE TABLE IF NOT EXISTS public.hoge_child_2 PARTITION OF public.hoge_child (PRIMARY KEY(id, hoge_id)) FOR VALUES  IN ('2');

INSERT INTO public.hoge_child(
  hoge_id
  ) VALUES (
    2
)
;

上記のSQLを実行して、psqlでpostgresというユーザーでPostgreSQLに繋いで簡単なSQLを実行してみます。

まずはトランザクションを開始します。

postgres=> BEGIN;
BEGIN

非常に簡単なクエリを実行します。

postgres=> select * from hoge_child where hoge_id = 1;
 id | hoge_id
----+---------
  1 |       1
(1 row)

次は分かりにくいですが、このバックエンドプロセスが保持しているロックの一覧を出しています。

postgres=> SELECT
  *
FROM
  pg_locks AS t1
  INNER JOIN (
    SELECT
      t4.oid, t4.relname
    FROM
      pg_class AS t4
    ) AS t5 ON t1.relation = t5.oid
WHERE
  pid = 15041
;
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      mode       | granted | fastpath |
  oid  |              relname
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------+
-------+-----------------------------------
 relation |    12295 |    16440 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
 16440 | hoge_child
 relation |    12295 |    16445 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
 16445 | hoge_child_1
 relation |    12295 |    16450 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
 16450 | hoge_child_1_pkey
 relation |    12295 |    16452 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
 16452 | hoge_child_2
 relation |    12295 |    16457 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
 16457 | hoge_child_2_pkey
 relation |    12295 |     2662 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
  2662 | pg_class_oid_index
 relation |    12295 |     2663 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
  2663 | pg_class_relname_nsp_index
 relation |    12295 |     3455 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
  3455 | pg_class_tblspc_relfilenode_index
 relation |    12295 |     1259 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
  1259 | pg_class
 relation |    12295 |    11577 |      |       |            |               |         |       |          | 3/66               | 15041 | AccessShareLock | t       | t        |
 11577 | pg_locks
(10 rows)

hoge_child_1だけでなく、hoge_child_2のロックも保持していることが分かります。共有テーブルにあるロック情報を保持しているハッシュテーブルの大きさはPostgreSQLの起動時に決まります。ハッシュテーブルの大きさを決めるパラメータは主にMaxConnectionsとmax_locks_per_xactです。前者は最大接続数、後者は1つのトランザクションの平均取得ロック数です。これらが実情とかけ離れていると思わぬ問題を引き起こします。著者の環境では、次のようなことが起こっていました。1万程度のテーブルに分割されているテーブルにSELECT文を実行すると、10000個のテーブルロック（とindexへのロック）が保持されます。一方で、ハッシュテーブルのサイズを決めるパラメータであるMaxConnectionsは800程度、max_locks_per_xactは64となっていました。こうした状況では、複数のトランザクションが数万個のテーブルロックを保持すると途端にDBのパフォーマンスが低下するということが起こっていました。

実装を覗く

分割されたテーブルのロックを取る部分のソースを見ようと思ったのですが、うまく見つけることができませんでした。なので、gdbを使ってデバッグすることにしました。
VagrantとVirtualBoxを使って、仮想環境上で、PostgreSQLをデバッグフラグを立ててビルドして、起動します。だいたい以下のような感じです。OSはdebian 9.8です。

vagrant up
vagrant ssh
# 以下は仮想環境内で、ユーザーはvagrant
wget http://ftp.postgresql.org/pub/source/v10.6/postgresql-10.6.tar.gz
tar xzf postgresql-10.6.tar.gz
cd postgresql-10.6
# ビルドに必要なライブラリをインストール
sudo apt-get update && sudo apt-get install gcc gdb libreadline-dev zlib1g-dev make
./configure --prefix=$HOME/postgresql-10.6/ --enable-debug --enable-cassert
# viでファイルを開き、CFLAGSの-O2を削除
vi src/Makefile.global
make && make install
export PATH=/home/vagrant/postgresql-10.6/src/bin:$PATH
initdb -D $HOME/database
pg_ctl -D /home/vagrant/database -l /home/vagrant/database/logfile start
# psqlでDBに入るためのユーザーを作る
createuser -h localhost -p 5432 -U vagrant postgres

だいたいこれでpsqlで中に入れると思います。バックエンドプロセスにgdbをアタッチします。ただし、15041はバックエンドプロセスのpidです。シンボルファイルはこのときに与えてやります。

gdb postgres 15041

そうすると、入力待ちになります。

PostgreSQLではテーブルのロックを取得するときにLockAcquireExtended()という関数を呼ぶのでとりあえず、そこにbreak pointを仕込むことにしました。

(gdb) b LockAcquireExtended
Breakpoint 1 at 0x5614476ef741: file lock.c, line 715.

ここで、SELECT * FROM hoge_child WHERE hoge_id = 1のようなSQLを実行すると途中で止まります。
最初に止まるのはシステムカタログに対するロックを取得するものだと思われます。そこで、最初を飛ばして何回目かでbtと打つと、次のような出力が得られます。

((gdb) bt
#0  LockAcquireExtended (locktag=0x7ffdf8c3c100, lockmode=1, sessionLock=0 '\000', dontWait=0 '\000', reportMemoryError=1 '\001', locallockp=0x7ffdf8c3c0f8) at lock.c:715
#1  0x00005614476ed9dc in LockRelationOid (relid=2611, lockmode=1) at lmgr.c:113
#2  0x0000561447351a2f in relation_open (relationId=2611, lockmode=1) at heapam.c:1122
#3  0x0000561447351cfa in heap_open (relationId=2611, lockmode=1) at heapam.c:1288
#4  0x000056144741ae4d in find_inheritance_children (parentrelId=16440, lockmode=1) at pg_inherits.c:84
#5  0x000056144741b0ee in find_all_inheritors (parentrelId=16440, lockmode=1, numparents=0x0) at pg_inherits.c:203
#6  0x000056144763d3fd in expand_inherited_rtentry (root=0x561448a54410, rte=0x561448a53950, rti=1) at prepunion.c:1422
#7  0x000056144763d2b7 in expand_inherited_tables (root=0x561448a54410) at prepunion.c:1342
#8  0x00005614476228f1 in subquery_planner (glob=0x561448a54378, parse=0x561448a53838, parent_root=0x0, hasRecursion=0 '\000', tuple_fraction=0) at planner.c:608
#9  0x0000561447621f54 in standard_planner (parse=0x561448a53838, cursorOptions=256, boundParams=0x0) at planner.c:333
#10 0x0000561447621ce2 in planner (parse=0x561448a53838, cursorOptions=256, boundParams=0x0) at planner.c:209
#11 0x000056144770e9be in pg_plan_query (querytree=0x561448a53838, cursorOptions=256, boundParams=0x0) at postgres.c:819
#12 0x000056144770eaef in pg_plan_queries (querytrees=0x561448a54340, cursorOptions=256, boundParams=0x0) at postgres.c:885
#13 0x000056144770edbf in exec_simple_query (query_string=0x561448a52710 "select * from hoge_child where hoge_id = 1;") at postgres.c:1050
#14 0x0000561447713421 in PostgresMain (argc=1, argv=0x5614489fec28, dbname=0x5614489fea88 "postgres", username=0x5614489d1bd0 "postgres") at postgres.c:4117
#15 0x0000561447670fea in BackendRun (port=0x5614489f3de0) at postmaster.c:4405
#16 0x00005614476706fa in BackendStartup (port=0x5614489f3de0) at postmaster.c:4077
#17 0x000056144766ca75 in ServerLoop () at postmaster.c:1755
#18 0x000056144766c004 in PostmasterMain (argc=3, argv=0x5614489cfa40) at postmaster.c:1363
#19 0x00005614475a609b in main (argc=3, argv=0x5614489cfa40) at main.c:228

ここで注目すべきはfind_inheritance_children(Oid parentrelId, LOCKMODE lockmode)です。これが第一引数はhoge_childテーブルのoid、第二引数はlockmode=1（AccessShareLock）で、呼ばれています。この関数のコメントは以下です。

* The specified lock type is acquired on each child relation (but not on the
* given rel; caller should already have locked it).  If lockmode is NoLock
* then no locks are acquired, but caller must beware of race conditions
* against possible DROPs of child relations.

なるほど、こいつが呼ばれているからか。。。実際、関数の中で分割テーブルたちのロックをfor文で取るようなコードとなっています。

/*
 * Acquire locks and build the result list.
 */
for (i = 0; i < numoids; i++)
{
    inhrelid = oidarr[i];

    if (lockmode != NoLock)
    {
        /* Get the lock to synchronize against concurrent drop */
        LockRelationOid(inhrelid, lockmode);

        /*
         * Now that we have the lock, double-check to see if the relation
         * really exists or not.  If not, assume it was dropped while we
         * waited to acquire lock, and ignore it.
         */
        if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(inhrelid)))
        {
            /* Release useless lock */
            UnlockRelationOid(inhrelid, lockmode);
            /* And ignore this relation */
            continue;
        }
    }

    list = lappend_oid(list, inhrelid);
}

回避策

SELECT * FROM hoge_child WHERE hoge_id = 1をSELECT * FROM hoge_child_1とすれば良いです。こうすればhoge_child_2のテーブルロックはもはや取得しません。