2009/12/16 Vishal Gupta <vishal.g@xxxxxxxxxxxx>: > 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@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) >> >> > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
