pg_stat_io, which comes with PostgreSQL version 16, is a view that shows statistics related to I/O operations in databases cumulatively and for the entire cluster, and can be quite useful when used and interpreted correctly. I/O operations are shown in the view grouped according to backend process types, for example, statistics related to operations of users connecting to the database are shown together as “backend_type”=”client backend”. Another column “context” indicates the content of the I/O operation, in other words its type, and can take the values of “normal”, “vacuum”, “bulkread”, “bulkwrite“. You can review detailed information about view columns here. In our article, we will examine the statistics resulting from simple operations in order to better understand the content offered by the pg_stat_io view.
We start our tests by creating a new cluster and querying the initial state of the view that will be filled cumulatively.
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
-------------------+----------+---------+-------+--------+------------+---------+----------+-------+-----------+--------+--------
autovacuum worker | relation | normal | 8 | 0 | 0 | 0 | 8192 | 19354 | 0 | | 0
client backend | relation | normal | 144 | 0 | 0 | 0 | 8192 | 2994 | 0 | | 0
(2 rows)Let’s create a two-column test table, insert 15 thousand random records into this table, and query the pg_stat_io view.
postgres=# create table io_test (c1 integer,c2 text);
postgres=# INSERT INTO io_test( c1, c2)
SELECT floor(random() * 999 + 1)::int,
left(md5(random()::text),3)
FROM generate_series(1,15000);
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
-------------------+----------+---------+-------+--------+------------+---------+----------+-------+-----------+--------+--------
autovacuum worker | relation | normal | 32 | 0 | 0 | 2 | 8192 | 25258 | 0 | | 0
client backend | relation | normal | 230 | 0 | 0 | 73 | 8192 | 19305 | 0 | | 0
(2 rows)Although we have written to the database with the insert operation, the “writes” value still appears as 0. The change that stands out to us is the change in the value in the “hits” column for the “client backend” type. At this point, it is useful to explain the “hits” column. This column is defined in the documentation as the number of times the requested block is found / accessed in shared buffers. In PostgreSQL, data inserted into a table is not written directly to the data file on disk, but to the shared buffer, that is, to RAM. Our insert operation also increased the “hits” value for the “client backend” type by writing to the empty areas on the shared buffer.
Now we re-query the view by manually triggering the checkpoint operation in the database.
postgres=# checkpoint;
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
-------------------+----------+---------+-------+--------+------------+---------+----------+-------+-----------+--------+--------
autovacuum worker | relation | normal | 32 | 0 | 0 | 2 | 8192 | 25589 | 0 | | 0
client backend | relation | normal | 230 | 0 | 0 | 73 | 8192 | 19358 | 0 | | 0
checkpointer | relation | normal | | 111 | 111 | | 8192 | | | | 32
(3 rows)In our query result, a record came up indicating that 111 write operations were performed by the Checkpointer process. Since each operation is 8192 bytes (the value of the op_bytes column), we see that approximately 880 KB of data was written. When we query the table size at this stage, we see that our table is approximately 600 KB. When we also take into account the internal operations, the values are consistent and we can say that the 15 thousand records of data held in shared buffers were transferred to the disk with the checkpoint operation. The conclusion we will draw is that the fact that the “write” number in the “client backend” type did not increase does not mean that users are not performing write operations in the database.
Now, let’s delete the data in our table and examine the result of the deletion process. However, we do not run this deletion process with a single delete statement, but by repeating the deletion of a random value 1000 times each time.
postgres=# do
$$
declare
i record;
begin
for i in 1..1000 loop
delete from io_test where c1=(SELECT floor(random() * 999 + 1)::int);
end loop;
end;
$$
;
postgres=# checkpoint;
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+--------
autovacuum worker | relation | normal | 32 | 0 | 0 | 2 | 8192 | 26638 | 0 | | 0
client backend | relation | normal | 277 | 0 | 0 | 73 | 8192 | 1273446 | 0 | | 0
checkpointer | relation | normal | | 111 | 111 | | 8192 | | | | 32
(3 rows)We see that the delete operation increases the “hits” value of the “client backend” type again, this time at a much higher rate. The reason for this high increase is that each delete statement reads the entire table without an index, and this data is in the shared buffer. Now let’s load a larger amount of data into the table.
postgres=# INSERT INTO io_test( c1, c2)
SELECT floor(random() * 999 + 1)::int,
left(md5(random()::text),3)
FROM generate_series(1,3000000);
postgres=# checkpoint;
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+--------
autovacuum worker | relation | normal | 32 | 0 | 0 | 4 | 8192 | 35316 | 0 | | 0
client backend | relation | normal | 277 | 0 | 0 | 7113 | 8192 | 2885411 | 0 | | 0
checkpointer | relation | normal | | 7166 | 7166 | | 8192 | | | | 46
(3 rows)We inserted 3 million rows of random data and then took a manual checkpoint. When we immediately query the pg_stat_io view, as expected, we see that in addition to the “hits” value in the “client backend” type and the “write” value coming from the checkpoint, one value has increased by a larger amount. This is the “extends” value in the “client backend” type. This value shows how many times the table extend, that is, the table’s on-disk space, has been increased, and in our test, we see that this operation has been performed more than 7000 times on a table that has reached 100MB with 3 million records.
This time, let’s update half of the data in the table.
postgres=# update io_test set c1=111 where c1<500;
UPDATE 1499783
postgres=# checkpoint;
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
-------------------+----------+---------+-------+--------+------------+---------+----------+----------+-----------+--------+--------
autovacuum worker | relation | normal | 32 | 0 | 0 | 4 | 8192 | 46692 | 0 | | 0
autovacuum worker | relation | vacuum | 22780 | 9670 | 0 | 0 | 8192 | 124412 | 32 | 22748 |
client backend | relation | normal | 279 | 0 | 0 | 14145 | 8192 | 10065347 | 0 | | 0
checkpointer | relation | normal | | 17708 | 17708 | | 8192 | | | | 50
After the update process is completed, the autovacuum process is activated and we see that a new statistic is “backend_type” = “autovacuum worker” and “context” = “vacuum”. We can say that this process reads and writes from the disk in addition to reading from shared buffers, thus the “dead rows” in our test table are cleaned and the freed space becomes usable again.
The operations we have done so far have not filled the shared buffers, and therefore the operations we have done have always been written to disk with a checkpoint operation. However, in PostgreSQL, the dirty buffer is written to disk by another process other than the checkpoint, this process is the “background writer”, and it steps in when necessary to ensure that there is always free space on the shared buffers and writes to disk. Now, we are re-inserting all the data in our table into our table, which will increase the use of shared buffers.
postgres=# insert into io_test select * from io_test ;
postgres=# select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs
from pg_stat_io WHERE reads <> 0 OR writes <> 0;
backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
-------------------+----------+---------+-------+--------+------------+---------+----------+----------+-----------+--------+--------
autovacuum worker | relation | normal | 32 | 0 | 0 | 4 | 8192 | 48536 | 0 | | 0
autovacuum worker | relation | vacuum | 22780 | 9670 | 0 | 0 | 8192 | 124412 | 32 | 22748 |
client backend | relation | normal | 279 | 0 | 0 | 14145 | 8192 | 10123567 | 0 | | 0
checkpointer | relation | normal | | 32109 | 32109 | | 8192 | | | | 50
background writer | relation | normal | | 6564 | 6528 | | 8192 | | | | 0
This operation created space pressure in the shared buffers usage and we saw a new statistic in the output of the pg_stat_io view as “backend_type” = “background writer”. We can also understand from the statistics that this backend process is writing to the disk again with the checkpoint.
With all these tests, we wanted to focus on some points that should be considered when examining the pg_stat_io view. You can review the documentation for the meanings of the other backend processes you see in the output of the view and the columns that we did not examine in our article, and you can contact us for your questions.
Bir yanıt yazın