Postgres Pro
Downloads
Home   >   mailing lists

Re: often PREPARE can generate high load (and sometimes minutes long unavailability) - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: often PREPARE can generate high load (and sometimes minutes long unavailability)
Date
Msg-id CAFj8pRDHyAK_2JHSVKZ5YQNGQmFGVcJKcpBXhFaS=vSSCH-vNw@mail.gmail.com
Whole thread Raw
In response to Re: often PREPARE can generate high load (and sometimes minutes long unavailability)  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: often PREPARE can generate high load (and sometimes minutes long unavailability)  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers
Tree view
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:


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



pgsql-hackers by date:

Previous
From: Prakash Itnal
Date:
Subject: [ISSUE] pg_dump: schema with OID 0 does not exist
Next
From: Etsuro Fujita
Date:
Subject: Re: inherit support for foreign tables