Message-ID: <4C52F186.1020901@ca.afilias.info>
Date: Fri, 30 Jul 2010 11:36:38 -0400
From: Brad Nicholson <bnichols@ca.afilias.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.11) Gecko/20100713 Lightning/1.0b1 Thunderbird/3.0.6
MIME-Version: 1.0
To: Bryan Payne <bpayne@speedfc.com>
CC: "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>
	<4C52EB72.8040904@speedfc.com>
In-Reply-To: <4C52EB72.8040904@speedfc.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.