Re: often PREPARE can generate high load (and sometimes minutes long unavailability)

Hello

2014-02-24 21:31 GMT+01:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> On Mon, Feb 24, 2014 at 7:02 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:
>
>>
>>
>>
>> 2014-02-23 21:32 GMT+01:00 Andres Freund <andres(at)2ndquadrant(dot)com>:
>>
>> Hi,
>>>
>>> On 2014-02-23 20:04:39 +0100, Pavel Stehule wrote:
>>> > There is relative few very long ProcArrayLocks lwlocks
>>> >
>>> > This issue is very pathologic on fast computers with more than 8 CPU.
>>> This
>>> > issue was detected after migration from 8.4 to 9.2. (but tested with
>>> same
>>> > result on 9.0) I see it on devel 9.4 today actualized.
>>> >
>>> > When I moved PREPARE from cycle, then described issues is gone. But
>>> when I
>>> > use a EXECUTE IMMEDIATELY, then the issue is back. So it looks it is
>>> > related to planner, ...
>>>
>>> In addition to the issue Jeff mentioned, I'd suggest trying the same
>>> workload with repeatable read. That can do *wonders* because of the
>>> reduced number of snapshots.
>>>
>>>
>> I tested it, and it doesn't help.
>>
>> Is there some patch, that I can test related to this issue?
>>
>
> This is the one that I was referring to:
>
> http://www.postgresql.org/message-id/11927.1384199294@sss.pgh.pa.us
>

I tested this patch with great success. Waiting on ProcArrayLocks are off.
Throughput is expected.

For described use case it is seriously interesting.

Regards

Pavel

light weight locks
lockname mode count avg
DynamicLocks Exclusive 8055 5003
DynamicLocks Shared 1666 50
LockMgrLocks Exclusive 129 36
IndividualLock Exclusive 34 48
IndividualLock Shared 21 7
BufFreelistLock Exclusive 12 8
WALWriteLock Exclusive 1 38194
ProcArrayLock Shared 1 8

> Cheers,
>
> Jeff
>
>
>