Horton

Re: Track the amount of time waiting due to cost_delay

Masahiro Ikeda <ikedamsh@oss.nttdata.com>

View thread
From: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, "Imseih (AWS), Sami" <simseih@amazon.com>, Masahiko Sawada <sawada.mshk@gmail.com>, pgsql-hackers@lists.postgresql.org, Robert Haas <robertmhaas@gmail.com>
Date: 2024-12-05T08:51:11Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add delay time to VACUUM/ANALYZE (VERBOSE) and autovacuum logs.

  2. Add cost-based vacuum delay time to progress views.

  3. Add is_analyze parameter to vacuum_delay_point().

  4. Refresh cost-based delay params more frequently in autovacuum

Hi,

I recently encountered a case where having this feature would have been 
very helpful.
Thank you for developing it! I have a few questions and comments.

Here are questions:

After this patch is merged, are you considering adding delayed_time
information to the logs output by log_autovacuum_min_duration?
In the case I experienced, it would have been great to easily understand
how much of the total execution time was spent on timed delays from the
already executed VACUUM logs.

Recently, this thread has not been active. Is the reason to wait for 
[1]?
[1] Vacuum statistics: https://commitfest.postgresql.org/50/5012/


Here are minor comments on the v7 patch:

+       Total amount of time spent in milliseconds waiting due to 
<varname>vacuum_cost_delay</varname>
+       or <varname>autovacuum_vacuum_cost_delay</varname>. In case of 
parallel

Why not use the <xref> element, for example, <xref 
linkend="guc-autovacuum-vacuum-cost-delay"/>,
as in the max_dead_tuple_bytes column?

+       vacuum the reported time is across all the workers and the 
leader. This
+       column is updated at a 1 Hz frequency (one time per second) so 
could show
+       slightly old values.

I wonder if "Hz frequency" is the best term for the context, as I 
couldn’t
find similar usage in other documents, though I’m not a native English 
speaker.
FWIW, the document contains a similar description.
* not more frequently than once per PGSTAT_MIN_INTERVAL milliseconds

IIUC, only the worker updates the column at a 1 Hz frequency. Would it 
be
better to rephrase the following?"
* The workers update the column no more frequently than once per second,
   so it could show slightly old values.

+			if (INSTR_TIME_GET_MILLISEC(time_since_last_report) > 
WORKER_REPORT_DELAY_INTERVAL)
+			{
+				pgstat_progress_parallel_incr_param(PROGRESS_VACUUM_TIME_DELAYED,
+													nap_time_since_last_report);
+				nap_time_since_last_report = 0;
+				last_report_time = delay_end;
+			}

IIUC, unsent delayed_time will disappear when the parallel workers exit
because they are not considered in parallel_vacuum_end(). I assume this
is intentional behavior, as it is an acceptable error for the use cases.
I didn't see any comments regarding this, so I wanted to confirm.


Regards,
-- 
Masahiro Ikeda
NTT DATA CORPORATION