On Wed, Jul 5, 2017 at 7:28 AM, Chris Travers <chris.trav...@gmail.com> wrote:
> > > On Wed, Jul 5, 2017 at 1:00 PM, PT <wmo...@potentialtech.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. > 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 <wmo...@potentialtech.com> >> > > > > -- > Best Wishes, > Chris Travers > > Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor > lock-in. > http://www.efficito.com/learn_more > -- -- Scott Mead Sr. Architect *OpenSCG <http://openscg.com>* http://openscg.com
