pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
6+ messages / 3 participants
[nested] [flat]
* 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
@ 2012-11-29 11:33 Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume Cottenceau @ 2012-11-29 11:33 UTC (permalink / raw)
To: pgsql-performance
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).
Using pg 9.2.1 compiled from sources, almost standard
configuration except shared_buffers at 512M, effective_cache_size
at 1536M, random_page_cost at 2, and vacuum delays increased.
Please find complete logs attached, and selected logs below:
After table creation + analyze:
Index Only Scan using i on ta (cost=0.00..156991.10 rows=2018667 width=4) (actual time=0.034..336.443 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
After vacuum:
Index Only Scan using i on ta (cost=0.00..50882.62 rows=2018667 width=4) (actual time=0.014..193.120 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
After vacuum analyze:
Index Only Scan using i on ta (cost=0.00..50167.13 rows=1990353 width=4) (actual time=0.015..193.035 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
After vacuum full:
Index Only Scan using i on ta (cost=0.00..155991.44 rows=1990333 width=4) (actual time=0.042..364.412 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
^^^^^^^ uh uh, looking bad
After vacuum full analyze:
Index Only Scan using i on ta (cost=0.00..157011.85 rows=2030984 width=4) (actual time=0.025..365.657 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
After vacuum:
Index Only Scan using i on ta (cost=0.00..51192.45 rows=2031000 width=4) (actual time=0.015..192.520 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
Thanks for any comments/hints,
Ref:
[1] http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
--
Guillaume Cottenceau
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
vacfull.sql (application/octet-stream, 715B) download
fulloutput.log (text/plain, 3.5K) download
DROP TABLE ta;
psql:/tmp/vacfull.sql:3: ERROR: table "ta" does not exist
CREATE TABLE ta (ca int, cb int, cc int);
CREATE TABLE
INSERT INTO ta VALUES (generate_series(1, 5), generate_series(1, 10000000), generate_series(1, 10000000));
INSERT 0 10000000
ANALYZE ta;
ANALYZE
CREATE INDEX i ON ta (ca, cb, cc);
CREATE INDEX
EXPLAIN ANALYZE SELECT cb FROM ta WHERE ca = 1 ORDER BY cb;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using i on ta (cost=0.00..156991.10 rows=2018667 width=4) (actual time=0.034..336.443 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
Total runtime: 385.023 ms
(4 rows)
VACUUM ta;
VACUUM
EXPLAIN ANALYZE SELECT cb FROM ta WHERE ca = 1 ORDER BY cb;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using i on ta (cost=0.00..50882.62 rows=2018667 width=4) (actual time=0.014..193.120 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
Total runtime: 241.079 ms
(4 rows)
VACUUM ANALYZE ta;
VACUUM
EXPLAIN ANALYZE SELECT cb FROM ta WHERE ca = 1 ORDER BY cb;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using i on ta (cost=0.00..50167.13 rows=1990353 width=4) (actual time=0.015..193.035 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
Total runtime: 241.101 ms
(4 rows)
VACUUM FULL ta;
VACUUM
EXPLAIN ANALYZE SELECT cb FROM ta WHERE ca = 1 ORDER BY cb;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using i on ta (cost=0.00..155991.44 rows=1990333 width=4) (actual time=0.042..364.412 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
Total runtime: 412.715 ms
(4 rows)
VACUUM FULL ANALYZE ta;
VACUUM
EXPLAIN ANALYZE SELECT cb FROM ta WHERE ca = 1 ORDER BY cb;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using i on ta (cost=0.00..157011.85 rows=2030984 width=4) (actual time=0.025..365.657 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
Total runtime: 414.223 ms
(4 rows)
VACUUM ta;
VACUUM
EXPLAIN ANALYZE SELECT cb FROM ta WHERE ca = 1 ORDER BY cb;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using i on ta (cost=0.00..51192.45 rows=2031000 width=4) (actual time=0.015..192.520 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
Total runtime: 240.918 ms
(4 rows)
DROP TABLE ta;
DROP TABLE
* Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
@ 2012-11-29 11:50 Pavan Deolasee <pavan.deolasee@gmail.com>
parent: Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 1 reply; 6+ messages in thread
From: Pavan Deolasee @ 2012-11-29 11:50 UTC (permalink / raw)
To: Guillaume Cottenceau <gc@mnc.ch>; +Cc: pgsql-performance
On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau <gc@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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
@ 2012-11-29 12:12 Andres Freund <andres@2ndquadrant.com>
parent: Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andres Freund @ 2012-11-29 12:12 UTC (permalink / raw)
To: Pavan Deolasee <pavan.deolasee@gmail.com>; +Cc: Guillaume Cottenceau <gc@mnc.ch>; pgsql-performance
On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote:
> On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau <gc@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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
@ 2012-11-29 12:29 Pavan Deolasee <pavan.deolasee@gmail.com>
parent: Andres Freund <andres@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: Pavan Deolasee @ 2012-11-29 12:29 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: Guillaume Cottenceau <gc@mnc.ch>; pgsql-performance
On Thu, Nov 29, 2012 at 5:42 PM, Andres Freund <andres@2ndquadrant.com>wrote:
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
@ 2012-11-29 12:36 Andres Freund <andres@2ndquadrant.com>
parent: Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andres Freund @ 2012-11-29 12:36 UTC (permalink / raw)
To: Pavan Deolasee <pavan.deolasee@gmail.com>; +Cc: Guillaume Cottenceau <gc@mnc.ch>; pgsql-performance
On 2012-11-29 17:59:39 +0530, Pavan Deolasee wrote:
> On Thu, Nov 29, 2012 at 5:42 PM, Andres Freund <andres@2ndquadrant.com>wrote:
>
> > 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
@ 2012-11-29 12:45 Pavan Deolasee <pavan.deolasee@gmail.com>
parent: Andres Freund <andres@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Pavan Deolasee @ 2012-11-29 12:45 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: Guillaume Cottenceau <gc@mnc.ch>; pgsql-performance
On Thu, Nov 29, 2012 at 6:06 PM, Andres Freund <andres@2ndquadrant.com>wrote:
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-11-29 12:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-29 11:33 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL Guillaume Cottenceau <gc@mnc.ch>
2012-11-29 11:50 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2012-11-29 12:12 ` Andres Freund <andres@2ndquadrant.com>
2012-11-29 12:29 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2012-11-29 12:36 ` Andres Freund <andres@2ndquadrant.com>
2012-11-29 12:45 ` Pavan Deolasee <pavan.deolasee@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox