On Wed, Jul 5, 2017 at 3:51 PM, Bill Moran <wmo...@potentialtech.com> wrote:
> On Wed, 5 Jul 2017 13:28:29 +0200 > 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. > > Ok, yup, that seems like an issue. > > > > 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. > > How long does it take when you run it manually? My experience is that > autovac > can take orders of magnitude longer with the default cost delays, but just > becuase you don't see it, doesn't mean it's not happening. Turn on autovac > logging and check the logs after a few days. > a few min for a normal vacuum, maybe 20-30 min for vacuum full (on one of the large tables). > > > > 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. > > Not sure how that statement is related to the comments I made preceeding > it. > Not using cost-based vacuum afaik but will check that. It is a good point. > > > > 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. > > It's possible that the early pages don't have enough usable space for the > updated > rows. Depending on your update patterns, you may end up with bloat > scattered across > many pages, with no individual page having enough space to be reused. That > seems > unlikely as the bloat becomes many times the used space, though. > The fire 35 pages are completely empty. As I say I have seen this sort of thing before (and I wonder if empty pages early in a table are somehow biased against in terms of writes). > > The pg_freespacemap extension should be useful in determining if that's > what's > happening. Combine that with turning on logging to ensure that autovacuum > is > actually operating effectively. > I am not convinced it is a vacuum problem. Would it be likely that large batch updates would linearly continue to write pages forward as a performance optimisation? > > -- > Bill Moran <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
