Re: pb with big volumes

[Marc Millas]

Hi David,

that's exactly  my question.
does the analyze buffers data, generated when track_io_timing is on, keep
track of multiple reloads of the same data while executing one operation ?

I ll do the test asap and report the results.

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Fri, Aug 11, 2023 at 6:41 AM David Rowley  wrote:

> On Fri, 11 Aug 2023 at 13:54, Ron  wrote:
> > Wouldn't IO contention make for additive timings instead of exponential?
>
> No, not necessarily. Imagine one query running that's doing a
> parameterised nested loop join resulting in the index on the inner
> side being descended several, say, million times.  Let's say there's
> *just* enough RAM/shared buffers so that the index pages, once the
> index is scanned the first time, all the required pages are cached
> which results in no I/O on subsequent index scans.  Now, imagine
> another similar query but with another index, let's say this index
> also *just* fits in cache.  Now, when these two queries run
> concurrently, they each evict buffers the other one uses.  Of course,
> the shared buffers code is written in such a way as to try and evict
> lesser used buffers first, but if they're all used about the same
> amount, then this can stuff occur.  The slowdown isn't linear.
>
> I've no idea if this is happening for the reported case. I'm just
> saying that it can happen. The OP should really post the results of:
> SET track_io_timing = ON; EXPLAIN (ANALYZE, BUFFERS) for both queries
> running independently then again when they run concurrently.
>
> David
> David
>
>
>

Re: pb with big volumes

[David Rowley]

On Mon, 14 Aug 2023 at 11:14, Marc Millas  wrote:
> that's exactly  my question.
> does the analyze buffers data, generated when track_io_timing is on, keep 
> track of multiple reloads of the same data while executing one operation ?

Yes, the timing for reads will include the time it took to fetch any
buffer that wasn't found in shared buffers.  Some of those may come
quickly from the kernel's page cache, some might come from disk. If
some other running query has evicted a buffer that the query has
previously used, then that's going to cause another pread, which will
be timed by track_io_timing and added to the count of buffers read in
the "BUFFERS" EXPLAIN output.

So, the BUFFERs EXPLAIN option showing similar amounts of reads
between the query running without the concurrent query and with the
concurrent query does not necessarily mean more buffers had to be
loaded from disk, just that fewer were found in shared buffers.  The
amount of time doing I/O as shown by track_io_timing is going to be
more interesting as that's really the only indication from within
PostgreSQL that you have to get an idea of if the buffers are coming
from the kernel's cache or from disk.  You'll probably want to
calculate the average time it took to get 1 buffer for each query to
make sense of that.

David