Re: Parallel Function calls using multiple processes

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)
>>
>>
>