Re: pg_stat_statements locking

From: Andrey Borodin <x4mmm(at)yandex-team(dot)ru>
To: Julien Rouhaud <rjuju123(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_stat_statements locking
Date: 2022-09-12 12:32:55
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> On 12 Sep 2022, at 13:40, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> I'm not a fan of that patch as it now silently ignores entries if the lwlock
> can't be acquired *immediately*, without any way to avoid that if your
> configuration and/or workload doesn't lead to this problem, or any way to know
> that entries were ignored.

Practically, workload of this configuration is uncommon. At least I could not find any reports of such locking.
But theoretically, all prerequisites of a disaster is very common (variety of queries + some QPS of pg_stat_statements view + small work_mem + occasional reset() or GC).

Maybe it's only a problem of programs that use pgss. pgwatch is calling pgss on every DB in the cluster, that's how check once in a minute became some queries per second.

Personally, I'd prefer if I could configure a timeout to aquire lock. That timeout would denote maximum delay that pgss can incur on the query. But we would need to extend LWLock API for this.

> On 12 Sep 2022, at 13:40, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> I think that the better long term approach is to move pg_stat_statements and
> the query texts to dynamic shared memory.

BTW we don't even need a dynamic memory. We need just a shared memory, probably pre-allocated.
I agree that pgss must reside in main memory only, never on disk.

But we still will have a possibility of long lock conflicts preventing queries from completing. And the ability to configure pgss hooks timeout would be useful anyway.

Best regards, Andrey Borodin.

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2022-09-12 13:09:24 Re: proposal: possibility to read dumped table's name from file
Previous Message Bharath Rupireddy 2022-09-12 12:28:08 Re: Assertion failure in WaitForWALToBecomeAvailable state machine