Date: Thu, 18 Sep 2014 11:58:21 +0200 (CEST)
From: "Mkrtchyan, Tigran" <tigran.mkrtchyan@desy.de>
To: pgsql-performance@postgresql.org
Message-ID: <88549888.42916.1411034301200.JavaMail.zimbra@desy.de>
In-Reply-To: <45142416.42827.1411033201079.JavaMail.zimbra@desy.de>
Subject: postgres 9.3 vs. 9.4
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Thread-Topic: postgres 9.3 vs. 9.4
Thread-Index: s5S9FQ6lY8IOzCp+Smo2k6KbOYD5GA==
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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?

Tigran.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance