Re: [PERFORM] 9.5alpha1 vs 9.4
On Jul 6, 2015 18:45, Josh Berkus wrote: > > On 07/05/2015 10:16 AM, Mkrtchyan, Tigran wrote: > > Thanks for the hin. My bad. The backup db and 9.5 had a different type on > > one of the foreign-key constrains char(36) vs varchar(36). > > > > The schema was screwed couple of days ago, byt performance numbers I > > checked only > > after migration to 9.5. > > Thank you for testing! > > Can you re-run your tests with the fixed schema? How does it look? With fixed schema performance equal to 9.4. I have updated my code to use ON CONFLICT statement. ~5% better compared with INSERT WHERE NOT EXIST. Really cool! Thanks. 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] New server: SSD/RAID recommendations?
On 07/06/2015 09:56 AM, Steve Crawford wrote: On 07/02/2015 07:01 AM, Wes Vaske (wvaske) wrote: For what it's worth, in my most recent iteration I decided to go with the Intel Enterprise NVMe drives and no RAID. My reasoning was thus: 1. Modern SSDs are so fast that even if you had an infinitely fast RAID card you would still be severely constrained by the limits of SAS/SATA. To get the full speed advantages you have to connect directly into the bus. Correct. What we have done in the past is use smaller drives with RAID 10. This isn't for the performance but for the longevity of the drive. We obviously could do this with Software RAID or Hardware RAID. 2. We don't typically have redundant electronic components in our servers. Sure, we have dual power supplies and dual NICs (though generally to handle external failures) and ECC-RAM but no hot-backup CPU or redundant RAM banks and...no backup RAID card. Intel Enterprise SSD already have power-fail protection so I don't need a RAID card to give me BBU. Given the MTBF of good enterprise SSD I'm left to wonder if placing a RAID card in front merely adds a new point of failure and scheduled-downtime-inducing hands-on maintenance (I'm looking at you, RAID backup battery). That's an interesting question. It definitely adds yet another component. I can't believe how often we need to "hotfix" a raid controller. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] New server: SSD/RAID recommendations?
On 07/02/2015 07:01 AM, Wes Vaske (wvaske) wrote: What about a RAID controller? Are RAID controllers even available for PCI-Express SSD drives, or do we have to stick with SATA if we need a battery-backed RAID controller? Or is software RAID sufficient for SSD drives? Quite a few of the benefits of using a hardware RAID controller are irrelevant when using modern SSDs. The great random write performance of the drives means the cache on the controller is less useful and the drives you’re considering (Intel’s enterprise grade) will have full power protection for inflight data. For what it's worth, in my most recent iteration I decided to go with the Intel Enterprise NVMe drives and no RAID. My reasoning was thus: 1. Modern SSDs are so fast that even if you had an infinitely fast RAID card you would still be severely constrained by the limits of SAS/SATA. To get the full speed advantages you have to connect directly into the bus. 2. We don't typically have redundant electronic components in our servers. Sure, we have dual power supplies and dual NICs (though generally to handle external failures) and ECC-RAM but no hot-backup CPU or redundant RAM banks and...no backup RAID card. Intel Enterprise SSD already have power-fail protection so I don't need a RAID card to give me BBU. Given the MTBF of good enterprise SSD I'm left to wonder if placing a RAID card in front merely adds a new point of failure and scheduled-downtime-inducing hands-on maintenance (I'm looking at you, RAID backup battery). 3. I'm streaming to an entire redundant server and doing regular backups anyway so I'm covered for availability and recovery should the SSD (or anything else in the server) fail. BTW, here's an article worth reading: https://blog.algolia.com/when-solid-state-drives-are-not-that-solid/ Cheers, Steve
Re: [PERFORM] 9.5alpha1 vs 9.4
On 07/05/2015 10:16 AM, Mkrtchyan, Tigran wrote: > Thanks for the hin. My bad. The backup db and 9.5 had a different type on > one of the foreign-key constrains char(36) vs varchar(36). > > The schema was screwed couple of days ago, byt performance numbers I checked > only > after migration to 9.5. Thank you for testing! Can you re-run your tests with the fixed schema? How does it look? -- 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] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
On Fri, Jul 3, 2015 at 9:48 AM, Graeme B. Bell wrote: > Hi everyone, > > I've written a new open source tool for easily parallelising SQL scripts in > postgres. [obligatory plug: https://github.com/gbb/par_psql ] > > Using it, I'm seeing a problem that I've also seen in other postgres projects > involving high degrees of parallelisation in the last 12 months. > > Basically: > > - I have machines here with up to 16 CPU cores and 128GB memory, very fast > SSDs and controller etc, carefully configured kernel/postgresql.conf for high > performance. > > - Ordinary queries parallelise nearly perfectly (e.g. SELECT some_stuff ...), > e.g. almost up to 16x performance improvement. > > - Non-DB stuff like GDAL, python etc. parallelise nearly perfectly. > > - HOWEVER calls to CPU-intensive user-defined pl/pgsql functions (e.g. SELECT > myfunction(some_stuff)) do not parallelise well, even when they are > independently defined functions, or accessing tables in a read-only way. They > hit a limit of 2.5x performance improvement relative to single-CPU > performance (pg9.4) and merely 2x performance (pg9.3) regardless of how many > CPU cores I throw at them. This is about 6 times slower than I'm expecting. > > I can't see what would be locking. It seems like it's the pl/pgsql > environment itself that is somehow locking or incurring some huge frictional > costs. Whether I use independently defined functions, independent source > tables, independent output tables, makes no difference whatsoever, so it > doesn't feel 'lock-related'. It also doesn't seem to be WAL/synchronisation > related, as the machines I'm using can hit absurdly high pgbench rates, and > I'm using unlogged tables for output. > > Take a quick peek here: > https://github.com/gbb/par_psql/blob/master/BENCHMARKS.md > > I'm wondering what I'm missing here. Any ideas? I'm not necessarily seeing your results. via pgbench, mmoncure@mernix2 11:34 AM ~$ ~/pgdev/bin/pgbench -n -T 60 -f b.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 658833 latency average: 0.091 ms tps = 10980.538470 (including connections establishing) tps = 10980.994547 (excluding connections establishing) mmoncure@mernix2 11:35 AM ~$ ~/pgdev/bin/pgbench -n -T 60 -c4 -j4 -f b.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 4 number of threads: 4 duration: 60 s number of transactions actually processed: 2847631 latency average: 0.084 ms tps = 47460.430447 (including connections establishing) tps = 47463.702074 (excluding connections establishing) b.sql: select f(); f(): create or replace function f() returns int as $$ begin return 1; end; $$ language plpgsql; the results are pretty volatile even with a 60s run, but I'm clearly not capped at 2.5x parallelization (my box is 4 core). It would help if you disclosed the function body you're benchmarking. If the problem is indeed on the sever, the next step I think is to profile the code and look for locking issues. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
