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

Hello

2014-02-24 21:31 GMT+01:00 Jeff Janes <jeff.janes@gmail.com>:

On Mon, Feb 24, 2014 at 7:02 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2014-02-23 21:32 GMT+01:00 Andres Freund <andres@2ndquadrant.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