Re: How to avoid Force Autovacuum

On Thu, Aug 8, 2013 at 11:18 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> There were some fixes for autovacuum problems in 9.2.3. Some other
> fixes will be coming when 9.2.5 is released. Many of your problems
> are likely to go away by staying up-to-date on minor releases.
>
> By setting this so high, you are increasing the amount of work
> autovacuum will need to do when it does work on a table. A smaller
> value tends to give less "bursty" performance. Also, any small,
> frequently-updated tables may bloat quite a bit in 50000
> transactions.
>
> Each autovacuum worker will allocate this much RAM. If all of your
> autovacuum workers wake up at once, would losing 2GB for each one
> from your cache cause a significant performance hit? (Since you
> didn't say how much RAM the machine has, it's impossible to tell.)
>
> What does running this in psql this show?:
>
> \x on
> select * from pg_database where datname = 'template0';
> select * from pg_stat_database where datname = 'template0';

In addition to Kevin's notes, I think it is also worth to look at the
result of the query below.

select name, setting from pg_settings
where name ~ 'vacuum' and setting <> reset_val;

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com