初めに
PostgreSQLはオープンソースのリレーショナルデータベース管理システムで、その中にはパフォーマンスを最適化するための様々な設定項目があります。その一つがeffective_cache_sizeです。
effective_cache_sizeについて
effective_cache_sizeは、PostgreSQLがクエリの実行計画を立てる際に、OSのキャッシュとPostgresの共有バッファにどれだけのデータが収まるのか見積もりを設定するパラメータです。この値は、データベースエンジンがどの程度ディスクI/Oに依存するかを判断するためのもので、データベースのパフォーマンスに大きな影響を与えます。
この値を大きく設定すると、PostgreSQLはデータの一部がキャッシュに残っていると想定し、スキャンのコストを低く見積もります。しかし、実際のキャッシュのサイズがこの値よりも小さい場合、想定外のディスクI/Oが発生し、パフォーマンスが低下する可能性があります。
逆に、この値を小さく設定すると、PostgreSQLはデータの大部分がディスクから読み込まれると想定し、スキャンのコストを高く見積もります。だからと言ってこの値を極端に小さく設定すれば良いわけではありません。なぜなら、キャッシュが十分に利用されていないと考えられ、パフォーマンスが低下する可能性があるからです。
したがって、effective_cache_sizeを設定する際は、システムの実際のメモリ使用状況を良く理解し、適切なバランスを見つけることが重要です。
検証
実際の挙動については、以下のように検証することができます。
設定の確認
effective_cache_sizeの設定を確認する。
postgres=# show effective_cache_size;
effective_cache_size
----------------------
4GB
(1 行)
テストテーブルの作成
まずはテスト用のテーブルを作成します。
CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
data TEXT
);
ランダムデータの挿入
次に、テストテーブルにランダムなテキストデータを大量に挿入します。
INSERT INTO test_table (data)
SELECT md5(random()::text)
FROM generate_series(1, 1000000);
このSQLはランダムなテキストデータを1,000,000レコード生成し挿入します。
ベースラインパフォーマンスの測定
ベースラインとなるパフォーマンスを測定するために、次のようなクエリを実行します。
EXPLAIN ANALYZE SELECT * FROM test_table WHERE data LIKE '%a%';
このクエリはテーブルから特定のパターンに一致するレコードを検索します。EXPLAIN ANALYZEを使用すると、クエリの実行計画と実行時間が表示されます。これがベースラインのパフォーマンスです。
結果
postgres=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE data LIKE '%a%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on test_table (cost=0.00..20834.00 rows=868687 width=37) (actual time=0.420..1456.837 rows=872800 loops=1)
Filter: (data ~~ '%a%'::text)
Rows Removed by Filter: 127200
Planning Time: 3.317 ms
Execution Time: 1718.939 ms
(5 行)
effective_cache_sizeの調整
次に、PostgreSQLの設定でeffective_cache_sizeの値を調整します。例えば、以下のように設定を変更することができます。
ALTER SYSTEM SET effective_cache_size='8GB';
SELECT pg_reload_conf();
パフォーマンスの再測定
effective_cache_sizeを変更した後、同じクエリを再度実行し、パフォーマンスを再測定します。
EXPLAIN ANALYZE SELECT * FROM test_table WHERE data LIKE '%a%';
結果
postgres=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE data LIKE '%a%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on test_table (cost=0.00..20834.00 rows=868687 width=37) (actual time=0.432..1469.091 rows=872800 loops=1)
Filter: (data ~~ '%a%'::text)
Rows Removed by Filter: 127200
Planning Time: 2.458 ms
Execution Time: 1734.722 ms
(5 行)
結果の比較と解析
最後に、effective_cache_sizeを変更前後のクエリ実行時間を比較します。これにより、effective_cache_sizeの値がクエリパフォーマンスにどのように影響するかを確認できます。今回はいまいち設定変更の影響を受けなかったので検証としてはいまいち。検証方法を改めます。
以上、PostgreSQLのeffective_cache_sizeについての基本的な説明とその挙動についての簡単な説明でした。最後に纏めです。
まとめ
effective_cache_sizeの定義
effective_cache_sizeは、PostgreSQLがクエリ実行計画を作成する際に想定する、OSのキャッシュとPostgreSQLの共有バッファに収まるデータ量を設定するパラメータであること。
影響
effective_cache_sizeの値が大きすぎると、ディスクI/Oが予想以上に発生し、パフォーマンスが低下する可能性がある。逆に値が小さすぎると、キャッシュが十分に利用されず、パフォーマンスが下がる可能性がある。
パフォーマンスチューニング
effective_cache_sizeを最適に設定するためには、実際のメモリ使用状況を理解し、適切なバランスを見つけることが重要である。
実際の挙動の検証
effective_cache_sizeの値を変更して、その影響を具体的に観察することができる。これにより、システムのパフォーマンスを最適化するための参考情報を得ることができる。