Vacuum timing in pg_stat_all_tables
In light of bb8dff9995f (add cost delay time to progress views), looking at
the output of 30a6ed0ce4b (track per-relation time spent on vacuum and
analyze), it struck me as a bit unclear of what the time is really showing.
Do we want to do something similar for the table views? Or if not, we
should probably at least document the effect of cost based vacuum delay on
those timings - as in if they are including it or not (which I do believe
they are).
While more stats are always nice :), I think just being clear about it in
the docs would perhaps be enough for now? Maybe just appending something
along the line of "(including cost based delaying)"?
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
On Tue, Mar 04, 2025 at 03:12:18PM +0100, Magnus Hagander wrote:
In light of bb8dff9995f (add cost delay time to progress views), looking at
the output of 30a6ed0ce4b (track per-relation time spent on vacuum and
analyze), it struck me as a bit unclear of what the time is really showing.Do we want to do something similar for the table views? Or if not, we
should probably at least document the effect of cost based vacuum delay on
those timings - as in if they are including it or not (which I do believe
they are).
I could see it being useful to have the total cost delay time in those
views. The information in the progress views goes away when vacuuming is
done, while the table views would retain it indefinitely. That being said,
I haven't judged the feasibility of adding it. I'm sure it can be done,
but I don't know whether it requires reworking commit bb8dff9995f.
While more stats are always nice :), I think just being clear about it in
the docs would perhaps be enough for now? Maybe just appending something
along the line of "(including cost based delaying)"?
I think this is also a reasonable thing to do regardless of whether we add
the cost delay time to the table views.
--
nathan
Hi,
On Tue, Mar 04, 2025 at 08:54:13AM -0600, Nathan Bossart wrote:
On Tue, Mar 04, 2025 at 03:12:18PM +0100, Magnus Hagander wrote:
In light of bb8dff9995f (add cost delay time to progress views), looking at
the output of 30a6ed0ce4b (track per-relation time spent on vacuum and
analyze), it struck me as a bit unclear of what the time is really showing.Do we want to do something similar for the table views? Or if not, we
should probably at least document the effect of cost based vacuum delay on
those timings - as in if they are including it or not (which I do believe
they are).I could see it being useful to have the total cost delay time in those
views. The information in the progress views goes away when vacuuming is
done, while the table views would retain it indefinitely.
+1, I think that could be useful to "retain" this information on a per-table
basis.
While more stats are always nice :), I think just being clear about it in
the docs would perhaps be enough for now? Maybe just appending something
along the line of "(including cost based delaying)"?
Like "more stats are always nice" I think that "more explanations in the doc" are
always nice, so I don't see any reason why not to add this extra explanation.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Hi,
On Tue, Mar 04, 2025 at 03:24:26PM +0000, Bertrand Drouvot wrote:
Like "more stats are always nice" I think that "more explanations in the doc" are
always nice, so I don't see any reason why not to add this extra explanation.
Attached an attempt to do so.
Note that it does not add extra explanation to "cost-based delay". If we feel the
need we could add a link to "<xref linkend="runtime-config-resource-vacuum-cost"/>"
like it has been done for delay_time in bb8dff9995f.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
v1-0001-Add-more-details-for-pg_stat_all_tables.patchtext/x-diff; charset=us-asciiDownload
From 8c333bcb97541b1238602ae84ea85d4792a5577f Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Date: Wed, 12 Mar 2025 10:36:13 +0000
Subject: [PATCH v1] Add more details for pg_stat_all_tables
30a6ed0ce4b added the [auto]vacuum_time and [auto]analyze _time fields to
pg_stat_all_tables and bb8dff9995f added cost-based vacuum delay time to
progress views. This commit highlights the fact that the fields added in 30a6ed0ce4b
include the time spent sleeping due to cost-based delay.
Suggested-by: Magnus Hagander <magnus@hagander.net>
---
doc/src/sgml/monitoring.sgml | 12 ++++++++----
1 file changed, 8 insertions(+), 4 deletions(-)
100.0% doc/src/sgml/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index aaa6586d3a4..a37ae4d2d1d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4047,7 +4047,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<structfield>total_vacuum_time</structfield> <type>double precision</type>
</para>
<para>
- Total time this table has been manually vacuumed, in milliseconds
+ Total time this table has been manually vacuumed, in milliseconds. (This
+ includes the time spent sleeping due to cost-based delay.)
</para></entry>
</row>
@@ -4057,7 +4058,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para>
<para>
Total time this table has been vacuumed by the autovacuum daemon,
- in milliseconds
+ in milliseconds. (This includes the time spent sleeping due to cost-based
+ delay.)
</para></entry>
</row>
@@ -4066,7 +4068,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<structfield>total_analyze_time</structfield> <type>double precision</type>
</para>
<para>
- Total time this table has been manually analyzed, in milliseconds
+ Total time this table has been manually analyzed, in milliseconds. (This
+ includes the time spent sleeping due to cost-based delay.)
</para></entry>
</row>
@@ -4076,7 +4079,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para>
<para>
Total time this table has been analyzed by the autovacuum daemon,
- in milliseconds
+ in milliseconds. (This includes the time spent sleeping due to cost-based
+ delay.)
</para></entry>
</row>
</tbody>
--
2.34.1
On Wed, Mar 12, 2025 at 10:52:57AM +0000, Bertrand Drouvot wrote:
Note that it does not add extra explanation to "cost-based delay". If we feel the
need we could add a link to "<xref linkend="runtime-config-resource-vacuum-cost"/>"
like it has been done for delay_time in bb8dff9995f.
Sorry for the delay, this has been sitting in my inbox for some time,
and I am catching with some of my backlog.
<para>
- Total time this table has been manually vacuumed, in milliseconds
+ Total time this table has been manually vacuumed, in milliseconds. (This
+ includes the time spent sleeping due to cost-based delay.)
</para></entry>Hmm, okay, adding this information to these four new fields is fine
here, so I'll apply that on HEAD shortly. I can see that this matches
with the style used for some of the other fields, like n_tup_upd for the
details regarding HOT.
--
Michael