Re: Strange case of database bloat

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.

>
> 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.

>
> 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.

>
> 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.

I would like to try to verify that however, if you have any ideas.

>
> --
> PT <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