pgBufferUsage.blk_{read|write}_time are zero although there are pgBufferUsage.local_blks_{read|written} - Mailing list pgsql-hackers

From Nazir Bilal Yavuz
Subject pgBufferUsage.blk_{read|write}_time are zero although there are pgBufferUsage.local_blks_{read|written}
Date
Msg-id CAN55FZ19Ss279mZuqGbuUNxka0iPbLgYuOQXqAKewrjNrp27VA@mail.gmail.com
Whole thread Raw
Responses Re: pgBufferUsage.blk_{read|write}_time are zero although there are pgBufferUsage.local_blks_{read|written}
List 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

pgsql-hackers by date:

Previous
From: vignesh C
Date:
Subject: Re: pg_upgrade and logical replication
Next
From: Alvaro Herrera
Date:
Subject: Re: psql: Add command to use extended query protocol