In the last post in this little series we’ve looked at the “extends” statistic in pg_stat_io. In this post we’ll look at the “eviction” statistic. Before we go into the details: What is an eviction? Imagine the buffer cache is currently full and PostgreSQL needs to load another block from disk (or the OS cache). For this to work a block which is currently cached in the buffer cache must be evicted (kicked out) to make room for the block to be loaded. And this is what the statistic is about: Tracking the number of evictions. This statistic can help you in identifying if the sizing of your buffer cache (shared_buffers) is either too small or too large.

The default size of the buffer cache on Linux is 128MB, and this is what we currently have:

postgres=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

Using the pg_buffercache extension we can check how many blocks are currently used right after the instance was started:

postgres=# select count(*) from pg_buffercache where relfilenode is not null;
 count 
-------
   287
(1 row)

This means that 287*8192 (again the block size of the instance) is currently used, which is a bit less than 2.3MB, 2351104 bytes.

This also means that we should not see any evictions in the pg_stat_io view:

postgres=# select backend_type,evictions 
             from pg_stat_io 
            where backend_type = 'client backend'
              and context = 'normal'
              and object ='relation';
  backend_type  | evictions 
----------------+-----------
 client backend |         0
(1 row)

To see any eviction we need to load around 126MB of data, so let’s do this:

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 pg_size_pretty(pg_relation_size('t'));
 pg_size_pretty 
----------------
 128 MB
(1 row)

This should be enough data to already see some evictions:

postgres=# select backend_type,evictions 
             from pg_stat_io 
            where backend_type = 'client backend'
              and context = 'normal'
              and object ='relation';
  backend_type  | evictions 
----------------+-----------
 client backend |       288
(1 row)

Let’s have a look how much currently is cached for our table:

postgres=# select c.relname, count(*) AS buffers
  from pg_class c
 inner join pg_buffercache b
       on b.relfilenode=c.relfilenode
 inner join pg_database d
       on (b.reldatabase=d.oid AND d.datname=current_database())
 where c.relname = 't'
 group by c.relname;

 relname | buffers 
---------+---------
 t       |   16237
(1 row)

This are (16237*8)/1024, around 127 MB of data. As the table is 128MB big and around 2MB was already used in the buffer cache some evictions needed to happen. 288 evictions means a bit more than 2MB so this seems to be consistent.

If we create a copy of this table like follows and then again have a look at the evictions but this time for all contexts we’ll see something like this:

postgres=# create table t2 as select * from t;
SELECT 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 |        32 | bulkread
 client backend |      2048 | bulkwrite
 client backend |       323 | normal
 client backend |         0 | vacuum

bulkread and bulkwrite means the blocks have been taken out of shared buffers and instead have been placed in a ring buffer for use of bulk I/O operations. The same is true for vacuum operations:

postgres=# update t set d = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
UPDATE 1000000
postgres=# vacuum t;
VACUUM
postgres=# select backend_type,evictions,context 
             from pg_stat_io 
            where backend_type = 'client backend'
              and object ='relation';
  backend_type  | evictions |  context  
----------------+-----------+-----------
 client backend |     16102 | bulkread
 client backend |      2048 | bulkwrite
 client backend |     18611 | normal
 client backend |        32 | vacuum

To summarize: When you see many evictions in context “normal” this might give you the hint that you buffer cache is too small. Seeing almost no evictions means probably your buffer cache is oversized.

Post Views: 854
Thumbnail [60x60]
by
Daniel Westermann