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.s
bruno da silva 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 f
Hello Tom. Thanks for your response.
I spent most of the time looking for evidence and checking other
installations with similar patterns since your response.
this installation is in the habit of doing pg_stat_statements_reset() a lot?
* resetting is very rare. How can I get "pgss->mean_query_len"
I wrote:
> bruno da silva 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, multipl
bruno da silva 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. *
Hello.
Are you quite sure this is a 9.5.21 version of the pg_stat_statements
extension? *I got version 1.3 from SELECT * FROM pg_extension;*
Is it possible that the pg_stat_tmp directory has been made non-writable? *hard
to tell if it was made non-writable during the outage. but now it is
writable
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. *
Are there any other signs of distr
bruno da silva 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. D
Hello.
After more investigation, we found that pgss_query_texts.stat of a size of
2.2GB. and this deployment has a 32bit pg.
and this errors:
*postgresql-2022-07-12-20:07:15.log.gz:[2022-07-14 11:17:06.713 EDT]
207.89.58.230(46964) {62c87db0.8eb2} LOG: out of
memorypostgresql-2022-07-12-20
It has been running at least since 2018.
I got that from the tail
=> /sys/kernel/mm/ksm/run <==
0
==> /sys/kernel/mm/transparent_hugepage/defrag <==
[always] madvise never
==> /sys/kernel/mm/transparent_hugepage/enabled <==
always madvise [never]
==> /sys/kernel/mm/transparent_hugepage/khugepa
Thanks, I will check it out.
On Thu, Jul 21, 2022 at 4:21 PM Justin Pryzby wrote:
> On Thu, Jul 21, 2022 at 03:59:30PM -0400, bruno da silva wrote:
> > OS/version: CentOS release 6.9 (Final)
>
> How are these set ?
>
> tail /sys/kernel/mm/ksm/run
> /sys/kernel/mm/transparent_hugepage/{defrag,e
On Thu, Jul 21, 2022 at 03:59:30PM -0400, bruno da silva wrote:
> OS/version: CentOS release 6.9 (Final)
How are these set ?
tail /sys/kernel/mm/ksm/run
/sys/kernel/mm/transparent_hugepage/{defrag,enabled,khugepaged/defrag}
/proc/sys/vm/zone_reclaim_mode
I suspect you may be suffering from i
On 2022-07-21 Th 14:37, bruno da silva wrote:
> Hello.
>
> I'm investigating an issue on a PostgresSql 9.5.21 installation that
> becomes unusable in an intermittent way. Simple queries like "select
> now();" could take 20s. commits take 2s. and all gets fixed after an
> engine restart.
>
> I loo
The issue started a month ago.
On Thu, Jul 21, 2022 at 3:59 PM bruno da silva wrote:
> Thanks for the quick response.
>
> OS/version: CentOS release 6.9 (Final)
>
> Hardware(non dedicated to the db, other services and app run the same
> server):
>
> Xeon(R) CPU E5-2690 v4 @ 2.60GHz - 56
Thanks for the quick response.
OS/version: CentOS release 6.9 (Final)
Hardware(non dedicated to the db, other services and app run the same
server):
Xeon(R) CPU E5-2690 v4 @ 2.60GHz - 56 cores - 504 GB RAM
logicaldrive 1 (1.5 TB, RAID 1, OK)
physicaldrive 1I:3:1 (port 1I:box 3:bay 1, S
On Thu, Jul 21, 2022 at 02:37:35PM -0400, bruno da silva wrote:
> I'm investigating an issue on a PostgresSql 9.5.21 installation that
> becomes unusable in an intermittent way. Simple queries like "select
> now();" could take 20s. commits take 2s. and all gets fixed after an engine
> restart.
>
>
Hello.
I'm investigating an issue on a PostgresSql 9.5.21 installation that
becomes unusable in an intermittent way. Simple queries like "select
now();" could take 20s. commits take 2s. and all gets fixed after an engine
restart.
I look into the pg logs and no signs of errors. and checkpoints are
17 matches
Mail list logo
