On Wed, Jul 5, 2017 at 7:28 AM, 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.
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.
What about anything 'WHERE state = 'idle in transaction' ?
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.
I've had similar issues when each update makes a row larger than any of the available slots. I had a workload (admittedly on an older version of postgres) where we were updating every row a few times a day. Each time, the row (a bytea field) would grow about 0.5 - 5.0%. This would prevent us from using freespace (it was all too small). The only way around this was :
1. Run manual table rebuilds (this was before pg_repack / reorg). Use pg_repack now
2. Fix the app
Essentially, I would do targeted, aggressive vacuuming and then, once a month (or once I hit a bloat threshold) do a repack (again, it was my custom process back then). This was the bandage until I could get the app fixed to stop churning so badly.
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@xxxxxxxxxxxxxxxxx>
--Best Wishes,Chris TraversEfficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
