Re: Strange case of database bloat

On Wed, Jul 5, 2017 at 3:51 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:

> On Wed, 5 Jul 2017 13:28:29 +0200
> Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:
>
> > On Wed, Jul 5, 2017 at 1:00 PM, PT <wmoran(at)potentialtech(dot)com> wrote:
> >
> > > 2x the working size for a frequently updated table isn't terrible
> bloat.
> > > Or are
> > > you saying it grows 2x every 24 hours and keeps growing? The real
> question
> > > is
> > > how often the table is being vacuumed. How long have you let the
> > > experiment run
> > > for? Does the table find an equilibrium size where it stops growing?
> Have
> > > you
> > > turned on logging for autovacuum to see how often it actually runs on
> this
> > > table?
> >
> > If it were only twice it would not bother me. The fact that it is twice
> > after 24 hrs, 3x after 48 hrs and 4x after 72 hrs is alarming.
>
> Ok, yup, that seems like an issue.
>
> > > No unremovable rows does not indicate that autovaccum is keeping up. It
> > > just
> > > indicates that you don't have a problem with uncommitted transactions
> > > holding
> > > rows for long periods of time.
> >
> > Right. I should have specified that I also have not seen auto vacuum in
> > pg_stat_activity with an unusual duration.
>
> How long does it take when you run it manually? My experience is that
> autovac
> can take orders of magnitude longer with the default cost delays, but just
> becuase you don't see it, doesn't mean it's not happening. Turn on autovac
> logging and check the logs after a few days.
>

a few min for a normal vacuum, maybe 20-30 min for vacuum full (on one of
the large tables).

>
> > > Have you looked at tuning the autovacuum parameters for this table?
> More
> > > frequent
> > > vacuums should keep things more under control. However, if the write
> load
> > > is
> > > heavy, you'll probably want to lower autovacuum_vacuum_cost_delay.
> > > Personally,
> > > I feel like the default value for this should be 0, but there are
> likely
> > > those
> > > that would debate that. In any event, if that setting is too high it
> can
> > > cause
> > > autovacuum to take so long that it can't keep up. In theory, setting it
> > > too low
> > > can cause autovaccum to have a negative performance impact, but I've
> never
> > > seen
> > > that happen on modern hardware.
> >
> > Most of the writes are periodic (hourly?) batch updates which are fairly
> > big.
>
> Not sure how that statement is related to the comments I made preceeding
> it.
>

Not using cost-based vacuum afaik but will check that. It is a good point.

>
> > > But that's all speculation until you know how frequently autovacuum
> runs on
> > > that table and how long it takes to do its work.
> >
> > Given the other time I have seen similar behaviour, the question in my
> mind
> > is why free pages near the beginning of the table don't seem to be
> re-used.
>
> It's possible that the early pages don't have enough usable space for the
> updated
> rows. Depending on your update patterns, you may end up with bloat
> scattered across
> many pages, with no individual page having enough space to be reused. That
> seems
> unlikely as the bloat becomes many times the used space, though.
>

The fire 35 pages are completely empty. As I say I have seen this sort of
thing before (and I wonder if empty pages early in a table are somehow
biased against in terms of writes).

>
> The pg_freespacemap extension should be useful in determining if that's
> what's
> happening. Combine that with turning on logging to ensure that autovacuum
> is
> actually operating effectively.
>

I am not convinced it is a vacuum problem. Would it be likely that large
batch updates would linearly continue to write pages forward as a
performance optimisation?

>
> --
> Bill Moran <wmoran(at)potentialtech(dot)com>
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more