Thread: autovacuum question

autovacuum question

From
Laszlo Nagy
Date:
  Hello,

I had a problem in my computer system yesterday. Some data has
disappeared. I was looking for a program that might have deleted it
accidentally. Before I found anything, the data suddenly came back. I
thought it must be a transaction id wraparound problem.

The system is running on FreeBSD 6.2. Postgresql version is 8.1.8 and it
was installed from FreeBSD's standard ports tree. It also installed a
script that vacuums all databases daily. This script was turned on, and
I got emails every day, telling me that the databases were vacuumed.
However, when I opened some tables from within pgadmin3, I got the
message "pg_autovacuum recommened".

Summary: I'm vacuuming all databases daily, but probably I had a
transaction wraparound.

I thought I should enable autovacuum. I read the docs and did the following:

1. Changed postgresql.conf:

stats_start_collector = on
stats_row_level = on
autovacuum = on

2. Restarted the postgresql service.

Now, I do not see a "separate autovacuum daemon" running. What did I
wrong? Here are my tips:

a.) atovacuum daemon is part of the "postgres" process, so I won't see
it running in the process list (how can I check if it is working?)
b.) there is a separate program that I should start (where?)
c.) I missed some extra configuration (what should I do?)
d.) something else

Please help me out.
Thanks,

   Laszlo


Re: autovacuum question

From
Jim Nasby
Date:
Autovacuum doesn't launch a separate daemon; you'll only see an
autovac process when an autovac is actually working.

Also, 8.1 has code to prevent wraparound, so that's not it (see
backend/access/transam/varsup.c)

On Mar 27, 2007, at 5:17 AM, Laszlo Nagy wrote:

>
>  Hello,
>
> I had a problem in my computer system yesterday. Some data has
> disappeared. I was looking for a program that might have deleted it
> accidentally. Before I found anything, the data suddenly came back.
> I thought it must be a transaction id wraparound problem.
>
> The system is running on FreeBSD 6.2. Postgresql version is 8.1.8
> and it was installed from FreeBSD's standard ports tree. It also
> installed a script that vacuums all databases daily. This script
> was turned on, and I got emails every day, telling me that the
> databases were vacuumed. However, when I opened some tables from
> within pgadmin3, I got the message "pg_autovacuum recommened".
>
> Summary: I'm vacuuming all databases daily, but probably I had a
> transaction wraparound.
>
> I thought I should enable autovacuum. I read the docs and did the
> following:
>
> 1. Changed postgresql.conf:
>
> stats_start_collector = on
> stats_row_level = on
> autovacuum = on
>
> 2. Restarted the postgresql service.
>
> Now, I do not see a "separate autovacuum daemon" running. What did
> I wrong? Here are my tips:
>
> a.) atovacuum daemon is part of the "postgres" process, so I won't
> see it running in the process list (how can I check if it is working?)
> b.) there is a separate program that I should start (where?)
> c.) I missed some extra configuration (what should I do?)
> d.) something else
>
> Please help me out.
> Thanks,
>
>   Laszlo
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



Re: autovacuum question

From
Laszlo Nagy
Date:
Jim Nasby írta:
> Autovacuum doesn't launch a separate daemon; you'll only see an
> autovac process when an autovac is actually working.
Where should I see it? The output of "ps ax | grep auto" is empty. Does
it mean that auto vacuum is not running? How can I check if there is a
problem?
>
> Also, 8.1 has code to prevent wraparound, so that's not it (see
> backend/access/transam/varsup.c)
Oh, then do you have any idea what caused this? E.g. a postgres client
seeing an older version of the data? "Older" means that the postgres
client was started today and it saw the data from 2 days ago, then
suddenly it started to see the fresh data.

Thanks,

  Laszlo


Re: autovacuum question

From
Alvaro Herrera
Date:
Laszlo Nagy wrote:
> Jim Nasby írta:
> >Autovacuum doesn't launch a separate daemon; you'll only see an
> >autovac process when an autovac is actually working.
> Where should I see it? The output of "ps ax | grep auto" is empty. Does
> it mean that auto vacuum is not running? How can I check if there is a
> problem?

Do "show autovacuum", and it will tell you if autovac is running.

> >Also, 8.1 has code to prevent wraparound, so that's not it (see
> >backend/access/transam/varsup.c)
> Oh, then do you have any idea what caused this? E.g. a postgres client
> seeing an older version of the data? "Older" means that the postgres
> client was started today and it saw the data from 2 days ago, then
> suddenly it started to see the fresh data.

Maybe because they issued "commit" in the session where they had an idle
open transaction?

--
Alvaro Herrera                                http://www.PlanetPostgreSQL.org
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)