From: Jesper Krogh <jesper@krogh.cc>
To: Robert Haas <robertmhaas@gmail.com>
To: pgsql-hackers@postgresql.org
Subject: Re: crash-safe visibility map, take four
Date: Wed, 23 Mar 2011 07:16:12 +0100
Message-ID: <4D89902C.7000004@krogh.cc> (raw)
In-Reply-To: <AANLkTino+UehSpXz+-ZP4Q58mSjW_JHx7Q4GUiPfRbC2@mail.gmail.com>
References: <AANLkTino+UehSpXz+-ZP4Q58mSjW_JHx7Q4GUiPfRbC2@mail.gmail.com>
On 2011-03-22 21:43, Robert Haas wrote:
> I took a crack at implementing the first approach described above,
> which seems to be by far the simplest idea we've come up with to date.
> Patch attached. It doesn't seem to be that complicated, which could
> mean either that it's not that complicated or that I'm missing
> something. Feel free to point and snicker in the latter case.
Looks simple, but there is now benefit on the usage side in the patch,
so it isn't really "testable" yet? I would love to spend some time testing
when its doable (even with rough corners.)
I'm still a bit puzzled with how it would end up working with a page-level
visibillity map bit for index-scans. There is a clear "drop off" in
usabillity
when the change rates of the table goes up, which may or may not be
relevant, but I cannot really judge, since I haven't even got a ballpark
figure about how much table churn would disable say 50% of the usage.
= Really naive suggestion approaching =
Another layout might be to simply drag out t_xmin, t_xmax pr row (8 bytes)
into a table by itself. This table will be way bigger than the one bit
per page
map, but could be "wal-logged" as any other change in the system?
It would, by definition make the visibility testing work (way faster
than today),
no matter how fast the underlying table changes.
State of today (PG 8.4) is that a query like this:
testdb=# select count(id) from testdb.seq where fts @@ to_tsquery('tag');
count
-------
69753
(1 row)
Time: 5863.600 ms
testdb=# select count(id) from testdb.seq where fts @@ to_tsquery('tag');
count
-------
69753
(1 row)
Time: 659.832 ms
testdb=# select count(id) from testdb.seq where fts @@ to_tsquery('tag');
count
-------
69753
(1 row)
Time: 1005.765 ms
Somewhere around 15ns / tuple (not bad at all).
(the first was probably "half warm")
The "average" rows per tuple is somewhere between 4 and 8 for this
table, assuming
8 and that the 69K are randomly distributed among the 16M other tuples
(fair assumption
in this case). The 600-1000ms for the fresh cache run are the timing to
drag:
69753*8192 (page size) = 571MB into memory for visibillity testing
alone, on warm cache
all pages being in main memory. Packing 16M tuples with 8 bytes / tuple
in a map would be
around 128MB.
given 8 bytes/row and random distribution of data, that would require us
to read all 128MB,
so a speedup of x4 on this example, but it would rougly let us count the
entire table in
the same time.
With regard to disk vs. memory hotness.. those 128MB compares to a table
size of 32GB
(with a toast table next to it of 64GB) but that shouldn't be touched by
above query.
The ns/tuple number (today) on a "thin" table in my system is
approaching 1ns / tuple.
If the page-level bitmap would be set "quite fast" on a fairly busy
system anyway, then
the above is just noise in the air, but I have currently no feeling, and
there is
some math in there I have trouble setting reliable ballpark numbers on.
There is, by all approaches room for significant improvements for the
visibillity
testing for a huge range of installations.
Can I drag out numbers of "frozenness of tuples" from my current systems
to fill in the
discussion? (how?)
Jesper
--
Jesper
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jesper@krogh.cc, robertmhaas@gmail.com
Subject: Re: crash-safe visibility map, take four
In-Reply-To: <4D89902C.7000004@krogh.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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