pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Calculating average block write time
6+ messages / 4 participants
[nested] [flat]

* Calculating average block write time
@ 2022-11-04 09:19 Thomas Kellerer <shammat@gmx.net>
  2022-11-18 10:11 ` Re: Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Thomas Kellerer @ 2022-11-04 09:19 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

Am I missing something, or is this simply not tracked (as of Postgres 15)?

Regards
Thomas







^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Calculating average block write time
  2022-11-04 09:19 Calculating average block write time Thomas Kellerer <shammat@gmx.net>
@ 2022-11-18 10:11 ` Thomas Kellerer <shammat@gmx.net>
  2022-11-18 15:51   ` Re: Calculating average block write time Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 1 reply; 6+ messages in thread

From: Thomas Kellerer @ 2022-11-18 10:11 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Calculating average block write time
  2022-11-04 09:19 Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  2022-11-18 10:11 ` Re: Calculating average block write time Thomas Kellerer <shammat@gmx.net>
@ 2022-11-18 15:51   ` Laurenz Albe <laurenz.albe@cybertec.at>
  2022-11-18 16:14     ` Re: Calculating average block write time Ron <ronljohnsonjr@gmail.com>
  2022-11-18 17:30     ` Re: Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  0 siblings, 2 replies; 6+ messages in thread

From: Laurenz Albe @ 2022-11-18 15:51 UTC (permalink / raw)
  To: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Calculating average block write time
  2022-11-04 09:19 Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  2022-11-18 10:11 ` Re: Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  2022-11-18 15:51   ` Re: Calculating average block write time Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-11-18 16:14     ` Ron <ronljohnsonjr@gmail.com>
  2022-11-18 16:24       ` Re: Calculating average block write time Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 6+ messages in thread

From: Ron @ 2022-11-18 16:14 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Calculating average block write time
  2022-11-04 09:19 Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  2022-11-18 10:11 ` Re: Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  2022-11-18 15:51   ` Re: Calculating average block write time Laurenz Albe <laurenz.albe@cybertec.at>
  2022-11-18 16:14     ` Re: Calculating average block write time Ron <ronljohnsonjr@gmail.com>
@ 2022-11-18 16:24       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2022-11-18 16:24 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Calculating average block write time
  2022-11-04 09:19 Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  2022-11-18 10:11 ` Re: Calculating average block write time Thomas Kellerer <shammat@gmx.net>
  2022-11-18 15:51   ` Re: Calculating average block write time Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-11-18 17:30     ` Thomas Kellerer <shammat@gmx.net>
  1 sibling, 0 replies; 6+ messages in thread

From: Thomas Kellerer @ 2022-11-18 17:30 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2022-11-18 17:30 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-04 09:19 Calculating average block write time Thomas Kellerer <shammat@gmx.net>
2022-11-18 10:11 ` Thomas Kellerer <shammat@gmx.net>
2022-11-18 15:51   ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-11-18 16:14     ` Ron <ronljohnsonjr@gmail.com>
2022-11-18 16:24       ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-18 17:30     ` Thomas Kellerer <shammat@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox