pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum/Analyze Doesn't seem to be running properly
5+ messages / 3 participants
[nested] [flat]
* Autovacuum/Analyze Doesn't seem to be running properly
@ 2020-03-03 02:57 Chris Kim <christopherkim22@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Kim @ 2020-03-03 02:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
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!
Thanks in advance for the help,
Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Autovacuum/Analyze Doesn't seem to be running properly
@ 2020-03-03 08:34 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Chris Kim <christopherkim22@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2020-03-03 08:34 UTC (permalink / raw)
To: Chris Kim <christopherkim22@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Autovacuum/Analyze Doesn't seem to be running properly
@ 2020-03-04 20:43 Chris Kim <christopherkim22@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Kim @ 2020-03-04 20:43 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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.albe@cybertec.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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Autovacuum/Analyze Doesn't seem to be running properly
@ 2020-03-04 20:52 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Chris Kim <christopherkim22@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2020-03-04 20:52 UTC (permalink / raw)
To: Chris Kim <christopherkim22@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
On 2020-Mar-04, Chris Kim wrote:
> 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?
Yes, a crash/restart does that.
> 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?
That's correct.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Autovacuum/Analyze Doesn't seem to be running properly
@ 2020-03-10 17:38 Chris Kim <christopherkim22@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: Chris Kim @ 2020-03-10 17:38 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
Thanks anyone for your help. This has helped me better understand
autoanalyze.
Thanks,
Chris
On Wed, Mar 4, 2020, 1:53 PM Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:
> On 2020-Mar-04, Chris Kim wrote:
>
> > 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?
>
> Yes, a crash/restart does that.
>
> > 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?
>
> That's correct.
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-03-10 17:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-03 02:57 Autovacuum/Analyze Doesn't seem to be running properly Chris Kim <christopherkim22@gmail.com>
2020-03-03 08:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-03-04 20:43 ` Chris Kim <christopherkim22@gmail.com>
2020-03-04 20:52 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-03-10 17:38 ` Chris Kim <christopherkim22@gmail.com>
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