Re: Autovacuum missing tables

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(at)postgresql(dot)org [mailto:pgsql-admin-
> owner(at)postgresql(dot)org] On Behalf Of Bryan Payne
> Sent: Friday, July 30, 2010 9:11 AM
> To: pgsql-admin(at)postgresql(dot)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(at)speedfc(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin