On 18/09/14 21:58, Mkrtchyan, Tigran wrote:
Hi Folk,
I am trying to investigate some performance issues which we have with postgres
(a different topic by itself) and tried postgres.9.4beta2, with a hope that it
perform better.
Turned out that 9.4 is 2x slower than 9.3.5 on the same hardware.
Some technical details:
Host: rhel 6.5 2.6.32-431.23.3.el6.x86_64
256 GB RAM, 40 cores, Intel(R) Xeon(R) CPU E5-2660 v2 @ 2.20GHz
2x160GB PCIe SSD DELL_P320h-MTFDGAL175SAH ( on one 9.3, on an other one 9.4
)
postgres tweaks:
default_statistics_target = 100
wal_writer_delay = 10s
vacuum_cost_delay = 50
synchronous_commit = off
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
effective_cache_size = 94GB
work_mem = 402MB
wal_buffers = 16MB
checkpoint_segments = 64
shared_buffers = 8GB
max_connections = 100
random_page_cost = 1.5
# other goodies
log_line_prefix = '%m <%d %u %r> %%'
log_temp_files = 0
log_min_duration_statement = 5
in both cases databases are fresh - no data.
Here is a results with pgbench.
9.3.5:
# /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 60
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 96361
tps = 1605.972262 (including connections establishing)
tps = 1606.064501 (excluding connections establishing)
statement latencies in milliseconds:
0.001391 \set nbranches 1 * :scale
0.000473 \set ntellers 10 * :scale
0.000430 \set naccounts 100000 * :scale
0.000533 \setrandom aid 1 :naccounts
0.000393 \setrandom bid 1 :nbranches
0.000468 \setrandom tid 1 :ntellers
0.000447 \setrandom delta -5000 5000
0.025161 BEGIN;
0.131317 UPDATE pgbench_accounts SET abalance = abalance +
:delta WHERE aid = :aid;
0.100211 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.117406 UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
0.114332 UPDATE pgbench_branches SET bbalance = bbalance +
:delta WHERE bid = :bid;
0.086660 INSERT INTO pgbench_history (tid, bid, aid, delta,
mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.035940 END;
9.4beta2:
# /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 60
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 34017
tps = 566.948384 (including connections establishing)
tps = 567.008666 (excluding connections establishing)
statement latencies in milliseconds:
0.001879 \set nbranches 1 * :scale
0.000526 \set ntellers 10 * :scale
0.000490 \set naccounts 100000 * :scale
0.000595 \setrandom aid 1 :naccounts
0.000421 \setrandom bid 1 :nbranches
0.000480 \setrandom tid 1 :ntellers
0.000484 \setrandom delta -5000 5000
0.055047 BEGIN;
0.172179 UPDATE pgbench_accounts SET abalance = abalance +
:delta WHERE aid = :aid;
0.135392 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.157224 UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
0.147969 UPDATE pgbench_branches SET bbalance = bbalance +
:delta WHERE bid = :bid;
0.123001 INSERT INTO pgbench_history (tid, bid, aid, delta,
mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.957854 END;
any ideas?
Hi Tigran,
Some ideas:
60s is too short for reliable results (default settings for checkpoints
is 300s so 600s is the typical elapsed time to get reasonably repeatable
numbers (to ensure you get about 1 checkpoint in your run). In addition
I usually do
psql <<!
CHECKPOINT;
!
Plus
$ sleep 10
before each run so that I've got some confidence that we are starting
from approximately the same state each time (and getting hopefully only
*one* checkpoint per run)!
Cheers
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
