Re: [PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL
On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau g...@mnc.ch wrote: Hello, I am toying around with 9.2.1, trying to measure/determine how index-only scans can improve our performance. A small script which is attached to this mail, shows that as long as the table has been VACUUM FULL'd, there is a unusual high amount of heap fetches. It is strange that the visibilitymap_test predicate fails in these situations, is the visibility map somehow trashed in this situation? It should not, or at least the documentation[1] should state it (my understanding is that vacuum full does *more* than vacuum, but nothing less) (note to usual anti vacuum full trollers: I know you hate vacuum full). I don't find it very surprising given that VACUUM FULL is now implemented as a CLUSTER command which rewrites the entire heap, thus invalidating all the visibility map info whatsoever. The code paths that VACUUM FULL and LAZY VACUUM takes are now completely different. Even with the old VACUUM FULL we would have seen some impact on heap fetches because it used to move tuples around and thus potentially resetting visibility map bits. But its definitely going to be worse with the new implementation. Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits set to visible, thats an entirely different question. I don't think it can, but then I haven't thought through this completely. Thanks, Pavan
Re: [PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL
On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote: On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau g...@mnc.ch wrote: Hello, I am toying around with 9.2.1, trying to measure/determine how index-only scans can improve our performance. A small script which is attached to this mail, shows that as long as the table has been VACUUM FULL'd, there is a unusual high amount of heap fetches. It is strange that the visibilitymap_test predicate fails in these situations, is the visibility map somehow trashed in this situation? It should not, or at least the documentation[1] should state it (my understanding is that vacuum full does *more* than vacuum, but nothing less) (note to usual anti vacuum full trollers: I know you hate vacuum full). I don't find it very surprising given that VACUUM FULL is now implemented as a CLUSTER command which rewrites the entire heap, thus invalidating all the visibility map info whatsoever. Me neither. Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits set to visible, thats an entirely different question. I don't think it can, but then I haven't thought through this completely. It can't set everything to visible as it also copies RECENTLY_DEAD tuples and tuples which are not yet visible to other transactions, but it should be relatively easy to keep enough information about whether it can set the current page to all visible. At least for the data in the main relation, the toast tables are a different matter. Just tracking whether the page in rewriteheap.c's state-rs_buffer contains only tuples that are clearly visible according to the xmin horizon seems to be enough. The current effect of resetting the VM has the disadvantage of making the next autovacuum basically a full table vacuum without any benefits... Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL
On Thu, Nov 29, 2012 at 5:42 PM, Andres Freund and...@2ndquadrant.comwrote: On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote: Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits set to visible, thats an entirely different question. I don't think it can, but then I haven't thought through this completely. It can't set everything to visible as it also copies RECENTLY_DEAD tuples and tuples which are not yet visible to other transactions, but it should be relatively easy to keep enough information about whether it can set the current page to all visible. Yeah, that looks fairly easy to have. Thinking about it more, now that we have ability to skip WAL for the case when a table is created and populated in the same transaction, we could also set the visibility map bits for such a table (if we are not doing that already). That should be fairly safe too. Thanks, Pavan
Re: [PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL
On 2012-11-29 17:59:39 +0530, Pavan Deolasee wrote: On Thu, Nov 29, 2012 at 5:42 PM, Andres Freund and...@2ndquadrant.comwrote: On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote: Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits set to visible, thats an entirely different question. I don't think it can, but then I haven't thought through this completely. It can't set everything to visible as it also copies RECENTLY_DEAD tuples and tuples which are not yet visible to other transactions, but it should be relatively easy to keep enough information about whether it can set the current page to all visible. Yeah, that looks fairly easy to have. Thinking about it more, now that we have ability to skip WAL for the case when a table is created and populated in the same transaction, we could also set the visibility map bits for such a table (if we are not doing that already). That should be fairly safe too. I don't think the latter would be safe at all. Every repeatable read transaction that started before the table creation would see that tables content based on the visibilitymap instead of seeing it as empty. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL
On Thu, Nov 29, 2012 at 6:06 PM, Andres Freund and...@2ndquadrant.comwrote: On 2012-11-29 17:59:39 +0530, Pavan Deolasee wrote: Yeah, that looks fairly easy to have. Thinking about it more, now that we have ability to skip WAL for the case when a table is created and populated in the same transaction, we could also set the visibility map bits for such a table (if we are not doing that already). That should be fairly safe too. I don't think the latter would be safe at all. Every repeatable read transaction that started before the table creation would see that tables content based on the visibilitymap instead of seeing it as empty. Yeah, but that should be easy to fix, no ? We know the transaction that created the table and we can check if that transaction is visible to our snapshot or not. If the creating transaction itself is not visible, the data in the table is not visible either. OTOH if the creating transaction is visible and is committed, we can trust the visibility map as well. Thats probably better than scanning the entire table just to find that we can/can't see all/any rows. Its getting slightly off-topic, so my apologies anyways. Thanks, Pavan
