Re: [PERFORM] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
No, of course it doesn't. It appears that you didn't look at the repo or read my previous mail before you wrote this. FFS, I *ran* some of the tests and reported on results. With you in CC. Just checked back. So you did. I'm sorry, I made the mistake I accused you of. But... why then did you say I hadn't provided him with individual functions, when you've seen the repo yourself? I don't understand. You knew they're there. What I mean is that I don't just run random code from some random github repository. Sure, but surely that's not an issue when the SQL functions are also seperately provided and clearly labelled in the repo? Do you feel there is a difference about the trustworthiness of isolated files containing an SQL function presented in a github repo, and SQL functions presented in an email? I am not sure I can agree with that idea, I think they are both just SQL functions. The difference is that one also offers you a bit more if you want to check/try it. I do not wish to antagonise you either, so please go and look at the repo before you write the next reply. Over and out. Seems there has been a misunderstanding here and I feel I'm still missing something in what you're saying. Sorry Andres. Let's just forget this. I don't think we disagree especially on this and I am not looking to make an enemy here. Also, thanks for running the benchmarks to get some numbers. Graeme. -- 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 2015-07-09 10:30:35 +, Graeme B. Bell wrote: Well, that requires reviewing the source code of the run script and such. No, of course it doesn't. It appears that you didn't look at the repo or read my previous mail before you wrote this. FFS, I *ran* some of the tests and reported on results. With you in CC. What I mean is that I don't just run random code from some random github repository. I do not wish to antagonise you either, so please go and look at the repo before you write the next reply. Over and out. -- 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 08 Jul 2015, at 13:20, Andres Freund and...@anarazel.de wrote: On 2015-07-08 11:13:04 +, Graeme B. Bell wrote: I'm guessing you are maybe pressed for time at the moment because I already clearly included this on the last email, as well as the links to the alternative benchmarks with the same problem I referred to on both of my last emails which are also trivial to drop into pgbench (cut/paste). You realize that you want something here, not Merlin, right? Hi Andreas, My email was saying it's not helpful for anyone on the list for him to keep asking me to give him X and me to keep sending it. Do you disagree with that idea? I tried to phrase my request politely, but perhaps I failed. If you have suggestions for better ways to say I already sent it, twice more politely in this situation, I'd welcome them off list. He asked me to disclose the function body I was testing. I did that, *and* also disclosed the entire approach to the benchmark too in a way that made it trivial for him or others to replicate the situation I'd found. I'm pretty sure you should not be discouraging this kind of thing in bug/performance reports. I get your point that when you're asking for other people to look at something with you, don't antagonise them. I didn't intend it as antagonising and Merlin hasn't mailed me anything to say he was antagonised. I'm quite sure he's capable of defending himself or communicating with me himself if he does feel antagonised by something. I hope we can end the discussion of that here? Merlin, if you were antagonised, sorry, I did not mean to antagonise you. I just wanted to just wanted make it clear that I'd sent you what you asked for, + more, and that I was surprised you hadn't noticed it. To clear up the issue I build a little test harness around your comment below. http://github.com/gbb/t; Well, that requires reviewing the source code of the run script and such. No, of course it doesn't. It appears that you didn't look at the repo or read my previous mail before you wrote this. I do not wish to antagonise you either, so please go and look at the repo before you write the next reply. http://github.com/gbb/t Just pick any function you like, there are 6 there, and 3 of them demonstrate 2 different problems, all of it is clearly documented. When you open up the repo, there are the tests https://github.com/gbb/t/tree/master/tests You don't need to review any code from the run script. The functions are there as isolated files and what they are intended to demonstrate is clearly described with text and graphics. I could see your point if I had mailed out some giant script with a bunch of SQL calls embedded in its guts, but that's the opposite of what I did here. Did you find it difficult to navigate the repo structure (2 folders, a few files)? If so please let me know off-list what was difficult and I will see if I can improve it. I think we shouldn't discuss this on two threads (-performance, -bugs), that makes it hard to follow. Given Tom's more detailed answer I think the -bugs thread already contains more pertinent information. I don't necessarily disagree with this idea, but... How many people concerned with performance are following the -bugs list? How much space is there for discussion of this on -bugs? Since only working solutions for this performance problem so far are all user-side rather than commiter-side, why would you want to restrict that information to a commiter-side list? It has developed this way because I noticed it as a performance issue first, then decided to report it as a potential bug. Perhaps it would be useful to keep the discussion separate as the -commiter side aspects (how to fix this at the server level) and -user side (what you can do to improve performance right now). I will defer to general opinion on this in my follow-up posts. Graeme. -- 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 07 Jul 2015, at 22:52, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Jul 7, 2015 at 3:33 PM, Graeme B. Bell graeme.b...@nibio.no wrote: Hi Merlin, Long story short - thanks for the reply, but you're not measuring anything about the parallelism of code running in a pl/pgsql environment here. You're just measuring whether postgres can parallelise entering that environment and get back out. Don't get me wrong - it's great that this scales well because it affects situations where you have lots of calls to trivial functions. However it's not the problem I'm talking about. I mean 'real' pl'pgsql functions. e.g. things that you might find in postgis or similar. Maybe so. But it will be a lot easier for me (and others on this) list if you submit a self contained test case that runs via pgbench. Hi Merlin, I'm guessing you are maybe pressed for time at the moment because I already clearly included this on the last email, as well as the links to the alternative benchmarks with the same problem I referred to on both of my last emails which are also trivial to drop into pgbench (cut/paste). e.g. did you see these parts of my previous email To clear up the issue I build a little test harness around your comment below. http://github.com/gbb/t; Just pick any function you like, there are 6 there, and 3 of them demonstrate 2 different problems, all of it is clearly documented. I haven't used perf with pgbench before, and I can't run any code today. If you're interested in this but short on time, maybe you can glance at the repo above and just add 'perf' at the appropriate point in the rbuild wrapper. Graeme. -- 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 2015-07-08 11:13:04 +, Graeme B. Bell wrote: I'm guessing you are maybe pressed for time at the moment because I already clearly included this on the last email, as well as the links to the alternative benchmarks with the same problem I referred to on both of my last emails which are also trivial to drop into pgbench (cut/paste). You realize that you want something here, not Merlin, right? e.g. did you see these parts of my previous email To clear up the issue I build a little test harness around your comment below. http://github.com/gbb/t; Well, that requires reviewing the source code of the run script and such. I think we shouldn't discuss this on two threads (-performance, -bugs), that makes it hard to follow. Given Tom's more detailed answer I think the -bugs thread already contains more pertinent information. -- 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?
Hi Merlin, Long story short - thanks for the reply, but you're not measuring anything about the parallelism of code running in a pl/pgsql environment here. You're just measuring whether postgres can parallelise entering that environment and get back out. Don't get me wrong - it's great that this scales well because it affects situations where you have lots of calls to trivial functions. However it's not the problem I'm talking about. I mean 'real' pl'pgsql functions. e.g. things that you might find in postgis or similar. If you re-read my previous email or look at par_psql (http://parpsql.com) and look at the benchmarks there you'll maybe see more about what I'm talking about. To clear up the issue I build a little test harness around your comment below. If anyone was wondering if it's par_psql itself that causes bad scaling in postgres. The answer is clearly no. :-) What I found this evening is that there are several problems here. I did some testing here using a machine with 16 physical cores and lots of memory/IO. - Using a table as a source of input rather than a fixed parameter e.g. 'select col1... ' vs. 'select 3'. Please note I am not talking about poor performance, I am talking about poor scaling of performance to multicore. There should be no reason for this when read-locks are being taken on the table, and no reason for this when it is combined with e.g. a bunch of pl/pgsql work in a function. However the impact of this problem is only seen above 8 cores where performance crashes. - Using pl/pgsql itself intensively (e.g. anything non-trivial) causes horrifically bad scaling above 2 cores on the systems I've tested and performance crashes very hard soon after. This matches what I've seen elsewhere in big projects and in par_psql's tests. Of course, it could be some wacky postgresql.conf setting (I doubt it here), so I'd be glad if others could give it a try. If you're bored, set the time to 5s and run, from testing I can tell you it shouldn't alter the results. The repo will be up in around 30 minutes time on http://github.com/gbb/t, and I'm going to submit it as a bug to the pg bugs list. Graeme. On 06 Jul 2015, at 18:40, Merlin Moncure mmonc...@gmail.com wrote: On Fri, Jul 3, 2015 at 9:48 AM, Graeme B. Bell graeme.b...@nibio.no 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
Re: [PERFORM] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
On Tue, Jul 7, 2015 at 3:33 PM, Graeme B. Bell graeme.b...@nibio.no wrote: Hi Merlin, Long story short - thanks for the reply, but you're not measuring anything about the parallelism of code running in a pl/pgsql environment here. You're just measuring whether postgres can parallelise entering that environment and get back out. Don't get me wrong - it's great that this scales well because it affects situations where you have lots of calls to trivial functions. However it's not the problem I'm talking about. I mean 'real' pl'pgsql functions. e.g. things that you might find in postgis or similar. Maybe so. But it will be a lot easier for me (and others on this) list if you submit a self contained test case that runs via pgbench. From there it's a simple matter of a perf top and other standard locking diagnostic tests and also rules out any suspicion of 3rd party issues. This will also get better feedback on -bugs. 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] 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 graeme.b...@nibio.no 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
[PERFORM] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
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? Graeme. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
