From: Joe Conway <mail@joeconway.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: ravi.s.agrawal23@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18141: sorry, too many clients error occurring very frequently
Date: Sun, 1 Oct 2023 14:27:05 -0400
Message-ID: <ac88d4c9-692c-07ff-03de-fb3f524d5b0f@joeconway.com> (raw)
In-Reply-To: <1848411.1696183788@sss.pgh.pa.us>
References: <18141-198c521115e07b7c@postgresql.org>
<CAMkU=1zXTm-zAq2jRoscmqDmDGPjRy_18BhOn_-Jb0TuGbHHbg@mail.gmail.com>
<b3d50783-fc7d-0239-5343-6bfe7f654334@joeconway.com>
<1848411.1696183788@sss.pgh.pa.us>
On 10/1/23 14:09, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> Maybe, maybe not. I have seen two other cases that are similar. One was
>> an upgrade from 12.8 to 12.12 and the other an upgrade from 13.4 to 13.8.
>
>> I checked and 12.8 was stamped is the same date as 13.4, and 12.12 the
>> same day as 13.8.
>
>> In both cases queries against pg_stat_statements suddenly started taking
>> more memory leading to spillage to pg_temp and a step degradation in
>> overall performance. In at least one of those cases the
>> solution/workaround was to increase work_mem. In the other I think
>> pg_stat_statements was disabled.
>
>> Myself and at least one other hacker looked at the pg_stat_statements
>> specific changes in that time interval and saw no smoking gun.
>
>> But it is possible that something else backpatched to both branches
>> between Aug 09, 2021 and Aug 8, 2022 has caused a more general
>> performance regression which we have yet to track down.
>
> Hmm. My first instinct is to wonder about changes in plan selection.
> How complex were the troublesome queries?
I think both of these cases involved a number of common attributes:
* The queries against pg_stat_statements were
relatively complex
* The other queries on the system were relatively
long and complex (and thus the query string length
in pg_stat_statements)
* Prior to the upgrade the systems were overall
keeping up, but extremely busy
In one case it seems that the upgrade caused a significant increase of
temp file usage. This impacted the system enough that other active
queries took longer, and thus number of active connections increased.
Raising work_mem eliminated the temp file usage and cpu loads dropped
back to similar levels as they were prior to the minor upgrade.
The other case had different specifics, but generally involved increased
memory usage. In that one eliminating the use of pg_stat_statements
restored performance. They did not try raising work_mem (as I understand
it), and I did not get any info regarding temp file spillage there.
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: mail@joeconway.com, tgl@sss.pgh.pa.us, jeff.janes@gmail.com, ravi.s.agrawal23@gmail.com
Subject: Re: BUG #18141: sorry, too many clients error occurring very frequently
In-Reply-To: <ac88d4c9-692c-07ff-03de-fb3f524d5b0f@joeconway.com>
References: <18141-198c521115e07b7c@postgresql.org> <CAMkU=1zXTm-zAq2jRoscmqDmDGPjRy_18BhOn_-Jb0TuGbHHbg@mail.gmail.com> <b3d50783-fc7d-0239-5343-6bfe7f654334@joeconway.com> <1848411.1696183788@sss.pgh.pa.us> <ac88d4c9-692c-07ff-03de-fb3f524d5b0f@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox