Thread: Understanding pg_stat_io.evictions

Understanding pg_stat_io.evictions

From
"Daniel Westermann (DWE)"
Date:
Hi,

I am trying to understand the evictions statistic in pg_stat_io. I know what evictions are, so this is not the
question.
Given this:

postgres=# select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# select pg_stat_reset_shared('io');
 pg_stat_reset_shared
----------------------

(1 row)

postgres=# create table t ( a int, b text, c text, d text );
CREATE TABLE

postgres=# insert into t select i,  md5(i::text), md5(i::text), md5(i::text) from generate_series(1,1000000) i;
INSERT 0 1000000

postgres=# select backend_type,evictions,context
             from pg_stat_io
            where backend_type = 'client backend'
              and object ='relation';
  backend_type  | evictions |  context
----------------+-----------+-----------
 client backend |         0 | bulkread
 client backend |         0 | bulkwrite
 client backend |       207 | normal
 client backend |         0 | vacuum

Shouldn't these evictions show up under context blkwrite? The description in docs is:

"Number of times a block has been written out from a shared or local buffer in order to make it available for another
use.

In context normal, this counts the number of times a block was evicted from a buffer and replaced with another block.
Incontexts bulkwrite, bulkread, and vacuum, this counts the number of times a block was evicted from shared buffers in
orderto add the shared buffer to a separate, size-limited ring buffer for use in a bulk I/O operation." 

As far as I understand this, a ring buffer is used in this case. Do I miss something?

Many thanks in advance
Daniel



Re: Understanding pg_stat_io.evictions

From
Kyotaro Horiguchi
Date:
At Fri, 28 Jul 2023 10:36:46 +0000, "Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> wrote in 
> postgres=# insert into t select i,  md5(i::text), md5(i::text), md5(i::text) from generate_series(1,1000000) i;
..
>  client backend |       207 | normal
>  client backend |         0 | vacuum
> 
> Shouldn't these evictions show up under context blkwrite? The description in docs is:

No, that's not the case because INSERT doesn't execute a bulk
write. It is is only performed during COPY FROM, and the write side of
some other comands, such as CREATE AS (and some internal table-copy
operations.).

> As far as I understand this, a ring buffer is used in this case. Do I miss something?

Maybe you're confusiong it with bulk-read cases?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



Re: Understanding pg_stat_io.evictions

From
"Daniel Westermann (DWE)"
Date:
>> postgres=# insert into t select i,  md5(i::text), md5(i::text), md5(i::text) from generate_series(1,1000000) i;
>..
>>  client backend |       207 | normal
>>  client backend |         0 | vacuum
>>
>> Shouldn't these evictions show up under context blkwrite? The description in docs is:

>No, that's not the case because INSERT doesn't execute a bulk
>write. It is is only performed during COPY FROM, and the write side of
>some other comands, such as CREATE AS (and some internal table-copy
>operations.).

Thanks, makes sense.

>> As far as I understand this, a ring buffer is used in this case. Do I miss something?

>Maybe you're confusiong it with bulk-read cases?

Yes, you're right.

Thank you
Daniel