From: Tom Lane <tgl@sss.pgh.pa.us>
To: bruno da silva <brunogiovs@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
Date: Tue, 02 Aug 2022 13:25:33 -0400
Message-ID: <2659868.1659461133@sss.pgh.pa.us> (raw)
In-Reply-To: <CAB+Nuk9_xmFc0js-JCOiUqQJL978FA4udD4yr4b9ha=3bUj59g@mail.gmail.com>
References: <CAB+Nuk93fL1Q9eLOCotvLP07g7RAv4vbdrkm0cVQohDVMpAb9A@mail.gmail.com>
<CAB+Nuk8BT4nroR+AWvka=a9qQxLEfByDC0tNbhY5mG4oaCzwBw@mail.gmail.com>
<2519232.1659455961@sss.pgh.pa.us>
<CAB+Nuk9_xmFc0js-JCOiUqQJL978FA4udD4yr4b9ha=3bUj59g@mail.gmail.com>
bruno da silva <brunogiovs@gmail.com> writes:
> Do you have a lot of especially long statements being tracked
> in the pg_stat_statements view?* well, the view was showing the query
> column null.*
> * but looking on pgss_query_texts.stat there are very large sql
> statements, of around ~ 400kb, multiple thousands. *
Hm. We try to recover from such failures by (a) resetting all the view's
query text fields to null and (b) truncating the file --- well, unlinking
it and creating it as empty. It seems like (a) happened and (b) didn't.
It's pretty hard to explain that from the code though. Are you quite
sure this is a 9.5.21 version of the pg_stat_statements extension?
Is it possible that the pg_stat_tmp directory has been made non-writable?
regards, tom lane
> Are there any other signs of distress
> in the postmaster log, like complaints about being unable to write
> pgss_query_texts.stat? *no, just complaints for reading it. *
> Thanks
> On Tue, Aug 2, 2022 at 11:59 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> bruno da silva <brunogiovs@gmail.com> writes:
> After more investigation, we found that pgss_query_texts.stat of a size
>> of
> 2.2GB. and this deployment has a 32bit pg.
>>
>> Hm ... we've heard one previous report of pg_stat_statements' query text
>> file getting unreasonably large, but it's not clear how that can come
>> to be. Do you have a lot of especially long statements being tracked
>> in the pg_stat_statements view? Are there any other signs of distress
>> in the postmaster log, like complaints about being unable to write
>> pgss_query_texts.stat?
>>
>> regards, tom lane
>>
> --
> Bruno da Silva
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, brunogiovs@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
In-Reply-To: <2659868.1659461133@sss.pgh.pa.us>
* 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