1. 環境情報 & 初期化
pgbenchを利用して、AmazonLinux2023、PostgreSQL16で負荷をかけてみます。その際に、postgresql.confを変更してみてどうなるかも確認してみます。
1.1 環境の情報
OS、データベース、インスタンスタイプは下記の表の通りです。OSのキャッシュが強力なようだったので、前回とはインスタンスタイプを変更したうえで試しています（microからsmallへ変更）。microのままだとあまり差が出なかったためです。
今回は最低限の内容でどこまで耐えるかも確認してみます。
| 項目 | 内容 |
|---|---|
| OS | AmazonLinux 2023 |
| データベース | PostgreSQL16 |
| インスタンスタイプ | t3.micro (2 vCPU、 1GiB) → t3.small (2 vCPU、 2GiB) |
1.2 postgresql.confの変更内容
今回は下記項目を変更して、実行してみます。変更させるのはメモリー設定で、shared_buffers（共有メモリ）とwork_memの2つです。
最終的に、今回試してみて効果が明確に出せたのは、「work_mem」のみです。
| 項目 | デフォルト値 | 変更後（１） | 変更後（２） |
|---|---|---|---|
| shared_buffers | 128MB | 512MB | 512MB |
| work_mem | 4MB | 64MB | 128MB |
| max_connections | 100 | 400 | 400 |
1.3 試験用のSQL
標準のpgbench実行では、「明らかにわかりやすい差」が出なかったため、下記のSQLで試しています。work_memを利用する内容をSQLに落とし込んでいます。
-- hashjoin.sql 内部的にデータを6万行取得して、ソートして、結合を実施する
SELECT a.aid, a.abalance, t.tbalance
FROM (
SELECT aid, abalance, bid
FROM pgbench_accounts
ORDER BY abalance DESC
LIMIT 60000
) a
JOIN pgbench_tellers t ON a.bid = t.bid;
1.4 pgbenchでの初期化 & 毎回のリセット項目
スケール「300」で初期化します、
こちらも最初、「30」（pgbench_accountsが300万行、約384MB+インデックス）で初期化しましたが、差異がほぼでなかったので、こちらのサイズにしてみました。が、今思えば「LIMIT 60000」するなら、30でもOKですね。
$ pgbench -i -s 300 -U postgres pgbench_test
dropping old tables...
creating tables...
generating data (client-side)...
30000000 of 30000000 tuples (100%) done (elapsed 54.03 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 114.63 s (drop tables 0.11 s, create tables 0.01 s, client-side generate 54.34 s, vacuum 7.36 s, primary keys 52.80 s).
# 3000万行の結果が返ってくる
$ psql -U postgres -d pgbench_test -c "SELECT count(*) FROM pgbench_accounts;"
count
----------
30000000
(1 row)
# 統計リセット
$ psql -U postgres -d pgbench_test -c "SELECT pg_stat_reset();"
# キャッシュクリア
$ sudo systemctl stop postgresql
$ sync && echo 3 | sudo tee /proc/sys/vm/drop_caches
$ sudo systemctl start postgresql
# ウォームアップ（データを一部更新）
$ pgbench -c 10 -j 4 -T 30 -U postgres pgbench_test
2. 負荷をかけての実行
pgbenchをpostgresql.confを変更して、下記で実行します。
| パラメータ | 値 | 意味 |
|---|---|---|
| -c | 10 | クライアント数 (Clients)数が10個 |
| -j | 4 | スレッド数 (Jobs) |
| -T | 60 | 実行時間 (Time) |
2.1 work_mem を4MBで実行
SQL実行時に各セッションで一時ファイルが発生してしまう状況（4MBに収まらない）で実行する。
$ pgbench -c 10 -j 4 -T 60 -f /tmp/hashjoin.sql -U postgres pgbench_test
Password:
pgbench (16.12)
starting vacuum...end.
transaction type: /tmp/hashjoin.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 10
number of failed transactions: 0 (0.000%)
latency average = 280463.805 ms
initial connection time = 75.969 ms
tps = 0.035655 (without initial connection time)
2.2 work_mem を64MBで実行
SQL実行時に各セッションで一時ファイルが発生しない状況（64MBに収まる）で実行する。
$ pgbench -c 10 -j 4 -T 60 -f /tmp/hashjoin.sql -U postgres pgbench_test
pgbench (16.12)
starting vacuum...end.
transaction type: /tmp/hashjoin.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 13
number of failed transactions: 0 (0.000%)
latency average = 97343.710 ms
initial connection time = 76.242 ms
tps = 0.102729 (without initial connection time)
2.3 work_mem を128MBで実行
SQL実行時に各セッションで一時ファイルが発生しない状況（128MBに収まる）で実行する。
pgbench (16.12)
starting vacuum...end.
transaction type: /tmp/hashjoin.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 13
number of failed transactions: 0 (0.000%)
latency average = 90086.845 ms
initial connection time = 77.163 ms
tps = 0.111004 (without initial connection time)
2.4 まとめ
上記の3つの実行結果をまとめる、下記の表のようになります。
4MBと64MBと間に明らかに差が発生しています。
これこそがwork_memに収まらずに、一時ファイルへの書き込みが発生してしまい、処理時間が明らかに差異が発生しました。
| work_mem | TPS | latency | 処理件数 |
|---|---|---|---|
| 4MB | 0.0356 | 280s | 10件 |
| 64MB | 0.1027 | 97s | 13件 |
| 128MB | 0.1110 | 90s | 13件 |
TPS（Transactions Per Second）とは、1秒あたりに処理できるトランザクション数です。
work_mem=4MB → TPS=0.035
= 1秒間に0.035トランザクション
= 1トランザクションに約28秒かかっている
work_mem=64MB → TPS=0.102
= 1秒間に0.102トランザクション
= 1トランザクションに約10秒かかっている
TPSが高いほど処理時間が早いと判断できます。このようなかたちで、明確に差異が出る形で検証できました。
更に128MBに上げたとしても、64MBで既にメモリーを確保出来ているため、速度差は大きく出ていないことも確認できています。
2.5 ちなみにshared_buffersを上げすぎると
t3.micro（RAM 1GB）を戻して、shared_buffersを768MBで、work_memを128MBにして、スケールも500に初期化して、下記を実行するとどうなるか？
$ pgbench -c 300 -j 4 -T 60 -U postgres pgbench_test
（結果は返ってくることもあり）
別ターミナルで、リアルタイムに監視します。
sudo journalctl -k -f
（省略）
Apr 27 21:54:57 ip-XX-X-X-XXX.ap-northeast-1.compute.internal kernel:
oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/user.slice/user-1000.slice/user@1000.service/init.scope,task=systemd,pid=1640,uid=1000
Apr 27 21:54:57 ip-XX-X-X-XXX.ap-northeast-1.compute.internal kernel:
Out of memory: Killed process 1640 (systemd) total-vm:22804kB, anon-rss:3644kB, file-rss:0kB, shmem-rss:0kB, UID:1000 pgtables:84kB oom_score_adj:100
こんな感じで、OOM Killerにより、今回はsystemdプロセスがkillされました。
本番環境では、「スワップメモリ」の設定をお願いします。
https://dev.classmethod.jp/articles/how-to-create-swap-memory-in-linux-on-ec2-jp/
2.6 次回に向けて
今回、明確に処理時間で差を出すことが出来ました。「実行計画」を出力することで、一時ファイルが発生する・発生しないを確認してから実施することで、このような形で確認することが出来ました。
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.aid, a.abalance, t.tbalance
FROM (
SELECT aid, abalance, bid
FROM pgbench_accounts
ORDER BY abalance DESC
LIMIT 60000
) a
JOIN pgbench_tellers t ON a.bid = t.bid;
こちらのSQLを「/tmp/hashjoin.sql」を保存して、実行すると下記の結果が出力されます。
$ psql -U postgres -d pgbench_test -f /tmp/hashjoin.sql
QUERY PLAN
--------------------------------------------------
Sort (cost=8230584.70..8232082.26 rows=599027 width=12) (actual time=41603.207..41763.576 rows=600000 loops=1)
Sort Key: pgbench_accounts.abalance DESC
Sort Method: external merge Disk: 12984kB
Buffers: shared hit=112 read=493800, temp read=83941 written=163593
-> Hash Join (cost=8148373.02..8162863.78 rows=599027 width=12) (actual time=41390.891..41583.979 rows=600000 loops
=1)
Hash Cond: (pgbench_accounts.bid = t.bid)
Buffers: shared hit=112 read=493800, temp read=82318 written=161961
-> Limit (cost=8148277.52..8155278.01 rows=60000 width=352) (actual time=41386.912..41512.526 rows=60000 loop
s=1)
Buffers: shared hit=112 read=493772, temp read=82318 written=161961
-> Gather Merge (cost=8148277.52..11076819.98 rows=25100040 width=352) (actual time=41386.909..41508.09
3 rows=60000 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=112 read=493772, temp read=82318 written=161961
-> Sort (cost=8147277.50..8178652.55 rows=12550020 width=352) (actual time=41242.289..41246.724 r
ows=20910 loops=3)
Sort Key: pgbench_accounts.abalance DESC
Sort Method: external merge Disk: 215672kB
Buffers: shared hit=112 read=493772, temp read=82318 written=161961
Worker 0: Sort Method: external merge Disk: 215160kB
Worker 1: Sort Method: external merge Disk: 215160kB
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..619272.20 rows=12550020 width=352) (ac
tual time=0.347..32565.337 rows=10000000 loops=3)
Buffers: shared read=493772
-> Hash (cost=58.00..58.00 rows=3000 width=8) (actual time=3.938..3.940 rows=3000 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 150kB
Buffers: shared read=28
-> Seq Scan on pgbench_tellers t (cost=0.00..58.00 rows=3000 width=8) (actual time=0.434..2.843 rows=30
00 loops=1)
Buffers: shared read=28
Planning:
Buffers: shared hit=84 read=19
Planning Time: 10.626 ms
Execution Time: 42531.397 ms
(30 rows)
注目した部分は下記の部分です。
4MBの際には、「external merge Disk」になってます。
Worker 0: Sort Method: external merge Disk: 215160kB
Worker 1: Sort Method: external merge Disk: 215160kB
64MB（下記は128MBの際）は、「top-N heapsort Memory」になってます。
Worker 0: Sort Method: top-N heapsort Memory: 5418kB
Worker 1: Sort Method: top-N heapsort Memory: 5417kB
Diskでソートではなく、Memoryでソートのため処理時間が劇的に早くなってます。
やはり、実際の環境でもこのあたりの状況を確認することが重要だと感じた一例です。
次回以降は、このような「実行計画」だとか、「統計情報」を確認していきたいと思います。