On Wed, Jul 5, 2017 at 3:51 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
On Wed, 5 Jul 2017 13:28:29 +0200
Chris Travers <chris.travers@xxxxxxxxx> wrote:
> On Wed, Jul 5, 2017 at 1:00 PM, PT <wmoran@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx>
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
