Message-ID: <4C52F342.6050400@speedfc.com>
Date: Fri, 30 Jul 2010 10:44:02 -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>
	<4C52EB72.8040904@speedfc.com> <4C52F186.1020901@ca.afilias.info>
In-Reply-To: <4C52F186.1020901@ca.afilias.info>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

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