Re: pb with big volumes - Mailing list pgsql-general

From David Rowley
Subject Re: pb with big volumes
Date
Msg-id CAApHDvr90RbUTbijJHOgE1DYptYo3murM0nnFH+oyE9ROr7u-w@mail.gmail.com
Whole thread Raw
In response to Re: pb with big volumes  (Marc Millas <marc.millas@mokadb.com>)
List pgsql-general
On Mon, 14 Aug 2023 at 11:14, Marc Millas <marc.millas@mokadb.com> 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
whileexecuting 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



pgsql-general by date:

Previous
From: Marc Millas
Date:
Subject: Re: pb with big volumes
Next
From: "Mario Diangelo"
Date:
Subject: Best strategy to perform individual incremental backups