pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum missing tables
9+ messages / 5 participants
[nested] [flat]
* Autovacuum missing tables
@ 2010-07-29 14:00 Bryan Payne <bpayne@speedfc.com>
0 siblings, 1 reply; 9+ messages in thread
From: Bryan Payne @ 2010-07-29 14:00 UTC (permalink / raw)
To: pgsql-admin
Autovacuum is sometimes skipping certain tables. The tables in question
are pretty busy. A vacuum analyze works fine when run manually. The
tables in question were successfully autovacuumed this morning, but that
is the first time since the 23rd where it worked. I'm not finding any
logging info regarding the autovacuum. I'm not sure if autovacuum is
seeing table locks or what, but is there a way to run a more aggressive
autovacuum?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Autovacuum missing tables
@ 2010-07-30 14:39 Bryan Payne <bpayne@speedfc.com>
parent: Bryan Payne <bpayne@speedfc.com>
0 siblings, 1 reply; 9+ messages in thread
From: Bryan Payne @ 2010-07-30 14:39 UTC (permalink / raw)
To: pgsql-admin
Autovacuum is sometimes skipping certain tables. The tables in question
are pretty busy. A vacuum analyze works fine when run manually. The
tables in question were successfully autovacuumed this morning, but that
is the first time since the 23rd where it worked. I'm not finding any
logging info regarding the autovacuum. I'm not sure if autovacuum is
seeing table locks or what, but is there a way to run a more aggressive
autovacuum?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovacuum missing tables
@ 2010-07-30 14:46 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Bryan Payne <bpayne@speedfc.com>
0 siblings, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2010-07-30 14:46 UTC (permalink / raw)
To: pgsql-admin; Bryan Payne <bpayne@speedfc.com>
Bryan Payne <bpayne@speedfc.com> wrote:
> Autovacuum is sometimes skipping certain tables. The tables in
> question are pretty busy. A vacuum analyze works fine when run
> manually. The tables in question were successfully autovacuumed
> this morning, but that is the first time since the 23rd where it
> worked. I'm not finding any logging info regarding the autovacuum.
> I'm not sure if autovacuum is seeing table locks or what, but is
> there a way to run a more aggressive autovacuum?
For starters, we need to know what version of PostgreSQL this is:
select version();
It wouldn't hurt to see the contents of your postgresql.conf (with
all comments removed) and to know something about the table, and how
it's used. One way to get that information would be:
VACUUM ANALYZE VERBOSE tablename;
-Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovacuum missing tables
@ 2010-07-30 15:10 Bryan Payne <bpayne@speedfc.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 3 replies; 9+ messages in thread
From: Bryan Payne @ 2010-07-30 15:10 UTC (permalink / raw)
To: pgsql-admin
Version:
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
(Ubuntu 4.2.4-1ubuntu3)
Postgresql.conf:
data_directory = '/var/lib/postgresql/8.3/main'
hba_file = '/etc/postgresql/8.3/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.3/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/8.3-main.pid'
listen_addresses = '*'
port = 5432
max_connections = 550
ssl = true
shared_buffers = 1GB
work_mem = 24MB
maintenance_work_mem = 256MB
max_fsm_pages = 1300000
archive_mode = off
enable_seqscan = off
log_destination = 'syslog'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_min_duration_statement = 2000
log_connections = true
log_line_prefix = '%t <%u%%%d> '
autovacuum = on
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
backslash_quote = off
Table info (this table shows last autovacuum on 7/28):
hostname=> VACUUM ANALYZE VERBOSE hierarchy_pull;
INFO: vacuuming "public.hierarchy_pull"
INFO: "hierarchy_pull": found 0 removable, 1906 nonremovable row
versions in 78 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2714 unused item pointers.
47 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_17600"
INFO: index "pg_toast_17600_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_17600": found 0 removable, 0 nonremovable row versions
in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.hierarchy_pull"
INFO: "hierarchy_pull": scanned 78 of 78 pages, containing 1906 live
rows and 0 dead rows; 1906 rows in sample, 1906 estimated total rows
VACUUM
On 07/30/2010 09:46 AM, Kevin Grittner wrote:
> Bryan Payne<bpayne@speedfc.com> wrote:
>
>
>> Autovacuum is sometimes skipping certain tables. The tables in
>> question are pretty busy. A vacuum analyze works fine when run
>> manually. The tables in question were successfully autovacuumed
>> this morning, but that is the first time since the 23rd where it
>> worked. I'm not finding any logging info regarding the autovacuum.
>> I'm not sure if autovacuum is seeing table locks or what, but is
>> there a way to run a more aggressive autovacuum?
>>
>
> For starters, we need to know what version of PostgreSQL this is:
>
> select version();
>
> It wouldn't hurt to see the contents of your postgresql.conf (with
> all comments removed) and to know something about the table, and how
> it's used. One way to get that information would be:
>
> VACUUM ANALYZE VERBOSE tablename;
>
> -Kevin
> .
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovacuum missing tables
@ 2010-07-30 15:36 Brad Nicholson <bnichols@ca.afilias.info>
parent: Bryan Payne <bpayne@speedfc.com>
2 siblings, 1 reply; 9+ messages in thread
From: Brad Nicholson @ 2010-07-30 15:36 UTC (permalink / raw)
To: Bryan Payne <bpayne@speedfc.com>; +Cc: pgsql-admin
On 10-07-30 11:10 AM, Bryan Payne wrote:
> Version:
> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
> (Ubuntu 4.2.4-1ubuntu3)
>
> Postgresql.conf:
> data_directory = '/var/lib/postgresql/8.3/main'
> hba_file = '/etc/postgresql/8.3/main/pg_hba.conf'
> ident_file = '/etc/postgresql/8.3/main/pg_ident.conf'
> external_pid_file = '/var/run/postgresql/8.3-main.pid'
> listen_addresses = '*'
> port = 5432
> max_connections = 550
> ssl = true
> shared_buffers = 1GB
> work_mem = 24MB
> maintenance_work_mem = 256MB
> max_fsm_pages = 1300000
> archive_mode = off
> enable_seqscan = off
> log_destination = 'syslog'
> log_directory = 'pg_log'
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
> log_rotation_age = 1d
> log_min_duration_statement = 2000
> log_connections = true
> log_line_prefix = '%t <%u%%%d> '
> autovacuum = on
> datestyle = 'iso, mdy'
> lc_messages = 'en_US.UTF-8'
> lc_monetary = 'en_US.UTF-8'
> lc_numeric = 'en_US.UTF-8'
> lc_time = 'en_US.UTF-8'
> default_text_search_config = 'pg_catalog.english'
> backslash_quote = off
>
> Table info (this table shows last autovacuum on 7/28):
> hostname=> VACUUM ANALYZE VERBOSE hierarchy_pull;
> INFO: vacuuming "public.hierarchy_pull"
> INFO: "hierarchy_pull": found 0 removable, 1906 nonremovable row
> versions in 78 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 2714 unused item pointers.
> 47 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: vacuuming "pg_toast.pg_toast_17600"
> INFO: index "pg_toast_17600_index" now contains 0 row versions in 1
> pages
> DETAIL: 0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "pg_toast_17600": found 0 removable, 0 nonremovable row
> versions in 0 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: analyzing "public.hierarchy_pull"
> INFO: "hierarchy_pull": scanned 78 of 78 pages, containing 1906 live
> rows and 0 dead rows; 1906 rows in sample, 1906 estimated total rows
> VACUUM
>
Autovacuum won't vacuum this table as it stands as there are no dead
rows. There is no work for vacuum to do, so it's just a waste of cycles
to run it. Of course, your manual vacuum may have cleared out the dead
tuples, but based on the fact that there are not any more in place, I
wonder about the activity on the table. When you say the tables are
busy - define what you mean by busy in terms of INSERT/UPDATE/DELETE?
The docs explain how autovacuum calculates when to vacuum tables to
vacuum here:
http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM
If the table is not being vacuumed frequently enough, you may need to
change your autovacuum settings.
> On 07/30/2010 09:46 AM, Kevin Grittner wrote:
>> Bryan Payne<bpayne@speedfc.com> wrote:
>>
>>> Autovacuum is sometimes skipping certain tables. The tables in
>>> question are pretty busy. A vacuum analyze works fine when run
>>> manually. The tables in question were successfully autovacuumed
>>> this morning, but that is the first time since the 23rd where it
>>> worked. I'm not finding any logging info regarding the autovacuum.
>>> I'm not sure if autovacuum is seeing table locks or what, but is
>>> there a way to run a more aggressive autovacuum?
>>
>> For starters, we need to know what version of PostgreSQL this is:
>>
>> select version();
>>
>> It wouldn't hurt to see the contents of your postgresql.conf (with
>> all comments removed) and to know something about the table, and how
>> it's used. One way to get that information would be:
>>
>> VACUUM ANALYZE VERBOSE tablename;
>>
>> -Kevin
>> .
>>
>
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovacuum missing tables
@ 2010-07-30 15:38 Benjamin Krajmalnik <kraj@servoyant.com>
parent: Bryan Payne <bpayne@speedfc.com>
2 siblings, 0 replies; 9+ messages in thread
From: Benjamin Krajmalnik @ 2010-07-30 15:38 UTC (permalink / raw)
To: Bryan Payne <bpayne@speedfc.com>; pgsql-admin
What may be happening is that it does not have enough time to run based
on your autovacuum settings.
Check your setting for autovacuum_vacuum_cost_limit. I believe default
is 200 - max is 10000 which is what I have mine set to due to very heavy
table utilization.
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of Bryan Payne
> Sent: Friday, July 30, 2010 9:11 AM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Autovacuum missing tables
>
> Version:
> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
> (Ubuntu 4.2.4-1ubuntu3)
>
> Postgresql.conf:
> data_directory = '/var/lib/postgresql/8.3/main'
> hba_file = '/etc/postgresql/8.3/main/pg_hba.conf'
> ident_file = '/etc/postgresql/8.3/main/pg_ident.conf'
> external_pid_file = '/var/run/postgresql/8.3-main.pid'
> listen_addresses = '*'
> port = 5432
> max_connections = 550
> ssl = true
> shared_buffers = 1GB
> work_mem = 24MB
> maintenance_work_mem = 256MB
> max_fsm_pages = 1300000
> archive_mode = off
> enable_seqscan = off
> log_destination = 'syslog'
> log_directory = 'pg_log'
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
> log_rotation_age = 1d
> log_min_duration_statement = 2000
> log_connections = true
> log_line_prefix = '%t <%u%%%d> '
> autovacuum = on
> datestyle = 'iso, mdy'
> lc_messages = 'en_US.UTF-8'
> lc_monetary = 'en_US.UTF-8'
> lc_numeric = 'en_US.UTF-8'
> lc_time = 'en_US.UTF-8'
> default_text_search_config = 'pg_catalog.english'
> backslash_quote = off
>
> Table info (this table shows last autovacuum on 7/28):
> hostname=> VACUUM ANALYZE VERBOSE hierarchy_pull;
> INFO: vacuuming "public.hierarchy_pull"
> INFO: "hierarchy_pull": found 0 removable, 1906 nonremovable row
> versions in 78 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 2714 unused item pointers.
> 47 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: vacuuming "pg_toast.pg_toast_17600"
> INFO: index "pg_toast_17600_index" now contains 0 row versions in 1
> pages
> DETAIL: 0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "pg_toast_17600": found 0 removable, 0 nonremovable row
versions
> in 0 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: analyzing "public.hierarchy_pull"
> INFO: "hierarchy_pull": scanned 78 of 78 pages, containing 1906 live
> rows and 0 dead rows; 1906 rows in sample, 1906 estimated total rows
> VACUUM
>
> On 07/30/2010 09:46 AM, Kevin Grittner wrote:
> > Bryan Payne<bpayne@speedfc.com> wrote:
> >
> >
> >> Autovacuum is sometimes skipping certain tables. The tables in
> >> question are pretty busy. A vacuum analyze works fine when run
> >> manually. The tables in question were successfully autovacuumed
> >> this morning, but that is the first time since the 23rd where it
> >> worked. I'm not finding any logging info regarding the autovacuum.
> >> I'm not sure if autovacuum is seeing table locks or what, but is
> >> there a way to run a more aggressive autovacuum?
> >>
> >
> > For starters, we need to know what version of PostgreSQL this is:
> >
> > select version();
> >
> > It wouldn't hurt to see the contents of your postgresql.conf (with
> > all comments removed) and to know something about the table, and how
> > it's used. One way to get that information would be:
> >
> > VACUUM ANALYZE VERBOSE tablename;
> >
> > -Kevin
> > .
> >
> >
>
> --
> 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] 9+ messages in thread
* Re: Autovacuum missing tables
@ 2010-07-30 15:39 Alvaro Herrera <alvherre@commandprompt.com>
parent: Bryan Payne <bpayne@speedfc.com>
2 siblings, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2010-07-30 15:39 UTC (permalink / raw)
To: Bryan Payne <bpayne@speedfc.com>; +Cc: pgsql-admin
Excerpts from Bryan Payne's message of vie jul 30 11:10:42 -0400 2010:
> Table info (this table shows last autovacuum on 7/28):
> hostname=> VACUUM ANALYZE VERBOSE hierarchy_pull;
> INFO: vacuuming "public.hierarchy_pull"
> INFO: "hierarchy_pull": found 0 removable, 1906 nonremovable row
> versions in 78 pages
> DETAIL: 0 dead row versions cannot be removed yet.
Eh, this says that this table didn't need a vacuum at all (no removable
rows). Maybe that's why autovacuum didn't process it?
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovacuum missing tables
@ 2010-07-30 15:44 Bryan Payne <bpayne@speedfc.com>
parent: Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 9+ messages in thread
From: Bryan Payne @ 2010-07-30 15:44 UTC (permalink / raw)
To: pgsql-admin
Yeah, this is one that is only occasionally busy. The other tables in
question have been getting autovacuumed as of late, but we had a span of
about a week where it was getting passed up. I'm going to check out the
vacuum_cost_limit someone mentioned and see where that takes me. Thanks
for everyone's help.
On 07/30/2010 10:36 AM, Brad Nicholson wrote:
> On 10-07-30 11:10 AM, Bryan Payne wrote:
>
>> Version:
>> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
>> (Ubuntu 4.2.4-1ubuntu3)
>>
>> Postgresql.conf:
>> data_directory = '/var/lib/postgresql/8.3/main'
>> hba_file = '/etc/postgresql/8.3/main/pg_hba.conf'
>> ident_file = '/etc/postgresql/8.3/main/pg_ident.conf'
>> external_pid_file = '/var/run/postgresql/8.3-main.pid'
>> listen_addresses = '*'
>> port = 5432
>> max_connections = 550
>> ssl = true
>> shared_buffers = 1GB
>> work_mem = 24MB
>> maintenance_work_mem = 256MB
>> max_fsm_pages = 1300000
>> archive_mode = off
>> enable_seqscan = off
>> log_destination = 'syslog'
>> log_directory = 'pg_log'
>> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
>> log_rotation_age = 1d
>> log_min_duration_statement = 2000
>> log_connections = true
>> log_line_prefix = '%t<%u%%%d> '
>> autovacuum = on
>> datestyle = 'iso, mdy'
>> lc_messages = 'en_US.UTF-8'
>> lc_monetary = 'en_US.UTF-8'
>> lc_numeric = 'en_US.UTF-8'
>> lc_time = 'en_US.UTF-8'
>> default_text_search_config = 'pg_catalog.english'
>> backslash_quote = off
>>
>> Table info (this table shows last autovacuum on 7/28):
>> hostname=> VACUUM ANALYZE VERBOSE hierarchy_pull;
>> INFO: vacuuming "public.hierarchy_pull"
>> INFO: "hierarchy_pull": found 0 removable, 1906 nonremovable row
>> versions in 78 pages
>> DETAIL: 0 dead row versions cannot be removed yet.
>> There were 2714 unused item pointers.
>> 47 pages contain useful free space.
>> 0 pages are entirely empty.
>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>> INFO: vacuuming "pg_toast.pg_toast_17600"
>> INFO: index "pg_toast_17600_index" now contains 0 row versions in 1
>> pages
>> DETAIL: 0 index row versions were removed.
>> 0 index pages have been deleted, 0 are currently reusable.
>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>> INFO: "pg_toast_17600": found 0 removable, 0 nonremovable row
>> versions in 0 pages
>> DETAIL: 0 dead row versions cannot be removed yet.
>> There were 0 unused item pointers.
>> 0 pages contain useful free space.
>> 0 pages are entirely empty.
>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>> INFO: analyzing "public.hierarchy_pull"
>> INFO: "hierarchy_pull": scanned 78 of 78 pages, containing 1906 live
>> rows and 0 dead rows; 1906 rows in sample, 1906 estimated total rows
>> VACUUM
>>
>>
> Autovacuum won't vacuum this table as it stands as there are no dead
> rows. There is no work for vacuum to do, so it's just a waste of cycles
> to run it. Of course, your manual vacuum may have cleared out the dead
> tuples, but based on the fact that there are not any more in place, I
> wonder about the activity on the table. When you say the tables are
> busy - define what you mean by busy in terms of INSERT/UPDATE/DELETE?
>
> The docs explain how autovacuum calculates when to vacuum tables to
> vacuum here:
>
> http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM
>
> If the table is not being vacuumed frequently enough, you may need to
> change your autovacuum settings.
>
>
>
>
>> On 07/30/2010 09:46 AM, Kevin Grittner wrote:
>>
>>> Bryan Payne<bpayne@speedfc.com> wrote:
>>>
>>>
>>>> Autovacuum is sometimes skipping certain tables. The tables in
>>>> question are pretty busy. A vacuum analyze works fine when run
>>>> manually. The tables in question were successfully autovacuumed
>>>> this morning, but that is the first time since the 23rd where it
>>>> worked. I'm not finding any logging info regarding the autovacuum.
>>>> I'm not sure if autovacuum is seeing table locks or what, but is
>>>> there a way to run a more aggressive autovacuum?
>>>>
>>> For starters, we need to know what version of PostgreSQL this is:
>>>
>>> select version();
>>>
>>> It wouldn't hurt to see the contents of your postgresql.conf (with
>>> all comments removed) and to know something about the table, and how
>>> it's used. One way to get that information would be:
>>>
>>> VACUUM ANALYZE VERBOSE tablename;
>>>
>>> -Kevin
>>> .
>>>
>>>
>>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovacuum missing tables
@ 2010-07-30 15:55 Brad Nicholson <bnichols@ca.afilias.info>
parent: Bryan Payne <bpayne@speedfc.com>
0 siblings, 0 replies; 9+ messages in thread
From: Brad Nicholson @ 2010-07-30 15:55 UTC (permalink / raw)
To: Bryan Payne <bpayne@speedfc.com>; +Cc: pgsql-admin
On 10-07-30 11:44 AM, Bryan Payne wrote:
> Yeah, this is one that is only occasionally busy. The other tables in
> question have been getting autovacuumed as of late, but we had a span
> of about a week where it was getting passed up. I'm going to check out
> the vacuum_cost_limit someone mentioned and see where that takes me.
> Thanks for everyone's help.
>
I don't think that vacuum cost limit is your problem. If they was, the
other tables would most likely be having the same problem. It really
looks like this table does not need to be vacuumed, and autovacuum is
behaving correctly.
Look at the number of dead tuples (pg_stat_user_tables.n_dead_tup). If
this number is below the vacuum threshold based on the calculation in
the manual I referenced, this table will not get vacuumed until it
crosses that threshold. You may want to set up a cronjob to sample this
over time.
Monitoring your activity on this table will also be of interest. Take a
daily snapshot of pg_stat_user_table for the table you are concerned
about and look at the difference in values between the two days.
From those snapshots, you can look at the number of deletes (n_tup_del)
and the number of live updates (n_tup_upd - n_tup_hot_upd).
Those are the two operations that will require vacuuming. If those
numbers are low, the table will get vacuumed less frequently. If they
are 0, the table will never get vacuumed.
> On 07/30/2010 10:36 AM, Brad Nicholson wrote:
>> On 10-07-30 11:10 AM, Bryan Payne wrote:
>>> Version:
>>> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
>>> (Ubuntu 4.2.4-1ubuntu3)
>>>
>>> Postgresql.conf:
>>> data_directory = '/var/lib/postgresql/8.3/main'
>>> hba_file = '/etc/postgresql/8.3/main/pg_hba.conf'
>>> ident_file = '/etc/postgresql/8.3/main/pg_ident.conf'
>>> external_pid_file = '/var/run/postgresql/8.3-main.pid'
>>> listen_addresses = '*'
>>> port = 5432
>>> max_connections = 550
>>> ssl = true
>>> shared_buffers = 1GB
>>> work_mem = 24MB
>>> maintenance_work_mem = 256MB
>>> max_fsm_pages = 1300000
>>> archive_mode = off
>>> enable_seqscan = off
>>> log_destination = 'syslog'
>>> log_directory = 'pg_log'
>>> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
>>> log_rotation_age = 1d
>>> log_min_duration_statement = 2000
>>> log_connections = true
>>> log_line_prefix = '%t<%u%%%d> '
>>> autovacuum = on
>>> datestyle = 'iso, mdy'
>>> lc_messages = 'en_US.UTF-8'
>>> lc_monetary = 'en_US.UTF-8'
>>> lc_numeric = 'en_US.UTF-8'
>>> lc_time = 'en_US.UTF-8'
>>> default_text_search_config = 'pg_catalog.english'
>>> backslash_quote = off
>>>
>>> Table info (this table shows last autovacuum on 7/28):
>>> hostname=> VACUUM ANALYZE VERBOSE hierarchy_pull;
>>> INFO: vacuuming "public.hierarchy_pull"
>>> INFO: "hierarchy_pull": found 0 removable, 1906 nonremovable row
>>> versions in 78 pages
>>> DETAIL: 0 dead row versions cannot be removed yet.
>>> There were 2714 unused item pointers.
>>> 47 pages contain useful free space.
>>> 0 pages are entirely empty.
>>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>>> INFO: vacuuming "pg_toast.pg_toast_17600"
>>> INFO: index "pg_toast_17600_index" now contains 0 row versions in 1
>>> pages
>>> DETAIL: 0 index row versions were removed.
>>> 0 index pages have been deleted, 0 are currently reusable.
>>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>>> INFO: "pg_toast_17600": found 0 removable, 0 nonremovable row
>>> versions in 0 pages
>>> DETAIL: 0 dead row versions cannot be removed yet.
>>> There were 0 unused item pointers.
>>> 0 pages contain useful free space.
>>> 0 pages are entirely empty.
>>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>>> INFO: analyzing "public.hierarchy_pull"
>>> INFO: "hierarchy_pull": scanned 78 of 78 pages, containing 1906 live
>>> rows and 0 dead rows; 1906 rows in sample, 1906 estimated total rows
>>> VACUUM
>>>
>> Autovacuum won't vacuum this table as it stands as there are no dead
>> rows. There is no work for vacuum to do, so it's just a waste of cycles
>> to run it. Of course, your manual vacuum may have cleared out the dead
>> tuples, but based on the fact that there are not any more in place, I
>> wonder about the activity on the table. When you say the tables are
>> busy - define what you mean by busy in terms of INSERT/UPDATE/DELETE?
>>
>> The docs explain how autovacuum calculates when to vacuum tables to
>> vacuum here:
>>
>> http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM
>>
>>
>> If the table is not being vacuumed frequently enough, you may need to
>> change your autovacuum settings.
>>
>>
>>
>>> On 07/30/2010 09:46 AM, Kevin Grittner wrote:
>>>> Bryan Payne<bpayne@speedfc.com> wrote:
>>>>
>>>>> Autovacuum is sometimes skipping certain tables. The tables in
>>>>> question are pretty busy. A vacuum analyze works fine when run
>>>>> manually. The tables in question were successfully autovacuumed
>>>>> this morning, but that is the first time since the 23rd where it
>>>>> worked. I'm not finding any logging info regarding the autovacuum.
>>>>> I'm not sure if autovacuum is seeing table locks or what, but is
>>>>> there a way to run a more aggressive autovacuum?
>>>> For starters, we need to know what version of PostgreSQL this is:
>>>>
>>>> select version();
>>>>
>>>> It wouldn't hurt to see the contents of your postgresql.conf (with
>>>> all comments removed) and to know something about the table, and how
>>>> it's used. One way to get that information would be:
>>>>
>>>> VACUUM ANALYZE VERBOSE tablename;
>>>>
>>>> -Kevin
>>>> .
>>>>
>>
>
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-07-30 15:55 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-29 14:00 Autovacuum missing tables Bryan Payne <bpayne@speedfc.com>
2010-07-30 14:39 ` Bryan Payne <bpayne@speedfc.com>
2010-07-30 14:46 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-30 15:10 ` Bryan Payne <bpayne@speedfc.com>
2010-07-30 15:36 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-07-30 15:44 ` Bryan Payne <bpayne@speedfc.com>
2010-07-30 15:55 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-07-30 15:38 ` Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-30 15:39 ` Alvaro Herrera <alvherre@commandprompt.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