Re: autovacuum benchmarking ...

On Wed, Dec 3, 2008 at 3:34 PM, AmitKumar Jain <amitjain(at)synechron(dot)com> wrote:
> Hi Team
>
> 1. Can we know the how much time will take ongoing vacuum process in DB?
> 2. Any benchmarking tool for vacuum process.
> 3. Can we know what exact operations has been done by Vacuum process.
> 4. I studied that only VACUUM process doesnot lock the table but due to it
> insertion in table gets very slow. when i killed vacuum daemon insertion get
> faster is it bug in 8.3.3
>
>
> I know I should not ask all these questions in one go but i need urgently
> its answer as vacuum process is just screwing my production database. I
> would thankful for any suggestion on above question...

At least they're all related, and you're not spamming a half dozen
pgsql lists, so really, it's fine to put them all together.

1: Vacuum takes as long as it takes. On faster machines it runs
faster. Generally it's very I/O dependent, so lots of hard drives
will make it run faster. It's also influenced by how much other
activity is going on. Since it doesn't lock, it's not a big deal as
long as it finishes in a reasonable amount of time.
2: \timing
vacuum tablename;
3: logging, I believe you have to turn log_min_messages down (up?) to
get log entries. also look in pg_stat_user_tables.
4: You need to look into the settings autovacuum_vacuum_cost_delay and
vacuum_cost_delay. Setting them to 10 or 20 or at most 30 or 40 will
slow down vacuum enough that it shouldn't too heavily impact the rest
of the system.