Re: Autovacuum/Analyze Doesn't seem to be running properly

In my testing it looks like pg_stat_reset was ran, but I haven't been able
to verify this from my logs.

Is there another way the stats could get reset?

I also found that I need to look at the column last_autoanalyze, not
last_analyze.

Does autovacuum use the stats from pg_stat_user_table meaning if there 50
live tuples and 1000 dead tuples, but then the stats are reset, those dead
tuples would never get cleaned up until there was new activity on that
table?

Thank you,
Chris

On Tue, Mar 3, 2020, 1:34 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Mon, 2020-03-02 at 19:57 -0700, Chris Kim wrote:
> > I have a cluster with these parameters (no changed settings for any of
> the database/tables):
> >
> > autovacuum | on
>
>
> > autovacuum_analyze_scale_factor | 0.05
>
>
> > autovacuum_analyze_threshold | 25
>
>
> > autovacuum_freeze_max_age | 200000000
>
>
> > autovacuum_max_workers | 6
>
>
> > autovacuum_multixact_freeze_max_age | 400000000
>
>
> > autovacuum_naptime | 15s
>
>
> > autovacuum_vacuum_cost_delay | 20ms
>
>
> > autovacuum_vacuum_cost_limit | -1
>
>
> > autovacuum_vacuum_scale_factor | 0.1
> > autovacuum_vacuum_threshold | 25
>
>
> > autovacuum_work_mem | -1
> >
> > I have about 20 database with thousands of tuples (live and dead) but
> when I look at the pg_stat_user_tables within each database:
> > SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum,
> last_analyze
> > FROM pg_stat_user_tables;
> >
> > all n_live_tups are 0 and all dead tups are 0, with null data for
> last_vacuum, and last_analyze which seems very odd to me.
> >
> > I can see there's live rows with select count and then I analyze a
> database, then I see live and dead tuples, and last_analyze shows the
> timestamp of when I just ran ANALYZE;
> >
> > Am I missing something here or is this abnormal behaviour?
> >
> > I'm also seeing my databases are above 90% towards hitting my
> autovacuum_max_freeze-age using this command.
> > SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
> > FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname.
> > FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings
> WHERE name = 'autovacuum_freeze_max_age') AS foo
> > ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;
> >
> > I also have looked for vacuum via ps -ef | grep vacuum but didn't get
> any return.
> >
> > And select * from pg_stat_progress_vacuum shows nothing as well.
> >
> > I shouldn't have to create cron jobs to run analyze and vacuum manually
> for each database, but I don't know how to further investigate this issue.
> >
> > Has anyone seen this before? Any advise would be greatly appreciated!
>
> Some ideas:
>
> - Did somebody run
>
> SELECT pg_stat_reset();
>
> That would reset the statistics and explain that you see nothing in
> "pg_stat_user_tables".
>
> - After you ANALYZE the database, do you see something in
> "pg_stat_user_tables"?
> You should see something then.
>
> - Another explaination for the missing statistics could be a problem with
> the
> statistics collector process. Do you see and pertinent errors in the
> database log?
>
> If you restart the database, do you get errors from the statistics
> collector?
> Does a restart fix the problem?
>
> - Are the "track_activities" and "track_counts" parameters set to on?
>
> - Hitting 90% of autovacuum_freeze_max_age is no problem.
> It only means that you'll get some anti-wraparound autovacuums some time
> soon.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>