Дано: выделенный сервер с актуальной версией Debian, ОС 64-битная. Всего памяти 16 гигабайт, постгресу выделено 12.
При прохождении теста производительности pgbench СУБД версии 9.1.3 существенно (на 15-25%) обгоняет 9.5.1. Настройки при этом максимально идентичные, разница только в замене checkpoint_segments на max_wal_size в 9.5. Такое поведение проявляется как при тестировании с базой, полностью помещающейся в оперативную память, так и со здоровой. От количества потоков/пользователей зависимости тоже не выявлено.
Конфиг СУБД:
max_connections = 100
shared_buffers = 3GB
effective_cache_size = 9GB
work_mem = 10485kB
maintenance_work_mem = 768MB
checkpoint_segments = 64 # (max_wal_size = 3GB в 9.5.1)
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
synchronous_commit = off
Листинг бенчмарка версии 9.1.3:
transaction type: TPC-B (sort of)
scaling factor: 90
query mode: simple
number of clients: 8
number of threads: 4
duration: 3600 s
number of transactions actually processed: 21235649
latency average: 1.356 ms
latency stddev: 60.161 ms
tps = 5894.565542 (including connections establishing)
tps = 5894.569960 (excluding connections establishing)
statement latencies in milliseconds:
0.003138 \set nbranches 1 * :scale
0.000992 \set ntellers 10 * :scale
0.000833 \set naccounts 100000 * :scale
0.001294 \setrandom aid 1 :naccounts
0.000933 \setrandom bid 1 :nbranches
0.000885 \setrandom tid 1 :ntellers
0.000972 \setrandom delta -5000 5000
0.036384 BEGIN;
0.494754 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.126514 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.194743 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.255106 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.174111 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.056807 END;
Листинг бенчмарка версии 9.5.1:
transaction type: TPC-B (sort of)
scaling factor: 90
query mode: simple
number of clients: 8
number of threads: 4
duration: 3600 s
number of transactions actually processed: 17340024
latency average: 1.659 ms
latency stddev: 60.418 ms
tps = 4816.671963 (including connections establishing)
tps = 4816.675388 (excluding connections establishing)
statement latencies in milliseconds:
0.003314 \set nbranches 1 * :scale
0.001043 \set ntellers 10 * :scale
0.000864 \set naccounts 100000 * :scale
0.001308 \setrandom aid 1 :naccounts
0.001014 \setrandom bid 1 :nbranches
0.000935 \setrandom tid 1 :ntellers
0.001011 \setrandom delta -5000 5000
0.042623 BEGIN;
0.325323 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.189450 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.282068 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.440800 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.291404 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.068419 END;
