Dear Pavel,
Thanks for quick response.
Yes I am using explicit locks but only at row-level, not at table level, will this cause sequential execution?
select ...on primary key..... for update;
My objective is to execute this function in parallel for different PK entities, for same primary key value, sequential is fine.
Also I have noted that though the function execution after the code calls is taking more than 2-3 minutes but ,
when I have put in notice statements for start/end of function it has only taken a second in pg_log.
Code is definitely not the problem here, as earlier function logic was getting executed as multiple queries
from the code and were working fine for even higher load before I moved to function for the same. This problem started after moving to function only.
I doubt that query is the reason for slowness, and it seems to be more related to database load at times, because it happens 3-4 times out of every 4000 transactions.
pg_stat doesn't show any locks at the moment, but might not reflect the actual scenario as currently the transactions are working fine.
----- Original Message -----
From: "Pavel Stehule" <pavel.stehule@xxxxxxxxx>
To: "Vishal Gupta" <vishal.g@xxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Sent: Wednesday, December 16, 2009 1:04:48 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: Re: Parallel Function calls using multiple processes
2009/12/16 Vishal Gupta <vishal.g@xxxxxxxxxxxx>:
> Problem: Function call typically takes 2-3 millisecond but at times
> exceeding 2-3 Minutes.
> =====================================================
> From the DB logs it seems when multiple processes are trying to execute the
> function,
> execution takes sequentially rather than parallel, which means Nth thread
> will have to wait for (N-1)*ExecutionTime before getting its turn
it's depend - if there are some locks then yes.
but reason could be a slow query inside procedure too - look on
pg_stat_activity table, if there are processes waiting for lock.
see http://old.nabble.com/Query-is-slow-when-executing-in-procedure-td26490782.html
Regards
Pavel Stehule
>
> Is my observation correct? If yes then what is the solution for this? If not
> where/how to find the exact cause of the above problem?
> =====================================================
> DB Version: 8.2
> Function Details:
> --returns numeric, takes 10 parameters
> --select query to validate data
> --row level lock for select and validate
> --bare minimum business logic
> --update data
> --couple of inserts for transaction logs/account management
> --also note that few of the tables have audit triggers causing the row to be
> inserted in audit table with the action (only Update/Insert/Delete)
>
>
Thanks for quick response.
Yes I am using explicit locks but only at row-level, not at table level, will this cause sequential execution?
select ...on primary key..... for update;
My objective is to execute this function in parallel for different PK entities, for same primary key value, sequential is fine.
Also I have noted that though the function execution after the code calls is taking more than 2-3 minutes but ,
when I have put in notice statements for start/end of function it has only taken a second in pg_log.
Code is definitely not the problem here, as earlier function logic was getting executed as multiple queries
from the code and were working fine for even higher load before I moved to function for the same. This problem started after moving to function only.
I doubt that query is the reason for slowness, and it seems to be more related to database load at times, because it happens 3-4 times out of every 4000 transactions.
pg_stat doesn't show any locks at the moment, but might not reflect the actual scenario as currently the transactions are working fine.
Regards,
Vishal Gupta - 9910991635
Vishal Gupta - 9910991635
----- Original Message -----
From: "Pavel Stehule" <pavel.stehule@xxxxxxxxx>
To: "Vishal Gupta" <vishal.g@xxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Sent: Wednesday, December 16, 2009 1:04:48 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: Re: Parallel Function calls using multiple processes
2009/12/16 Vishal Gupta <vishal.g@xxxxxxxxxxxx>:
> Problem: Function call typically takes 2-3 millisecond but at times
> exceeding 2-3 Minutes.
> =====================================================
> From the DB logs it seems when multiple processes are trying to execute the
> function,
> execution takes sequentially rather than parallel, which means Nth thread
> will have to wait for (N-1)*ExecutionTime before getting its turn
it's depend - if there are some locks then yes.
but reason could be a slow query inside procedure too - look on
pg_stat_activity table, if there are processes waiting for lock.
see http://old.nabble.com/Query-is-slow-when-executing-in-procedure-td26490782.html
Regards
Pavel Stehule
>
> Is my observation correct? If yes then what is the solution for this? If not
> where/how to find the exact cause of the above problem?
> =====================================================
> DB Version: 8.2
> Function Details:
> --returns numeric, takes 10 parameters
> --select query to validate data
> --row level lock for select and validate
> --bare minimum business logic
> --update data
> --couple of inserts for transaction logs/account management
> --also note that few of the tables have audit triggers causing the row to be
> inserted in audit table with the action (only Update/Insert/Delete)
>
>
