From: Bryan Payne <bpayne@speedfc.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Autovacuum missing tables
Date: Fri, 30 Jul 2010 10:44:02 -0500
Message-ID: <4C52F342.6050400@speedfc.com> (raw)
In-Reply-To: <4C52F186.1020901@ca.afilias.info>
References: <4C518960.3040004@speedfc.com>
<4C52E42C.8070701@speedfc.com>
<4C529F7B0200002500033F00@gw.wicourts.gov>
<4C52EB72.8040904@speedfc.com>
<4C52F186.1020901@ca.afilias.info>
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
>>> .
>>>
>>>
>>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bpayne@speedfc.com
Subject: Re: Autovacuum missing tables
In-Reply-To: <4C52F342.6050400@speedfc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox