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 <t...@sss.pgh.pa.us> wrote: > bruno da silva <brunogi...@gmail.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
