From: Justin Pryzby <pryzby@telsasoft.com>
To: Francesco De Angelis <franc.tozzus@gmail.com>
Cc: pgsql-performance@lists.postgresql.org, Michael Lewis <mlewis@entrata.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: Michel SALAIS <msalais@msym.fr>
Subject: Re: Fwd: different execution time for the same query (and same DB status)
Date: Wed, 10 Mar 2021 02:27:53 -0600
Message-ID: <20210310082753.GR2021@telsasoft.com> (raw)
In-Reply-To: <CAHWr2Cfhfx=9CUP1gKPY-9aw02UrijsE=YKD3FcmxfYZoSZTHA@mail.gmail.com> <CAHWr2CeC_Q+kOBY7O3HmX1qVRHch26yiDw6HeUZGGaBpANubVQ@mail.gmail.com>
On Sat, Mar 06, 2021 at 10:40:00PM +0100, Francesco De Angelis wrote:
> The problem is the following: the query can take between 20 seconds and 4
> minutes to complete. Most of times, when I run the query for the first time
> after the server initialisation, it takes 20 seconds; but if I re-run it
> again (without changing anything) right after the first execution, the
> probability to take more than 4 minutes is very high.
On Tue, Mar 09, 2021 at 11:58:05PM +0100, Francesco De Angelis wrote:
> With such a value, I noticed also the following phenomenon: in addition to
> variable execution times (as previusly stated, the range is between 20
> seconds and 4 minutes),
You said it takes between 20s and 4min (240s), but both the explain analyze
show ~1300s.
explain analyze can be slower than the query, due to timing overhead.
Is that what's happening here? You could try explain(analyze,timing off,buffers).
You should send a result for the "20sec" result, and one for the "4min" result,
to compare.
I assume the crash is a result of OOM - you could find the result in dmesg
output ("Out of memory: Killed process") or the postgres logfile will say
"terminated by signal 9: Killed". It's important to avoid setting work_mem so
high that the process is killed and has to go into recovery mode.
--
Justin
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: pryzby@telsasoft.com, franc.tozzus@gmail.com, mlewis@entrata.com, tgl@sss.pgh.pa.us, rjuju123@gmail.com, msalais@msym.fr
Subject: Re: Fwd: different execution time for the same query (and same DB status)
In-Reply-To: <20210310082753.GR2021@telsasoft.com>
* 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