public inbox for [email protected]
help / color / mirror / Atom feedVacuum Analyze
2+ messages / 2 participants
[nested] [flat]
* Vacuum Analyze
@ 2024-06-28 21:09 Michael Wallach <[email protected]>
2024-06-29 04:49 ` Re: Vacuum Analyze Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Michael Wallach @ 2024-06-28 21:09 UTC (permalink / raw)
To: [email protected]
Hi,
Having issue with DB where we see SQL get slow, not sure what is causing but queries that have been running sub second jump to 2-5 secs. Seems when we manually run VACUUM ANALYZE query perf immediately improves. To add to this we have a “job” to trigger VACUUM ANALYZE to run every day but despite this, some acton against DB results in performance regarding that is only fixed by again running VACUUM ANALYZE.
What I’m trying to understand is what should I be looking at as before/after in the tables to determine what exactly VACUUM ANALYZE might be affecting to identify cause, what is corrected in DB such that perf improves? The DB is being exercised by custom apps via API so not sure what is happening that is causing DB to quickly become non-performant?
Have run this query but not sure what in results might explain why after vacuum perf is better, bad before. Also, anything else in DB we could look at as before/after?
SELECT relname,
n_tup_upd as "updates",
n_tup_del as "deletes",
n_live_tup as "live_tuples",
n_dead_tup as "dead_tuples",
trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
to_char(last_vacuum, 'YYYY-MM-DD HH24:MI:SS') as vacuum_date,
to_char(last_analyze, 'YYYY-MM-DD HH24:MI:SS') as analyze_date,
to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date,
to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
FROM pg_stat_all_tables
ORDER BY last_autovacuum;
Mike W
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Vacuum Analyze
2024-06-28 21:09 Vacuum Analyze Michael Wallach <[email protected]>
@ 2024-06-29 04:49 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-06-29 04:49 UTC (permalink / raw)
To: Michael Wallach <[email protected]>; [email protected]
On Fri, 2024-06-28 at 17:09 -0400, Michael Wallach wrote:
> Having issue with DB where we see SQL get slow, not sure what is causing but
> queries that have been running sub second jump to 2-5 secs. Seems when we manually
> run VACUUM ANALYZE query perf immediately improves. To add to this we have a “job”
> to trigger VACUUM ANALYZE to run every day but despite this, some acton against DB
> results in performance regarding that is only fixed by again running VACUUM ANALYZE.
>
> What I’m trying to understand is what should I be looking at as before/after in the
> tables to determine what exactly VACUUM ANALYZE might be affecting to identify cause,
> what is corrected in DB such that perf improves? The DB is being exercised by custom
> apps via API so not sure what is happening that is causing DB to quickly become
> non-performant?
You'd have to investigate that more closely.
The key tool is EXPLAIN (ANALYZE, BUFFERS) SELECT ...
Do that when the query is fast and then again later when the query is slow.
The output tells you what exactly the database is doing to execute the query,
and it also tells you what parts are slow in the second case. Once you understand
the cause of the problem, you can usually address it in a much more precise fashion.
VACUUM (ANALYZE) on the whole database is a pretty big hammer. Perhaps all that
is necessary is to tune autovacuum to ANALYZE or VACUUM a certain table more often.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-06-29 04:49 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-28 21:09 Vacuum Analyze Michael Wallach <[email protected]>
2024-06-29 04:49 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox