Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries

Hello Guys.

I'd like to report back on this issue as I've been monitoring on this
installation that has very large distinct sqls and I noticed something that
isn't probably new here but I'd like to confirm that again.

So after I reduced the pg_stat_statements.max from 10k to 3k
pgss_query_texts.stat was peaking at a reasonable size of ~450MB and by
monitoring the file size I was able to have a 1min window interval when the
pgss_query_texts.stat gc was happening. but whenever a gc was detected a
bunch of statements would get logged on the pg log as slow statements and
all would report taking around 1s some statements are like "BEGIN",
"COMMIT" then last week I asked for another reduction from 3k to 300
pg_stat_statements.max and those slow statement reports aren't happening
anymore even if pgss_query_texts.stat gc still occurs.

my question is: is it safe to assume that because the gc of
pgss_query_texts.stat requires a global lock this is a limitation of
pg_stat_statements current implementation?

Thanks

On Wed, Aug 3, 2022 at 11:17 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> bruno da silva <brunogiovs(at)gmail(dot)com> writes:
> > *Question: *Besides the gc issue that you mentioned, having a large (
> 700MB
> > or 1GB ) pgss_query_texts.stat could cause slowness in pg_stat_statement
> > processing
> > than leading to slower query responses with a 32bit PG? I'm thinking in
> > reducing pg_stat_statements.max from 10k to 3k
>
> Whether or not we've fully identified the problem, I think cutting
> pg_stat_statements.max is a good idea. Especially as long as you're
> stuck on an unsupported PG version.
>
> regards, tom lane
>

--
Bruno da Silva