Re: Vacuum: allow usage of more than 1GB of work mem

<p dir="ltr"><p dir="ltr">On Sep 14, 2016 5:18 PM, "Robert Haas" <<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br /> ><br /> > On Wed, Sep 14, 2016 at
8:16AM, Pavan Deolasee<br /> > <<a href="mailto:pavan.deolasee@gmail.com">pavan.deolasee@gmail.com</a>>
wrote:<br/> > > Ah, thanks. So MaxHeapTuplesPerPage sets the upper boundary for the per page<br /> > >
bitmapsize. Thats about 36 bytes for 8K page. IOW if on an average there<br /> > > are 6 or more dead tuples per
page,bitmap will outperform the current<br /> > > representation, assuming max allocation for bitmap. If we can
useadditional<br /> > > estimates to restrict the size to somewhat more conservative value and then<br /> >
>keep overflow area, then probably the break-even happens even earlier than<br /> > > that. I hope this gives
usa good starting point, but let me know if you<br /> > > think it's still a wrong approach to pursue.<br />
><br/> > Well, it's certainly a bigger change.  I think the big concern is that<br /> > the amount of memory
nowbecomes fixed based on the table size.  So<br /> > one problem is that you have to figure out what you're going
todo if<br /> > the bitmap doesn't fit in maintenance_work_mem.  A related problem is<br /> > that it might fit
butuse more memory than before, which could cause<br /> > problems for some people.  Now on the other hand it could
alsouse<br /> > less memory for some people, and that would be good.<br /> ><br /> > I am kind of doubtful
aboutthis whole line of investigation because<br /> > we're basically trying pretty hard to fix something that I'm
notsure<br /> > is broken.    I do agree that, all other things being equal, the TID<br /> > lookups will
probablybe faster with a bitmap than with a binary<br /> > search, but maybe not if the table is large and the
numberof dead<br /> > TIDs is small, because cache efficiency is pretty important.  But even<br /> > if it's
alwaysfaster, does TID lookup speed even really matter to<br /> > overall VACUUM performance? Claudio's early
resultssuggest that it<br /> > might, but maybe that's just a question of some optimization that<br /> > hasn't
beendone yet.<br /> ><br /> > I'm fairly sure that our number one priority should be to minimize the<br /> >
numberof cases where we need to do multiple scans of the indexes to<br /> > stay within maintenance_work_mem.  If
we'resatisfied we've met that<br /> > goal, then within that we should try to make VACUUM as fast as<br /> >
possiblewith as little memory usage as possible.  I'm not 100% sure I<br /> > know how to get there, or how much
workit's worth expending.  In<br /> > theory we could even start with the list of TIDs and switch to the<br /> >
bitmapif the TID list becomes larger than the bitmap would have been,<br /> > but I don't know if it's worth the
effort.<br/> ><br /> > /me thinks a bit.<br /> ><br /> > Actually, I think that probably *is* worthwhile,
specificallybecause<br /> > it might let us avoid multiple index scans in cases where we currently<br /> >
requirethem.  Right now, our default maintenance_work_mem value is<br /> > 64MB, which is enough to hold a little
overten million tuples.  It's<br /> > also large enough to hold a bitmap for a 14GB table.  So right now if<br />
>you deleted, say, 100 tuples per page you would end up with an index<br /> > vacuum cycles for every ~100,000
pages= 800MB, whereas switching to<br /> > the bitmap representation for such cases would require only one index<br
/>> vacuum cycle for every 14GB, more than an order of magnitude<br /> > improvement!<br /> ><br /> > On
theother hand, if we switch to the bitmap as the ONLY possible<br /> > representation, we will lose badly when there
arescattered updates -<br /> > e.g. 1 deleted tuple every 10 pages.  So it seems like we probably<br /> > want to
haveboth options.  One tricky part is figuring out how we<br /> > switch between them when memory gets tight; we
haveto avoid bursting<br /> > above our memory limit while making the switch.  And even if our<br /> > memory
limitis very high, we want to avoid using memory gratuitously;<br /> > I think we should try to grow memory usage
incrementallywith either<br /> > representation.<br /> ><br /> > For instance, one idea to grow memory usage
incrementallywould be to<br /> > store dead tuple information separately for each 1GB segment of the<br /> >
relation. So we have an array of dead-tuple-representation objects,<br /> > one for every 1GB of the relation.  If
thereare no dead tuples in a<br /> > given 1GB segment, then this pointer can just be NULL.  Otherwise, it<br />
>can point to either the bitmap representation (which will take ~4.5MB)<br /> > or it can point to an array of
TIDs(which will take 6 bytes/TID).<br /> > That could handle an awfully wide variety of usage patterns<br /> >
efficiently;it's basically never worse than what we're doing today,<br /> > and when the dead tuple density is high
forany portion of the<br /> > relation it's a lot better.<br /> ><br /> > --<br /> > Robert Haas<br /> >
EnterpriseDB:<a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /> > The Enterprise PostgreSQL
Company<br/> ><br /> ><br /> > --<br /> > Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> > To make changes to your
subscription:<br/> > <a
href="http://www.postgresql.org/mailpref/pgsql-hackers">http://www.postgresql.org/mailpref/pgsql-hackers</a><p
dir="ltr">I'dsay it's an idea worth pursuing. It's the base idea behind roaring bitmaps, arguably the best overall
compressedbitmap implementation. <br />