Re: BUG #18141: sorry, too many clients error occurring very frequently

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