Re: [PERFORM] postgres 9.3 vs. 9.4
On 19/09/14 17:53, Mkrtchyan, Tigran wrote: - Original Message - From: "Mark Kirkwood" 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
- Original Message - > From: "Mark Kirkwood" > To: "Tigran Mkrtchyan" > Cc: "Merlin Moncure" , "postgres performance list" > > 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" > >>> To: "Merlin Moncure" , "Tigran Mkrtchyan" > >>> > >>> Cc: "postgres performance list" > >>> 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 > wrote: > > > > 9.3.5: > > 0.035940END; > > > > > > 9.4beta2: > > 0.957854END; > > > 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 10 * :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.012818BEGIN; > 0.065403UPDATE pgbench_accounts SET abalance = abalance + > :delta WHERE > aid = :aid; > 0.048516SELECT abalance FROM pgbench_accounts WHERE aid = :aid; > 0.058343UPDATE pgbench_tellers SET tbalance = tbalance + :delta > WHERE > tid = :tid; > 0.057763UPDATE pgbench_branches SET bbalance = bbalance + > :delta WHERE > bid = :bid; > 0.043293INSERT INTO pgbench_history (tid, bid, aid, delta, > mtime) > VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); > 0.017087END; > > > 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 mil
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" To: "Merlin Moncure" , "Tigran Mkrtchyan" Cc: "postgres performance list" 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 wrote: 9.3.5: 0.035940END; 9.4beta2: 0.957854END; 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 10 * :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.012818BEGIN; 0.065403 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.048516SELECT 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.017087END; 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 10 * :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.011724BEGIN; 0.083750 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.045553SELECT 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, :d
Re: [PERFORM] postgres 9.3 vs. 9.4
On 19/09/14 09:10, Mkrtchyan, Tigran wrote: - Original Message - From: "Mark Kirkwood" To: "Merlin Moncure" , "Tigran Mkrtchyan" Cc: "postgres performance list" 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 wrote: 9.3.5: 0.035940END; 9.4beta2: 0.957854END; 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... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
On Thu, Sep 18, 2014 at 2:10 PM, Mkrtchyan, Tigran wrote: > > > - Original Message - > > From: "Mark Kirkwood" > > To: "Merlin Moncure" , "Tigran Mkrtchyan" < > tigran.mkrtch...@desy.de> > > Cc: "postgres performance list" > > 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 > > > wrote: > > >> > > >> 9.3.5: > > >> 0.035940END; > > >> > > >> > > >> 9.4beta2: > > >> 0.957854END; > > > > > > > > > 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
Re: [PERFORM] postgres 9.3 vs. 9.4
On Thu, Sep 18, 2014 at 11:10 PM, Mkrtchyan, Tigran wrote: > > > - Original Message - >> From: "Mark Kirkwood" >> To: "Merlin Moncure" , "Tigran Mkrtchyan" >> >> Cc: "postgres performance list" >> 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 >> > wrote: >> >> >> >> 9.3.5: >> >> 0.035940END; >> >> >> >> >> >> 9.4beta2: >> >> 0.957854END; >> > > > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
- Original Message - > From: "Mark Kirkwood" > To: "Merlin Moncure" , "Tigran Mkrtchyan" > > Cc: "postgres performance list" > 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 > > wrote: > >> > >> 9.3.5: > >> 0.035940END; > >> > >> > >> 9.4beta2: > >> 0.957854END; > > > > > > 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 > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
- Original Message -
> From: "Merlin Moncure"
> To: "Tigran Mkrtchyan"
> Cc: "postgres performance list"
> 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
> wrote:
> >
> > 9.3.5:
> > 0.035940END;
> >
> >
> > 9.4beta2:
> > 0.957854END;
>
>
> 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
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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 wrote: 9.3.5: 0.035940END; 9.4beta2: 0.957854END; 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran wrote: > > 9.3.5: > 0.035940END; > > > 9.4beta2: > 0.957854END; 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
On Sep 18, 2014 9:32 PM, Andrew Dunstan wrote: > > > On 09/18/2014 03:09 PM, Mkrtchyan, Tigran wrote: > > > > - Original Message - > >> From: "Josh Berkus" > >> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
On 09/18/2014 03:09 PM, Mkrtchyan, Tigran wrote: - Original Message - From: "Josh Berkus" 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.957854END; 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
- Original Message - > From: "Josh Berkus" > 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.957854END; > >>> > > > >> > > >> > 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 > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
On 09/18/2014 08:09 AM, Mkrtchyan, Tigran wrote: >>> 9.4beta2: >>> > > >> > ... >> > >>> > > 0.957854END; >>> > > >> > >> > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
- Original Message - > From: "Jeff Janes" > To: "Tigran Mkrtchyan" > 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 > 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.035940END; > > > > > > 9.4beta2: > > > ... > > > 0.957854END; > > > > Looks like IO. Postgres internal IO? May be. We get 600MB/s on this SSDs. Tigran. > > Cheers, > > Jeff > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres 9.3 vs. 9.4
On Thu, Sep 18, 2014 at 2:58 AM, 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 ) > 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.035940END; > > > 9.4beta2: > ... > 0.957854END; > Looks like IO. Cheers, Jeff
Re: [PERFORM] postgres 9.3 vs. 9.4
- Original Message - > From: "Mark Kirkwood" > To: "Tigran Mkrtchyan" , > 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 10 * :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.025161BEGIN; > > 0.131317UPDATE pgbench_accounts SET abalance = abalance + > > :delta WHERE > > aid = :aid; > > 0.100211SELECT abalance FROM pgbench_accounts WHERE aid = :aid; > > 0.117406UPDATE pgbench_tellers SET tbalance = tbalance + :delta > > WHERE tid > > = :tid; > > 0.114332UPDATE pgbench_branches SET bbalance = bbalance + > > :delta WHERE > > bid = :bid; > > 0.086660INSERT INTO pgbench_history (tid, bid, aid, delta, > > mtime) VALUES > > (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); > > 0.035940END; > > > > > > 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 10 * :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.055047BEGIN; > > 0.172179UPDATE pgbench_accounts SET abalance = abalance + > > :delta WHERE > > aid = :aid; > > 0.135392SELECT abalance FROM pgbench_accounts WHERE aid = :aid; > > 0.157224UPDATE pgbench_tellers SET tbalance = tbalance + :delta > > WHERE tid > > = :tid; > > 0.147969UPDATE pgbench_branches SET bbalance = bbalance + > > :delta WHERE > > bid = :bid; > > 0.123001INSERT INTO pgbench_history (tid, bid, aid, delta, > > mtime) VALUES > > (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); > > 0.957854END; > > > > 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..
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 10 * :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.025161BEGIN; 0.131317UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.100211SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.117406UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.114332UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.086660INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.035940END; 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 10 * :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.055047BEGIN; 0.172179UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.135392SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.157224UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.147969UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.123001INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.957854END; 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
[PERFORM] 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 10 * :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.025161BEGIN; 0.131317UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.100211SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.117406UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.114332UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.086660INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.035940END; 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 10 * :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.055047BEGIN; 0.172179UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.135392SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.157224UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.147969UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.123001INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.957854END; 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
Re: [PERFORM] Aggregating tsqueries
On 09/17/2014 07:56 AM, Alexander Hill wrote:
Hello,
I have a table of tree nodes with a tsquery column. To get a subtree's
tsquery, I need to OR all of its nodes' tsqueries together.
I defined a custom aggregate using tsquery_or:
CREATE AGGREGATE tsquery_or_agg (tsquery)
(
sfunc = tsquery_or,
stype = tsquery
);
but I've found that
tsquery_or_agg(query)
is about a hundred times slower than this:
('(' || string_agg(query::text, ')|(') || ')')::tsquery
That works perfectly so I'm happy to continue doing it, but I'm curious to
know why the difference is so great and if anything can be done about it?
string_agg's state transition function uses a buffer that's expanded as
needed. At every step, the next string is appended to the buffer. Your
custom aggregate is less efficient, because it constructs a new tsquery
object at every step. In every step, a new tsquery object is allocated
and the old result and the next source tsquery are copied to it. That's
much more expensive.
If you're not shy of writing C code, you could write a more efficient
version of tsquery_or_agg too, using a similar technique.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
