public inbox for [email protected]  

help / color / mirror / Atom feed

From: Michael Wallach <[email protected]>
To: [email protected]
Subject: Vacuum Analyze
Date: Fri, 28 Jun 2024 17:09:57 -0400
Message-ID: <[email protected]> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Vacuum Analyze
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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