pgBufferUsage.blk_{read|write}_time are zero although there are pgBufferUsage.local_blks_{read|written}

From: Nazir Bilal Yavuz <byavuz81(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pgBufferUsage.blk_{read|write}_time are zero although there are pgBufferUsage.local_blks_{read|written}
Date: 2023-09-15 09:46:56
Message-ID: CAN55FZ19Ss279mZuqGbuUNxka0iPbLgYuOQXqAKewrjNrp27VA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I found that pgBufferUsage.blk_{read|write}_time are zero although there
are pgBufferUsage.local_blks_{read|written}. For example, when you run
(track_io_timing should be on):

CREATE EXTENSION pg_stat_statements;
CREATE TEMP TABLE example_table (id serial PRIMARY KEY, data text);
INSERT INTO example_table (data) SELECT 'Some data'
FROM generate_series(1, 100000);
UPDATE example_table SET data = 'Updated data';
SELECT query, local_blks_read, local_blks_written,
blk_read_time, blk_write_time FROM pg_stat_statements
WHERE query like '%UPDATE%';

on master:

query | UPDATE example_table SET data = $1
local_blks_read | 467
local_blks_written | 2087
blk_read_time | 0
blk_write_time | 0

There are two reasons of that:

1- When local_blks_{read|written} are incremented,
pgstat_count_io_op_time() is called with IOOBJECT_TEMP_RELATION. But in
pgstat_count_io_op_time(), pgBufferUsage.blk_{read|write}_time increments
are called only when io_object is IOOBJECT_RELATION. The first patch
attached fixes that.

2- in ExtendBufferedRelLocal() and in ExtendBufferedRelShared(), extend
calls increment local_blks_written and shared_blks_written respectively.
But these extends are not counted while calculating the blk_write_time. If
there is no specific reason to not do that, I think these extends needs to
be counted in blk_write_time. The second patch attached does that.

Results after applying first patch:

query | UPDATE example_table SET data = $1
local_blks_read | 467
local_blks_written | 2087
blk_read_time | 0.30085
blk_write_time | 1.475123

Results after applying both patches:

query | UPDATE example_table SET data = $1
local_blks_read | 467
local_blks_written | 2087
blk_read_time | 0.329597
blk_write_time | 4.050305

Any kind of feedback would be appreciated.

Regards,
Nazir Bilal Yavuz
Microsoft

Attachment Content-Type Size
v1-0002-Include-IOOp-IOOP_EXTENDs-while-calculating-block.patch text/x-diff 949 bytes
v1-0001-Increase-pgBufferUsage.blk_-read-write-_time-when.patch text/x-diff 1.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2023-09-15 09:51:22 Re: psql: Add command to use extended query protocol
Previous Message vignesh C 2023-09-15 09:42:16 Re: pg_upgrade and logical replication