----- Original Message ----- > From: "Mark Kirkwood" <mark.kirkwood@xxxxxxxxxxxxxxx> > To: "Tigran Mkrtchyan" <tigran.mkrtchyan@xxxxxxx>, pgsql-performance@xxxxxxxxxxxxxx > Sent: Thursday, September 18, 2014 12:17:45 PM > Subject: 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)! Sure, I can run a longer tests with longer breaks in between. 9.3.5 # /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 600 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 600 s number of transactions actually processed: 1037297 tps = 1728.826406 (including connections establishing) tps = 1728.836277 (excluding connections establishing) statement latencies in milliseconds: 0.001471 \set nbranches 1 * :scale 0.000456 \set ntellers 10 * :scale 0.000411 \set naccounts 100000 * :scale 0.000524 \setrandom aid 1 :naccounts 0.000364 \setrandom bid 1 :nbranches 0.000437 \setrandom tid 1 :ntellers 0.000424 \setrandom delta -5000 5000 0.024217 BEGIN; 0.118966 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.092483 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.108232 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.107978 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.080137 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.034015 END; 9.4beta2 # /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 600 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 600 s number of transactions actually processed: 373454 tps = 622.422377 (including connections establishing) tps = 622.429494 (excluding connections establishing) statement latencies in milliseconds: 0.001252 \set nbranches 1 * :scale 0.000417 \set ntellers 10 * :scale 0.000384 \set naccounts 100000 * :scale 0.000466 \setrandom aid 1 :naccounts 0.000344 \setrandom bid 1 :nbranches 0.000411 \setrandom tid 1 :ntellers 0.000397 \setrandom delta -5000 5000 0.047489 BEGIN; 0.157164 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.119992 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.141147 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.132492 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.108917 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.889112 END; Tigran. > > Cheers > > Mark > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
