Thread: postgres 9.3 vs. 9.4
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.
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
----- Original Message -----
> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>, pgsql-performance@postgresql.org
> Sent: Thursday, September 18, 2014 12:17:45 PM
> Subject: Re: [PERFORM] 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
>
On Thu, Sep 18, 2014 at 2:58 AM, Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de> 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 )
Why are the versions segregated that way? Are you sure they are configured identically?
postgres tweaks:
default_statistics_target = 100
wal_writer_delay = 10s
vacuum_cost_delay = 50
synchronous_commit = off
Are you sure that synchronous_commit is actually off on the 9.4 instance?
9.3.5:
# /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 60
...
0.035940 END;
9.4beta2:
...
0.957854 END;
Looks like IO.
Cheers,
Jeff
----- Original Message ----- > From: "Jeff Janes" <jeff.janes@gmail.com> > To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> > Cc: pgsql-performance@postgresql.org > Sent: Thursday, September 18, 2014 4:56:22 PM > Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > > On Thu, Sep 18, 2014 at 2:58 AM, Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de > > 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 ) > > > > Why are the versions segregated that way? Are you sure they are configured > identically? es, they are configured identically > > > > > > postgres tweaks: > > > > > > default_statistics_target = 100 > > wal_writer_delay = 10s > > vacuum_cost_delay = 50 > > synchronous_commit = off > > > > Are you sure that synchronous_commit is actually off on the 9.4 instance? yes, synchronous_commit is off. > > 9.3.5: > > > > # /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 60 > > > > ... > > > > 0.035940 END; > > > > > > 9.4beta2: > > > ... > > > 0.957854 END; > > > > Looks like IO. Postgres internal IO? May be. We get 600MB/s on this SSDs. Tigran. > > Cheers, > > Jeff >
On 09/18/2014 08:09 AM, Mkrtchyan, Tigran wrote: >>> 9.4beta2: >>> > > >> > ... >> > >>> > > 0.957854 END; >>> > > >> > >> > Looks like IO. > Postgres internal IO? May be. We get 600MB/s on this SSDs. While it's possible that this is a Postgres issue, my first thought is that the two SSDs are not actually identical. The 9.4 one may either have a fault, or may be mostly full and heavily fragmented. Or the Dell PCIe card may have an issue. You are using "scale 1" which is a < 1MB database, and one client and 1 thread, which is an interesting test I wouldn't necessarily have done myself. I'll throw the same test on one of my machines and see how it does. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
----- Original Message ----- > From: "Josh Berkus" <josh@agliodbs.com> > To: pgsql-performance@postgresql.org > Sent: Thursday, September 18, 2014 7:54:24 PM > Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > > On 09/18/2014 08:09 AM, Mkrtchyan, Tigran wrote: > >>> 9.4beta2: > >>> > > > >> > ... > >> > > >>> > > 0.957854 END; > >>> > > > >> > > >> > Looks like IO. > > Postgres internal IO? May be. We get 600MB/s on this SSDs. > > While it's possible that this is a Postgres issue, my first thought is > that the two SSDs are not actually identical. The 9.4 one may either > have a fault, or may be mostly full and heavily fragmented. Or the Dell > PCIe card may have an issue. We have tested both SSDs and they have identical IO characteristics and as I already mentioned, both databases are fresh, including filesystem. > > You are using "scale 1" which is a < 1MB database, and one client and 1 > thread, which is an interesting test I wouldn't necessarily have done > myself. I'll throw the same test on one of my machines and see how it does. this scenario corresponds to our use case. We need a high transaction rate per for a single client. Currently I can get only ~1500 tps. Unfortunately, posgtress does not tell me where the bottleneck is. Is this is defensively not the disk IO. Thanks for the help, Tigran. > > -- > Josh Berkus > PostgreSQL Experts Inc. > http://pgexperts.com > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
On 09/18/2014 03:09 PM, Mkrtchyan, Tigran wrote: > > ----- Original Message ----- >> From: "Josh Berkus" <josh@agliodbs.com> >> To: pgsql-performance@postgresql.org >> Sent: Thursday, September 18, 2014 7:54:24 PM >> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 >> >> On 09/18/2014 08:09 AM, Mkrtchyan, Tigran wrote: >>>>> 9.4beta2: >>>>> ... >>>>> >>>>>>> 0.957854 END; >>>>>>> >>>>> Looks like IO. >>> Postgres internal IO? May be. We get 600MB/s on this SSDs. >> While it's possible that this is a Postgres issue, my first thought is >> that the two SSDs are not actually identical. The 9.4 one may either >> have a fault, or may be mostly full and heavily fragmented. Or the Dell >> PCIe card may have an issue. > > We have tested both SSDs and they have identical IO characteristics and > as I already mentioned, both databases are fresh, including filesystem. > >> You are using "scale 1" which is a < 1MB database, and one client and 1 >> thread, which is an interesting test I wouldn't necessarily have done >> myself. I'll throw the same test on one of my machines and see how it does. > this scenario corresponds to our use case. We need a high transaction rate > per for a single client. Currently I can get only ~1500 tps. Unfortunately, > posgtress does not tell me where the bottleneck is. Is this is defensively > not the disk IO. > > > This is when you dig out tools like perf, maybe. cheers andrew
On Sep 18, 2014 9:32 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > > > On 09/18/2014 03:09 PM, Mkrtchyan, Tigran wrote: > > > > ----- Original Message ----- > >> From: "Josh Berkus" <josh@agliodbs.com> > >> To: pgsql-performance@postgresql.org > >> Sent: Thursday, September 18, 2014 7:54:24 PM > >> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > >> > >> On 09/18/2014 08:09 AM, Mkrtchyan, Tigran wrote: > >>>>> 9.4beta2: > >>>>> ... > >>>>> > >>>>>>> 0.957854 END; > >>>>>>> > >>>>> Looks like IO. > >>> Postgres internal IO? May be. We get 600MB/s on this SSDs. > >> While it's possible that this is a Postgres issue, my first thought is > >> that the two SSDs are not actually identical. The 9.4 one may either > >> have a fault, or may be mostly full and heavily fragmented. Or the Dell > >> PCIe card may have an issue. > > > > We have tested both SSDs and they have identical IO characteristics and > > as I already mentioned, both databases are fresh, including filesystem. > > > >> You are using "scale 1" which is a < 1MB database, and one client and 1 > >> thread, which is an interesting test I wouldn't necessarily have done > >> myself. I'll throw the same test on one of my machines and see how it does. > > this scenario corresponds to our use case. We need a high transaction rate > > per for a single client. Currently I can get only ~1500 tps. Unfortunately, > > posgtress does not tell me where the bottleneck is. Is this is defensively > > not the disk IO. > > > > > > > > > This is when you dig out tools like perf, maybe. Do you have a better suggestions ? > > cheers > > andrew
On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de> wrote: > > 9.3.5: > 0.035940 END; > > > 9.4beta2: > 0.957854 END; time being spent on 'END' is definitely suggesting i/o related issues. This is making me very skeptical that postgres is the source of the problem. I also thing synchronous_commit is not set properly on the new instance (or possibly there is a bug or some such). Can you verify via: select * from pg_settings where name = 'synchronous_commit'; on both servers? What is iowait? For pci-e SSD, these drives don't seem very fast... merlin
On 19/09/14 08:32, Merlin Moncure wrote: > On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran > <tigran.mkrtchyan@desy.de> wrote: >> >> 9.3.5: >> 0.035940 END; >> >> >> 9.4beta2: >> 0.957854 END; > > > time being spent on 'END' is definitely suggesting i/o related issues. > This is making me very skeptical that postgres is the source of the > problem. I also thing synchronous_commit is not set properly on the > new instance (or possibly there is a bug or some such). Can you > verify via: > > select * from pg_settings where name = 'synchronous_commit'; > > on both servers? > Yes, does look suspicious. It *could* be that the 9.4 case is getting unlucky and checkpointing just before the end of the 60s run, and 9.3 isn't. > What is iowait? For pci-e SSD, these drives don't seem very fast... > > > These look like rebranded Micron P320's and should be extremely fast...However I note that my Crucial/Micron M550's are very fast for most writes *but* are much slower for sync writes (and fsync) that happen at commit... Cheers Mark
----- Original Message -----
> From: "Merlin Moncure" <mmoncure@gmail.com>
> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>
> Cc: "postgres performance list" <pgsql-performance@postgresql.org>
> Sent: Thursday, September 18, 2014 10:32:20 PM
> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>
> On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran
> <tigran.mkrtchyan@desy.de> wrote:
> >
> > 9.3.5:
> > 0.035940 END;
> >
> >
> > 9.4beta2:
> > 0.957854 END;
>
>
> time being spent on 'END' is definitely suggesting i/o related issues.
> This is making me very skeptical that postgres is the source of the
> problem. I also thing synchronous_commit is not set properly on the
> new instance (or possibly there is a bug or some such). Can you
> verify via:
>
> select * from pg_settings where name = 'synchronous_commit';
>
> on both servers?
here you are:
9.4beta2
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4beta2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)
postgres=# select * from pg_settings where name = 'synchronous_commit';
name | setting | unit | category | short_desc
| extra_desc | context | vartype |
source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile
| sourceline
--------------------+---------+------+----------------------------+-------------------------------------------------------+------------+---------+---------+-------
-------------+---------+---------+-----------------------------+----------+-----------+-----------------------------------------+------------
synchronous_commit | off | | Write-Ahead Log / Settings | Sets the current transaction's synchronization
level.| | user | enum | config
uration file | | | {local,remote_write,on,off} | on | off |
/var/lib/pgsql/9.4/data/postgresql.conf| 622
(1 row)
9.3.5
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)
postgres=# select * from pg_settings where name = 'synchronous_commit';
name | setting | unit | category | short_desc
| extra_desc | context | vartype |
source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile
| sourceline
--------------------+---------+------+----------------------------+-------------------------------------------------------+------------+---------+---------+-------
-------------+---------+---------+-----------------------------+----------+-----------+-----------------------------------------+------------
synchronous_commit | off | | Write-Ahead Log / Settings | Sets the current transaction's synchronization
level.| | user | enum | config
uration file | | | {local,remote_write,on,off} | on | off |
/var/lib/pgsql/9.3/data/postgresql.conf| 166
(1 row)
>
> What is iowait? For pci-e SSD, these drives don't seem very fast...
iostat, top and pg_top never show iowait greater than 0.7%
Tigran.
>
> merlin
>
----- Original Message ----- > From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> > To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> > Cc: "postgres performance list" <pgsql-performance@postgresql.org> > Sent: Thursday, September 18, 2014 10:56:36 PM > Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > > On 19/09/14 08:32, Merlin Moncure wrote: > > On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran > > <tigran.mkrtchyan@desy.de> wrote: > >> > >> 9.3.5: > >> 0.035940 END; > >> > >> > >> 9.4beta2: > >> 0.957854 END; > > > > > > time being spent on 'END' is definitely suggesting i/o related issues. > > This is making me very skeptical that postgres is the source of the > > problem. I also thing synchronous_commit is not set properly on the > > new instance (or possibly there is a bug or some such). Can you > > verify via: > > > > select * from pg_settings where name = 'synchronous_commit'; > > > > on both servers? > > > > Yes, does look suspicious. It *could* be that the 9.4 case is getting > unlucky and checkpointing just before the end of the 60s run, and 9.3 > isn't. 10 minutes run had the same results. Is there some kind of statistics which can tell there time is spend? Or the only way is to run on solaris with dtrace? For me it's more important to find why I get only 1500tps with 9.3. The test with 9.4 was just a hope for a magic code change that will give me a better performance. Tigran. > > > What is iowait? For pci-e SSD, these drives don't seem very fast... > > > > > > > > These look like rebranded Micron P320's and should be extremely > fast...However I note that my Crucial/Micron M550's are very fast for > most writes *but* are much slower for sync writes (and fsync) that > happen at commit... > > 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 >
On Thu, Sep 18, 2014 at 11:10 PM, Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de> wrote: > > > ----- Original Message ----- >> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >> To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> >> Cc: "postgres performance list" <pgsql-performance@postgresql.org> >> Sent: Thursday, September 18, 2014 10:56:36 PM >> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 >> >> On 19/09/14 08:32, Merlin Moncure wrote: >> > On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran >> > <tigran.mkrtchyan@desy.de> wrote: >> >> >> >> 9.3.5: >> >> 0.035940 END; >> >> >> >> >> >> 9.4beta2: >> >> 0.957854 END; >> > > > 10 minutes run had the same results. > > Is there some kind of statistics which can tell there time is spend? > Or the only way is to run on solaris with dtrace? For me it's more important > to find why I get only 1500tps with 9.3. The test with 9.4 was just a hope for > a magic code change that will give me a better performance. Can you test 9.3 on the 9.4 computer? Regards
On Thu, Sep 18, 2014 at 2:10 PM, Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de> wrote:
----- Original Message -----
> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>
> Cc: "postgres performance list" <pgsql-performance@postgresql.org>
> Sent: Thursday, September 18, 2014 10:56:36 PM
> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>
> On 19/09/14 08:32, Merlin Moncure wrote:
> > On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran
> > <tigran.mkrtchyan@desy.de> wrote:
> >>
> >> 9.3.5:
> >> 0.035940 END;
> >>
> >>
> >> 9.4beta2:
> >> 0.957854 END;
> >
> >
> > time being spent on 'END' is definitely suggesting i/o related issues.
> > This is making me very skeptical that postgres is the source of the
> > problem. I also thing synchronous_commit is not set properly on the
> > new instance (or possibly there is a bug or some such). Can you
> > verify via:
> >
> > select * from pg_settings where name = 'synchronous_commit';
> >
> > on both servers?
> >
>
> Yes, does look suspicious. It *could* be that the 9.4 case is getting
> unlucky and checkpointing just before the end of the 60s run, and 9.3
> isn't.
10 minutes run had the same results.
Is there some kind of statistics which can tell there time is spend?
Probably the first thing I'd so is strace -p the backend process with -T and -ttt while pgbench is running and watch a few seconds go by to see if anything stands out. Then strace -c and see what that shows.
pg_test_fsync with the file put in each of the pg_xlog directory. (Actually, that is probably the first thing to do.)
run pgbench with -l and see if the throughput is smooth or spiky.
What does sar, top or vmstat say?
Run with track_io_timing = on and with pg_stat_statements and see what they show. Also turn on log_checkpoints.
Cheers,
Jeff
On 19/09/14 09:10, Mkrtchyan, Tigran wrote: > > > ----- Original Message ----- >> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >> To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> >> Cc: "postgres performance list" <pgsql-performance@postgresql.org> >> Sent: Thursday, September 18, 2014 10:56:36 PM >> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 >> >> On 19/09/14 08:32, Merlin Moncure wrote: >>> On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran >>> <tigran.mkrtchyan@desy.de> wrote: >>>> >>>> 9.3.5: >>>> 0.035940 END; >>>> >>>> >>>> 9.4beta2: >>>> 0.957854 END; >>> >>> >>> time being spent on 'END' is definitely suggesting i/o related issues. >>> This is making me very skeptical that postgres is the source of the >>> problem. I also thing synchronous_commit is not set properly on the >>> new instance (or possibly there is a bug or some such). Can you >>> verify via: >>> >>> select * from pg_settings where name = 'synchronous_commit'; >>> >>> on both servers? >>> >> >> Yes, does look suspicious. It *could* be that the 9.4 case is getting >> unlucky and checkpointing just before the end of the 60s run, and 9.3 >> isn't. > > 10 minutes run had the same results. > > Is there some kind of statistics which can tell there time is spend? > Or the only way is to run on solaris with dtrace? For me it's more important > to find why I get only 1500tps with 9.3. The test with 9.4 was just a hope for > a magic code change that will give me a better performance. > > Interesting. With respect to dtrace, you can use systemtap on Linux to achieve similar things. However before getting too carried away with that - we already *know* that 9.4 is spending longer in END (i.e commit) than 9.3 is. I'd recommend you see what wal_sync_method is set to on both systems. If it is the same, then my suspicion is that one of the SSD's needs to be trimmed [1]. You can do this by running: $ fstrim /mountpoint Also - are you using the same filesystem and mount options on each SSD? Cheers Mark [1] if fact, for the paranoid - I usually secure erase any SSD before performance testing, and then check the SMART counters too...
On 19/09/14 10:16, Mark Kirkwood wrote:
> On 19/09/14 09:10, Mkrtchyan, Tigran wrote:
>>
>>
>> ----- Original Message -----
>>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
>>> To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan"
>>> <tigran.mkrtchyan@desy.de>
>>> Cc: "postgres performance list" <pgsql-performance@postgresql.org>
>>> Sent: Thursday, September 18, 2014 10:56:36 PM
>>> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>>>
>>> On 19/09/14 08:32, Merlin Moncure wrote:
>>>> On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran
>>>> <tigran.mkrtchyan@desy.de> wrote:
>>>>>
>>>>> 9.3.5:
>>>>> 0.035940 END;
>>>>>
>>>>>
>>>>> 9.4beta2:
>>>>> 0.957854 END;
>>>>
>>>>
>>>> time being spent on 'END' is definitely suggesting i/o related issues.
>>>> This is making me very skeptical that postgres is the source of the
>>>> problem. I also thing synchronous_commit is not set properly on the
>>>> new instance (or possibly there is a bug or some such). Can you
>>>> verify via:
>>>>
>>>> select * from pg_settings where name = 'synchronous_commit';
>>>>
>>>> on both servers?
>>>>
>>>
>>> Yes, does look suspicious. It *could* be that the 9.4 case is getting
>>> unlucky and checkpointing just before the end of the 60s run, and 9.3
>>> isn't.
>>
>> 10 minutes run had the same results.
>>
>> Is there some kind of statistics which can tell there time is spend?
>> Or the only way is to run on solaris with dtrace? For me it's more
>> important
>> to find why I get only 1500tps with 9.3. The test with 9.4 was just a
>> hope for
>> a magic code change that will give me a better performance.
>>
>>
>
> Interesting. With respect to dtrace, you can use systemtap on Linux to
> achieve similar things.
>
> However before getting too carried away with that - we already *know*
> that 9.4 is spending longer in END (i.e commit) than 9.3 is. I'd
> recommend you see what wal_sync_method is set to on both systems. If it
> is the same, then my suspicion is that one of the SSD's needs to be
> trimmed [1]. You can do this by running:
>
> $ fstrim /mountpoint
>
> Also - are you using the same filesystem and mount options on each SSD?
>
> Cheers
>
> Mark
>
> [1] if fact, for the paranoid - I usually secure erase any SSD before
> performance testing, and then check the SMART counters too...
>
Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3
one for 9.4), see below for results.
I'm running xfs on them with trim/discard enabled:
$ mount|grep pg
/dev/sdd4 on /mnt/pg94 type xfs (rw,discard)
/dev/sdc4 on /mnt/pg93 type xfs (rw,discard)
I'm *not* seeing any significant difference between 9.3 and 9.4, and the
numbers are both about 2x your best number, which is food for thought
(those P320's should toast my M550 for write performance...).
9.3:
$ pgbench -r -j 1 -c 1 -T 60 bench
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: 194615
tps = 3243.567115 (including connections establishing)
tps = 3243.771688 (excluding connections establishing)
statement latencies in milliseconds:
0.000798 \set nbranches 1 * :scale
0.000302 \set ntellers 10 * :scale
0.000276 \set naccounts 100000 * :scale
0.000330 \setrandom aid 1 :naccounts
0.000265 \setrandom bid 1 :nbranches
0.000278 \setrandom tid 1 :ntellers
0.000298 \setrandom delta -5000 5000
0.012818 BEGIN;
0.065403 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
aid = :aid;
0.048516 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.058343 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE
tid = :tid;
0.057763 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
bid = :bid;
0.043293 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.017087 END;
9.4:
$ pgbench -r -j 1 -c 1 -T 60 bench
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: 194130
latency average: 0.309 ms
tps = 3235.488190 (including connections establishing)
tps = 3235.560235 (excluding connections establishing)
statement latencies in milliseconds:
0.000460 \set nbranches 1 * :scale
0.000231 \set ntellers 10 * :scale
0.000224 \set naccounts 100000 * :scale
0.000258 \setrandom aid 1 :naccounts
0.000252 \setrandom bid 1 :nbranches
0.000266 \setrandom tid 1 :ntellers
0.000272 \setrandom delta -5000 5000
0.011724 BEGIN;
0.083750 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
aid = :aid;
0.045553 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.054412 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE
tid = :tid;
0.053371 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
bid = :bid;
0.041501 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.015273 END;
configuration:
logging_collector = 'on'
wal_writer_delay = '10s'
vacuum_cost_delay = 50
synchronous_commit = 'off'
wal_buffers = '16MB'
checkpoint_segments = 64
shared_buffers = '2GB'
max_connections = 100
random_page_cost = 1.5
----- Original Message ----- > From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> > To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> > Cc: "Merlin Moncure" <mmoncure@gmail.com>, "postgres performance list" <pgsql-performance@postgresql.org> > Sent: Friday, September 19, 2014 12:49:05 AM > Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > > On 19/09/14 10:16, Mark Kirkwood wrote: > > On 19/09/14 09:10, Mkrtchyan, Tigran wrote: > >> > >> > >> ----- Original Message ----- > >>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> > >>> To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan" > >>> <tigran.mkrtchyan@desy.de> > >>> Cc: "postgres performance list" <pgsql-performance@postgresql.org> > >>> Sent: Thursday, September 18, 2014 10:56:36 PM > >>> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > >>> > >>> On 19/09/14 08:32, Merlin Moncure wrote: > >>>> On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran > >>>> <tigran.mkrtchyan@desy.de> wrote: > >>>>> > >>>>> 9.3.5: > >>>>> 0.035940 END; > >>>>> > >>>>> > >>>>> 9.4beta2: > >>>>> 0.957854 END; > >>>> > >>>> > >>>> time being spent on 'END' is definitely suggesting i/o related issues. > >>>> This is making me very skeptical that postgres is the source of the > >>>> problem. I also thing synchronous_commit is not set properly on the > >>>> new instance (or possibly there is a bug or some such). Can you > >>>> verify via: > >>>> > >>>> select * from pg_settings where name = 'synchronous_commit'; > >>>> > >>>> on both servers? > >>>> > >>> > >>> Yes, does look suspicious. It *could* be that the 9.4 case is getting > >>> unlucky and checkpointing just before the end of the 60s run, and 9.3 > >>> isn't. > >> > >> 10 minutes run had the same results. > >> > >> Is there some kind of statistics which can tell there time is spend? > >> Or the only way is to run on solaris with dtrace? For me it's more > >> important > >> to find why I get only 1500tps with 9.3. The test with 9.4 was just a > >> hope for > >> a magic code change that will give me a better performance. > >> > >> > > > > Interesting. With respect to dtrace, you can use systemtap on Linux to > > achieve similar things. > > > > However before getting too carried away with that - we already *know* > > that 9.4 is spending longer in END (i.e commit) than 9.3 is. I'd > > recommend you see what wal_sync_method is set to on both systems. If it > > is the same, then my suspicion is that one of the SSD's needs to be > > trimmed [1]. You can do this by running: > > > > $ fstrim /mountpoint > > > > Also - are you using the same filesystem and mount options on each SSD? > > > > Cheers > > > > Mark > > > > [1] if fact, for the paranoid - I usually secure erase any SSD before > > performance testing, and then check the SMART counters too... > > > > Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3 > one for 9.4), see below for results. > > I'm running xfs on them with trim/discard enabled: > > $ mount|grep pg > /dev/sdd4 on /mnt/pg94 type xfs (rw,discard) > /dev/sdc4 on /mnt/pg93 type xfs (rw,discard) > > > I'm *not* seeing any significant difference between 9.3 and 9.4, and the > numbers are both about 2x your best number, which is food for thought > (those P320's should toast my M550 for write performance...). cool! any details on OS and other options? I still get the same numbers as before. Tigran. > > > 9.3: > > $ pgbench -r -j 1 -c 1 -T 60 bench > 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: 194615 > tps = 3243.567115 (including connections establishing) > tps = 3243.771688 (excluding connections establishing) > statement latencies in milliseconds: > 0.000798 \set nbranches 1 * :scale > 0.000302 \set ntellers 10 * :scale > 0.000276 \set naccounts 100000 * :scale > 0.000330 \setrandom aid 1 :naccounts > 0.000265 \setrandom bid 1 :nbranches > 0.000278 \setrandom tid 1 :ntellers > 0.000298 \setrandom delta -5000 5000 > 0.012818 BEGIN; > 0.065403 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE > aid = :aid; > 0.048516 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; > 0.058343 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE > tid = :tid; > 0.057763 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE > bid = :bid; > 0.043293 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) > VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); > 0.017087 END; > > > 9.4: > > $ pgbench -r -j 1 -c 1 -T 60 bench > 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: 194130 > latency average: 0.309 ms > tps = 3235.488190 (including connections establishing) > tps = 3235.560235 (excluding connections establishing) > statement latencies in milliseconds: > 0.000460 \set nbranches 1 * :scale > 0.000231 \set ntellers 10 * :scale > 0.000224 \set naccounts 100000 * :scale > 0.000258 \setrandom aid 1 :naccounts > 0.000252 \setrandom bid 1 :nbranches > 0.000266 \setrandom tid 1 :ntellers > 0.000272 \setrandom delta -5000 5000 > 0.011724 BEGIN; > 0.083750 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE > aid = :aid; > 0.045553 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; > 0.054412 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE > tid = :tid; > 0.053371 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE > bid = :bid; > 0.041501 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) > VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); > 0.015273 END; > > configuration: > > logging_collector = 'on' > wal_writer_delay = '10s' > vacuum_cost_delay = 50 > synchronous_commit = 'off' > wal_buffers = '16MB' > checkpoint_segments = 64 > shared_buffers = '2GB' > max_connections = 100 > random_page_cost = 1.5 > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
On 19/09/14 17:53, Mkrtchyan, Tigran wrote: > > > ----- Original Message ----- >> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3 >> one for 9.4), see below for results. >> >> I'm running xfs on them with trim/discard enabled: >> >> $ mount|grep pg >> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard) >> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard) >> >> >> I'm *not* seeing any significant difference between 9.3 and 9.4, and the >> numbers are both about 2x your best number, which is food for thought >> (those P320's should toast my M550 for write performance...). > > cool! any details on OS and other options? I still get the same numbers > as before. > Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation). I saw the suggestion that Didier made to run 9.3 on the SSD that you were using for 9.4, and see if it suddenly goes slow - then we'd know it's something about the disk (or filesystem/mount options). Can you test this? Cheers Mark
----- Original Message ----- > From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> > To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> > Cc: "Merlin Moncure" <mmoncure@gmail.com>, "postgres performance list" <pgsql-performance@postgresql.org> > Sent: Friday, September 19, 2014 8:26:27 AM > Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > > On 19/09/14 17:53, Mkrtchyan, Tigran wrote: > > > > > > ----- Original Message ----- > >> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> > > >> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3 > >> one for 9.4), see below for results. > >> > >> I'm running xfs on them with trim/discard enabled: > >> > >> $ mount|grep pg > >> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard) > >> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard) > >> > >> > >> I'm *not* seeing any significant difference between 9.3 and 9.4, and the > >> numbers are both about 2x your best number, which is food for thought > >> (those P320's should toast my M550 for write performance...). > > > > cool! any details on OS and other options? I still get the same numbers > > as before. > > > > Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation). > > I saw the suggestion that Didier made to run 9.3 on the SSD that you > were using for 9.4, and see if it suddenly goes slow - then we'd know > it's something about the disk (or filesystem/mount options). Can you > test this? swapping the disks did not change the results. Nevertheless, I run the same test on my fedora20 laptop 8GB RAM, i7 2.2GHz and got 2600tps! I am totally confused now! Is it kernel version? libc? Tigran. > > Cheers > > Mark >
On 19/09/14 19:24, Mkrtchyan, Tigran wrote: > > > ----- Original Message ----- >> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> >> Cc: "Merlin Moncure" <mmoncure@gmail.com>, "postgres performance list" <pgsql-performance@postgresql.org> >> Sent: Friday, September 19, 2014 8:26:27 AM >> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 >> >> On 19/09/14 17:53, Mkrtchyan, Tigran wrote: >>> >>> >>> ----- Original Message ----- >>>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >> >>>> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3 >>>> one for 9.4), see below for results. >>>> >>>> I'm running xfs on them with trim/discard enabled: >>>> >>>> $ mount|grep pg >>>> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard) >>>> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard) >>>> >>>> >>>> I'm *not* seeing any significant difference between 9.3 and 9.4, and the >>>> numbers are both about 2x your best number, which is food for thought >>>> (those P320's should toast my M550 for write performance...). >>> >>> cool! any details on OS and other options? I still get the same numbers >>> as before. >>> >> >> Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation). >> >> I saw the suggestion that Didier made to run 9.3 on the SSD that you >> were using for 9.4, and see if it suddenly goes slow - then we'd know >> it's something about the disk (or filesystem/mount options). Can you >> test this? > > > swapping the disks did not change the results. > Nevertheless, I run the same test on my fedora20 laptop > 8GB RAM, i7 2.2GHz and got 2600tps! I am totally > confused now! Is it kernel version? libc? > > Well, that's progress anyway! I guess you could try fedora 20 on the Dell server and see if that makes any difference. But yes, confusing. Having been dealing with a high end Dell server myself recently (R920), some re-reading of any manuals you can find might be useful, we were continually surprised how easy it was to have everything configured *slow*... and the detail in the manuals...could be better! Cheers Mark
On 19/09/14 19:24, Mkrtchyan, Tigran wrote: > > > ----- Original Message ----- >> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> >> Cc: "Merlin Moncure" <mmoncure@gmail.com>, "postgres performance list" <pgsql-performance@postgresql.org> >> Sent: Friday, September 19, 2014 8:26:27 AM >> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 >> >> On 19/09/14 17:53, Mkrtchyan, Tigran wrote: >>> >>> >>> ----- Original Message ----- >>>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >> >>>> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3 >>>> one for 9.4), see below for results. >>>> >>>> I'm running xfs on them with trim/discard enabled: >>>> >>>> $ mount|grep pg >>>> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard) >>>> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard) >>>> >>>> >>>> I'm *not* seeing any significant difference between 9.3 and 9.4, and the >>>> numbers are both about 2x your best number, which is food for thought >>>> (those P320's should toast my M550 for write performance...). >>> >>> cool! any details on OS and other options? I still get the same numbers >>> as before. >>> >> >> Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation). >> >> I saw the suggestion that Didier made to run 9.3 on the SSD that you >> were using for 9.4, and see if it suddenly goes slow - then we'd know >> it's something about the disk (or filesystem/mount options). Can you >> test this? > > > swapping the disks did not change the results. > > Do you mean that 9.3 was still faster using the disk that 9.4 had used? If so that strongly suggests that there is something you have configured differently in the 9.4 installation [1]. Not wanting to sound mean - but it is really easy to accidentally connect to the wrong instance when there are two on the same box (ahem, yes , done it myself). So perhaps another look at the 9.4 vs 9.3 setup (or even posti the config files postgresql.conf + postgresql.auto.conf for 9.4 here). Regards Mark [1] In the light of my previous test of (essentially) your config + numerous other folk have been benchmarking 9.4.
On Fri, Sep 19, 2014 at 6:58 PM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote: > On 19/09/14 19:24, Mkrtchyan, Tigran wrote: >> >> >> >> ----- Original Message ----- >>> >>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >>> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> >>> Cc: "Merlin Moncure" <mmoncure@gmail.com>, "postgres performance list" >>> <pgsql-performance@postgresql.org> >>> Sent: Friday, September 19, 2014 8:26:27 AM >>> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 >>> >>> On 19/09/14 17:53, Mkrtchyan, Tigran wrote: >>>> >>>> >>>> >>>> ----- Original Message ----- >>>>> >>>>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> >>> >>> >>>>> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3 >>>>> one for 9.4), see below for results. >>>>> >>>>> I'm running xfs on them with trim/discard enabled: >>>>> >>>>> $ mount|grep pg >>>>> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard) >>>>> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard) >>>>> >>>>> >>>>> I'm *not* seeing any significant difference between 9.3 and 9.4, and >>>>> the >>>>> numbers are both about 2x your best number, which is food for thought >>>>> (those P320's should toast my M550 for write performance...). >>>> >>>> >>>> cool! any details on OS and other options? I still get the same numbers >>>> as before. >>>> >>> >>> Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my >>> workstation). >>> >>> I saw the suggestion that Didier made to run 9.3 on the SSD that you >>> were using for 9.4, and see if it suddenly goes slow - then we'd know >>> it's something about the disk (or filesystem/mount options). Can you >>> test this? >> >> >> >> swapping the disks did not change the results. >> >> > > Do you mean that 9.3 was still faster using the disk that 9.4 had used? If > so that strongly suggests that there is something you have configured > differently in the 9.4 installation [1]. Not wanting to sound mean - but it > is really easy to accidentally connect to the wrong instance when there are > two on the same box (ahem, yes , done it myself). So perhaps another look at > the 9.4 vs 9.3 setup (or even posti the config files postgresql.conf + > postgresql.auto.conf for 9.4 here). Huh. Where did the 9.4 build come from? I wonder if there are some debugging options set. Can you check 9.4 pg_settings for value of"debug_assertions"? If it's set true, you might want to consider hand compiling postgres until 9.4 is released... merlin
Hi Merlin,
you are right, in 9.4 the debug_assertions are on:
# /etc/init.d/postgresql-9.4 start
Starting postgresql-9.4 service: [ OK ]
# psql -U postgres
psql (9.4beta2)
Type "help" for help.
postgres=# select name,setting from pg_settings where name='debug_assertions';
name | setting
------------------+---------
debug_assertions | on
(1 row)
postgres=# \q
# /etc/init.d/postgresql-9.4 stop
Stopping postgresql-9.4 service: [ OK ]
# /etc/init.d/postgresql-9.3 start
Starting postgresql-9.3 service: [ OK ]
# psql -U postgres
psql (9.4beta2, server 9.3.5)
Type "help" for help.
postgres=# select name,setting from pg_settings where name='debug_assertions';
name | setting
------------------+---------
debug_assertions | off
(1 row)
postgres=# \q
#
The rpms are coming from Postgres official repo:
http://yum.postgresql.org/9.4/redhat/rhel-$releasever-$basearch
Tigran.
----- Original Message -----
> From: "Merlin Moncure" <mmoncure@gmail.com>
> To: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> Cc: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>, "postgres performance list" <pgsql-performance@postgresql.org>
> Sent: Monday, September 22, 2014 3:37:50 PM
> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>
> On Fri, Sep 19, 2014 at 6:58 PM, Mark Kirkwood
> <mark.kirkwood@catalyst.net.nz> wrote:
> > On 19/09/14 19:24, Mkrtchyan, Tigran wrote:
> >>
> >>
> >>
> >> ----- Original Message -----
> >>>
> >>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> >>> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>
> >>> Cc: "Merlin Moncure" <mmoncure@gmail.com>, "postgres performance list"
> >>> <pgsql-performance@postgresql.org>
> >>> Sent: Friday, September 19, 2014 8:26:27 AM
> >>> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
> >>>
> >>> On 19/09/14 17:53, Mkrtchyan, Tigran wrote:
> >>>>
> >>>>
> >>>>
> >>>> ----- Original Message -----
> >>>>>
> >>>>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> >>>
> >>>
> >>>>> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3
> >>>>> one for 9.4), see below for results.
> >>>>>
> >>>>> I'm running xfs on them with trim/discard enabled:
> >>>>>
> >>>>> $ mount|grep pg
> >>>>> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard)
> >>>>> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard)
> >>>>>
> >>>>>
> >>>>> I'm *not* seeing any significant difference between 9.3 and 9.4, and
> >>>>> the
> >>>>> numbers are both about 2x your best number, which is food for thought
> >>>>> (those P320's should toast my M550 for write performance...).
> >>>>
> >>>>
> >>>> cool! any details on OS and other options? I still get the same numbers
> >>>> as before.
> >>>>
> >>>
> >>> Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my
> >>> workstation).
> >>>
> >>> I saw the suggestion that Didier made to run 9.3 on the SSD that you
> >>> were using for 9.4, and see if it suddenly goes slow - then we'd know
> >>> it's something about the disk (or filesystem/mount options). Can you
> >>> test this?
> >>
> >>
> >>
> >> swapping the disks did not change the results.
> >>
> >>
> >
> > Do you mean that 9.3 was still faster using the disk that 9.4 had used? If
> > so that strongly suggests that there is something you have configured
> > differently in the 9.4 installation [1]. Not wanting to sound mean - but it
> > is really easy to accidentally connect to the wrong instance when there are
> > two on the same box (ahem, yes , done it myself). So perhaps another look
> > at
> > the 9.4 vs 9.3 setup (or even posti the config files postgresql.conf +
> > postgresql.auto.conf for 9.4 here).
>
> Huh. Where did the 9.4 build come from? I wonder if there are some
> debugging options set. Can you check 9.4 pg_settings for value
> of"debug_assertions"? If it's set true, you might want to consider
> hand compiling postgres until 9.4 is released...
>
> merlin
>
On Tue, Sep 23, 2014 at 7:58 AM, Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de> wrote: > Hi Merlin, > > you are right, in 9.4 the debug_assertions are on: > > # /etc/init.d/postgresql-9.4 start > Starting postgresql-9.4 service: [ OK ] > # psql -U postgres > psql (9.4beta2) > Type "help" for help. > > postgres=# select name,setting from pg_settings where name='debug_assertions'; > name | setting > ------------------+--------- > debug_assertions | on > (1 row) (plz try to not top-post). That's not not really unexpected: 9.4 is still in beta. If you're just doing raw performance testing consider building a postgres instance from source (but, instead of compiling into /usr/local/bin, I'd keep it all in private user folder for easy removal). For example, if I downloaded the source into /home/mmoncure/pgdev/src, i'd approximately do: cd /home/mmoncure/pgdev/src ./configure --prefix=/home/mmoncure/pgdev # if configure gripes about missing readline, go grab the libreadline-dev rpm etc and repeat above make -j4 && make install export PATH=/home/mmoncure/pgdev/bin:$PATH export PGDATA=/home/mmoncure/pgdev/data # use C locale. may not be appropriate in your case initdb --no-locale --encoding=UTF8 pg_ctl start This should suffice any beta performance testing you need to do. When 9.4 proper comes out, just stop the database and kill the pgdev folder (taking a backup first if you need to preserve stuff). merlin
Hi Merlin et al. after building postgres 9.4 myself from sources I get the same performance as with 9.3. The difference was in the value of debug_assertions setting. Now the next step. Why my 3 years old laptop gets x1.8 times more tps than my one month old server? And Mark Kirkwood's desktop gets x2 times more tps as well? Is there some special optimization for i7 which does not work with Intel(R) Xeon(R) CPU E5-2660? Thanks, Tigran. ----- Original Message ----- > From: "Merlin Moncure" <mmoncure@gmail.com> > To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de> > Cc: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>, "postgres performance list" <pgsql-performance@postgresql.org> > Sent: Tuesday, September 23, 2014 4:21:13 PM > Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > > On Tue, Sep 23, 2014 at 7:58 AM, Mkrtchyan, Tigran > <tigran.mkrtchyan@desy.de> wrote: > > Hi Merlin, > > > > you are right, in 9.4 the debug_assertions are on: > > > > # /etc/init.d/postgresql-9.4 start > > Starting postgresql-9.4 service: [ OK ] > > # psql -U postgres > > psql (9.4beta2) > > Type "help" for help. > > > > postgres=# select name,setting from pg_settings where > > name='debug_assertions'; > > name | setting > > ------------------+--------- > > debug_assertions | on > > (1 row) > > > (plz try to not top-post). > > That's not not really unexpected: 9.4 is still in beta. If you're > just doing raw performance testing consider building a postgres > instance from source (but, instead of compiling into /usr/local/bin, > I'd keep it all in private user folder for easy removal). > > For example, if I downloaded the source into /home/mmoncure/pgdev/src, > i'd approximately do: > > cd /home/mmoncure/pgdev/src > ./configure --prefix=/home/mmoncure/pgdev > # if configure gripes about missing readline, go grab the > libreadline-dev rpm etc and repeat above > make -j4 && make install > export PATH=/home/mmoncure/pgdev/bin:$PATH > export PGDATA=/home/mmoncure/pgdev/data > # use C locale. may not be appropriate in your case > initdb --no-locale --encoding=UTF8 > pg_ctl start > > This should suffice any beta performance testing you need to do. When > 9.4 proper comes out, just stop the database and kill the pgdev folder > (taking a backup first if you need to preserve stuff). > > merlin > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
On 24/09/14 21:23, Mkrtchyan, Tigran wrote: > Hi Merlin et al. > > after building postgres 9.4 myself from sources I get the same performance as > with 9.3. The difference was in the value of debug_assertions setting. > > Now the next step. Why my 3 years old laptop gets x1.8 times more tps than my one month old server? > And Mark Kirkwood's desktop gets x2 times more tps as well? Is there some special optimization > for i7 which does not work with Intel(R) Xeon(R) CPU E5-2660? > > Yes - firstly, nicely done re finding the assertions (my 9.4 beta2 was built from src - never thought to mention sorry)! I'd guess that you are seeing some bios setting re the p320 SSD - it *should* be seriously fast...but does not seem to be. You could try running some pure IO benchmarks to confirm this (e.g fio). Also see if the manual for however it is attached to the system allows for some optimized-for-ssd settings that tend to work better (altho these usually imply the drive is plugged into an adapter card of some kind - mind you your p320 *does* used a custom connector that does 2.5" SATA to PCIe style interconnect so I'd look to debug that first). Cheers Mark
With pg_test_timing I can see, that overhead is 48 nsec on my server and 32 nsec on the laptop. what makes this difference and have it any influence on the overall performance? Tigran. ----- Original Message ----- > From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz> > To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>, "Merlin Moncure" <mmoncure@gmail.com> > Cc: "postgres performance list" <pgsql-performance@postgresql.org> > Sent: Wednesday, September 24, 2014 12:04:12 PM > Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 > > On 24/09/14 21:23, Mkrtchyan, Tigran wrote: > > Hi Merlin et al. > > > > after building postgres 9.4 myself from sources I get the same performance > > as > > with 9.3. The difference was in the value of debug_assertions setting. > > > > Now the next step. Why my 3 years old laptop gets x1.8 times more tps than > > my one month old server? > > And Mark Kirkwood's desktop gets x2 times more tps as well? Is there some > > special optimization > > for i7 which does not work with Intel(R) Xeon(R) CPU E5-2660? > > > > > > Yes - firstly, nicely done re finding the assertions (my 9.4 beta2 was > built from src - never thought to mention sorry)! > > I'd guess that you are seeing some bios setting re the p320 SSD - it > *should* be seriously fast...but does not seem to be. You could try > running some pure IO benchmarks to confirm this (e.g fio). Also see if > the manual for however it is attached to the system allows for some > optimized-for-ssd settings that tend to work better (altho these usually > imply the drive is plugged into an adapter card of some kind - mind you > your p320 *does* used a custom connector that does 2.5" SATA to PCIe > style interconnect so I'd look to debug that first). > > Cheers > > Mark > >
Hi Tigran,
my debugging tips:
>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
As I know PCIe SSD DELL_P320h = Micron P320h
so my suggestions:
#1. Check/Upgrade to the latest Micron kernel driver
*** "RHEL version 6.1–6.5: kmod-mtip32xx-<version>.el6.x86_64_rhel6ux.rpm"
#2. And check "Technical Note P320h/P420m SSD Performance Optimization and Testing"
strange :
**** "Table 1: PCIe SSD Hardware and Software Requirements "
***** "Processors with clock speeds greater than 3 GHz (recommended for best performance)" ( you have now: 2.20GHz )
***** "Up to 8 CPU cores (logical + physical) with hyperthreading (recommended)" ( you have 20! )
so check performance with "Turning off Hyper-Threading"
Imre
2014-09-24 15:03 GMT+02:00 Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>:
With pg_test_timing I can see, that overhead is 48 nsec on my server and 32 nsec on the laptop.
what makes this difference and have it any influence on the overall performance?
Tigran.
----- Original Message -----
> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>, "Merlin Moncure" <mmoncure@gmail.com>
> Cc: "postgres performance list" <pgsql-performance@postgresql.org>
> Sent: Wednesday, September 24, 2014 12:04:12 PM
> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>> On 24/09/14 21:23, Mkrtchyan, Tigran wrote:
> > Hi Merlin et al.
> >
> > after building postgres 9.4 myself from sources I get the same performance
> > as
> > with 9.3. The difference was in the value of debug_assertions setting.
> >
> > Now the next step. Why my 3 years old laptop gets x1.8 times more tps than
> > my one month old server?
> > And Mark Kirkwood's desktop gets x2 times more tps as well? Is there some
> > special optimization
> > for i7 which does not work with Intel(R) Xeon(R) CPU E5-2660?
> >
> >
>
> Yes - firstly, nicely done re finding the assertions (my 9.4 beta2 was
> built from src - never thought to mention sorry)!
>
> I'd guess that you are seeing some bios setting re the p320 SSD - it
> *should* be seriously fast...but does not seem to be. You could try
> running some pure IO benchmarks to confirm this (e.g fio). Also see if
> the manual for however it is attached to the system allows for some
> optimized-for-ssd settings that tend to work better (altho these usually
> imply the drive is plugged into an adapter card of some kind - mind you
> your p320 *does* used a custom connector that does 2.5" SATA to PCIe
> style interconnect so I'd look to debug that first).
>
> 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
On 25/09/14 01:03, Mkrtchyan, Tigran wrote:
>
> With pg_test_timing I can see, that overhead is 48 nsec on my server and 32 nsec on the laptop.
> what makes this difference and have it any influence on the overall performance?
>
Hmm - 22 nsec for my workstation, so while it could be a factor, your
laptop and my workstation performed the pgbench about the same, so I'd
look elsewhere - in particlular sync IO performance:
$ cd <where ssd mounted>
$ pg_test_fsync
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 140.231 ops/sec 7131
usecs/op
fdatasync 138.159 ops/sec 7238
usecs/op
fsync 137.680 ops/sec 7263
usecs/op
fsync_writethrough n/a
open_sync 137.202 ops/sec 7289
usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 68.832 ops/sec 14528
usecs/op
fdatasync 135.994 ops/sec 7353
usecs/op
fsync 137.454 ops/sec 7275
usecs/op
fsync_writethrough n/a
open_sync 69.092 ops/sec 14473
usecs/op
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
1 * 16kB open_sync write 136.904 ops/sec 7304
usecs/op
2 * 8kB open_sync writes 68.857 ops/sec 14523
usecs/op
4 * 4kB open_sync writes 34.744 ops/sec 28782
usecs/op
8 * 2kB open_sync writes write failed: Invalid argument