CPUの設定
- ロックに待たされるとCPUがアイドルしてしまう
- が、PostgreSQLではロックを十分に小さな範囲に絞るので競合は起こりにくい
クライアント接続設定
TABLE pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+-------------------------+------------------------------
| | 60 | | | | | | | 2020-02-02 05:55:26.707822+00 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher
| | 62 | 10 | postgres | | | | | 2020-02-02 05:55:26.708161+00 | | | | Activity | LogicalLauncherMain | | | | | logical replication launcher
13408 | postgres | 1962 | 10 | postgres | psql | | | -1 | 2020-02-02 16:04:34.987328+00 | 2020-02-02 16:04:42.854666+00 | 2020-02-02 16:04:42.854666+00 | 2020-02-02 16:04:42.854668+00 | | | active | | 493 | TABLE pg_stat_activity; | client backend
| | 58 | | | | | | | 2020-02-02 05:55:26.707524+00 | | | | Activity | BgWriterHibernate | | | | | background writer
| | 57 | | | | | | | 2020-02-02 05:55:26.707363+00 | | | | Activity | CheckpointerMain | | | | | checkpointer
| | 59 | | | | | | | 2020-02-02 05:55:26.707688+00 | | | | Activity | WalWriterMain | | | | | walwriter
(6 rows)
- PostgreSQLは、クライアントのからの1要求あたりバックエンドプロセスが1つ立ち上がる
13408 | postgres | 1962 | 10 | postgres | psql | | | -1 | 2020-02-02 16:04:34.987328+00 | 2020-02-02 16:04:42.854666+00 | 2020-02-02 16:04:42.854666+00 | 2020-02-02 16:04:42.854668+00 | | | active | | 493 | TABLE pg_stat_activity; | client backend
- バックエンドへのCPUの割り当てはカーネルが行う(プリエンプティブ)
- ので、CPUコア数より接続数が多くても機能上問題はない
- が、プロセスのコンテキストスイッチが頻発すると性能に響く
- ので、
max_connections
で最大同時接続数を適宜絞る
postgres=# SELECT name,setting,context,boot_val,reset_val FROM pg_settings WHERE name='max_connections';
name | setting | context | boot_val | reset_val
-----------------+---------+------------+----------+-----------
max_connections | 100 | postmaster | 100 | 100
(1 row)
- restart必須
クライアント接続設定の注意点
- スタンバイサーバの
max_connections
がプライマリ以上であること superuser_reserved_connections
: スーパーユーザ用の予約本数
postgres=# SELECT name,setting,context,boot_val,reset_val FROM pg_settings WHERE name='superuser_reserved_connections';
name | setting | context | boot_val | reset_val
--------------------------------+---------+------------+----------+-----------
superuser_reserved_connections | 3 | postmaster | 3 | 3
(1 row)
-
一般ユーザの同時接続数は、これと、さらにスタンバイサーバとの接続も差し引いた分
- 例: デフォルト設定で、スタンバイサーバが2台ならば正味95
ロックの設定
- PostgreSQLにはデッドロックの自動検出機能がある
- デッドロックの検出は負担が大きい
- ので、頻繁に起こらないように猶予時間オプションがある(
deadlock_timeout
)
postgres=# SELECT name,setting,context,boot_val,reset_val FROM pg_settings WHERE name SIMILAR TO '%lock_timeout';
name | setting | context | boot_val | reset_val
------------------+---------+-----------+----------+-----------
deadlock_timeout | 1000 | superuser | 1000 | 1000
lock_timeout | 0 | user | 0 | 0
(2 rows)
- ロック待ち時間がこれを超えるとデッドロック検出が走る
-
トランザクションの平均的な処理時間よりも長く設定しよう
- ただし、本当にデッドロックが発生したときのデッドロック解消・ログ通知は遅くなる
- デッドロック検出ログ: こういうやつ
2020-02-02 16:29:17.051 UTC [2127] ERROR: deadlock detected
2020-02-02 16:29:17.051 UTC [2127] DETAIL: Process 2127 waits for ShareLock on transaction 496; blocked by process 1962.
Process 1962 waits for ShareLock on transaction 495; blocked by process 2127.
Process 2127: update tbl set col=col+1 where col = 2;
Process 1962: update tbl set col=col+1 where col = 1;
2020-02-02 16:29:17.051 UTC [2127] HINT: See server log for query details.
2020-02-02 16:29:17.051 UTC [2127] CONTEXT: while updating tuple (0,2) in relation "tbl"
2020-02-02 16:29:17.051 UTC [2127] STATEMENT: update tbl set col=col+1 where col = 2;
SET deadlock_timeout=10000;
とかしてあると、ロック待ち状態が10秒くらいたったあとにデッドロックが検出され、このログが吐かれる
メモリの設定
-
I/O性能差の話
- いちいちdiskを読みたくない
- でもdisk上のデータを全てメモリに乗せることはできない
OSのメモリ設定
wand@DESKTOP-2PJLLS0:~$ sysctl kernel.shmmax
wand@DESKTOP-2PJLLS0:~$ sysctl kernel.shmall
kernel.shmmax = 18446744073692774399
kernel.shmall = 18446744073692774399
sysctl -w
(write)でカーネルパラメータ書き換え可能
sudo sysctl --write kernel.shmmax=17179869184
kernel.shmmax = 17179869184
sysctl kernel.shmmax
cat /proc/sys/kernel/shmmax
kernel.shmmax = 17179869184
17179869184
- サーバ再起動時にデフォルトに戻る
- 永続化するには
/etc/sysctl.conf
に書き込むこと
SYSCTL.CONF(5) File Formats SYSCTL.CONF(5)
NAME
sysctl.conf - sysctl preload/configuration file
DESCRIPTION
sysctl.conf is a simple file containing sysctl
values to be read in and set by sysctl. The
syntax is simply as follows:
# comment
; comment
token = value
Note that blank lines are ignored, and white‐
space before and after a token or value is
ignored, although a value can contain white‐
space within. Lines which begin with a # or ;
are considered comments and ignored.
EXAMPLE
# sysctl.conf sample
#
kernel.domainname = example.com
; this one has a space which will be written to the sysctl!
kernel.modprobe = /sbin/mod probe
FILES
/run/sysctl.d/*.conf
/etc/sysctl.d/*.conf
/usr/local/lib/sysctl.d/*.conf
/usr/lib/sysctl.d/*.conf
/lib/sysctl.d/*.conf
/etc/sysctl.conf
The paths where sysctl preload files
usually exist. See also sysctl option
--system.
SEE ALSO
sysctl(8)
AUTHOR
George Staikos ⟨staikos@0wned.org⟩
REPORTING BUGS
Please send bug reports to
⟨procps@freelists.org⟩
procps-ng January 2012 SYSCTL.CONF(5)
PostgreSQLのメモリ設定
共有メモリ領域の設定
- postgresql.confの
shared_buffers
がカーネルパラメータshmmax
を超えると、PostgreSQLサーバ起動時に怒られる kernel.shmmax
が16GB、shared_buffers
が32GBの例
postgres@bded7b391853:~/data2$ pg_ctl start -D $PWD -o "-p 5433"
waiting for server to start....2020-02-02 17:10:40.937 UTC [97] LOG: starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-02-02 17:10:40.937 UTC [97] LOG: listening on IPv4 address "0.0.0.0", port 5433
2020-02-02 17:10:40.937 UTC [97] LOG: listening on IPv6 address "::", port 5433
2020-02-02 17:10:40.945 UTC [97] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-02-02 17:10:40.948 UTC [97] FATAL: could not map anonymous shared memory: Cannot allocate memory
2020-02-02 17:10:40.948 UTC [97] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 35207938048 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
2020-02-02 17:10:40.948 UTC [97] LOG: database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.
-
shared_buffers
は適切な大きさに-
小さすぎると、使い切ったときにページが追い出される
- Clocksweepアルゴリズム
- 再読込でオーバヘッド
-
大きすぎてもダメ
- 探索に時間がかかる
- チェックポイント(書き戻し)の負荷も高くなる
-
- 共有メモリに関する設定
SELECT name,context,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN (
'shared_buffers',
'wal_buffers',
'max_connections',
'max_prepared_transactions',
'max_locks_per_transaction'
);
name | context | setting | boot_val | reset_val
---------------------------+------------+---------+----------+-----------
max_connections | postmaster | 100 | 100 | 100
max_locks_per_transaction | postmaster | 64 | 64 | 64
max_prepared_transactions | postmaster | 0 | 0 | 0
shared_buffers | postmaster | 16384 | 1024 | 16384
wal_buffers | postmaster | 512 | -1 | 512
(5 rows)
wal_buffers
- デフォルトの
-1
は「shared_buffers
の1/32」を意味する - 公式
wal_buffers (integer)
The amount of shared memory used for WAL data that has not yet been written to disk. The default setting of -1
selects a size equal to 1/32nd (about 3%) of shared_buffers, but not less than 64kB nor more than the size of one
-
共有メモリは何の管理に使うのか
- クライアント接続情報
- プリペアドトランザクション
- 起動時に確保される量の概算
max_connections * 400
+ max_prepared_transactions * 600
+ max_locks_per_transaction * (max_connections + max_prepared_transactions) * 270
デフォルトだと1.7MBくらい
プロセスメモリ領域のその設定について
postgres=# SELECT name,setting,context,unit,boot_val,reset_val FROM pg_settings WHERE name LIKE '%work_mem';
name | setting | context | unit | boot_val | reset_val
----------------------+---------+---------+------+----------+-----------
autovacuum_work_mem | -1 | sighup | kB | -1 | -1
maintenance_work_mem | 65536 | user | kB | 65536 | 65536
work_mem | 4096 | user | kB | 4096 | 4096
(3 rows)
-
work_mem
- ソートやJOINで使うやつ
- 1クエリ内で当該処理が複数ある場合は、処理ごとにこの値が確保される
-
maintenance_work_mem
VACUUM
,CREATE INDEX
,ALTER TABLE
とかでつかう値
-
autovacuum_work_mem
- PostgreSQL 9.4以降
- autovacuumワーカプロセス1つあたりの使用メモリの設定
-
-1
(デフォルト)なら代わりにmaintenance_work_mem
を使う- デフォルト設定だと、メンテナンスのために一時的に
maintenance_work_mem
を大きくしたときにautovacuumワーカプロセスの使用メモリ量も増加してしまう - これを避けるために、Ver 9.4以降ではこのパラメータを設定しておくことが推奨される
- デフォルト設定だと、メンテナンスのために一時的に
HugePage設定 (PostgreSQL 9.4以降)
- 公式
-
-
Linuxカーネルで、標準のページサイズとは別に大規模なメモリーページを管理できる機能
- 標準: 4kBとか16kBとか
- HugePage: 2048kBとか
-
- PostgreSQLでは共有メモリを大きくすることで性能向上をはかる
- が、メモリ空間が大きくなると、メモリ管理に用いるページテーブルも大きくなる
- ページテーブルが大きくなると、CPU負荷が増加してしまう
- HugePageでページ1つあたりの容量を大きくし、ページテーブルを小さくすることで、CPU負荷を低減できる
sysctl vm.nr_hugepages
- 手元の環境で試したら0HugePage数は0だった
vm.nr_hugepages = 0
- 設定すべき値の算出
- PostgreSQLプロセスのメモリのピーク値取得
postgres@bdbad7709a38:/$ grep ^VmPeak /proc/1/status
VmPeak: 213916 kB
- HugePageの1ページあたりの容量を取得
postgres@bdbad7709a38:/$ grep ^Hugepagesize /proc/meminfo
Hugepagesize: 2048 kB
- 割り算してHugePage数を算出する
213916 kB / 2048 kB = 104.451171875 -> 105
-
sysctl -w
で書き込む/etc/sysctl.conf
で永続化
- 他のアプリケーションとも共用するものなので、合計値を設定すること
ディスクの設定
- ディスクI/Oがボトルネックになりがち
OSのディスク設定
cat /sys/block/sda/queue/scheduler
- WSLだとnoopしかなかった
[noop]
PostgreSQLのディスク設定
- I/O schedulerとは
-
CPUのプリエンプションのI/O版みたいなやつ
-
noop
- OSはスケジュールに関与しない
-
deadline
- I/O要求の待ち時間に限界値(deadline)を設ける
- deadlineに近いI/O要求を優先
-
cfq (Completely Fair Queueing)
- すべてのI/O要求を均等に処理
- CentOSデフォルト
-
- 少数のプロセスからランダムアクセスI/O要求が来るような場合はdeadlineが適する
-
PostgreSQLはそういう性質なので
deadline
推奨-
「少数のプロセス」
- bgwriter
- wal writer
-
-
カーネルよりも物理側に委ねたほうが良い場合はnoopという選択肢も
- RAID
-
SSD
- ランダムアクセスに強い
システム上の制限を設けるためのパラメータ
- かえなくていいよ
性能に影響を与えるパラメータ
-
「I/O要求を出す少数のプロセス」に関わるやつ
- bgwriter
- wal writer
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
-
バックグラウンドライタの性能に関するパラメータのデフォルト値
- 200ms周期で動作
- 書き出しは同時に100ファイルまで
-
直近の書き込みに対して2.0倍まで処理すべきと予想
- 瞬間的な増加に備えてデフォルト高めになっている
#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------
#wal_writer_delay = 200ms # 1-10000 milliseconds
...
# - Checkpoints -
#checkpoint_timeout = 5min # range 30s-1d
max_wal_size = 1GB
min_wal_size = 80MB
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
-
WALライタの性能に関するパラメータのデフォルト値
- 200ms間隔で動作
- 最後のチェックポイントからWALファイルが1GBを超えたらチェックポイント
- または5分たったらチェックポイント
- チェックポイント後も80MBは削除されない(再利用)
-
次のチェックポイントまでの間隔のうち半分くらいの時間でダーティバッファ書き出しが完了するようにI/Oを分散する
- checkpointcompletiontargetの調整
-
チェックポイント処理はダーティバッファ書き出しだけではないので、1.0は避ける
- さもないと、チェックポイント処理が完了しなくなる
- チェックポイントの前にバックグラウンドライタがダーティバッファを書き込んでおくとチェックポイントのI/O負荷を抑えられる