public inbox for [email protected]
help / color / mirror / Atom feedStored Procedure Performance
13+ messages / 5 participants
[nested] [flat]
* Stored Procedure Performance
@ 2017-10-03 14:33 Purav Chovatia <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Purav Chovatia @ 2017-10-03 14:33 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-03 14:54 Laurenz Albe <[email protected]>
parent: Purav Chovatia <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Laurenz Albe @ 2017-10-03 14:54 UTC (permalink / raw)
To: Purav Chovatia <[email protected]>; pgsql-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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-03 15:17 Adam Brusselback <[email protected]>
parent: Laurenz Albe <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Adam Brusselback @ 2017-10-03 15:17 UTC (permalink / raw)
To: Purav Chovatia <[email protected]>; +Cc: pgsql-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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-03 15:28 Pavel Stehule <[email protected]>
parent: Adam Brusselback <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Pavel Stehule @ 2017-10-03 15:28 UTC (permalink / raw)
To: Adam Brusselback <[email protected]>; +Cc: Purav Chovatia <[email protected]>; pgsql-performance
2017-10-03 17:17 GMT+02:00 Adam Brusselback <[email protected]>:
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 13:59 Purav Chovatia <[email protected]>
parent: Laurenz Albe <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Purav Chovatia @ 2017-10-11 13:59 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-performance
Thanks Laurenz, am having a look at perf.
Can you pls help understand what exactly do you mean when you say "PL/pgSQL
is not optimized for performance like PL/SQL". Do you mean to indicate that
app firing queries/DMLs directly would be a better option as compared to
putting those in Stored Procs?
Regards
On 3 October 2017 at 20:24, Laurenz Albe <[email protected]> wrote:
> 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 14:11 Purav Chovatia <[email protected]>
parent: Pavel Stehule <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Purav Chovatia @ 2017-10-11 14:11 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: Adam Brusselback <[email protected]>; pgsql-performance
Thanks.
We looked at pg_stat_statements and we see execution count & total time
taken. But that still does not help me to identify why is it slow or what
is taking time or where is the wait.
btw, does pg_stat_statements add considerable overhead? Coming from the
Oracle world, we are very used to such execution stats, and hence we are
planning to add this extension as a default to all our production
deployments.
Its a single row select using PK, single row update using PK and a single
row insert, so I dont see anything wrong with the code. So auto_explain
would not add any value, I believe.
Basically, on an Oracle server, I would minimally look at statspack/awr
report & OS stats (like cpu, iostat & memory) to start with. What should I
look for in case of a Postgres server.
Thanks & Regards
On 3 October 2017 at 20:58, Pavel Stehule <[email protected]> wrote:
>
>
> 2017-10-03 17:17 GMT+02:00 Adam Brusselback <[email protected]>:
>
>> There is also the option of pg_stat_statements: https://ww
>> w.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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 14:20 Pavel Stehule <[email protected]>
parent: Purav Chovatia <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Pavel Stehule @ 2017-10-11 14:20 UTC (permalink / raw)
To: Purav Chovatia <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-performance
2017-10-11 15:59 GMT+02:00 Purav Chovatia <[email protected]>:
> Thanks Laurenz, am having a look at perf.
>
> Can you pls help understand what exactly do you mean when you say "PL/pgSQL
> is not optimized for performance like PL/SQL". Do you mean to indicate that
> app firing queries/DMLs directly would be a better option as compared to
> putting those in Stored Procs?
>
PL/pgSQL is perfect glue for SQL. SQL queries has same speed without
dependency on environment that executed it.
This sentence mean, so PLpgSQL is not designed for intensive mathematics
calculation. PL/SQL is self govering environment ... it has own data
types, it has own implementation of logical and mathematics operators.
PLpgSQL is layer over SQL engine - and has not own types, has not own
operators. Any expression is translated to SQL and then is interpreted by
SQL expression interpret. Maybe in next few years there will be a JIT
compiler. But it is not now. This is current bottleneck of PLpgSQL. If your
PL code is glue for SQL queries (implementation of some business
processes), then PLpgSQL is fast enough. If you try to calculate numeric
integration or derivation of some functions, then PLpgSQL is slow. It is
not too slow - the speed is comparable with PHP, but it is significantly
slower than C language.
PostgreSQL has perfect C API - so intensive numeric calculations are
usually implemented as C extension.
Regards
Pavel
>
> Regards
>
> On 3 October 2017 at 20:24, Laurenz Albe <[email protected]> wrote:
>
>> 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
>>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:05 Purav Chovatia <[email protected]>
parent: Pavel Stehule <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Purav Chovatia @ 2017-10-11 16:05 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-performance
Thanks Pavel. Our SPs are not doing any mathematical calculations. Its
mostly if-else, so I would expect good performance.
On 11 October 2017 at 19:50, Pavel Stehule <[email protected]> wrote:
>
>
> 2017-10-11 15:59 GMT+02:00 Purav Chovatia <[email protected]>:
>
>> Thanks Laurenz, am having a look at perf.
>>
>> Can you pls help understand what exactly do you mean when you say "PL/pgSQL
>> is not optimized for performance like PL/SQL". Do you mean to indicate that
>> app firing queries/DMLs directly would be a better option as compared to
>> putting those in Stored Procs?
>>
>
> PL/pgSQL is perfect glue for SQL. SQL queries has same speed without
> dependency on environment that executed it.
>
> This sentence mean, so PLpgSQL is not designed for intensive mathematics
> calculation. PL/SQL is self govering environment ... it has own data
> types, it has own implementation of logical and mathematics operators.
> PLpgSQL is layer over SQL engine - and has not own types, has not own
> operators. Any expression is translated to SQL and then is interpreted by
> SQL expression interpret. Maybe in next few years there will be a JIT
> compiler. But it is not now. This is current bottleneck of PLpgSQL. If your
> PL code is glue for SQL queries (implementation of some business
> processes), then PLpgSQL is fast enough. If you try to calculate numeric
> integration or derivation of some functions, then PLpgSQL is slow. It is
> not too slow - the speed is comparable with PHP, but it is significantly
> slower than C language.
>
> PostgreSQL has perfect C API - so intensive numeric calculations are
> usually implemented as C extension.
>
> Regards
>
> Pavel
>
>
>>
>> Regards
>>
>> On 3 October 2017 at 20:24, Laurenz Albe <[email protected]>
>> wrote:
>>
>>> 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
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:37 Adam Brusselback <[email protected]>
parent: Purav Chovatia <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Adam Brusselback @ 2017-10-11 16:37 UTC (permalink / raw)
To: Purav Chovatia <[email protected]>; +Cc: Pavel Stehule <[email protected]>; Laurenz Albe <[email protected]>; pgsql-performance
Is there any error handling in there? I remember seeing performance
issues if you put in any code to catch exceptions.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:52 Purav Chovatia <[email protected]>
parent: Adam Brusselback <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Purav Chovatia @ 2017-10-11 16:52 UTC (permalink / raw)
To: Adam Brusselback <[email protected]>; +Cc: Pavel Stehule <[email protected]>; Laurenz Albe <[email protected]>; pgsql-performance
Yes, there is some code to catch exceptions like unique constraint
violation and no data found. Do you suggest we trying by commenting that
part? btw, the dataset is a controlled one, so what I can confirm is we are
not hitting any exceptions.
Thanks
On 11 October 2017 at 22:07, Adam Brusselback <[email protected]>
wrote:
> Is there any error handling in there? I remember seeing performance
> issues if you put in any code to catch exceptions.
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:54 Adam Brusselback <[email protected]>
parent: Purav Chovatia <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Adam Brusselback @ 2017-10-11 16:54 UTC (permalink / raw)
To: Purav Chovatia <[email protected]>; +Cc: Pavel Stehule <[email protected]>; Laurenz Albe <[email protected]>; pgsql-performance
> Yes, there is some code to catch exceptions like unique constraint violation and no data found. Do you suggest we trying by commenting that part?
That is likely it. Comment that out and test.
If you still need to handle a unique violation, see if you can instead
use the ON CONFLICT clause on the INSERT.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 19:06 Pavel Stehule <[email protected]>
parent: Purav Chovatia <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Pavel Stehule @ 2017-10-11 19:06 UTC (permalink / raw)
To: Purav Chovatia <[email protected]>; +Cc: Adam Brusselback <[email protected]>; Laurenz Albe <[email protected]>; pgsql-performance
2017-10-11 18:52 GMT+02:00 Purav Chovatia <[email protected]>:
> Yes, there is some code to catch exceptions like unique constraint
> violation and no data found. Do you suggest we trying by commenting that
> part? btw, the dataset is a controlled one, so what I can confirm is we are
> not hitting any exceptions.
>
If it is possible, don't do it in cycle, or use exception handling only
when it is necessary, not from pleasure.
Regards
Pavel
> Thanks
>
> On 11 October 2017 at 22:07, Adam Brusselback <[email protected]>
> wrote:
>
>> Is there any error handling in there? I remember seeing performance
>> issues if you put in any code to catch exceptions.
>>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-14 10:21 phb07 <[email protected]>
parent: Purav Chovatia <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: phb07 @ 2017-10-14 10:21 UTC (permalink / raw)
To: Purav Chovatia <[email protected]>; Pavel Stehule <[email protected]>; +Cc: Adam Brusselback <[email protected]>; pgsql-performance
Le 11/10/2017 à 16:11, Purav Chovatia a écrit :
> Thanks.
>
> We looked at pg_stat_statements and we see execution count & total
> time taken. But that still does not help me to identify why is it slow
> or what is taking time or where is the wait.
>
> btw, does pg_stat_statements add considerable overhead? Coming from
> the Oracle world, we are very used to such execution stats, and hence
> we are planning to add this extension as a default to all our
> production deployments.
>
> Its a single row select using PK, single row update using PK and a
> single row insert, so I dont see anything wrong with the code. So
> auto_explain would not add any value, I believe.
>
> Basically, on an Oracle server, I would minimally look at
> statspack/awr report & OS stats (like cpu, iostat & memory) to start
> with. What should I look for in case of a Postgres server.
You could have a look at the PoWA extension
(http://dalibo.github.io/powa/). It has the same purpose as AWR.
>
> Thanks & Regards
>
> On 3 October 2017 at 20:58, Pavel Stehule <[email protected]
> <mailto:[email protected]>> wrote:
>
>
>
> 2017-10-03 17:17 GMT+02:00 Adam Brusselback
> <[email protected] <mailto:[email protected]>>:
>
> There is also the option of pg_stat_statements:
> https://www.postgresql.org/docs/current/static/pgstatstatements.html
> <https://www.postgresql.org/docs/current/static/pgstatstatements.html;
> and auto_explain:
> https://www.postgresql.org/docs/current/static/auto-explain.html
> <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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2017-10-14 10:21 UTC | newest]
Thread overview: 13+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-03 14:33 Stored Procedure Performance Purav Chovatia <[email protected]>
2017-10-03 14:54 ` Laurenz Albe <[email protected]>
2017-10-03 15:17 ` Adam Brusselback <[email protected]>
2017-10-03 15:28 ` Pavel Stehule <[email protected]>
2017-10-11 14:11 ` Purav Chovatia <[email protected]>
2017-10-14 10:21 ` phb07 <[email protected]>
2017-10-11 13:59 ` Purav Chovatia <[email protected]>
2017-10-11 14:20 ` Pavel Stehule <[email protected]>
2017-10-11 16:05 ` Purav Chovatia <[email protected]>
2017-10-11 16:37 ` Adam Brusselback <[email protected]>
2017-10-11 16:52 ` Purav Chovatia <[email protected]>
2017-10-11 16:54 ` Adam Brusselback <[email protected]>
2017-10-11 19:06 ` Pavel Stehule <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox