5

I have a couple of postgres 8.3 databases, and they are showing symptoms of lack of vacuuming.

I've checked the config, and they are in the default state of having the autovacummer turned on. Also, I see in my postgres logs "autovacuum launcher started" when I start the database.

The problem is, I never actually see a vacuum running. I also never see anything in the logs about a vacuum taking place (should I be seeing anything?)

So how do I tell if the vacuumer is working? Is there a way to see when the last vacuum completed, for example? Better yet, is there some documentation someone can point me to with the answer to that question?

Thanks!

jedberg
  • 2,291
  • 22
  • 21

3 Answers3

7

pg_stat_user_tables has columns last_autovacuum and last_autoanalyze. They will be updated whenever it does something.

Magnus Hagander
  • 2,227
  • 15
  • 9
  • Awesome, that was exactly what I was looking for! Incidentally, everything is being vacuumed as expected. – jedberg Aug 12 '09 at 22:58
1

At a higher level, PgSnap is your friend ;-)

Here is un example of last vaccuum report

Matthieu
  • 443
  • 4
  • 12
0

Google is your friend, and www.postgresql.org even greater:

18.9. Automatic Vacuuming

log_autovacuum_min_duration (integer)

Causes each action executed by autovacuum to be logged if it ran for at least the specified number of milliseconds. Setting this to zero logs all autovacuum actions. Minus-one (the default) disables logging autovacuum actions. For example, if you set this to 250ms then all automatic vacuums and analyzes that run 250ms or longer will be logged. Enabling this parameter can be helpful in tracking autovacuum activity. This setting can only be set in the postgresql.conf file or on the server command line.

slovon
  • 957
  • 5
  • 12
  • Thanks! That is the doc for postgres 8.4, but it appears to apply to 8.3 as well. The only reason I didn't accept your answer is because I didn't have to restart with Magnus's answer, but it they let me, I would have accepted your answer too. :) – jedberg Aug 12 '09 at 22:59
  • No problem :) It was anyway easy to find in the docs :) – slovon Aug 13 '09 at 20:47