PostgreSQL Parameter
パラメータの確認
PostgreSQLのパラメータは、show all, showコマンドやpg_settingsを参照する事で、現在インスタンスに設定されているパラメータを確認する事が出来ます。SETコマンドで変更する事も可能です。
- show command
POC=> show work_mem;
work_mem
----------
4MB
(1 row)- pg_settings
POC=> \x
Expanded display is on.
POC=> select name,setting,unit,context,category,short_desc from pg_settings where name like '%mem%';
-[ RECORD 1 ]--------------------------------------------------------------------------------
name | autovacuum_work_mem
setting | -1
unit | kB
context | sighup
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used by each autovacuum worker process.
-[ RECORD 2 ]--------------------------------------------------------------------------------
name | dynamic_shared_memory_type
setting | posix
unit |
context | postmaster
category | Resource Usage / Memory
short_desc | Selects the dynamic shared memory implementation used.
-[ RECORD 3 ]--------------------------------------------------------------------------------
name | hash_mem_multiplier
setting | 1
unit |
context | user
category | Resource Usage / Memory
short_desc | Multiple of work_mem to use for hash tables.
-[ RECORD 4 ]--------------------------------------------------------------------------------
name | logical_decoding_work_mem
setting | 65536
unit | kB
context | user
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for logical decoding.
-[ RECORD 5 ]--------------------------------------------------------------------------------
name | maintenance_work_mem
setting | 65536
unit | kB
context | user
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for maintenance operations.
-[ RECORD 6 ]--------------------------------------------------------------------------------
name | shared_memory_type
setting | mmap
unit |
context | postmaster
category | Resource Usage / Memory
short_desc | Selects the shared memory implementation used for the main shared memory region.
-[ RECORD 7 ]--------------------------------------------------------------------------------
name | work_mem
setting | 4096
unit | kB
context | user
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for query workspaces.
POC=>
SETコマンドは実行時設定パラメータを変更します。 実行時パラメータの多くは稼働中にSETコマンドで変更できます。SETは現行セッションで使用される値にのみ影響することに注意してください。
SETコマンドで設定出来る値は、UserまたはSuperuserパラメター でSET LOCALの場合、発行したトランザクション内に限定されます。
POC=> show work_mem;
work_mem
----------
4MB
(1 row)
POC=> set work_mem = 8192;
SET
POC=> show work_mem;
work_mem
----------
8MB
(1 row)
POC=> \x
Expanded display is on.
POC=> show enable_seqscan;
-[ RECORD 1 ]--+---
enable_seqscan | on
POC=> set enable_seqscan = off;
SET
POC=> show enable_seqscan;
-[ RECORD 1 ]--+----
enable_seqscan | off
パラメータの設定変更
パラメータを設定するには、postgresql.confファイルを編集する事になります。通常、postgresql.confはdataディレクトリに格納されています。
~/git/rdbms-docker/postgresql/docker/postgresql/data [ 9:36:28]> cat postgresql.conf | head -n 100
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()". Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: B = bytes Time units: us = microseconds
# kB = kilobytes ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# TB = terabytes h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - TCP settings -
# see "man tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds;
# 0 selects the system default
#client_connection_check_interval = 0 # time between checks for client
# disconnection while running queries;
# 0 for never
sighup
PostgreSQLプロセスがSIGHUPシグナルを受け取ったタイミングで、設定リロードして反映させる事が可能です。pg_ctl reload, pg_reload_conf関数を利用して、インスタンスを停止せずに設定を反映させる事が可能です。
POC=# select name,setting,unit,context,category,short_desc from pg_settings where name like '%max_standby_archive_delay%';
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------
name | max_standby_archive_delay
setting | 30000
unit | ms
context | sighup
category | Replication / Standby Servers
short_desc | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.
/*** postgresql.confファイルを編集 ***/
POC=# SELECT pg_reload_conf();
-[ RECORD 1 ]--+--
pg_reload_conf | t
POC=# select name,setting,unit,context,category,short_desc from pg_settings where name like '%max_standby_archive_delay%';
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------
name | max_standby_archive_delay
setting | -1
unit | ms
context | sighup
category | Replication / Standby Servers
short_desc | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.ALTER SYSTEMによるパラメータ変更
ALTER SYSTEMはデータベースクラスタ全体にわたるサーバ設定パラメータを変更するのに使われます。 postgresql.confファイルを編集する方法よりも、コンテナ等を利用するケースが増えているので、この方が便利な場合もあるかと思います。 ALTER SYSTEMは指定されたパラメータ設定をpostgresql.auto.confファイルに書き出し、これがpostgresql.confに加えて読み込まれます。 パラメータをDEFAULTに設定する、あるいはこれの別表記であるRESETを使うと、postgresql.auto.confファイルから、その設定のエントリを削除します。 エントリをすべて削除するにはRESET ALLを使用します。
備考：MySQLでも8.0から、同等の機能としてSET PERSISTが追加されています。
- ALTER SYSTEMによるパラメータの変更
POC=# ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM
POC=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
POC=# \q
~/git/rdbms-docker/postgresql [ 9:53:10]> docker-compose stop
Stopping postgresql_postgres_1 ... done
~/git/rdbms-docker/postgresql [ 9:53:17]> docker-compose start
Starting postgres ... done
~/git/rdbms-docker/postgresql [ 9:53:22]> psql -h 127.0.0.1 -U postgres POC
Password for user postgres:
psql (14.1 (Ubuntu 14.1-1.pgdg18.04+1), server 13.5 (Debian 13.5-1.pgdg110+1))
Type "help" for help.
POC=# show shared_buffers;
shared_buffers
----------------
256MB
(1 row)- postgresql.auto.confの確認
~/git/rdbms-docker/postgresql [ 9:56:30]> docker-compose ps
Name Command State Ports
--------------------------------------------------------------------------------------------------------
postgresql_postgres_1 docker-entrypoint.sh postgres Up 0.0.0.0:5432->5432/tcp,:::5432->5432/tcp
~/git/rdbms-docker/postgresql [ 9:56:34]> docker exec -it postgresql_postgres_1 bash
root@f5276f9ac1f9:/# cat /var/lib/postgresql/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_buffers = '256MB'
root@f5276f9ac1f9:/#
設定はpostgresql.confにInclude可能なので、サーバー毎に異なる値だけINCLUDEしておくのも良さそう。
参照：ALTER SYSTEM
SETコマンド補足：
SETコマンドはパラメータの変更だけでは無く、以下の様に制約を一時的に無効にしたりする場合にも使う事が可能です。
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS ALL IMMEDIATE;MySQL Parameter
パラメータの確認
“show global variables;” , “show session variables;”で全体的なパラメータを確認する事が出来ます。また、SETコマンドで動的パラメータを変更する事も可能です。
mysql> show global variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
1 row in set (0.07 sec)
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> select @@global.max_connections;
+--------------------------+
| @@global.max_connections |
+--------------------------+
| 100 |
+--------------------------+
1 row in set (0.00 sec)
mysql> show session variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
1 row in set (0.01 sec)
mysql> select @@session.max_connections;
ERROR 1238 (HY000): Variable 'max_connections' is a GLOBAL variable
mysql> select @@session.max_join_size;
+-------------------------+
| @@session.max_join_size |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
1 row in set (0.00 sec)
Global Variableはサーバー全体に設定します。Session Variableは接続したセッションのみに有効なので、バッチジョブだけSort Bufferを増やしてメモリー内でソート出来るように設定したりする事が可能です。セッションから抜けて、再接続した場合はグローバル変数が適用されます。
mysql> show global variables like 'sort%';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| sort_buffer_size | 2097152 |
+------------------+---------+
1 row in set (0.01 sec)
mysql> set sort_buffer_size = 2097152 * 2;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'sort%';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| sort_buffer_size | 2097152 |
+------------------+---------+
1 row in set (0.00 sec)
mysql> show session variables like 'sort%';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| sort_buffer_size | 4194304 |
+------------------+---------+
1 row in set (0.01 sec)
備考：sort_buffer_sizeを増やす事で、ディスクソートを抑制出来るので以下のsort_merge_passesの値の増加を抑える事が可能です。
スコープがGlobalかSession単位で変更可能かどうかは以下のマニュアルを参照してください。
パラメータの設定変更
MySQLインストール時やコンテナ初期起動時にデータディレクトリー、文字コード、メモリー設定等の基本設定をオプションファイル(my.cnf)に設定して起動しますが。起動後に設定を永続化したい場合や、サーバー全体で設定したいパラメータがある場合は此方に設定をします。全部で600以上ものパラーメータがあるので、全てを設定する事は現実的に不可能ですし、MySQLのDefault設定を利用した方が良いケースも多いので、明らかに必要なパラメータだけ設定すれば良いでしょう。上記と同じく、各パラメータに関しては、以下のマニュアルを参照してください。
~/git/rdbms-docker/mysql [ 8:25:34]> docker-compose ps
Name Command State Ports
------------------------------------------------------------------------------------------------------
mysql_db_1 docker-entrypoint.sh mysqld Up 0.0.0.0:3306->3306/tcp,:::3306->3306/tcp, 33060/tcp
~/git/rdbms-docker/mysql [ 8:25:36]> docker exec -it mysql_db_1 bash
root@cc4a2ccc85df:/# cat /etc/mysql/conf.d/my.cnf
[mysqld]
character-set-server = utf8mb4
collation_server = utf8mb4_general_ci
max_allowed_packet = 8M
default_storage_engine = InnoDB
max_connections = 100
max_user_connections = 50
thread_cache_size = 100
# InnoDB
innodb_buffer_pool_size = 128M
# Compatibility
default_authentication_plugin=mysql_native_password
# Session variables
sort_buffer_size = 2M
tmp_table_size = 8M
read_buffer_size = 10M
read_rnd_buffer_size = 1M
join_buffer_size = 1M
max_heap_table_size = 8M
log_timestamps = SYSTEM
# Security Option
secure_file_priv = "/tmp"
[client]
default-character-set = utf8mb4
root@cc4a2ccc85df:/#※上記の例ではdefault_authentication_pluginを指定していますが、MySQL8.0から認証プラグインが変更されているので古いアプリケーションから接続した場合等に認証がエラーになる事があるので、意図的にこちらで設定しています。上記の様なパラメータも状況に応じて適宜追加して頂くと良いでしょう。
SET PERSISTによるパラメータの永続化
MySQL8.0以前は、永続的なパラメータは,my.cnfで変更するのがセオリーでしたが、運用する中でSETコマンドで設定した変数を永続化したいという要望も多く(SETコマンドで設定した内容が再起動で消えてしまう為)、SET PERSISTというコマンドが追加され、設定が再起動後などにも永続化される様になりました。my.cnfファイルを読み込んだ後に、mysqld-auto.cnfというファイルにSET PERSISTで追加されたパラメータが読み込まれる仕様になります。オプションファイルは後で読み込んだ変数に上書きされる為、mysqld-auto.cnfの設定が反映されます。
mysql> SELECT * FROM performance_schema.persisted_variables\G
*************************** 1. row ***************************
VARIABLE_NAME: sort_buffer_size
VARIABLE_VALUE: 4194304
1 row in set (0.01 sec)
mysql> SELECT * FROM performance_schema.variables_info where VARIABLE_NAME = 'sort_buffer_size'\G
*************************** 1. row ***************************
VARIABLE_NAME: sort_buffer_size
VARIABLE_SOURCE: DYNAMIC
VARIABLE_PATH:
MIN_VALUE: 32768
MAX_VALUE: 18446744073709551615
SET_TIME: 2022-03-03 22:46:14.900617
SET_USER: admin
SET_HOST: NULL
1 row in set (0.00 sec)
mysql> RESET PERSIST sort_buffer_size;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM performance_schema.persisted_variables\G
Empty set (0.00 sec)
mysql> SET PERSIST sort_buffer_size=4194304;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.persisted_variables\G
*************************** 1. row ***************************
VARIABLE_NAME: sort_buffer_size
VARIABLE_VALUE: 4194304
1 row in set (0.01 sec)
SET PERSISTで設定した内容は以下の様に再起動した後も反映されている事が確認出来ます。
mysql> SELECT * FROM performance_schema.variables_info WHERE VARIABLE_NAME like '%max_connection%'\G
*************************** 1. row ***************************
VARIABLE_NAME: max_connections
VARIABLE_SOURCE: GLOBAL
VARIABLE_PATH: /etc/mysql/conf.d/my.cnf
MIN_VALUE: 1
MAX_VALUE: 100000
SET_TIME: NULL
SET_USER: NULL
SET_HOST: NULL
*************************** 2. row ***************************
VARIABLE_NAME: mysqlx_max_connections
VARIABLE_SOURCE: COMPILED
VARIABLE_PATH:
MIN_VALUE: 1
MAX_VALUE: 65535
SET_TIME: NULL
SET_USER: NULL
SET_HOST: NULL
2 rows in set (0.00 sec)
mysql> SELECT * FROM performance_schema.variables_info where VARIABLE_NAME = 'sort_buffer_size'\G
*************************** 1. row ***************************
VARIABLE_NAME: sort_buffer_size
VARIABLE_SOURCE: PERSISTED
VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf
MIN_VALUE: 32768
MAX_VALUE: 18446744073709551615
SET_TIME: 2022-03-03 22:49:59.723196
SET_USER: admin
SET_HOST: NULL
1 row in set (0.00 sec)
mysql> SELECT * FROM performance_schema.persisted_variables\G
*************************** 1. row ***************************
VARIABLE_NAME: sort_buffer_size
VARIABLE_VALUE: 4194304
1 row in set (0.00 sec)
Docker環境等でもmy.cnfを変更しないでも,コマンドでパラメータが設定出来るので便利ですね。
SETコマンド補足：
SETコマンドはパラメータの変更だけでは無く、以下の様に制約を一時的に無効にしたりする場合にも使う事が可能です。以下のSETコマンドでは一時的に、外部キー制約を無効化・有効化しています。
SET FOREIGN_KEY_CHECKS = 0
SET FOREIGN_KEY_CHECKS = 1パラメータに関しては、参考になるサイトがあるので短時間で効率良く確認する事が出来ます。
- POSTGRESQLCO.NF https://postgresqlco.nf/ja/doc/param/
- MySQLユーザー会の会長のとみたさんが公開されているパラメータ比較シート https://mysql-params.tmtms.net/mysqld/?vers=5.7.21,8.0.19&diff=true