はじめに
本項ではPostgreSQLのパラメータ設定を行う方法と注意点を解説します。PostgreSQLのパラメータにはいろいろありますが、ここでは一般的にpostgresql.confというパラメータ設定ファイルで設定されるもの(GUCパラメータ)を意味します。
なお、パラメータには設定変更を有効にするためPostgreSQLの再起動が必要だったり、特定の権限がないと設定・変更できなかったりと、有効化のタイミングや権限の話もありますが本項では割愛しています。あくまで設定方法にのみ焦点を当てています。
設定ファイル
一般的にPostgreSQLのパラメータ設定を行うことになるのは設定ファイルでのパラメータ指定です。
postgresql.conf
デフォルトで使用されるパラメータ設定ファイルです。postgresql.confには同一パラメータを複数回指定することができます。この場合、後から読まれたパラメータが有効になります。
work_mem = 2048
work_mem = 4096
上記をpostgresql.confに記載した場合、work_memというパラメータは4096となります。
include / include_if_exists / include_dir
postgresql.confにはinclude、include_if_exists、include_dirというパラメータを指定できます。includeにはファイル名、include_dirにはディレクトリ名を指定します。指定したファイル、およびディレクトリにある.confというサフィックスを持つファイルを追加で読み込みます。include_if_existsは指定されたファイルがあれば読み、なけれでそのまま無視します。
複数のDBクラスタで共通となる設定をpostgresql.confで指定し、個別の設定をinclude*で指定した設定ファイル名に指定することで管理を楽にできます。
なお、includeで読まれるファイルに記載されるパラメータと同じパラメータをincludeの後でも指定した場合は、前述の通りに後勝ちとなります。
# 例
include = 'myapp.conf' # myapp.conf内に work_mem = 4096を指定していたとする
work_mem = 2048 # myapp.confの設定よりここのwork_mem = 2048が優先される
その他、include_dirの中に複数の.confファイルを置いた場合、それらのファイルの読み込み順序がCロケールのファイル名に沿って決まる、などにも注意する必要があります。詳細はマニュアルを見てみてください。
postgresql.auto.conf
PostgreSQLではALTER SYSTEMというSQLでパラメータを変更することができます。このクエリで変更を行った場合、postgresql.auto.confという設定ファイルに設定が追記されます。
-- 以下のコマンドによりpostgresql.auto.confにwork_mem = '12MB'が追記される
=# ALTER SYSTEM SET work_mem TO '12MB';
ALTER SYSTEM
-- 以下のコマンドによりpostgresql.auto.confからwork_mem = ... の設定が削除される
=# ALTER SYSTEM RESET work_mem;
ALTER SYSTEM
PostgreSQL起動時のコマンドラインでのパラメータ指定
PostgreSQLの起動時にパラメータを指定することができます。
pg_ctl / postgres
PostgreSQLの起動時に使用されるpg_ctlコマンドで -o オプションと組みあわて -c パラメータ名=設定値 の形でパラメータを指定します。
$ pg_ctl -D base/pgsql175/ start -o "-c work_mem=16384" -o "-c shared_buffers='384MB'"
(省略)
server started
$ psql
psql (17.5)
Type "help" for help.
postgres=# SHOW shared_buffers ;
shared_buffers
----------------
384MB
(1 row)
postgres=# SHOW work_mem;
work_mem
----------
16MB
(1 row)
上記はshared_buffersとwork_memを設定しています。pg_ctl コマンドは、正確には postgres コマンドのラッパー的なもので、上記はpostgresコマンドに -o オプションでパラメータ指定を渡していることになります。
オブジェクトへのパラメータ指定
データベースやロールなどにパラメータを指定することができます。ワークロードや要件の異なるデータベースやロール単位で個別のパラメータ指定(ログの出し方やリソースの許容量を変える)をすることがあります。
DATABASE
データベース個別にパラメータを設定することができます。
=# ALTER DATABASE app_oltp SET random_page_cost = 1.0;
ALTER DATABASE
ROLE/USER
ロールやユーザ個別にパラメータを設定することができます。また、特定のデータベースでのみ有効となるようなパラメータ設定をすることもできます。
=# ALTER ROLE admin SET log_statement = 'all';
ALTER ROLE
-- olap_dbというデータベースでのみ、adiminロールではrandom_page_costを6.0にする
=# ALTER ROLE admin IN DATABASE app_olap SET random_page_cost = 6.0;
ALTER ROLE
データベースとロールに設定したパラメータ確認
ALTER DATABASEやALTER ROLEで行ったパラメータ設定は、pg_db_role_settingというシステム
テーブルで確認できます。そのままの場合、データベース名やロール名が分かりにくいため、以下のようなクエリを発行すると良いでしょう。
=# SELECT COALESCE(d.datname, '-') dbname, setrole::regrole rolename, setconfig FROM pg_db_role_setting p LEFT JOIN pg_database d ON p.setdatabase = d.oid;
dbname | rolename | setconfig
----------+----------+------------------------
app_oltp | - | {random_page_cost=1.0}
- | admin | {log_statement=all}
app_olap | admin | {random_page_cost=6.0}
(3 rows)
dbname(データベース名)、rolename(ロール名)が - になっている箇所はそれぞれDB共通、あるいはロール共通を意味します。例えば上記の場合、app_oltpではどのようなロールでもrandom_page_costというパラメータは1.0になります。
TABLESPACE
テーブルスペースには、seq_page_cost、random_page_cost、effective_io_concurrency、maintenance_io_concurrencyの４つのパタメータに限定して設定が可能です。
=# ALTER TABLESPACE tsp1 SET ( random_page_cost = 1.0);
ALTER TABLESPACE
設定はpg_tablespaceシステムテーブルで確認できます。
=# SELECT * FROM pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16384 | tsp1 | 10 | | {random_page_cost=1.0}
(3 rows)
TABLE / INDEX
テーブルやインデックスにも個別のパラメータ設定を行うことができます。テーブルなどに設定可能なパラメータのうち、GUCパラメータに関連するものはVACUUMやパラレルクエリに関するものとなります。
-- t1テーブルに対するautovacuumを無効にする
=# ALTER TABLE t1 SET (autovacuum_enabled = off);
ALTER TABLE
テーブルやインデックスに対して設定可能なパラメータ詳細についてはマニュアルを参考にしてください。
接続時のパラメータ指定
接続時に接続用のURIなどでパラメータを指定することができます。
接続時パラメータ
PostgreSQLのlibpq(Cクライアントライブラリ)では、接続用の文字列(URI)でoptionsによるパラメータ指定を行えます。psqlもlibpqを使っていますが、以下の例のような指定が可能です。(パラメータ指定の中で必要となる空白や=の文字列は%エンコードしています)
$ psql postgresql://localhost:5432/postgres?options=-c%20work_mem%3D3333%20-c%20log_statement%3Dddl -c "SHOW work_mem; SHOW log_statement"
work_mem
----------
3333kB
(1 row)
log_statement
---------------
ddl
(1 row)
環境変数
上記ではURIの中で指定していましたが、環境変数を使って指定することも可能です。環境変数はPGOPTIONSになります。-c オプション名=設定値 の形で複数指定できます。
$ export PGOPTIONS="-c work_mem=6666 -c log_statement=all"
$ psql -c "SHOW work_mem;SHOW log_statement;"
work_mem
----------
6666kB
(1 row)
log_statement
---------------
all
(1 row)
PostgreSQLではPGOPTIONS以外にもlibpqが使う環境変数には様々あります。
セッション内での動的な設定
データベースへの接続後に、パラメータを設定可能です。一部のドライバやPostgreSQLのpg_dumpやpg_upgradeなどのユーティリティでは必要に応じて動的にパラメータを変更しています。
SET / SET LOCAL
セッション内でSETコマンドによりパラメータを設定します。基本的にSETで設定したパラメータ指定はそのセッションが終了するまで有効です。SET LOCALコマンドを使うと現在のトランザクションが終了した際に自動で変更前の設定に戻すことができます。
=# SHOW work_mem ;
work_mem
----------
4MB
(1 row)
=# BEGIN;
BEGIN
=*# SET LOCAL work_mem = '8MB'; -- 今のトランザクション中だけwork_memを増やす
SET
=*# SHOW work_mem ;
work_mem
----------
8MB
(1 row)
=*# ABORT; -- トランザクションが終了すると元に戻る(COMMITなどでも同じ)
ROLLBACK
=# SHOW work_mem ;
work_mem
----------
4MB
(1 row)
pg_hint_plan
PostgreSQLでHINT機能を使えるようにするpg_hint_planという拡張機能では、クエリの実行時(実行計画作成時)にのみ一部のパラメータを変更できます。変更可能なのは主に実行計画に関するパラメータです。HINT句の使い方として、一般的なのはスキャン方法や結合方法の指定となるので、パラメータをHINT句で設定するのはややマイナーなケースです、
=# CREATE TABLE t1 (c1 int);
CREATE TABLE
=# INSERT INTO t1 SELECT 1;
INSERT 0 1
=# ANALYZE t1;
ANALYZE
=# EXPLAIN SELECT * FROM t1;
QUERY PLAN
--------------------------------------------------
Seq Scan on t1 (cost=0.00..1.01 rows=1 width=4)
(1 row)
-- SeqScanのコストを上げてみる(チューニングとしての意味はないです)
=# /*+ Set(seq_page_cost 10) */ EXPLAIN SELECT * FROM t1;
QUERY PLAN
---------------------------------------------------
Seq Scan on t1 (cost=0.00..10.01 rows=1 width=4)
(1 row)
pg_hint_planについては以下。
その他
その他の、やや本筋とはズレるこぼれ話的なトピックです。
initdb
initdbはDBクラスタの初期化などで使用されるコマンドです。initdbによりDBクラスタを作成しますが、その際にデフォルトなるpostgresql.confも作成されます。postgresql.confのデフォルト値をinitdb時に変更することができます。
-- 以下はpostgresql.confでの初期値としてwork_memとlog_min_duration_statementを変更している例
initdb -D base/pgsql175 --c work_mem=8000 -c log_min_duration_statement=2000
拡張機能
前述でドライバやPostgreSQLのユーティリティコマンドが内部で動的にパラメータを設定していると記載しましたが、その他にも拡張機能(Extension)内でも同様にパラメータを設定しているものもあります。
主には、余計なノイズとならないようにログ出力を抑止したり、コネクションやクエリのタイムアウトを設けて長時間のスタックが発生することを防止するなどの用途で行われます。
注意点
ここまで代表的なパラメータ設定の方法を述べてきましたが、いくつか押さえておくと良い注意点を挙げておきます。
パラメータの評価順序
パラメータの設定はいくつかの方法があることを紹介しましたが、同じパラメータを複数の方法で指定した場合にどれが有効になるかを以下に示します。上から下へ評価されていき、後(下)で指定されたものが有効になります。複数箇所でパラメータ設定をしている場合は意図しない設定で上書きされないように注意しましょう。
- postgresql.confの設定
- postgresql.auto.confの設定
- PostgreSQLの起動時の設定(pg_ctl/postgresコマンド)
- データベースの設定(ALTER DATABASE)
- ロールの設定(ALTER ROLE)
- 接続時の設定(URI指定)
- 接続時の指定(環境変数 PGOPTIONS指定)
- SETによる設定
- pg_hint_planによる設定
- テーブルスペースの設定
- テーブル/インデックスの設定
基本的に様々な方法で設定を細かく指定してしまうと、管理が煩雑になるだけでなく、思わぬところで上書きされてしまうリスクもあります。
SET ROLE / SET SESSION AUTHORIZAITION
PostgreSQLでは一時的に現在のロールやセッションの識別子を別のロール識別子に変更するSET ROLE / SET SESSION AUTHORIZAITIONコマンドがあります。ALTER ROLEでロールAにパラメータ設定(param1をデフォルトXからZへ変更)を施した場合、SET ROLE時に当該のロールAになればパラメータ設定が有効になること(param1はZ)を期待したいところですが、現在は有効になりません(param1はX)。
-- wm12mbというロールを作ってwork_memを12MBに設定する
=# CREATE ROLE wm12mb LOGIN ;
CREATE ROLE
=# ALTER ROLE wm12mb SET work_mem TO '12MB';
ALTER ROLE
-- wm12mbロールセットするがwork_memは変わらず
=# SET ROLE wm12mb ;
SET
=> SHOW work_mem ;
work_mem
----------
4MB
(1 row)
-- 一旦元のロールに戻してSET SESSION AUTHORIZATIONで変えてみるも同様
=> RESET ROLE;
RESET
=# SET SESSION AUTHORIZATION wm12mb ;
SET
=> SHOW work_mem ;
work_mem
----------
4MB
(1 row)
これが仕様かどうかは定かではないですが、注意ポイントとして把握しておくと良いかもしれません。