HOT patch - version 15
On 9/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I see this the other way around: if it doesn't work on system catalogs,
it probably doesn't work, period. I'm not in favor of treating the
catalogs differently.
Please see the revised patches attached. The combo patch applies cleanly
over
current HEAD. The incremental patch contains changes since the last patch.
The patch passes all but three regression tests. The failures are
cosmetic in nature.
Although I am comfortable with the patch, I wanted to send this out today
because tomorrow is my last work day before I go on leave for rest of
the week. I shall try to respond to mails and work a bit during that period,
but I won't be active as usual. I can fix any minor left over things
tomorrow
though.
The patch now supports HOT update on system catalogs. This requires us
to wait for deleting/inserting transaction if we encounter
DELETE_IN_PROGRESS/INSERT_IN_PROGRESS tuple while building an index.
I got worried if this could add a new deadlock scenario, but I believe
system catalog reindexing is not very common, so may be we can live
with that.
Another thing that worries be is the handling of RECENTLY_DEAD tuples
while rebuilding system catalogs indexes. For non-system relations, we
don't make the new index available for queries if any RECENTLY_DEAD
tuples are skipped while building the index. We can do the same for
system catalogs, though we may need more work to make that happen.
But since system catalogs are always run with SnapshotNow, do we need
to worry about RECENTLY_DEAD tuples ? ISTM that we should never return
RECENTLY_DEAD tuples with SnapshotNow. Does this sound ok ?
The changes to heap_update() signature are reverted. The caller can check if
the
new tuple satisfies HeapTupleIsHeapOnly - which signifies that the update
was HOT update. This has also helped us limit the changes to support
system catalogs, because there are several invocation of
simple_heap_update(), followed by CatalogUpdateIndexes(). We can
now check for heap-only tuple in CatalogUpdateIndexes and skip
index inserts for HOT tuples.
As per Tom's suggestion, relcache is now responsible for building list
of HOT attributes. This needs to happen only once unless there are schema
changes. Also, we use just a single bitmap to track normal and system
attributes - offset by FirstLowInvalidHeapAttributeNumber
As per discussion, the page pruning and repair fragmentation is clubbed
together. In the SELECT path, we conditionally try for exclusive lock. If
the exclusive lock is also cleanup lock, we prune and defrag the page.
As the patch stands, during index fetch we try to prune only the chain
originating at that tuple. I am wondering if we should change that
and prune all the tuple chains in the page ?
I haven't removed the avgFSM logic used to time the pruning of
the page. But we can remove that if we feel its not worth the
complexity. May be a simple test of free space as factor of the
BLCKSZ would suffice.
MaxHeapTuplesPerPage is reverted back to the original value.
I could have removed hot_update from xl_heap_update. But that
would have complicated the heap_xlog_update() code. So I
left it unchanged right now. But we still feel thats worth doing, I
will make that change.
The IsPlanValidForThisTransaction() nows uses PlannerGlobal
as per Tom's suggestion, thus making it reentrant. I am not sure if
I have got it completely right though.
Apart from the changes based on recent feedback, I have also
added the previously discussed changes to track dead_space in
the relation and trigger autovaccum when the percentage of
dead_space goes beyond the threshold. vac_update_scale
is still a percentage of dead_space to the relation size.
vac_update_threshold is right now number of blocks, but we need
to discuss and finalize the changes. One good thing is if we do
it this way, autoanalyze trigger will be based on total number of
HOT and non-HOT updates (along with inserts/deletes)
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
Pavan Deolasee wrote:
together. In the SELECT path, we conditionally try for exclusive lock. If
the exclusive lock is also cleanup lock, we prune and defrag the page.
As the patch stands, during index fetch we try to prune only the chain
originating at that tuple. I am wondering if we should change that
and prune all the tuple chains in the page ?
I am thinking we are best just doing the index chain we already have to
read.
If you are modifying the table (like with UPDATE) it makes sense to be
more aggressive and do the whole page because you know there are
probably other table modifications, but for an index lookup there isn't
any knowledge of whether the table is being modified so looking at more
than we need seems like overkill.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
I am thinking we are best just doing the index chain we already have to
read.
If you are modifying the table (like with UPDATE) it makes sense to be
more aggressive and do the whole page because you know there are
probably other table modifications, but for an index lookup there isn't
any knowledge of whether the table is being modified so looking at more
than we need seems like overkill.
Uh, why would any of this code at all execute during a pure lookup?
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
I am thinking we are best just doing the index chain we already have to
read.If you are modifying the table (like with UPDATE) it makes sense to be
more aggressive and do the whole page because you know there are
probably other table modifications, but for an index lookup there isn't
any knowledge of whether the table is being modified so looking at more
than we need seems like overkill.Uh, why would any of this code at all execute during a pure lookup?
No idea. It seems an index lookup tries to prune a heap chain, and he
was asking if it should look at other chains on the page; I said not.
Whether the index lookup should prune the heap chain is another issue.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
Uh, why would any of this code at all execute during a pure lookup?
No idea. It seems an index lookup tries to prune a heap chain, and he
was asking if it should look at other chains on the page; I said not.
Whether the index lookup should prune the heap chain is another issue.
ISTM the only time we should be doing HOT cleanup work is when we are
trying to insert a new tuple on that page --- and maybe even only when
we try and it doesn't fit straight off. Otherwise you're pushing
maintenance work into foreground query pathways, which is exactly where
I don't think we should be headed.
regards, tom lane
"Bruce Momjian" <bruce@momjian.us> writes:
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
I am thinking we are best just doing the index chain we already have to
read.If you are modifying the table (like with UPDATE) it makes sense to be
more aggressive and do the whole page because you know there are
probably other table modifications, but for an index lookup there isn't
any knowledge of whether the table is being modified so looking at more
than we need seems like overkill.Uh, why would any of this code at all execute during a pure lookup?
No idea. It seems an index lookup tries to prune a heap chain, and he
was asking if it should look at other chains on the page; I said not.
Whether the index lookup should prune the heap chain is another issue.
Pruning chains is kind of the whole point of the exercise no?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
Uh, why would any of this code at all execute during a pure lookup?
No idea. It seems an index lookup tries to prune a heap chain, and he
was asking if it should look at other chains on the page; I said not.
Whether the index lookup should prune the heap chain is another issue.ISTM the only time we should be doing HOT cleanup work is when we are
trying to insert a new tuple on that page --- and maybe even only when
we try and it doesn't fit straight off. Otherwise you're pushing
maintenance work into foreground query pathways, which is exactly where
I don't think we should be headed.
Ah, as I understand it you can't actually do the pruning then because the
executor holds references to source tuple on the page. In other words you can
never "get the vacuum lock" there because you already have the page pinned
yourself.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
On 9/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
ISTM the only time we should be doing HOT cleanup work is when we are
trying to insert a new tuple on that page --- and maybe even only when
we try and it doesn't fit straight off. Otherwise you're pushing
maintenance work into foreground query pathways, which is exactly where
I don't think we should be headed.Ah, as I understand it you can't actually do the pruning then because the
executor holds references to source tuple on the page. In other words you
can
never "get the vacuum lock" there because you already have the page pinned
yourself.
I don't think executor holding a reference is a problem because when
we check for vacuum lock, we have already pinned the page anyways.
But moving the old tuple around deep down in the UPDATE code path
(when we realize that there is no free space) is an issue. I know Heikki
tried to do it this way - but then moved the pruning code to lookup
code. Heikki ?
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates. Every lookup to the
visible tuple in this chain would be CPU expensive since it would require
long chain following.
The downside is of course that SELECT may occasionally do more work.
But since we ensure that we do the extra work only when there is no
contention on the page, ISTM that the downside is limited.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
"Pavan Deolasee" <pavan.deolasee@gmail.com> writes:
On 9/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
Ah, as I understand it you can't actually do the pruning then because the
executor holds references to source tuple on the page. In other words you
can never "get the vacuum lock" there because you already have the page
pinned yourself.I don't think executor holding a reference is a problem because when
we check for vacuum lock, we have already pinned the page anyways.
But that's the point. Even though the pin-count is 1 and it may look like we
have the vacuum lock we really don't. The fact that the buffer was already
pinned by us means that there are already pointers around referencing tuples
on that page. If we move them around those pointers become garbage. In fact
Postgres avoids copying data if it can get by with a pointer at the original
tuple on disk so some of the pointers will be Datums pointing at individual
columns in the tuples in the page.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Pavan Deolasee wrote:
On 9/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
ISTM the only time we should be doing HOT cleanup work is when we are
trying to insert a new tuple on that page --- and maybe even only when
we try and it doesn't fit straight off. Otherwise you're pushing
maintenance work into foreground query pathways, which is exactly where
I don't think we should be headed.Ah, as I understand it you can't actually do the pruning then because the
executor holds references to source tuple on the page. In other words you
can
never "get the vacuum lock" there because you already have the page pinned
yourself.I don't think executor holding a reference is a problem because when
we check for vacuum lock, we have already pinned the page anyways.
But moving the old tuple around deep down in the UPDATE code path
(when we realize that there is no free space) is an issue. I know Heikki
tried to do it this way - but then moved the pruning code to lookup
code. Heikki ?
When I suggested that we get rid of the LP_DELETE flag for heap tuples,
the tuple-level fragmentation and all that, and just take the vacuum
lock and call PageRepairFragmentation, I was thinking that we'd do it in
heap_update and only when we run out of space on the page. But as Greg
said, it doesn't work because you're already holding a reference to at
least one tuple on the page, the one you're updating, by the time you
get to heap_update. That's why I put the pruning code to heap_fetch
instead. Yes, though the amortized cost is the same, it does push the
pruning work to the foreground query path.
That was a reason for separating the pruning and defragmenting a page.
We could do the pruning in heap_update, and only do the defragmenting in
heap_fetch. I was also thinking of an optimization to the pruning, so
that while we scan the page and remove dead tuples, we also check if we
leave behind an empty gap that's big enough to accommodate the new tuple
we're inserting, and reuse that space immediately, without defragmenting.
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates. Every lookup to the
visible tuple in this chain would be CPU expensive since it would require
long chain following.
Yes. For that as well, we could prune only, but not defragment, the page
in the lookup path.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
When I suggested that we get rid of the LP_DELETE flag for heap tuples,
the tuple-level fragmentation and all that, and just take the vacuum
lock and call PageRepairFragmentation, I was thinking that we'd do it in
heap_update and only when we run out of space on the page. But as Greg
said, it doesn't work because you're already holding a reference to at
least one tuple on the page, the one you're updating, by the time you
get to heap_update. That's why I put the pruning code to heap_fetch
instead. Yes, though the amortized cost is the same, it does push the
pruning work to the foreground query path.
The amortized cost is only "the same" if every heap_fetch is associated
with a heap update. I feel pretty urgently unhappy about this choice.
Have you tested the impact of the patch on read-mostly workloads?
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates.
How is that, if the same number of prune attempts would occur?
regards, tom lane
Tom Lane wrote:
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
When I suggested that we get rid of the LP_DELETE flag for heap tuples,
the tuple-level fragmentation and all that, and just take the vacuum
lock and call PageRepairFragmentation, I was thinking that we'd do it in
heap_update and only when we run out of space on the page. But as Greg
said, it doesn't work because you're already holding a reference to at
least one tuple on the page, the one you're updating, by the time you
get to heap_update. That's why I put the pruning code to heap_fetch
instead. Yes, though the amortized cost is the same, it does push the
pruning work to the foreground query path.The amortized cost is only "the same" if every heap_fetch is associated
with a heap update. I feel pretty urgently unhappy about this choice.
Have you tested the impact of the patch on read-mostly workloads?
I haven't. Someone should. We have a tester working on a test suite with
many small CPU-bound performance test cases; hopefully we'll get those
test cases and results out soon.
Assuming the rule for when to prune would be the same whether we do it
in heap_fetch or heap_update, I don't see how the total cost would be
different. (that's a bad assumption, though, see below)
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates.How is that, if the same number of prune attempts would occur?
It wouldn't. To avoid the long HOT chains, we want to prune more often
than what's needed to just make room for updates. I'm not sure what the
exact rules are in the current patch.
That's a pretty sensitive tradeoff, we want to prune often to cut the
long HOT chains, but not too often because it's pretty expensive to
acquire the vacuum lock and move tuples around. I don't think we've
found the optimal solution yet. Separating the pruning and defragmenting
might help.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
Tom Lane wrote:
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates.How is that, if the same number of prune attempts would occur?
It wouldn't. To avoid the long HOT chains, we want to prune more often
than what's needed to just make room for updates.
I don't follow. HOT chains can only get longer by updates.
regards, tom lane
Heikki Linnakangas wrote:
That's a pretty sensitive tradeoff, we want to prune often to cut the
long HOT chains, but not too often because it's pretty expensive to
acquire the vacuum lock and move tuples around. I don't think we've
found the optimal solution yet. Separating the pruning and defragmenting
might help.
Does defragmenting force writing a full page image to the WAL afterwards?
Or does it just log the fact that the page was defragmented, and the actual
work is redone on recovery?
In the first case, over-zealous defragmenting might be costly in terms of
WAL traffic too, not only in term of CPU usage.
greetings, Florian Pflug
Tom Lane wrote:
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
Tom Lane wrote:
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates.How is that, if the same number of prune attempts would occur?
It wouldn't. To avoid the long HOT chains, we want to prune more often
than what's needed to just make room for updates.I don't follow. HOT chains can only get longer by updates.
Yes. We don't seem to be on the same wavelength...
Imagine a page with just one tuple on it:
1
After a bunch of updates, it looks like this
1 -> 2 -> 3 -> 4 -> 5
1 is the tuple the indexes point to, others are heap only.
Every time you do an index lookup, you have to follow that chain from 1
to 5. Which gets expensive. That's why we want to prune the chain to
make it shorter, even if there's still plenty of room on the page for
updates, and even if we're never going to update it again.
That's the theory. I don't know *how* expensive following the chain
really is, compared to index scans skipping over entries marked as
killed. I don't remember seeing any measurements of that.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Florian Pflug wrote:
Heikki Linnakangas wrote:
That's a pretty sensitive tradeoff, we want to prune often to cut the
long HOT chains, but not too often because it's pretty expensive to
acquire the vacuum lock and move tuples around. I don't think we've
found the optimal solution yet. Separating the pruning and defragmenting
might help.Does defragmenting force writing a full page image to the WAL afterwards?
Or does it just log the fact that the page was defragmented, and the actual
work is redone on recovery?
No, you just log a note that it was defragmented.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
Tom Lane wrote:
I don't follow. HOT chains can only get longer by updates.
Yes. We don't seem to be on the same wavelength...
Imagine a page with just one tuple on it:
1
After a bunch of updates, it looks like this
1 -> 2 -> 3 -> 4 -> 5
1 is the tuple the indexes point to, others are heap only.
But if we were attempting prune at every update, at least some of the
later updates should have managed to prune. "2" should certainly be
gone at this point, unless there's lots of contention for the page,
in which case pruning at select won't make things better.
regards, tom lane
Heikki Linnakangas escribi�:
Every time you do an index lookup, you have to follow that chain from 1
to 5. Which gets expensive. That's why we want to prune the chain to
make it shorter, even if there's still plenty of room on the page for
updates, and even if we're never going to update it again.That's the theory. I don't know *how* expensive following the chain
really is, compared to index scans skipping over entries marked as
killed. I don't remember seeing any measurements of that.
I suggest you let that be. Chain following can't be *that* expensive --
and if it is, then pruning would be a lot more expensive, so it's not a
thing you want to be doing for every heap_fetch.
Pruning is going to take place on next vacuum anyway, isn't it?
AFAIUI the point is that you can't do cleanup selectively only on UPDATE
because of pointers that your process may already have to the page,
which is why you wanted to do it on every heap_fetch. I suggest it is
optimization which can be left for a later version (8.4?)
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Tom Lane wrote:
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
Imagine a page with just one tuple on it:
1
After a bunch of updates, it looks like this
1 -> 2 -> 3 -> 4 -> 5
1 is the tuple the indexes point to, others are heap only.
But if we were attempting prune at every update, at least some of the
later updates should have managed to prune. "2" should certainly be
gone at this point, unless there's lots of contention for the page,
in which case pruning at select won't make things better.
Oh I see. Yeah, hopefully you don't end up with long chains too often.
You don't need contention for it, though, a long-running transaction
will cause it. Or a transaction that inserts a tuple and updates it
multiple times in the same transaction.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Alvaro Herrera wrote:
Pruning is going to take place on next vacuum anyway, isn't it?
Yes. If HOT is working well, you're not going to run vacuum very often,
though.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
