Re: Autovacuum fails to keep visibility map up-to-date in mostly-insert-only-tables

On 10/9/14, 4:19 PM, Andres Freund wrote:
> On 2014-10-09 18:16:46 -0300, Alvaro Herrera wrote:
>> >Andres Freund wrote:
>>> > >On 2014-10-09 18:03:00 -0300, Alvaro Herrera wrote:
>>>> > > >Bruce Momjian wrote:
>>>> > > >
>>>>> > > > >I agree this is a serious problem. We have discussed various options,
>>>>> > > > >but have not decided on anything. The TODO list has:
>>>>> > > > >
>>>>> > > > > https://wiki.postgresql.org/wiki/Todo
>>>>> > > > >
>>>>> > > > > Improve setting of visibility map bits for read-only and insert-only
>>>>> > > > > workloads
>>>>> > > > >
>>>>> > > > > http://www.postgresql.org/message-id/20130906001437.GA29264@momjian.us
>>>> > > >
>>>> > > >I hate to repeat myself, but I think autovacuum could be modified to run
>>>> > > >actions other than vacuum and analyze. In this specific case we could
>>>> > > >be running a table scan that checks only pages that don't have the
>>>> > > >all-visible bit set, and see if it can be set.
>>> > >
>>> > >Isn't that*precisely* what a plain vacuum run does?
>> >
>> >Well, it also scans for dead tuples, removes them, and needs to go
>> >through indexes to remove their references.
> IIRC it doesn't do most of that if that there's no need. And if it's a
> insert only table without rollbacks. I*do* think there's some
> optimizations we could make in general.

No, it always attempts dead tuple removal. The "weird" part is that if it's not doing a freeze it will just punt on a page if it can't get the cleanup lock. I have to believe that could seriously screw up autovacuum scheduling.

Now that we have forks, I'm wondering if it would be best to come up with a per-page system that could be used to determine when a table needs background work to be done. The visibility map could serve a lot of this purpose, but I'm not sure if it would work for getting hint bits set in the background. I think it would also be a win if we had a way to advance relfrozenxid and relminmxid. Perhaps something that simply remembered the last XID that touched each page...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com