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
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>
Comments: In-reply-to bruno da silva <brunogiovs@gmail.com>
	message dated "Tue, 02 Aug 2022 13:02:41 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2659867.1659461133.1@sss.pgh.pa.us>
Date: Tue, 02 Aug 2022 13:25:33 -0400
Message-ID: <2659868.1659461133@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2659868.1659461133%40sss.pgh.pa.us>
Precedence: bulk

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