On Tue, Apr 29, 2014 at 4:59 PM, Elanchezhiyan Elango
<elanelango@gmail.com> wrote:
[...]
> With the above query pattern with intensive updates and deletes, I need to
> do some aggressive vacuuming.
>
> Current strategy:I am running with default autovacuum settings (postgres
> 9.1.9) and I tried doing a 'vacuum full' for the 8 high traffic tables
> (Tables1 and Tables3) every night. But after a point, the 'vacuum full's
> started timing out (with 4min timeout) every night. I think this is because
> the table is growing bigger (~5GB) and doing a vacuum full every night is
> probably not feasible.
>
> Going with the default autovacuum settings and not doing 'vacuum full' at
> all is also not enough for my usecase. Whenever vacuum full succeeded every
> night, it did seem to reclaim a considerable amount of space. So I assume,
> autovacuum is not able to reclaim all space.
It is able to reclaim space at the end of the table (truncate empty
(no live tuples) pages in the tail), and depending on the autovacuum
settings you might get the result that is very close to the full
vacuum. Keep in mind that to reuse the space of deleted or updated
tuples (on the low level (tuple level) any update is roughly
delete+insert) vacuum/autovacuum should mark them as available first.
So, to avoid bloat, you need your vacuum to manage this faster than
your logic produces new dead tuples. The allowed bloat fraction can be
controlled with _threashold and _scale_factor parameters.
> What approach should I take? Do I require 'vacuum full'? What autovaccum
> settings should I tweak so that I can avoid vacuum full, if possible, and
> maintain a steady state without bloating the tables?
I do not think you need vacuum full here.
Start with the settings below. They are pretty aggressive, so, after
some time, by analyzing logs (log_autovacuum_min_duration = 0 sets to
log all autovacuum calls), you might came to a decision to ease these
settings if it will be needed.
log_autovacuum_min_duration = 0
autovacuum_max_workers = 5
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 5ms
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru@gmail.com
