Re: Strange case of database bloat

On Wed, Jul 5, 2017 at 7:18 AM, 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?
>

Two points I think I forgot to mention:

This is PostgreSQL 9.5.1

Last I saw something similar was a more "minor" case on a larger db, on
PostgreSQL 9.3.x

The more minor case was a small table (maybe 20k rows) which had bloated to
1GB in size due to this same sort of problem but we ignored it because the
table was cached all the time and at the RAM we were using, it wasn't a
significant drain on performance. However, here it is.

First 20 CTIDs from one table:

(35,25)
(48,15)
(76,20)
(77,20)
(83,20)
(96,19)
(100,19)
(103,13)
(111,9)
(115,12)
(124,11)
(120,12)
(131,12)
(137,12)
(150,14)
(152,12)
(157,20)
(162,14)

> --
> Best Wishes,
> Chris Travers
>
> Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
> lock-in.
> http://www.efficito.com/learn_more
>

--
Best Wishes,
Chris Travers

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