Re: 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
> .
>
>