pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum behavior
8+ messages / 3 participants
[nested] [flat]
* Autovacuum behavior
@ 2015-07-30 18:49 John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: John Scalia @ 2015-07-30 18:49 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Autovacuum behavior
@ 2015-07-30 18:59 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2015-07-30 18:59 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Autovacuum behavior
@ 2015-07-30 19:04 John Scalia <jayknowsunix@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: John Scalia @ 2015-07-30 19:04 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Autovacuum behavior
@ 2015-07-30 19:07 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2015-07-30 19:07 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Autovacuum behavior
@ 2015-07-30 19:11 John Scalia <jayknowsunix@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: John Scalia @ 2015-07-30 19:11 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Autovacuum behavior
@ 2015-07-30 19:13 John Scalia <jayknowsunix@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: John Scalia @ 2015-07-30 19:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin
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
>>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Autovacuum behavior
@ 2015-07-31 04:44 Om Prakash Jaiswal <op12om@yahoo.co.in>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Om Prakash Jaiswal @ 2015-07-31 04:44 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
track_count = on;It is missing.
RegardsOm 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 = 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 = 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Autovacuum behavior
@ 2015-07-31 12:40 John Scalia <jayknowsunix@gmail.com>
parent: Om Prakash Jaiswal <op12om@yahoo.co.in>
0 siblings, 0 replies; 8+ messages in thread
From: John Scalia @ 2015-07-31 12:40 UTC (permalink / raw)
To: Om Prakash Jaiswal <op12om@yahoo.co.in>; +Cc: pgsql-admin
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.
>
> 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
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2015-07-31 12:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-30 18:49 Autovacuum behavior John Scalia <jayknowsunix@gmail.com>
2015-07-30 18:59 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-07-30 19:04 ` John Scalia <jayknowsunix@gmail.com>
2015-07-30 19:07 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-07-30 19:11 ` John Scalia <jayknowsunix@gmail.com>
2015-07-30 19:13 ` John Scalia <jayknowsunix@gmail.com>
2015-07-31 04:44 ` Om Prakash Jaiswal <op12om@yahoo.co.in>
2015-07-31 12:40 ` John Scalia <jayknowsunix@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