postgres=# select name, setting, unit from pg_settings WHERE name LIKE '%parallel%' OR name = 'max_worker_processes';
name | setting | unit
----------------------------------+---------+------
enable_parallel_append | on |
enable_parallel_hash | on |
force_parallel_mode | off |
max_parallel_maintenance_workers | 2 |
max_parallel_workers | 8 |
max_parallel_workers_per_gather | 2 |
max_worker_processes | 8 |
min_parallel_index_scan_size | 64 | 8kB
min_parallel_table_scan_size | 1024 | 8kB
parallel_leader_participation | on |
parallel_setup_cost | 1000 |
parallel_tuple_cost | 0.1 |
(12 rows)Parallel Index Scanを実験！その実力は？ | NTTテクノクロスブログ 2018
max_worker_processes
ワーカー毎に物理CPUが一つ必要なので、マシンのCPUの個数を20個に増やします
Zabbixのダッシュボード表示が遅くて困った時の対処方法を検証してみた（PostgreSQL実行計画解析と対処編） | SIOS Tech. Lab 2019
max_worker_processes は一種のハードリミットなので使用可能な物理コア数以下に設定
https://pgecons-sec-tech.github.io/tech-report/presentation/PGECons_20181018_parallel.pdf
レプリケーション
These parameters should be set on the standbys to values equal to or greater than on the primary to apply physical replication:
- max_connections
- max_prepared_transactions
- max_locks_per_transaction
- max_wal_senders
- max_worker_processes
Without this, replication will stop. (with the entire standby database on older versions)
Hot standby does not require max_worker_processes to be increased. But if you increased it on the primary, then on all standbys this setting should not be lower.
https://dba.stackexchange.com/questions/330240/configuration-of-max-worker-processes-in-postgresql
スタンバイサーバを起動しているときは、このパラメータを、マスタサーバの設定値と同じかそれ以上にしなければなりません。さもなければ、スタンバイサーバで問い合わせの実行ができなくなります。
https://postgresqlco.nf/doc/ja/param/max_worker_processes/
レプリケーション実施中にマスタサーバの設定値を変更して、スタンバイサーバが設定値以下になると止まる
FATAL: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 150) CONTEXT: WAL redo at 3AD/8F225378 for XLOG/PARAMETER_CHANGE: max_connections=150 max_worker _processes=16 max_prepared_xacts=0 max_locks_per_xact=64 wal_level=replica wal_log_hints=off track_commit_timestamp=off LOG: startup process (PID 68924) exited with exit code 1 LOG: terminating any other active server processes
