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

2014-02-25 11:29 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

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

Here is a update for 9.4

Regards

Pavel

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