はじめに
こんにちは！ディップ株式会社、データベースエンジニアの伊藤大地と申します。
弊社のはたらこねっとというサービスではAmazon Aurora PostgreSQLを利用しています。
パフォーマンスが突然悪くなったSQLに対してpg_hint_planのヒントテーブルを利用してアプリケーションの修正をせずに改善しました。
今回はヒントテーブルの利用方法とその際にハマったポイントについてまとめていきます。
誰に向けた記事なのか
- DBを利用する人
- PostgreSQLを使う人
- SQLチューニングをする人
前提条件
AuroraPostgreSQL ver15.8
pg_hint_plan 1.5.1
pg_hint_planとは?
pg_hint_planは、PostgreSQLにおけるクエリの実行計画にヒントを与えるための拡張モジュールです。これにより、プランナが作成する実行計画ではなく、ユーザーが指定した方法でクエリが実行されるように調整することができます。主にパフォーマンスチューニングの一環として使用されます。
pg_hint_planでできること
以下のようにpg_hint_planを利用することができます。
- SQLに対して直接ヒント句を付与
- ヒントテーブルの利用
Aurora PostgreSQLで利用するには2つの設定が必要になります。
Aurora PostgreSQLでの設定について
Aurora PostgreSQLでヒント句を利用するためには以下の設定が必要です。
- クラスターパラメーターグループの更新
- CREATE EXTENSIONの実施
クラスターパラメーターグループの更新
以下のパラメーターの更新が必要です。
今回はヒントテーブルを利用したかったのでenable_hint_tableも有効化しています。
| パラメータ名 | 設定した値 | 説明 |
|---|---|---|
| shared_preload_libraries | pg_hint_plan | pg_hint_planをプリロードさせます。 |
| pg_hint_plan.enable_hint | 1 | プランナーが、クエリの前のヒントコメントで指定された計画を使用するようにします。 |
| pg_hint_plan.enable_hint_table | 1 | プランナーが、テーブルルックアップを使用してヒントを取得します。 |
shared_preload_librariesの設定反映にはインスタンスの再起動が必要なのが注意点です。
CREATE EXTENSIONの実施
以下のコマンドを使ってpg_hint_planをインストールします。
create extension pg_hint_plan;
SQLに対して直接ヒント句を付与
pg_hint_planが利用できるようになったので早速使っていきましょう。 pg_hint_planを使用すると、SQL文に直接ヒント句を付与することができます。例えば、以下のように記述します。
/*+ SeqScan(a) */ SELECT * FROM table_a a;
上記の例では、table_aに対してシーケンシャルスキャンを強制しています。
ヒントテーブルの利用
pg_hint_planでは、ヒント句をヒントテーブルに保存し、特定のクエリが実行された際に自動的に適用させることもできます。 ここでは実際に検証した内容を記載していきます。
まず、テスト用のテーブルを作成し、データを挿入します。 DDLなどについてはAIに頼んで用意してもらいました。
テーブル作成
CREATE TABLE test_tbl ( id SERIAL PRIMARY KEY, column1 VARCHAR(255), column2 VARCHAR(255), column3 VARCHAR(255) );
インデックスの作成
ヒント句の効果を確認するために、インデックスを作成します。
CREATE INDEX test_tbl_index ON test_tbl (column3);
データ挿入
テスト用のデータを挿入します。大量のデータを挿入することで、インデックスの有効性をより明確に確認できます。
以下のSQLではgenerate_series関数を利用して1万件のデータを挿入しています。
INSERT INTO test_tbl (column1, column2, column3) SELECT md5(random()::text), md5(random()::text), CASE WHEN random() < 0.5 THEN 'value' ELSE 'other' END FROM generate_series(1, 10000);
ヒントテーブル利用前に実行計画確認
ヒントテーブルに登録すると該当のSQLが実行される際にヒント句が利用されるので先に確認しておきます。
explain (analyze, buffers)を利用した結果は以下のとおりです。
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_tbl (cost=58.91..255.21 rows=4984 width=66) (actual time=0.167..1.248 rows=4984 loops=1)
Recheck Cond: ((column3)::text = 'value'::text)
Heap Blocks: exact=134
Buffers: shared hit=140
-> Bitmap Index Scan on test_tbl_index (cost=0.00..57.66 rows=4984 width=0) (actual time=0.138..0.138 rows=4984 loops=1)
Index Cond: ((column3)::text = 'value'::text)
Buffers: shared hit=6
Planning:
Buffers: shared hit=61
Planning Time: 0.275 ms
Execution Time: 1.580 ms
(11 rows)
ヒントテーブルへの登録
SQLはスペースなども一致する必要があります。
実行計画を確認したいのでEXPLAIN (ANALYZE, BUFFERS)も含めて登録しています。
INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ( 'EXPLAIN (ANALYZE, BUFFERS) SELECT column1, column2 FROM test_tbl WHERE column3 = ?;', '', 'SeqScan(test_tbl)' );
以下のように確認できます。
select * from hint_plan.hints; id | norm_query_string | application_name | hints ---+-------------------------------------------------------------------------------------+------------------+--------------------- 6 | EXPLAIN (ANALYZE, BUFFERS) SELECT column1, column2 FROM test_tbl WHERE column3 = ?; | | SeqScan(test_tbl) (1 row)
SQLを実行してヒント句の適用を確認する
以下Seq ScanになっていてINDEXが使われていないので実行計画の作成の際にヒントテーブルに設定したヒント句を利用して
SQLが実行されたと判断できます。
EXPLAIN (ANALYZE, BUFFERS) SELECT column1, column2 FROM test_tbl WHERE column3 = 'value'; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Seq Scan on test_tbl (cost=0.00..259.00 rows=4984 width=66) (actual time=0.009..1.751 rows=4984 loops=1) Filter: ((column3)::text = 'value'::text) Rows Removed by Filter: 5016 Buffers: shared hit=134 Planning: Buffers: shared hit=1 Planning Time: 0.150 ms Execution Time: 2.069 ms (8 rows)
ヒントテーブルに登録したヒント句が効かない!?
実際に筆者がヒントテーブルを利用してヒント句を効かせようと思い検証していたところ全く効かなくて困る事象が発生しました。
まずは落ち着いて以下の点の確認を行います。
- ヒントテーブルの構造が正しいか。
- ヒント句の書式が正しいか。
- クエリがヒントテーブルで指定されたパターンに一致しているか。
確認しましたが問題ありませんでした(?)。
確認した内容としては
- ヒントテーブルの構造は正しいか。 hint_plan.hintsテーブルにSQLやヒント句を格納しています。 norm_query_stringにSQLが記載されているか、hintsにヒント句が入っているのかを確認しました。
- ヒント句の書式が正しいか。 直接ヒントテーブルに格納しているヒント句を使ってSQLを実行してみました。 ヒント句の有無での実行計画を見て指定通りの実行計画になっているのであれば、ヒントテーブルに登録したヒント句は合っていると判断しました。
- クエリがヒントテーブルで指定されたパターンに一致しているか。 ヒントテーブルに設定しているSQLと実行したSQLを比較して違いがないことを確認しました。
いったいどこに問題があるのだろうか。。。
原因は？
結論としては定数を?に置き換え漏れてました。
以下のようなSQLがあります。
SELECT * FROM ( SELECT column1, column2, column3, column4 FROM tbl_a WHERE column1 BETWEEN 1 AND 100 AND column2 = 0 AND NOT EXISTS ( SELECT 1 FROM tbl_b WHERE column3 = 1 ) ) a WHERE column4 = 'value';
ここからヒントテーブルに登録するには定数を?に置き換えないといけません。
自分は以下のように書き換えました。
SELECT column FROM ( SELECT column FROM tbl_a WHERE column BETWEEN ? AND ? AND column = ? AND NOT EXISTS ( SELECT 1 FROM tbl_b WHERE column = ? ) ) a WHERE column = ?;
お気づきでしょうか？
以下のSELECT 1にある1も定数でした。（そりゃそうじゃ）
SELECT 1 FROM tbl_b WHERE column = ?
以下のように設定して登録したら何事もなくヒントテーブルを利用してヒント句が適用されました。
SELECT column FROM ( SELECT column FROM tbl_a WHERE column BETWEEN ? AND ? AND column = ? AND NOT EXISTS ( SELECT ? FROM tbl_b WHERE column = ? ) ) a WHERE column = ?;
いざ本番適用！
検証は終えたので早速本番に適用しました。 これでパフォーマンス改善は完了！と思ったのですが実行されているSQLは遅かったのです。 実は事前にヒント句が効かないかもと予想はしていたのです。
Aurora PostgreSQLのログはCloud Watch Logsから確認することができるのですが、 実行されたSQLは以下のように「;」なしで記載されていました。
select * from xxx where yyy = 1
ヒントテーブルに登録したSQLには「;」を含めています。 「;」がないとそもそもSQLが実行できないんだから「;」を含めた方が正しいだろうと考えていたのですが 今回はCloud Watch Logsで確認した「;」なしじゃないとヒント句は効かないので注意が必要です。
筆者は事前にヒント句が効かないかもしれないと予想していたので すぐにヒント句を追加できるように準備をしていたので遅いことを確認したらすぐに適用して 今度は問題なくパフォーマンスが改善されたのでことなきを得ました。
まとめ
ヒントテーブルを利用すればアプリケーション側の改修は不要になるのでパフォーマンスチューニングがしやすくなります。 ただし、登録したSQLと実際に実行されるSQLについては一語一句一致する必要があるため注意が必要です。 このブログが参考になれば幸いです。
