Re: [PERFORM] How does max_parallel_workers_per_gather change load averages?
On 4 October 2017 at 08:48, Ben Nachtrieb wrote: > I have 2 cores and my max_parallel_workers_per_gather = 2 and > max_worker_processes = 8, but my load averages are between 8 and 5 with > scheduled at 1/189 to 5/195. Are these so high because I increased > max_parallel_workers_per_gather? My understanding is that if my load > averages are greater than my number of cores the system is overloaded. > Should I think about it differently once I increase > max_parallel_workers_per_gather? How should I think about it? Parallel query is not 100% efficient. For example, adding twice the CPU, in theory, will never double the performance, there's always some overhead to this. It's really only useful to do on systems with spare CPU cycles to perform this extra work. You don't seem to have much to spare, so you may get along better if you disable parallel query. -- 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] select with max functions
On 03/10/17 04:29, Tom Lane wrote: Mariel Cherkassky writes: explain analyze SELECT Ma.User_Id, COUNT(*) COUNT FROM Manuim Ma WHERE Ma.Bb_Open_Date = (SELECT Bb_Open_Date FROM Manuim Man WHERE Man.User_Id = Ma.User_Id order by bb_open_date desc limit 1 ) GROUP BY Ma.User_Id HAVING COUNT(*) > 1; The core problem with this query is that the sub-select has to be done over again for each row of the outer table, since it's a correlated sub-select (ie, it refers to Ma.User_Id from the outer table). Replacing a max() call with handmade logic doesn't do anything to help that. I'd try refactoring it so that you calculate the max Bb_Open_Date just once for each user id, perhaps along the lines of SELECT Ma.User_Id, COUNT(*) COUNT FROM Manuim Ma, (SELECT User_Id, max(Bb_Open_Date) as max FROM Manuim Man GROUP BY User_Id) ss WHERE Ma.User_Id = ss.User_Id AND Ma.Bb_Open_Date = ss.max GROUP BY Ma.User_Id HAVING COUNT(*) > 1; This is still not going to be instantaneous, but it might be better. It's possible that an index on (User_Id, Bb_Open_Date) would help, but I'm not sure. regards, tom lane Further ideas based on Tom's rewrite: If that MAX is still expensive it might be worth breaking SELECT User_Id, max(Bb_Open_Date) as max FROM Manuim Man GROUP BY User_Id out into a VIEW, and considering making it MATERIALIZED, or creating an equivalent trigger based summary table (there are examples in the docs of how to do 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
[PERFORM] How does max_parallel_workers_per_gather change load averages?
Hello, This is my first question on this list. How does max_parallel_workers_per_gather change Linux server load averages? I have 2 cores and my max_parallel_workers_per_gather = 2 and max_worker_processes = 8, but my load averages are between 8 and 5 with scheduled at 1/189 to 5/195. Are these so high because I increased max_parallel_workers_per_gather? My understanding is that if my load averages are greater than my number of cores the system is overloaded. Should I think about it differently once I increase max_parallel_workers_per_gather? How should I think about it? I am using postgres 9.6.
Re: [PERFORM] Stored Procedure Performance
2017-10-03 17:17 GMT+02:00 Adam Brusselback : > There is also the option of pg_stat_statements: https:// > www.postgresql.org/docs/current/static/pgstatstatements.html and > auto_explain: https://www.postgresql.org/docs/current/ > static/auto-explain.html > > These should help you identify what is slowing things down. There is no > reason I could think of you should be seeing a 10x slowdown between > Postgres and Oracle, so you'll likely have to just profile it to find out. > depends what is inside. The max 10x slow down is possible if you are hit some unoptimized cases. The times about 1ms - 10ms shows so procedure (code) can be very sensitive to some impacts. Regards Pavel
Re: [PERFORM] Stored Procedure Performance
There is also the option of pg_stat_statements: https://www.postgresql.org/docs/current/static/pgstatstatements.html and auto_explain: https://www.postgresql.org/docs/current/static/auto-explain.html These should help you identify what is slowing things down. There is no reason I could think of you should be seeing a 10x slowdown between Postgres and Oracle, so you'll likely have to just profile it to find out.
Re: [PERFORM] Stored Procedure Performance
Purav Chovatia wrote: > I come from Oracle world and we are porting all our applications to > postgresql. > > The application calls 2 stored procs, > - first one does a few selects and then an insert > - second one does an update > > The main table on which the insert and the update happens is truncated before > every performance test. > > We are doing about 100 executions of both of these stored proc per second. > > In Oracle each exec takes about 1millisec whereas in postgres its taking > 10millisec and that eventually leads to a queue build up in our application. > > All indices are in place. The select, insert & update are all single row > operations and use the PK. > > It does not look like any query taking longer but something else. How can I > check where is the time being spent? There are no IO waits, so its all on the > CPU. You could profile the PostgreSQL server while it is executing the workload, see for example https://wiki.postgresql.org/wiki/Profiling_with_perf That way you could see where the time is spent. PL/pgSQL is not optimized for performance like PL/SQL. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Stored Procedure Performance
Hello, I come from Oracle world and we are porting all our applications to postgresql. The application calls 2 stored procs, - first one does a few selects and then an insert - second one does an update The main table on which the insert and the update happens is truncated before every performance test. We are doing about 100 executions of both of these stored proc per second. In Oracle each exec takes about 1millisec whereas in postgres its taking 10millisec and that eventually leads to a queue build up in our application. All indices are in place. The select, insert & update are all single row operations and use the PK. It does not look like any query taking longer but something else. How can I check where is the time being spent? There are no IO waits, so its all on the CPU. btw, postgres and oracle both are installed on the same server, so no differences in env. All suggestions welcome but I am more of looking at tools or any profilers that I can use to find out where is the time being spent because we believe most of our applications will run into similar issues. The version is 9.6 on RHEL 7.2. Many thanks in advance. Regards, Purav
