Calculating average block write time

Thomas Kellerer schrieb am 04.11.2022 um 10:19:

I can easily calculate the average block read time using
pg_stat_database and divide blk_read_time by blks_read.

While there is a column blk_write_time, it seems that there is no
cummulative measure for the total number of blocks written.

Any ideas on how to get the equivalent of (blk_read_time / blks_read) for blk_write_time?

I thought about using tup_updated + tup_inserted + tup_deleted because each tuple change would require at least one block written.
However, a single block write could update multiple tuples so the result of that wouldn't be accurate (too low).

Thanks
Thomas

On Fri, 2022-11-18 at 11:11 +0100, Thomas Kellerer wrote:

Thomas Kellerer schrieb am 04.11.2022 um 10:19:

I can easily calculate the average block read time using
pg_stat_database and divide blk_read_time by blks_read.

While there is a column blk_write_time, it seems that there is no
cummulative measure for the total number of blocks written.

Any ideas on how to get the equivalent of (blk_read_time / blks_read) for blk_write_time?

I thought about using tup_updated + tup_inserted + tup_deleted because each tuple change would require at least one block written.
However, a single block write could update multiple tuples so the result of that wouldn't be accurate (too low).

You cannot get that, because most writes are done by the checkpointer, and
that does not distinguish between databases.

Yours,
Laurenz Albe
--
+43-670-6056265
CYBERTEC PostgreSQL International GmbH
Römerstraße 19, 2752 Wöllersdorf
Web: https://www.cybertec-postgresql.com

On 11/18/22 09:51, Laurenz Albe wrote:

On Fri, 2022-11-18 at 11:11 +0100, Thomas Kellerer wrote:

Thomas Kellerer schrieb am 04.11.2022 um 10:19:

I can easily calculate the average block read time using
pg_stat_database and divide blk_read_time by blks_read.

While there is a column blk_write_time, it seems that there is no
cummulative measure for the total number of blocks written.

Any ideas on how to get the equivalent of (blk_read_time / blks_read) for blk_write_time?

I thought about using tup_updated + tup_inserted + tup_deleted because each tuple change would require at least one block written.
However, a single block write could update multiple tuples so the result of that wouldn't be accurate (too low).

You cannot get that, because most writes are done by the checkpointer, and
that does not distinguish between databases.

For a similar reason to why WAL files can't be be "single database" without
significant rewrite?

--
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.com> writes:

On 11/18/22 09:51, Laurenz Albe wrote:

On Fri, 2022-11-18 at 11:11 +0100, Thomas Kellerer wrote:

Any ideas on how to get the equivalent of (blk_read_time / blks_read) for blk_write_time?

You cannot get that, because most writes are done by the checkpointer, and
that does not distinguish between databases.

For a similar reason to why WAL files can't be be "single database" without
significant rewrite?

Right, because we define a checkpoint as cluster-wide.

Another problem with trying to measure this is that we can only know
how long it took to shove the block out to kernel space. It's highly
unlikely that the physical write has happened by the time write()
returns, and we don't know (nor care that much) exactly when it does
happen. So it's not clear that the measurement would have much
real-world significance if we did calculate it.

You could argue that read() is also squishy since it might be satisfied
from kernel buffers rather than actually getting bits off the disk.
However, there is some physical reality there: before read() we did
not have the data, and afterwards we do.

regards, tom lane

Laurenz Albe schrieb am 18.11.2022 um 16:51:

I can easily calculate the average block read time using
pg_stat_database and divide blk_read_time by blks_read.

While there is a column blk_write_time, it seems that there is no
cummulative measure for the total number of blocks written.

Any ideas on how to get the equivalent of (blk_read_time / blks_read) for blk_write_time?

I thought about using tup_updated + tup_inserted + tup_deleted because each tuple change would require at least one block written.
However, a single block write could update multiple tuples so the result of that wouldn't be accurate (too low).

You cannot get that, because most writes are done by the checkpointer, and
that does not distinguish between databases.

Ah, OK. Makes sense.

Thanks