Re: Strange case of database bloat

On Wed, 5 Jul 2017 07:18:03 +0200
Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:

> Hi;
>
> First, I haven't seen major problems of database bloat in a long time which
> is why I find this case strange. I wanted to ask here what may be causing
> it.
>
> Problem:
> ==========
> Database is in the 100GB to 200GB size range, running on btrfs (not my
> choice) with nodatacow enabled (which I set up to fix a performance
> issue). The workload is a very heavy batch-update workload.
>
> The database bloats linearly. I have measured this on one table (of 149M
> rows).
>
> After vacuum full this table is (including indexes): 17GB
> Every 24 hrs, seems to add its original space in size to the file system
> +/-.
>
> Bloat seems to be affecting both indexes and underlying tables.
>
> Vacuum verbose does not indicate a disproportionate number of rows being
> unremovable. So autovacuum is keeping up without too much difficulty.
>
>
> Troubleshooting so far
> =======================
>
> filefrag finds a single extent on each file, so copy-on-write is not the
> culprit
>
> Selecting the smallest 10 values of ctid from one of the bloating tables
> shows the first page used is around page 35 with one row per used page (and
> large gaps in between).
>
> Questions
> ===========
> I assume that it is the fact that rows update frequently which is the
> problem here? But why doesn't Postgres re-use any of the empty disk pages?
>
> More importantly, is there anything that can be done to mitigate this issue
> other than a frequent vacuum full?

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?

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.

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.

But that's all speculation until you know how frequently autovacuum runs on
that table and how long it takes to do its work.

--
PT <wmoran(at)potentialtech(dot)com>