autovacuum not running
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?
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)
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@postgresql.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.org@postgresql.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.org@postgresql.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@postgresql.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@mail.gmail.com>
References: <19443.58406.qm@web60811.mail.yahoo.com> <e51f66da0708281433n53edc7cbu93ae45f4d35feda4@mail.gmail.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@myemma.com>
Cc: "Jeff Amiel" <becauseimjeff@yahoo.com>,
pgsql-general@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik@myemma.com>
Subject: Re: Out of Memory - 8.2.4
Date: Tue, 28 Aug 2007 17:15:57 -0500
To: Marko Kreen <markokr@gmail.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@yahoo.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....536870910I 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@myemma.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
Ben <bench@silentmedia.com> writes:
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....
Just to double-check, make sure stats_start_collector and
stats_row_level are on, and confirm that you see the stats collector
process in "ps". Also, do you have any of the autovac threshold
parameters at nondefault values?
The autovac process doesn't run continuously, so not seeing it in ps
is not proof that there's a problem. But if pg_stat_all_tables isn't
showing any evidence of vacuums happening, that's probably bad.
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?
I think you've gotta crank it up to DEBUG2 or so, which is gonna be
noisy :-(
regards, tom lane
On Tue, Aug 28, 2007 at 03:10:34PM -0700, Ben wrote:
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....
Last vacuum, or last autovacuum?
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Ben wrote:
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?
Right, it is running. Maybe the problem is that it is selecting one
database all the time and failing to finish vacuuming it for some
reason. We've actually seen it do that, several times, due to
unfortunate bugs in early 8.1 releases. AFAIR you are on 8.2.4 so that
doesn't affect you, but maybe there's some other explanation.
As Tom said, you need to move log_min_messages to DEBUG2. Note that if
there's an ERROR in the autovac process, it doesn't continue running!
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On Tue, 28 Aug 2007, Decibel! wrote:
On Tue, Aug 28, 2007 at 03:10:34PM -0700, Ben wrote:
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....Last vacuum, or last autovacuum?
Doh! I was looking at last_vacuum. Unfortunately, the *last_autovaccum*
column is completely blank on the tables I'm inserting into, so I guess
it's never worked there.
But I do see the stats collector running, and upping the log level to
debug2 does show that the autovacuum at least starts running, and never
seems to have any errors.
.....hmmmm......
After some tests, it seems that autovacuum doesn't kick in from simple
inserts, as the manual says it will. You have to delete and/or update as
well. Am I misreading the manual?
Ben <bench@silentmedia.com> writes:
After some tests, it seems that autovacuum doesn't kick in from simple
inserts, as the manual says it will. You have to delete and/or update as
well. Am I misreading the manual?
There's nothing to vacuum until you delete or update.
You should see some auto-analyze activity on an insert-only table, but
not vacuuming.
regards, tom lane