ITAGAKI Takahiro wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
If I'm reading the code correctly, DSM makes no attempt to keep the
chunks ordered by block number. If that's the case, vacuum needs to be
modified because it currently relies on the fact that blocks are scanned
and the dead
Hiroki Kataoka <[EMAIL PROTECTED]> wrote:
> Heikki Linnakangas wrote:
> > But does it
> > work for tables that have a small hot part that's updated very
> > frequently?
>
> I think there is no problem. Bloating will make pages including the
> unnecessary area which will not be accessed. Soo
Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
> We discussed it a long time ago already, but I really wished the DSM
> wouldn't need a fixed size shared memory area. It's one more thing the
> DBA needs to tune manually. It also means we need to have an algorithm
> for deciding what to keep in t
On Wed, Feb 28, 2007 at 04:10:09PM +0900, ITAGAKI Takahiro wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
>
> > At some point it might make sense to convert the FSM into a bitmap; that
> > way everything just scales with database size.
>
> > In the meantime, I'm not sure if it makes sense to
Hello, long time no see.
This topic looks interesting. I'm enrious of Itagaki-san and others.
I can't do now what I want, due to other work that I don't want to do
(isn't my boss seeing this?). I wish I could join the community some
day and contribute to the development like the great experts he
Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
I'd be happier if the DSM content could be
treated as just a hint.
If we don't have a frozen state, we can't use the DSM to implement
index-only scans.
To implement index-only scans, the DSM would have to be exp
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> At some point it might make sense to convert the FSM into a bitmap; that
> way everything just scales with database size.
> In the meantime, I'm not sure if it makes sense to tie the FSM size to
> the DSM size, since each FSM page requires 48x the stora
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> > I'd like to add some kind of logical flavors to max_fsm_pages
> > and max_dsm_pages.
>
> In the meantime, I'm not sure if it makes sense to tie the FSM size to
> the DSM size, since each FSM page requires 48x the storage of a DSM
> page. I think there
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I'd be happier if the DSM content could be
>> treated as just a hint.
> If we don't have a frozen state, we can't use the DSM to implement
> index-only scans.
To implement index-only scans, the DSM would have to be expected to
p
On Tue, Feb 27, 2007 at 05:38:39PM +0900, ITAGAKI Takahiro wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
>
> > > If we do UPDATE a tuple, the original page containing the tuple is marked
> > > as HIGH and the new page where the updated tuple is placed is marked as
> > > LOW.
> >
> > Don't y
On Tue, Feb 27, 2007 at 12:55:21AM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Yes, DSM would make FSM recovery more important, but I thought it was
> > recoverable now? Or is that only on a clean shutdown?
>
> Currently we throw away FSM during any non-clean restart.
Tom Lane wrote:
The main problem with the levels proposed by Takahiro-san is that any
transition from FROZEN to not-FROZEN *must* be exactly recovered,
because vacuum will never visit an allegedly frozen page at all. This
appears to require WAL-logging DSM state changes, which is a pretty
seriou
"Simon Riggs" <[EMAIL PROTECTED]> wrote:
> > If we combine this with the HOT patch, pages with HOT tuples are probably
> > marked as UNFROZEN because we don't bother vacuuming HOT tuples. They can
> > be removed incrementally and doesn't require explicit vacuums.
>
> Perhaps avoid DSM entries fo
Tom Lane <[EMAIL PROTECTED]> wrote:
> Vacuum for XID wraparound would have to hit every page regardless.
There is one problem at this point. If we want to guarantee that there
are no tuples that XIDs are older than pg_class.relfrozenxid, we must scan
all pages for XID wraparound for every vacuum
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> > If we do UPDATE a tuple, the original page containing the tuple is marked
> > as HIGH and the new page where the updated tuple is placed is marked as LOW.
>
> Don't you mean UNFROZEN?
No, the new tuples are marked as LOW. I intend to use UNFROZEN and
On Tue, 2007-02-27 at 00:55 -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Yes, DSM would make FSM recovery more important, but I thought it was
> > recoverable now? Or is that only on a clean shutdown?
>
> Currently we throw away FSM during any non-clean restart. This is
On Tue, 2007-02-27 at 12:05 +0900, ITAGAKI Takahiro wrote:
> If we combine this with the HOT patch, pages with HOT tuples are probably
> marked as UNFROZEN because we don't bother vacuuming HOT tuples. They can
> be removed incrementally and doesn't require explicit vacuums.
Perhaps avoid DSM ent
On Tue, 2007-02-27 at 12:05 +0900, ITAGAKI Takahiro wrote:
> I think it's better to get DSM and HOT together. DSM is good at
> complex updated cases but not at heavily updated cases. HOT has
> opposite aspects, as far as I can see. I think they can cover each
> other.
Very much agreed.
I'll be a
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Yes, DSM would make FSM recovery more important, but I thought it was
> recoverable now? Or is that only on a clean shutdown?
Currently we throw away FSM during any non-clean restart. This is
probably overkill but I'm quite unclear what would be a safe
On Tue, Feb 27, 2007 at 12:05:57PM +0900, ITAGAKI Takahiro wrote:
> Each heap pages have 4 states for dead space map; HIGH, LOW, UNFROZEN and
> FROZEN. VACUUM uses the states to reduce the number of target pages.
>
> - HIGH : High priority to vacuum. Maybe many dead tuples in the page.
> - LOW
This is the second proposal for Dead Space Map (DSM).
Here is the previous discussion:
http://archives.postgresql.org/pgsql-hackers/2006-12/msg01188.php
I'll post the next version of the Dead Space Map patch to -patches.
I've implemented 2bits/page bitmap and new vacuum commands.
Memory management
I can see that there are two issues in the design of Dead Space Map
in the recent discussions:
1. information accuracy of dead spaces
2. memory management
I'll write up the discussion about the 1st for now.
We need to increase page-tracking status for effective vacuum. 1 bit per
block is
On Jan 3, 2007, at 11:42 PM, ITAGAKI Takahiro wrote:
BTW, if we want to achieve the index-only scan, we might have to do
more
aggressive VACUUM FREEZE. There were many comments that we should
avoid
vacuuming pages that contain only unfrozen tuples or a few dead
tuples.
I think it it true for
"Jochem van Dieten" <[EMAIL PROTECTED]> wrote:
> On 12/28/06, ITAGAKI Takahiro wrote:
> >
> > | [TODO item] Allow data to be pulled directly from indexes
> > | Another idea is to maintain a bitmap of heap pages where all rows are
> > | visible to all backends, and allow index lookups to reference
On Sat, 2006-12-30 at 09:22 +1100, Russell Smith wrote:
> Simon Riggs wrote:
> > FSM code ignores any block with less space than 1 average tuple, which
> > is a pretty reasonable rule.
> >
> FSM serves a different purpose than DSM and therefore has an entirely
> different set of rules governing
Simon Riggs wrote:
On Fri, 2006-12-29 at 10:49 -0500, Tom Lane wrote:
"Simon Riggs" <[EMAIL PROTECTED]> writes:
I would suggest that we tracked whether a block has had 0, 1 or 1+
updates/deletes against it. When a block has 1+ it can then be
worthwhile to VACUUM it and to place it onto
On Fri, 2006-12-29 at 16:41 -0500, Tom Lane wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> > On Fri, 2006-12-29 at 10:49 -0500, Tom Lane wrote:
> >> Counterexample: table in which all tuples exceed half a page.
>
> > Current FSM code will ignore those too, if they are less than the
> > avera
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> On Fri, 2006-12-29 at 10:49 -0500, Tom Lane wrote:
>> Counterexample: table in which all tuples exceed half a page.
> Current FSM code will ignore those too, if they are less than the
> average size of the tuple so far requested. Thats a pretty wierd
> c
On Fri, 2006-12-29 at 10:49 -0500, Tom Lane wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> > I would suggest that we tracked whether a block has had 0, 1 or 1+
> > updates/deletes against it. When a block has 1+ it can then be
> > worthwhile to VACUUM it and to place it onto the FSM. Two dead
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> I would suggest that we tracked whether a block has had 0, 1 or 1+
> updates/deletes against it. When a block has 1+ it can then be
> worthwhile to VACUUM it and to place it onto the FSM. Two dead tuples is
> really the minimum space worth reclaiming on a
On Thu, 2006-12-28 at 21:35 +, Heikki Linnakangas wrote:
> I only used 1 bit, just like in Itagaki's approach.
1 bit may not be enough.
In many cases, a block will receive only 1 UPDATE or DELETE. If we then
mark this in the DSM, when we VACUUM that block it will not have
sufficient space f
Gavin Sherry wrote:
On Thu, 28 Dec 2006, Heikki Linnakangas wrote:
ITAGAKI Takahiro wrote:
I experimented with a different DSM design last winter. I got busy with
other things and never posted it AFAIR, but the idea was to store a
bitmap in the special area on every 32k heap page. That had some
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> I experimented with a different DSM design last winter. I got busy with
> other things and never posted it AFAIR, but the idea was to store a
> bitmap in the special area on every 32k heap page. That had some advantages:
> * doesn't require a new
Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
> > We use 1 bit per block, so we cannot separate pages that need either
> > vacuum or freeze only. The reason is that we cannot determine where to
> > record before/after updated tuples. If the transaction is commited,
> > before-version should be re
On 12/28/06, ITAGAKI Takahiro wrote:
| [TODO item] Allow data to be pulled directly from indexes
| Another idea is to maintain a bitmap of heap pages where all rows are
| visible to all backends, and allow index lookups to reference that bitmap
| to avoid heap lookups
It is not done yet, but we
On Thu, 28 Dec 2006, Heikki Linnakangas wrote:
> ITAGAKI Takahiro wrote:
> > Hello,
> >
> > NTT staffs are working on TODO item:
> > | Create a bitmap of pages that need vacuuming
> >
> > We call the bitmap "Dead Space Map" (DSM), that allows VACUUM to scan
> > only pages that need vacuuming or fr
ITAGAKI Takahiro wrote:
Hello,
NTT staffs are working on TODO item:
| Create a bitmap of pages that need vacuuming
We call the bitmap "Dead Space Map" (DSM), that allows VACUUM to scan
only pages that need vacuuming or freezing. We'd like to discuss the
design on hackers and make agreements wit
Hello,
NTT staffs are working on TODO item:
| Create a bitmap of pages that need vacuuming
We call the bitmap "Dead Space Map" (DSM), that allows VACUUM to scan
only pages that need vacuuming or freezing. We'd like to discuss the
design on hackers and make agreements with community.
We implement
On Tue, 28 Feb 2006 01:04:00 -0500, Tom Lane wrote
"Jim C. Nasby" writes:
On Mon, Feb 27, 2006 at 03:05:41PM -0500, Tom Lane wrote:
Moreover, you haven't pointed to any strong reason to adopt this
methodology. It'd only be a win when vacuuming pretty small numbers
of tuples, which is not the d
On Tue, Feb 28, 2006 at 11:58:44AM -0500, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > On Tue, Feb 28, 2006 at 01:18:14AM -0500, Greg Stark wrote:
> > > But I think the thought process went the other direction. If you have the
> > > bit
> > > intended for index scans indicating that the tuple is
Bruce Momjian writes:
> Greg Stark wrote:
> >
> > If you commit each update then your tuple might well be visible to other
> > transactions, how would you check that?
>
> You check the xmin/xmax using standard visibility rules.
Would that really find anything? Even putting aside your own trans
Greg Stark wrote:
>
> Bruce Momjian writes:
>
> > I should be clearer. Suppose you have a table with a single index on
> > the primary key. You are updating the row over and over again (a
> > typical case). You create the first row, commit, then it is updated
> > (two copies), commit, then yo
Greg Stark <[EMAIL PROTECTED]> writes:
> I originally thought you meant if you are repeatedly updating the same record
> within the same transaction. In that case sure you could reuse the space but
> a) only if it's big enough for the new record and b) how often do you really
> do that?
Also, it's
Bruce Momjian writes:
> Heikki Linnakangas wrote:
>> Index tuples have commit info hint bits in them, don't they? You would
>> still have to update those.
> Right, but consider that we would have to use the index to find the
> matching reusable heap to begin with, so we could clear the hint bit.
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> Index tuples have commit info hint bits in them, don't they?
Oooh, I forgot that one, but that's definitely strike 5.
And there's a strike 6: I'm pretty sure this idea breaks unique-index
checking. Somebody else trying to insert a tuple with a dup
Bruce Momjian writes:
> I should be clearer. Suppose you have a table with a single index on
> the primary key. You are updating the row over and over again (a
> typical case). You create the first row, commit, then it is updated
> (two copies), commit, then you update it again. That first c
Bruce Momjian writes:
> Tom Lane wrote:
>> The old row is ALWAYS visible to somebody, until you commit (if you ever
>> do). You can't simply overwrite existing data.
> Huh, I am not suggesting overwriting tuples you created, but tuples
> created by earlier transactions and now invisible to every
Heikki Linnakangas wrote:
> On Tue, 28 Feb 2006, Bruce Momjian wrote:
>
> > Tom Lane wrote:
> >> Bruce Momjian writes:
> >>> Tom Lane wrote:
> MVCC goes out the window, eh? Not to mention transaction rollback
> ability?
> >>
> >>> If the old row is not visible to any transactions, why
On Tue, 28 Feb 2006, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian writes:
Tom Lane wrote:
MVCC goes out the window, eh? Not to mention transaction rollback ability?
If the old row is not visible to any transactions, why would it not work?
The old row is ALWAYS visible to somebody
Tom Lane wrote:
> Bruce Momjian writes:
> > Tom Lane wrote:
> >> MVCC goes out the window, eh? Not to mention transaction rollback ability?
>
> > If the old row is not visible to any transactions, why would it not work?
>
> The old row is ALWAYS visible to somebody, until you commit (if you eve
Bruce Momjian writes:
> Tom Lane wrote:
>> MVCC goes out the window, eh? Not to mention transaction rollback ability?
> If the old row is not visible to any transactions, why would it not work?
The old row is ALWAYS visible to somebody, until you commit (if you ever
do). You can't simply overw
Tom Lane wrote:
> Bruce Momjian writes:
> > One idea Simon and I had was to reuse heap rows where all indexed values
> > in the old row and the new row were the same, meaning the heap value
> > could be replaced without changing the indexes at all. We thought this
> > would be very useful for fre
Bruce Momjian writes:
> One idea Simon and I had was to reuse heap rows where all indexed values
> in the old row and the new row were the same, meaning the heap value
> could be replaced without changing the indexes at all. We thought this
> would be very useful for frequently-updated rows. It
Jim C. Nasby wrote:
> On Tue, Feb 28, 2006 at 01:18:14AM -0500, Greg Stark wrote:
> > But I think the thought process went the other direction. If you have the
> > bit
> > intended for index scans indicating that the tuple is not "in doubt" ie,
> > it's
> > visible to every transaction, then that
Hannu Krosing <[EMAIL PROTECTED]> writes:
> Ãhel kenal päeval, T, 2006-02-28 kell 10:26, kirjutas Tom Lane:
>> The current bgwriter is incapable of looking at catalog contents as
>> such --- it operates only at the level of physical data blocks.
> Would'nt it still be possible to drop a table fr
On Tue, Feb 28, 2006 at 01:18:14AM -0500, Greg Stark wrote:
> But I think the thought process went the other direction. If you have the bit
> intended for index scans indicating that the tuple is not "in doubt" ie, it's
> visible to every transaction, then that also implies the tuple doesn't need t
Ühel kenal päeval, T, 2006-02-28 kell 10:16, kirjutas Alvaro Herrera:
> Hannu Krosing wrote:
>
> > In the "big table with hotspots" case, I doubt that the win from
> > btbulkdelete will outweight having to scan 10 index pages to get to
> > the 30 or 50 that can be bulkdeleted.
>
> Remember th
Ühel kenal päeval, T, 2006-02-28 kell 10:26, kirjutas Tom Lane:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > There is no fundamental reason, why vacuum needs to run in a transaction
> > itselt.
>
> I'll just take one point: you cannot hold locks unless you have a
> transaction, therefore you ca
Hannu Krosing <[EMAIL PROTECTED]> writes:
> Or do you man that an index entry pointing to a non-existing tuple is
> "corruption" ? It would be realtively easy to teach index access method
> to just ignore (or even delete) the dangling index entries.
No, I mean that an index entry pointing at a WRO
Hannu Krosing <[EMAIL PROTECTED]> writes:
> There is no fundamental reason, why vacuum needs to run in a transaction
> itselt.
I'll just take one point: you cannot hold locks unless you have a
transaction, therefore you cannot even look up the catalog info about
the table you wish to vacuum; let a
On Tue, Feb 28, 2006 at 09:52:24AM +0200, Hannu Krosing wrote:
> WHY does vacuum need to be a tranasction ? I thought it was a programmer
> workload optimisation (aka. lazyness :) ) to require ordinary lazy
> vacuum to be in transaction.
>
> There is no fundamental reason, why vacuum needs to run
Hannu Krosing wrote:
> In the "big table with hotspots" case, I doubt that the win from
> btbulkdelete will outweight having to scan 10 index pages to get to
> the 30 or 50 that can be bulkdeleted.
Remember that WAL traffic is fsync'ed, so it can be _much_ slower than
anything else.
--
Alva
On Mon, 27 Feb 2006, Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
On Mon, 27 Feb 2006, Tom Lane wrote:
This strikes me as a fairly bad idea, because it makes VACUUM dependent
on correct functioning of user-written code --- consider a functional
index involving a user-written
Ühel kenal päeval, T, 2006-02-28 kell 10:04, kirjutas Hannu Krosing:
> Ühel kenal päeval, E, 2006-02-27 kell 15:05, kirjutas Tom Lane:
> > Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> > > On Mon, 27 Feb 2006, Tom Lane wrote:
> > >> This strikes me as a fairly bad idea, because it makes VACUUM d
Ühel kenal päeval, T, 2006-02-28 kell 01:04, kirjutas Tom Lane:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > On Mon, Feb 27, 2006 at 03:05:41PM -0500, Tom Lane wrote:
> >> Moreover, you haven't pointed to any strong reason to adopt this
> >> methodology. It'd only be a win when vacuuming prett
Ühel kenal päeval, E, 2006-02-27 kell 15:05, kirjutas Tom Lane:
> Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> > On Mon, 27 Feb 2006, Tom Lane wrote:
> >> This strikes me as a fairly bad idea, because it makes VACUUM dependent
> >> on correct functioning of user-written code --- consider a func
Ühel kenal päeval, E, 2006-02-27 kell 13:17, kirjutas Tom Lane:
> Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> > Vacuum will need to be modified to use index lookups to find index tuples
> > corresponding the dead heap tuples. Otherwise you have to scan through
> > all the indexes anyway.
>
Tom Lane <[EMAIL PROTECTED]> writes:
> Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> > * implementation of index-only scans
>
> > An index scan would not have to check the visibility information of heap
> > tuples on those heap pages that are marked as clean in the dead space map.
> > This r
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Mon, Feb 27, 2006 at 03:05:41PM -0500, Tom Lane wrote:
>> Moreover, you haven't pointed to any strong reason to adopt this
>> methodology. It'd only be a win when vacuuming pretty small numbers
>> of tuples, which is not the design center for VACUUM,
On Mon, Feb 27, 2006 at 03:05:41PM -0500, Tom Lane wrote:
> Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> > On Mon, 27 Feb 2006, Tom Lane wrote:
> >> This strikes me as a fairly bad idea, because it makes VACUUM dependent
> >> on correct functioning of user-written code --- consider a functional
On Mon, Feb 27, 2006 at 01:17:36PM -0500, Tom Lane wrote:
> > * vacuuming pages one by one as they're written by bgwriter
>
> That's not happening. VACUUM has to be a transaction and the bgwriter
> does not run transactions; nor is it in any position to clean out index
> entries associated with a
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> On Mon, 27 Feb 2006, Tom Lane wrote:
>> This strikes me as a fairly bad idea, because it makes VACUUM dependent
>> on correct functioning of user-written code --- consider a functional
>> index involving a user-written function that was claimed to be
On Mon, 27 Feb 2006, Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
Vacuum will need to be modified to use index lookups to find index tuples
corresponding the dead heap tuples. Otherwise you have to scan through
all the indexes anyway.
This strikes me as a fairly bad idea, be
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> Vacuum will need to be modified to use index lookups to find index tuples
> corresponding the dead heap tuples. Otherwise you have to scan through
> all the indexes anyway.
This strikes me as a fairly bad idea, because it makes VACUUM dependent
on
Heikki,
On 2/27/06 9:53 AM, "Heikki Linnakangas" <[EMAIL PROTECTED]> wrote:
> My current implementation stores a bitmap of 32k bits in the special space
> of every 32k heap pages. Each bit in the bitmap corresponds one heap page.
> The bit is set every time a tuple is updated, and it's cleared b
Hi,
The idea of using a so called dead space map to speed up vacuum has come
up multiple times in this list in the last couple of years. I wrote an
initial implementation of it to measure the performance impact it has on
updates and on vacuum.
Potential uses for a dead space map are:
* spee
76 matches
Mail list logo
