pg_prewarmと複合indexを用いてPostgreSQLをチューニングし、劇的な改善を実現した話

技術本部の相澤です。日頃はSQLかSQLを出力するプログラムばかり書いています。
先日、PostgreSQL9.6系の検索アプリをチューニングする機会があり、pg_prewarmと複合indexを用いることで、劇的な改善を図ることができました。今回はそのときに使ったその2つの技術についてご紹介します。

チューニングしたアプリの概要

はじめに、今回私がチューニングしたアプリの概要をご説明します。

• PostgreSQL9.6系で動作している。
• アプリケーションのみフォルシアにて管理、OSは顧客が管理している。
• 約27000件のホテルとこれに紐づく約500万件の宿泊プランから特定の条件のホテル・プランを検索する。
• 件数増加等によりDBが肥大化し、postgres再起動時の検索と大量のプランがヒットする場合の施設検索が遅くなっていた。
• postgresのキャッシュのヒット率は良くないが、仕様の都合上改善できないので対象外とした。

データ構造と検索時のSQL

次に、データ構造の概要をご説明します。施設にはuniq にhotel_id、プランにはplan_idが割り振られています。
以下、テーブル構造です。

• hotel：hotel_idをprimary keyにしたホテルマスタ。施設の全情報を持つ。（施設並び順keyも然り）
• plan：plan_idをprimary keyにしたホテルマスタ。プランの全情報持つ。またjoin keyとしてhotel_idをもつ。

プラン情報から施設検索の際に発行されるSQLは次のとおりです。

SELECT {Hテーブル上のカラム}
FROM (
    SELECT hotel_id
    FROM plan P
    WHERE {検索条件から発行されるWHERE句}
    GROUP BY P.hotel_id
)s
INNER JOIN hotel H USING(hotel_id)
ORDER BY H.hotelorderkey1, H.hotelorderkey2, H.hotelorderkey3....
OFFSET.... LIMIT....  {一回に必要なのは10件程度}

チューニング方法

• postgres再起動時のチューニングには PostgreSQL9.4から実装されているEXTENSIONのpg_prewarmを適切に設定することで対応します。
• 大量のプランがヒットする場合の施設検索に関しては適切にindexを貼り、GROUP BY を速くすれば良いだろうということが実行計画から読み取れたので、複合indexを採用しチューニングしました。

方法１ pg_prewarmの設定によるチューニング

pg_prewarmとは？

SELECT pg_prewarm('hotel', 'buffer', 'main'); のようにテーブル名を指定して、特定のリレーションをキャッシュするモジュールです。postgresサーバーを再起動すると、テーブルがメモリから落ちてしまいます。テーブルがメモリに載っていない状態のままオンラインに出してしまうと、最初に一気にテーブルを読み込み、大量のI/Oが発生してしまい高負荷となりますが、pg_prewarmを用いて予めキャッシュしておけば高負荷を避けることができます。
また、キャッシュに乗り切らないテーブルサイズがある場合でも、キャッシュに乗る範囲でテーブルをキャッシュしておくだけで高負荷を避けることができます。

＜pg_prewarm 公式ドキュメント＞
https://www.postgresql.jp/document/9.6/html/pgprewarm.html

3つのモードはどれを使うべきか

pg_prewarmには3つのモードがあります。

以下の理由から、bufferを採用しました。

• 理由1：サービスに関してフォルシアでOSの管理をしておらず、権限や設定に関して変更ができないため。
• 理由2：pg_buffercacheを使えば、バッファキャッシュにどのテーブルがどれだけ乗っているか確認できるため（具体的な確認のSQLは下に記載があります）。

4つのテーブルをメモリに乗せるには、どの書き方が良いか

pg_prewarmを呼び出すクエリの書き方は、大きく2通り考えることが出来ます。今回は双方を検証しました。

パターン1：一つのSELECT文として呼び出す。

SELECT
     pg_prewarm('table1', 'buffer', 'main')
    ,pg_prewarm('table2', 'buffer', 'main')
    ,pg_prewarm('table3', 'buffer', 'main')
    ,pg_prewarm('table4', 'buffer', 'main')
    ; 

パターン2：それぞれ別のSELECT文として呼び出す。

SELECT     pg_prewarm('table1', 'buffer', 'main');
SELECT     pg_prewarm('table2', 'buffer', 'main');
SELECT     pg_prewarm('table3', 'buffer', 'main');
SELECT     pg_prewarm('table4', 'buffer', 'main');

