CPUの設定
- クライアント接続設定
  - クライアント接続設定の注意点
- ロックの設定
メモリの設定
ディスクの設定
- OSのディスク設定
- PostgreSQLのディスク設定
  - システム上の制限を設けるためのパラメータ
  - 性能に影響を与えるパラメータ

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以降)

公式
HugePageとは
- 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を分散する
  - checkpoint_completion_targetの調整
  - チェックポイント処理はダーティバッファ書き出しだけではないので、1.0は避ける
    - さもないと、チェックポイント処理が完了しなくなる
チェックポイントの前にバックグラウンドライタがダーティバッファを書き込んでおくとチェックポイントのI/O負荷を抑えられる

勉強日記

チラ裏

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch9 サーバ設定

CPUの設定

クライアント接続設定

クライアント接続設定の注意点

ロックの設定

メモリの設定

OSのメモリ設定

PostgreSQLのメモリ設定

共有メモリ領域の設定

プロセスメモリ領域のその設定について

HugePage設定 (PostgreSQL 9.4以降)

ディスクの設定

OSのディスク設定

PostgreSQLのディスク設定

システム上の制限を設けるためのパラメータ

性能に影響を与えるパラメータ