Re: autovacuum not running

Hm, I assumed it wasn't running because pg_stat_all_tables shows the last
vacuum from several weeks ago, and this is an active db. Also, I see no
vacuum activity in the logs. But "show autovacuum" does show it being
on....

So if it is running after all, how can I track down why things aren't
getting vacuumed? What log level are the autovacuum logs at?

On Tue, 28 Aug 2007, Alvaro Herrera wrote:

> Ben wrote:
>> My autovacuum daemon isn't running on 8.2.4, and I'm guessing it's because
>> I changed my unix socket directory in postgresql.conf. Is there a way I can
>> tell autovacuum which socket file to use, or which IP to connect to?
>
> It doesn't use a socket. How do you know it's not running? An easy
> test is connect with psql and do a "show autovacuum". If it's on, then
> it is running.
>
> --
> Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
> "El número de instalaciones de UNIX se ha elevado a 10,
> y se espera que este número aumente" (UPM, 1972)
>
>From pgsql-general-owner(at)postgresql(dot)org Tue Aug 28 19:16:16 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
by postgresql.org (Postfix) with ESMTP id 48C8E9F9757
for <pgsql-general-postgresql(dot)org(at)postgresql(dot)org>; Tue, 28 Aug 2007 19:16:16 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
with ESMTP id 27544-01 for <pgsql-general-postgresql(dot)org(at)postgresql(dot)org>;
Tue, 28 Aug 2007 19:16:08 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from smtp110.iad.emailsrvr.com (smtp110.iad.emailsrvr.com [207.97.245.110])
by postgresql.org (Postfix) with ESMTP id 8B4DA9F97B0
for <pgsql-general(at)postgresql(dot)org>; Tue, 28 Aug 2007 19:16:12 -0300 (ADT)
Received: by relay1.r1.iad.emailsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id 679BE4583CA;
Tue, 28 Aug 2007 18:16:11 -0400 (EDT)
In-Reply-To: <e51f66da0708281433n53edc7cbu93ae45f4d35feda4(at)mail(dot)gmail(dot)com>
References: <19443(dot)58406(dot)qm(at)web60811(dot)mail(dot)yahoo(dot)com> <e51f66da0708281433n53edc7cbu93ae45f4d35feda4(at)mail(dot)gmail(dot)com>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <04451391-4C0D-4AB7-A108-FA990C68431D(at)myemma(dot)com>
Cc: "Jeff Amiel" <becauseimjeff(at)yahoo(dot)com>,
pgsql-general(at)postgresql(dot)org
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik(at)myemma(dot)com>
Subject: Re: Out of Memory - 8.2.4
Date: Tue, 28 Aug 2007 17:15:57 -0500
To: Marko Kreen <markokr(at)gmail(dot)com>
X-Mailer: Apple Mail (2.752.3)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/1842
X-Sequence-Number: 119691

On Aug 28, 2007, at 4:33 PM, Marko Kreen wrote:

> On 8/24/07, Jeff Amiel <becauseimjeff(at)yahoo(dot)com> wrote:
>> Over last 2 days, have spotted 10 "Out of Memory"
>> errors in postgres logs (never saw before with same
>> app/usage patterns on tuned hardware/postgres under
>> FreeBSD)
>>
>> Aug 22 18:08:24 db-1 postgres[16452]: [ID 748848
>> local0.warning] [6-1] 2007-08-22 18:08:24 CDT ERROR:
>> out of memory.
>> Aug 22 18:08:24 db-1 postgres[16452]: [ID 748848
>> local0.warning] [6-2] 2007-08-22 18:08:24 CDT
>> DETAIL: Failed on request of size 536870910.
>>
>> What I found interesting is that It's ALWAYS the same
>> size....536870910
>>
>> I am running autovacuum and slony.....but I see
>> nothing in the logs anywhere near the "out of memory"
>> errors related to either (autovacuum used to under
>> 8.0.X log INFO messages every time it vacuumed which
>> came in handy...I assume it doesn't so this any more?)
>>
>>
>> The events are fairly spread out...and cannot (by
>> looking at app logs and rest of DB logs) correlate to
>> any specific query or activity.
>>
>> Any help would be appreciated
>
> I've experienced something similar. The reason turned out to be
> combination of overcommit=3Doff, big maint_mem and several parallel
> vacuums for fast-changing tables. Seems like VACUUM allocates
> full maint_mem before start, whatever the actual size of the table.
>
> Fix was to put "set maint_mem=3D32M" before small vacuums and
> serialize some of them.
>

I'm not sure about the OP but I know that we just run autovacuum so =20
no problem with parallel vacuums. In addition, Solaris doesn't have =20
overcommit.

Erik Jones

Software Developer | Emma=AE
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com