Обсуждение: Autovacuum behavior
Hi all,
The autovacuum settings for a 9.4.2 database are shown below, I'm not absolutely certain if I missed anything:
autovacuum = on
log_autovacuum_min_duration = 100
autovacuum_max_workers = 15
autovacuum_naptime = 10min
#autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 80
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.2
autovacuum_freeze_max_age = 100000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1
vacuum_freeze_min_age = 5000000
vacuum_freeze_table_age = 2500000
But, when I examine pg_stat_all_tables, I'm seeing a lot of tables where n_dead_tup is still a lot greater than n_live_tup. Mind you, these are all fairly small tables. I'm also seeing that the last_autovacuum ran about 11:22 AM CDT this morning.I would think the tables where there were no live tuples and a bunch of dead_tuples would have been vacuumed after 11:22 AM to clear the dead ones. Am I missing something?
--
Jay
John Scalia wrote: > Hi all, > > The autovacuum settings for a 9.4.2 database are shown below, I'm not > absolutely certain if I missed anything: > > autovacuum = on > log_autovacuum_min_duration = 100 > autovacuum_max_workers = 15 > autovacuum_naptime = 10min > #autovacuum_vacuum_threshold = 50 > autovacuum_analyze_threshold = 80 > autovacuum_vacuum_scale_factor = 0.1 > autovacuum_analyze_scale_factor = 0.2 > autovacuum_freeze_max_age = 100000000 > autovacuum_vacuum_cost_delay = 20ms > autovacuum_vacuum_cost_limit = -1 What's the vacuum_cost_limit setting? Maybe they are sleeping for too long, and don't have time to get to some of the other tables because all 15 workers are busy. This gets worse the more workers there are. I don't think the 10min naptime is doing you any favors, is it? If you want them to go faster, maybe you need to lower the cost_delay. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
autovacuum_vacuum_cost_limit is currently set at -1. Not really sure what it should be, as I still need to look that up.
On Thu, Jul 30, 2015 at 1:59 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
John Scalia wrote:
> Hi all,
>
> The autovacuum settings for a 9.4.2 database are shown below, I'm not
> absolutely certain if I missed anything:
>
> autovacuum = on
> log_autovacuum_min_duration = 100
> autovacuum_max_workers = 15
> autovacuum_naptime = 10min
> #autovacuum_vacuum_threshold = 50
> autovacuum_analyze_threshold = 80
> autovacuum_vacuum_scale_factor = 0.1
> autovacuum_analyze_scale_factor = 0.2
> autovacuum_freeze_max_age = 100000000
> autovacuum_vacuum_cost_delay = 20ms
> autovacuum_vacuum_cost_limit = -1
What's the vacuum_cost_limit setting? Maybe they are sleeping for too
long, and don't have time to get to some of the other tables because all
15 workers are busy. This gets worse the more workers there are.
I don't think the 10min naptime is doing you any favors, is it?
If you want them to go faster, maybe you need to lower the cost_delay.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
John Scalia wrote: > autovacuum_vacuum_cost_limit is currently set at -1. Not really sure what > it should be, as I still need to look that up. Yes, I saw that from your snippet, but that value means to use the value from vacuum_cost_limit. If that one is set to a positive value, it may lead to sleeps during vacuum. Also, tables could have values set in pg_class.reloptions, leading to sleeps. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Sure, I just replied too quickly as there was no vacuum_cost_limit, so I'm guessing the default of 200 is being used. I'll look in pg_class to see if anything is set.
On Thu, Jul 30, 2015 at 2:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
John Scalia wrote:
> autovacuum_vacuum_cost_limit is currently set at -1. Not really sure what
> it should be, as I still need to look that up.
Yes, I saw that from your snippet, but that value means to use the value
from vacuum_cost_limit. If that one is set to a positive value, it may
lead to sleeps during vacuum.
Also, tables could have values set in pg_class.reloptions, leading to
sleeps.
--
Álvaro Herrera http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
And there are no reloptions set for any tables in pg_class.
On Thu, Jul 30, 2015 at 2:11 PM, John Scalia <jayknowsunix@gmail.com> wrote:
Sure, I just replied too quickly as there was no vacuum_cost_limit, so I'm guessing the default of 200 is being used. I'll look in pg_class to see if anything is set.On Thu, Jul 30, 2015 at 2:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:John Scalia wrote:
> autovacuum_vacuum_cost_limit is currently set at -1. Not really sure what
> it should be, as I still need to look that up.
Yes, I saw that from your snippet, but that value means to use the value
from vacuum_cost_limit. If that one is set to a positive value, it may
lead to sleeps during vacuum.
Also, tables could have values set in pg_class.reloptions, leading to
sleeps.
--
Álvaro Herrera http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
track_count = on;
It is missing.
Regards
Om Prakash
On Friday, 31 July 2015 12:21 AM, John Scalia <jayknowsunix@gmail.com> wrote:
Hi all,
The autovacuum settings for a 9.4.2 database are shown below, I'm not absolutely certain if I missed anything:
autovacuum = on
log_autovacuum_min_duration = 100
autovacuum_max_workers = 15
autovacuum_naptime = 10min
#autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 80
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.2
autovacuum_freeze_max_age = 100000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1
vacuum_freeze_min_age = 5000000
vacuum_freeze_table_age = 2500000
But, when I examine pg_stat_all_tables, I'm seeing a lot of tables where n_dead_tup is still a lot greater than n_live_tup. Mind you, these are all fairly small tables. I'm also seeing that the last_autovacuum ran about 11:22 AM CDT this morning.I would think the tables where there were no live tuples and a bunch of dead_tuples would have been vacuumed after 11:22 AM to clear the dead ones. Am I missing something?
--
Jay
Thanks OM, I'll try setting that.
On Thu, Jul 30, 2015 at 11:44 PM, Om Prakash Jaiswal <op12om@yahoo.co.in> wrote:
track_count = on;It is missing.RegardsOm PrakashOn Friday, 31 July 2015 12:21 AM, John Scalia <jayknowsunix@gmail.com> wrote:Hi all,The autovacuum settings for a 9.4.2 database are shown below, I'm not absolutely certain if I missed anything:autovacuum = onlog_autovacuum_min_duration = 100autovacuum_max_workers = 15autovacuum_naptime = 10min#autovacuum_vacuum_threshold = 50autovacuum_analyze_threshold = 80autovacuum_vacuum_scale_factor = 0.1autovacuum_analyze_scale_factor = 0.2autovacuum_freeze_max_age = 100000000autovacuum_vacuum_cost_delay = 20msautovacuum_vacuum_cost_limit = -1vacuum_freeze_min_age = 5000000vacuum_freeze_table_age = 2500000But, when I examine pg_stat_all_tables, I'm seeing a lot of tables where n_dead_tup is still a lot greater than n_live_tup. Mind you, these are all fairly small tables. I'm also seeing that the last_autovacuum ran about 11:22 AM CDT this morning.I would think the tables where there were no live tuples and a bunch of dead_tuples would have been vacuumed after 11:22 AM to clear the dead ones. Am I missing something?--Jay