From: | Chris Kim <christopherkim22(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Autovacuum/Analyze Doesn't seem to be running properly |
Date: | 2020-03-03 02:57:19 |
Message-ID: | CAAEyfN6nQ338mf1m_HVedXrd+jRqS6=VtWgvvDb5PV_A+q0KAg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-03-03 08:34:42 | Re: Autovacuum/Analyze Doesn't seem to be running properly |
Previous Message | Madeline Cummins | 2020-02-28 04:14:26 | Re: test |