Thread: 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) >
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)
Attachment
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