Re: postgres 9.3 vs. 9.4

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