パターン1は、テーブルサイズを大きくしていくとクエリが長時間になりすぎて異常を検知してしまいました。パターン2では一つずつSQLが実行され、キャッシュしきれない場合は前から順に落ちていくだけでした。このため、パターン2の書き方で重要性の高い(I/Oにつながりやすい)テーブルを後ろで呼び出すように温めてやるのがよいと考えました。

テーブルがキャッシュから落ちているかどうかはEXTENSIONのpg_buffercacheを使うことで調査可能です。

CREATE EXTENSION pg_buffercache;
SELECT
    C.relname
    ,count(*) AS buffers
FROM
    pg_buffercache B
INNER JOIN pg_class C
    ON b.relfilenode = pg_relation_filenode(c.oid)
    AND
    b.reldatabase IN (
        0
        ,(
            SELECT oid
            FROM pg_database
            WHERE datname = current_database()
        )
    )
GROUP BY C.relname
ORDER BY 2 DESC
;

                 relname                | buffers 
----------------------------------------+---------
        plan                            |  167927
        hotel                           |    3418

＜参考にしたブログ＞
PostgreSQL Deep Dive

＜pg_buffercache 公式ドキュメント＞
https://www.postgresql.jp/document/9.6/html/pgprewarm.html

I/Oの高い時間を避ける

pg_prewarmはそれなりに時間が掛かるクエリで、実行中はI/Oが高いです。postgres再起動時に、pg_prewarmの終了を待って検索が行われるように設定をいれたところ、postgres再起動後の初期検索におけるI/Oが激減しました。

結果

元の状態ではpostgres再起動後はじめてのオンライン投入時に、高負荷な状態が5～10分程度続いていましたが、prewarmを採用した場合はオフライン状態2～5分程でI/Oが落ち着くようになりました。

方法２ 複合indexによるチューニング

検索ロジックについては、プラン情報から施設検索の際に発行されるSQLは以下のとおりです（再掲載）。

SELECT {Hテーブル上のカラム}
FROM (
    SELECT hotel_id
    FROM plan P
    WHERE {検索条件から発行されるWHERE句}
    GROUP BY P.hotel_id
)s
INNER JOIN hotel H USING(hotel_id)
ORDER BY H.hotelorderkey1, H.hotelorderkey2, H.hotelorderkey3....
OFFSET.... LIMIT....  {一回に必要なのは10件程度}

検索テーブルやjoinキーには基本的にindexが張ってありますが、複合indexはありませんでした。そのためWHERE句で十分にplanが絞れなかった場合に、GROUP BYの処理に時間がかかってしまっていました。

hotel_orderに関して、当然ながらhotel_idによって一意に定まります。ならばplanテーブルにあらかじめ持たせることで、以下のようなSQLに変更しても同じ意味になります。

カラムの定義

ROW_NUMBER () OVER (ORDER BY H.hotelorderkey1, H.hotelorderkey2, H.hotelorderkey3....) AS hotel_order

SELECT {Hテーブル上のカラム}
FROM (
    SELECT P.hotel_order, P.hotel_id
    FROM plan P
    WHERE {検索条件から発行されるWHERE句}
    GROUP BY P.hotel_order, P.hotel_id -- hotel_idだけのときと同じです
    ORDER BY P.hotel_order, P.hotel_id -- hotel_orderだけのときの同じです
)s
INNER JOIN hotel H USING(hotel_id)
ORDER BY  s.hotel_order  -- inner joinの前後で施設並び順は維持されないので、再度並べ直しが必要です
OFFSET.... LIMIT....  {一回に必要なのは10件程度}

SELECT, GROUP BY,ORDER のキーが一致しましたので以下の複合indexを用意すれば、indexを用いて高速でサブクエリを終えることができます。

CREATE INDEX hotel_order_index_on_plan ON plan (hotel_order, hotel_id);

多様なORDERパターンがありますが、このようなhotel_orderを何種類か準備すればいいだけ。DB構築も検索ロジックもそう難しくなるものではありません。

結果

もともと2秒以上かかっていた検索クエリが、約1/700の3msで終了するようになりました。余談ですがこれをサービス反映したところ、検索が早くなるだけではなく、前半で問題にしていたI/Oも更に下がりました。

さいごに

上記2つの改修は、実際のサービスを使う際にも体感できるレベルの改善になりました。遅いクエリに着目した高速化は、ユーザーの最も悪い経験を向上させるため、コスパが良いと感じています。ご参考になれば幸いです。