はじめに
こんにちは！ディップ株式会社、データベースエンジニアの伊藤大地と申します。
弊社のはたらこねっとというサービスでは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
Discussion