Thread

1. Understanding pg_stat_io.evictions

   Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> — 2023-07-28T10:36:46Z

   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. In contexts bulkwrite, bulkread, and vacuum, this counts the number of times a block was evicted from shared buffers in order to 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
   
   
   
   

2. Re: Understanding pg_stat_io.evictions

   Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2023-07-31T01:32:25Z

   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
   
   
   
   

3. Re: Understanding pg_stat_io.evictions

   Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> — 2023-07-31T07:57:23Z

   >> 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