Hi, On Mon, Jun 10, 2024 at 08:12:46PM +0000, Imseih (AWS), Sami wrote: > >> This sounds like useful information to me. > > > Thanks for looking at it! > > The VacuumDelay is the only visibility available to > gauge the cost_delay. Having this information > advertised by pg_stat_progress_vacuum as is being proposed > is much better.
Thanks for looking at it! > However, I also think that the > "number of times" the vacuum went into delay will be needed > as well. Both values will be useful to tune cost_delay and cost_limit. Yeah, I think that's a good idea. With v1 one could figure out how many times the delay has been triggered but that does not work anymore if: 1) cost_delay changed during the vacuum duration or 2) the patch changes the way time_delayed is measured/reported (means get the actual wait time and not the theoritical time as v1 does). > > It may also make sense to accumulate the total_time in delay > and the number of times delayed in a cumulative statistics [0] > view to allow a user to trend this information overtime. > I don't think this info fits in any of the existing views, i.e. > pg_stat_database, so maybe a new view for cumulative > vacuum stats may be needed. This is likely a separate > discussion, but calling it out here. +1 > >> IIUC you'd need to get information from both pg_stat_progress_vacuum and > >> pg_stat_activity in order to know what percentage of time was being spent > >> in cost delay. Is that how you'd expect for this to be used in practice? > > > Yeah, one could use a query such as: > > > select p.*, now() - a.xact_start as duration from pg_stat_progress_vacuum p > > JOIN pg_stat_activity a using (pid) > > Maybe all progress views should just expose the > "beentry->st_activity_start_timestamp " > to let the user know when the current operation began. Yeah maybe, I think this is likely a separate discussion too, thoughts? Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
