Re: FSM and VM file

On 05/03/10 15:14, akp geek wrote:
> Hi All -
>
> I have turned on the auto vacuum on the slave and for some
> reason the db is getting bloated up. The master size is only 1G and the
> slave is at 9.2GB now. I did cluster on couple of tables also. did any one
> run into this situation? Can you please help?

OK - so autovacuum is now running on the slave, but your database keeps
growing anyway.

So - either:

1. You have a long-running transaction blocking the vacuum.
2. Your autovacuum isn't running often enough.

The first step with #1 is to check for idle transactions. Look in the
system view: pg_stat_activity
SELECT * FROM pg_stat_activity;
Is there anything there that started a long time ago (probably marked
IDLE IN TRANSACTION)?

For #2, I would start with seeing what tables are affected, then check
your configuration settings. It might be that autovacuum_naptime is too
large for your workload, or (auto)vacuum_cost_delay is too high.

http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#AUTOVACUUM

http://developer.postgresql.org/pgdocs/postgres/runtime-config-autovacuum.html#RUNTIME-CONFIG-AUTOVACUUM

--
Richard Huxton
Archonet Ltd