Re: Autovacuum missing tables

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