Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id DEB79632948 for ; Fri, 30 Jul 2010 12:38:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 97192-07 for ; Fri, 30 Jul 2010 15:38:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.illumen.com (unknown [64.207.29.137]) by mail.postgresql.org (Postfix) with ESMTP id 6B50D632743 for ; Fri, 30 Jul 2010 12:38:36 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: Autovacuum missing tables Date: Fri, 30 Jul 2010 09:38:33 -0600 Message-ID: In-Reply-To: <4C52EB72.8040904@speedfc.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Autovacuum missing tables Thread-Index: Acsv+YRL4xSDV38cRNO6c9Qz22bPNAAA1OEA References: <4C518960.3040004@speedfc.com> <4C52E42C.8070701@speedfc.com> <4C529F7B0200002500033F00@gw.wicourts.gov> <4C52EB72.8040904@speedfc.com> From: "Benjamin Krajmalnik" To: "Bryan Payne" , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.107 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201007/217 X-Sequence-Number: 35236 What may be happening is that it does not have enough time to run based on your autovacuum settings. Check your setting for autovacuum_vacuum_cost_limit. I believe default is 200 - max is 10000 which is what I have mine set to due to very heavy table utilization. > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Bryan Payne > Sent: Friday, July 30, 2010 9:11 AM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Autovacuum missing tables >=20 > Version: > PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 > (Ubuntu 4.2.4-1ubuntu3) >=20 > Postgresql.conf: > data_directory =3D '/var/lib/postgresql/8.3/main' > hba_file =3D '/etc/postgresql/8.3/main/pg_hba.conf' > ident_file =3D '/etc/postgresql/8.3/main/pg_ident.conf' > external_pid_file =3D '/var/run/postgresql/8.3-main.pid' > listen_addresses =3D '*' > port =3D 5432 > max_connections =3D 550 > ssl =3D true > shared_buffers =3D 1GB > work_mem =3D 24MB > maintenance_work_mem =3D 256MB > max_fsm_pages =3D 1300000 > archive_mode =3D off > enable_seqscan =3D off > log_destination =3D 'syslog' > log_directory =3D 'pg_log' > log_filename =3D 'postgresql-%Y-%m-%d_%H%M%S.log' > log_rotation_age =3D 1d > log_min_duration_statement =3D 2000 > log_connections =3D true > log_line_prefix =3D '%t <%u%%%d> ' > autovacuum =3D on > datestyle =3D 'iso, mdy' > lc_messages =3D 'en_US.UTF-8' > lc_monetary =3D 'en_US.UTF-8' > lc_numeric =3D 'en_US.UTF-8' > lc_time =3D 'en_US.UTF-8' > default_text_search_config =3D 'pg_catalog.english' > backslash_quote =3D off >=20 > Table info (this table shows last autovacuum on 7/28): > hostname=3D> 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 >=20 > On 07/30/2010 09:46 AM, Kevin Grittner wrote: > > Bryan Payne 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 > > . > > > > >=20 > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin