Thread: Parallel Function calls using multiple processes
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
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)
=====================================================
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
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)
2009/12/16 Vishal Gupta <vishal.g@pinelabs.com>: > 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) > >
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@gmail.com>
To: "Vishal Gupta" <vishal.g@pinelabs.com>
Cc: pgsql-performance@postgresql.org
Sent: Wednesday, December 16, 2009 1:04:48 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: Re: [PERFORM] Parallel Function calls using multiple processes
2009/12/16 Vishal Gupta <vishal.g@pinelabs.com>:
> 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@gmail.com>
To: "Vishal Gupta" <vishal.g@pinelabs.com>
Cc: pgsql-performance@postgresql.org
Sent: Wednesday, December 16, 2009 1:04:48 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi
Subject: Re: [PERFORM] Parallel Function calls using multiple processes
2009/12/16 Vishal Gupta <vishal.g@pinelabs.com>:
> 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)
>
>
2009/12/16 Vishal Gupta <vishal.g@pinelabs.com>: > 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. it should be a different problem - in bgwriter configuration. There could help migration on 8.3, maybe. http://old.nabble.com/Checkpoint-tuning-on-8.2.4-td17685494.html Regards Pavel Stehule > > Regards, > Vishal Gupta - 9910991635 > > > ----- Original Message ----- > From: "Pavel Stehule" <pavel.stehule@gmail.com> > To: "Vishal Gupta" <vishal.g@pinelabs.com> > Cc: pgsql-performance@postgresql.org > Sent: Wednesday, December 16, 2009 1:04:48 PM GMT +05:30 Chennai, Kolkata, > Mumbai, New Delhi > Subject: Re: [PERFORM] Parallel Function calls using multiple processes > > 2009/12/16 Vishal Gupta <vishal.g@pinelabs.com>: >> 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) >> >> >