From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Chris Kim <christopherkim22@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Autovacuum/Analyze Doesn't seem to be running properly
Date: Tue, 03 Mar 2020 09:34:42 +0100
Message-ID: <aa2145cd2bb6033b2f1c900b90d2c13520144ec1.camel@cybertec.at> (raw)
In-Reply-To: <CAAEyfN6nQ338mf1m_HVedXrd+jRqS6=VtWgvvDb5PV_A+q0KAg@mail.gmail.com>
References: <CAAEyfN6nQ338mf1m_HVedXrd+jRqS6=VtWgvvDb5PV_A+q0KAg@mail.gmail.com>
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
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: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, christopherkim22@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Autovacuum/Analyze Doesn't seem to be running properly
In-Reply-To: <aa2145cd2bb6033b2f1c900b90d2c13520144ec1.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox