Message-ID: <4C52EB72.8040904@speedfc.com>
Date: Fri, 30 Jul 2010 10:10:42 -0500
From: Bryan Payne <bpayne@speedfc.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6
MIME-Version: 1.0
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Autovacuum missing tables
References: <4C518960.3040004@speedfc.com> <4C52E42C.8070701@speedfc.com>
	<4C529F7B0200002500033F00@gw.wicourts.gov>
In-Reply-To: <4C529F7B0200002500033F00@gw.wicourts.gov>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

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