Обсуждение: Multi-Versions and Vacuum
Thanks to all the people that responed. Summaries and notes:-
VACUUM
The consensus seems to be that old copies are not removed until a Vacuum is
performed. When a Vacuum is performed then only versions older than the
start of the oldest transaction are removed.
(Compare with Oracle -- a certain amount of space is allocated for multi
versions ("Rollback Segments"). Old versions are moved there, I think.
When the space is exhausted then then old versions are dropped
automatically. Occasionally this can cause transactions to be aborted
because the old versions they need are no longer available.)
What actually is the difference between Vaccum and Vacuum Full? I assume
that the former must release some disk space, otherwise it would be useless?
STORAGE
It appears that the versions are stored in the same blocks as the normal
data. Also, seems that the whole row is copied (correct?). This will
reduce the efficiency of the cache to some extent by having blocks
containing non-pertinent data.
LOCKING
The docs mentioned phantoms in the Serializable section, implying that they
were not trapped by Read Committed. But they are, good.
My note about Oracle is that in its Read Committed mode transactions are
safer without being Serializable. It is half way between what Postgres call
Read Committed and Serializable. And if I think that in that regard Oracle
got it right. Safer transactions without Serializable rollback problems
with no real downside. Particularly for reports.
USING THE LOGS FOR MULTI VERSIONS
If anyone know why this is a bad idea I'd be interested. Seems to be a win
win to me. No need to vacuum, no additional overhead during update (you
need the logs anyway), fast queries, less disk space. (This is not what
Oracle does.)
TIME TRAVEL
Sounds fun. But hard to get right.
EXISTING DOCS
When this thread settles I'll add a note to the interactive docs.
(Thanks Neil for taking the trouble to provide doc fragments. But if you
read them again you will note that they are not at all clear on the actual
question that I asked. Which is why I asked them.)
On Thu, Jul 18, 2002 at 09:23:48PM -0700, Anthony Berglas wrote:
> (Compare with Oracle -- a certain amount of space is allocated for multi
> versions ("Rollback Segments"). Old versions are moved there, I think.
> When the space is exhausted then then old versions are dropped
> automatically. Occasionally this can cause transactions to be aborted
> because the old versions they need are no longer available.)
IMHO, that is much worse than the scheme we have now -- particularly
since some kind of auto-vacuuming system is on the horizon.
> What actually is the difference between Vaccum and Vacuum Full? I assume
> that the former must release some disk space, otherwise it would be useless?
http://developer.postgresql.org/docs/postgres/sql-vacuum.html , in
particular:
VACUUM FULL does more extensive processing, including moving of tuples
across blocks to try to compact the table to the minimum number of disk
blocks. This form is much slower and requires an exclusive lock on each
table while it is being processed.
There is more information on VACUUM FULL here:
http://developer.postgresql.org/docs/postgres/routine-vacuuming.html
> EXISTING DOCS
>
> When this thread settles I'll add a note to the interactive docs.
Might be a better idea to send a patch for the SGML to pgsql-patches. Or
if you'd rather not do that, just send any additions you'd like to me
and I'll make the necessary SGML changes and send that to -patches.
> (Thanks Neil for taking the trouble to provide doc fragments. But if you
> read them again you will note that they are not at all clear on the actual
> question that I asked. Which is why I asked them.)
On the contrary, 2 of your 3 questions were clearly answered in the
docs, as is the VACUUM FULL question you just asked. If you think the
docs are unclear or incomplete, can you be more specific?
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Anthony Berglas wrote:
>
> Thanks to all the people that responed. Summaries and notes:-
>
> LOCKING
>
> The docs mentioned phantoms in the Serializable section, implying
> that they were not trapped by Read Committed. But they are, good.
>
> My note about Oracle is that in its Read Committed mode transactions
> are safer without being Serializable.
What's safer ?
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
If Oracle uses multiple version concurrency, then why does it appear to do
row locking (I use Oracle at work, but I do not admin it)
The only downside I have seen to MVCS is the extra disk space, but with
the performance improvements in a highly concurrent use environment, it is
well worth it.
Q: Does VACUUM FULL remove the rows that are too many for the FSM? Or,
are they just stuck? I know that regular VACUUM will not allow reuse of
overflowed rows. Also, how does one calculate the propper size for the
FSM?
______________________________________________________________________________
Your mouse has moved.
You must restart Windows for your changes to take effect.
#!/usr/bin/perl
print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);
grant wrote: > If Oracle uses multiple version concurrency, then why does it appear to do > row locking (I use Oracle at work, but I do not admin it) > > The only downside I have seen to MVCS is the extra disk space, but with > the performance improvements in a highly concurrent use environment, it is > well worth it. > > Q: Does VACUUM FULL remove the rows that are too many for the FSM? Or, > are they just stuck? I know that regular VACUUM will not allow reuse of > overflowed rows. Also, how does one calculate the propper size for the > FSM? VACUUM full removes all unused rows from disk _including_ ones that aren't in the free space map. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> grant wrote:
>> Q: Does VACUUM FULL remove the rows that are too many for the FSM? Or,
>> are they just stuck? I know that regular VACUUM will not allow reuse of
>> overflowed rows.
You seem to know things that aren't true. What is an "overflowed row"
anyway?
> VACUUM full removes all unused rows from disk _including_ ones that
> aren't in the free space map.
The FSM isn't interested in individual rows in the first place; it just
records the amount of free space per *page*.
After VACUUM does its thing (and yes, it removes everything it legally
can) it reports the amount of free space it found on each page to the
FSM, and the FSM remembers as much of that info as it has room for ---
preferring pages with more free space, if it has to be selective.
Subsequently, when any backend needs to add a tuple to a relation, it
first looks in the FSM to see if there is an extant page with enough
room. If there's no suitable entry in the FSM, then try the last extant
page; if no luck there, add a new page.
regards, tom lane
On Fri, Jul 19, 2002 at 12:35:23PM -0400, Tom Lane wrote:
> After VACUUM does its thing (and yes, it removes everything it legally
> can) it reports the amount of free space it found on each page to the
> FSM, and the FSM remembers as much of that info as it has room for ---
> preferring pages with more free space, if it has to be selective.
I think this is where the confusion lies. The ever-growing tables
that people are experiencing is somehow related to the new
non-blocking vacuum and the value of the free space map settings.
Previous posts on the matter have left some (well, me, at least) with
the idea that the non-blocking vacuum doesn't get everything, just in
case the free space map values are too small.
But if I understand now what you're saying, it's this: the vacuum
_does_ get everything, but if the free space map settings are too
small, the free space map doesn't know about that free space;
therefore, the space never gets re-used for future data, because as
far as the postmaster is concerened, the space isn't available. So
the tables keep growing because they have to append their data at the
end of the file (because the free space map hasn't reported any free
space). Run vacuum a few times like this, and there is a substantial
amount of unused space in the tables, where the free space map
doesn't have an entry for it. Is that right?
If so, it should be possible to calculate what one needs for the
max_fsm_pages setting by calculating how much data is replaced or
deleted in any given period, vacuum-to-vacuum, I guess. What one
would need to do is calculate how much space a row is taking up on
disk, and then set the max_fsm_pages to comprise (almost?) enough
space to keep a map big enough to include all the rows likely to be
recovered by vacuum. Or am I completely mad? (I know, I know, "Yes"
is a possible answer there.)
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes:
> But if I understand now what you're saying, it's this: the vacuum
> _does_ get everything, but if the free space map settings are too
> small, the free space map doesn't know about that free space;
> therefore, the space never gets re-used for future data, because as
> far as the postmaster is concerened, the space isn't available. So
> the tables keep growing because they have to append their data at the
> end of the file (because the free space map hasn't reported any free
> space). Run vacuum a few times like this, and there is a substantial
> amount of unused space in the tables, where the free space map
> doesn't have an entry for it. Is that right?
Well, the design theory was that once you free enough space on a
particular page, it *will* get into the FSM and then will be filled up.
The fact that small amounts of free space don't get re-used quickly
doesn't bother me a whole lot; the table size should still stabilize
at some proportion of free to used space that depends on your vacuum
frequency.
However, if the FSM is vastly smaller than the number of
actively-changing pages in your database, then that argument breaks down
--- in particular, if the FSM can't even keep track of all the
completely-empty pages in your database then you are going to suffer
progressive leakage. I'm not sure where the problem gets bad enough to
worry about, and have not had time to try to do a probabilistic
analysis.
If we could determine where trouble sets in, perhaps we could detect the
situation and print warnings during VACUUM? Another possibility is to
auto-size FSM during postmaster startup, perhaps as a fraction of the
total DB size measured by "du". (But that probably doesn't work if
you've been playing symlink games.) In any case we need more knowledge
about appropriate FSM sizes than we have at the moment.
The whole FSM concept is new in 7.2 and I'm sure it still needs
refinement. Ideas welcome.
regards, tom lane
So, then, even if there is an overflow of pages with free space, stepping up the vacuum schedule may help this eventually catch up. As for an automated way to set this up, if the FSM size cannot be adjusted without a restart of the postmaster, then on startup, you could take a number of pages currently with free space or a percentage of the number of pages, which ever is greater. The percentage could have a default, and also be configurable.
grant wrote: > > So, then, even if there is an overflow of pages with free space, stepping > up the vacuum schedule may help this eventually catch up. Depends. If you only UPDATE that table, there will never be less freespace again, unless you VACUUM FULL. In average every UPDATE frees as much space as it reuses. If your data grows, via INSERTS, then yes, it should catch up someday if you VACUUM often enough. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Fri, Jul 19, 2002 at 02:11:09PM -0400, Tom Lane wrote:
> However, if the FSM is vastly smaller than the number of
> actively-changing pages in your database, then that argument breaks down
> --- in particular, if the FSM can't even keep track of all the
> completely-empty pages in your database then you are going to suffer
> progressive leakage.
That's consistent with the reports people have made, because it was
always in the case of databases with extremely high turnover that it
turned up. So at the very least, people who anticipate high turnover
on their systems should set the free space map higher (and maybe
vacuum more often -- AFAIK, though, no-one has yet documented the
performance penalty of non-blocking vacuum. I guess it should be no
worse than any other client, but I'm not sure, and haven't tested
yet).
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Added to TODO:
* Allow free space map to be auto-sized or warn when it is too small
---------------------------------------------------------------------------
Andrew Sullivan wrote:
> On Fri, Jul 19, 2002 at 12:35:23PM -0400, Tom Lane wrote:
>
> > After VACUUM does its thing (and yes, it removes everything it legally
> > can) it reports the amount of free space it found on each page to the
> > FSM, and the FSM remembers as much of that info as it has room for ---
> > preferring pages with more free space, if it has to be selective.
>
> I think this is where the confusion lies. The ever-growing tables
> that people are experiencing is somehow related to the new
> non-blocking vacuum and the value of the free space map settings.
> Previous posts on the matter have left some (well, me, at least) with
> the idea that the non-blocking vacuum doesn't get everything, just in
> case the free space map values are too small.
>
> But if I understand now what you're saying, it's this: the vacuum
> _does_ get everything, but if the free space map settings are too
> small, the free space map doesn't know about that free space;
> therefore, the space never gets re-used for future data, because as
> far as the postmaster is concerened, the space isn't available. So
> the tables keep growing because they have to append their data at the
> end of the file (because the free space map hasn't reported any free
> space). Run vacuum a few times like this, and there is a substantial
> amount of unused space in the tables, where the free space map
> doesn't have an entry for it. Is that right?
>
> If so, it should be possible to calculate what one needs for the
> max_fsm_pages setting by calculating how much data is replaced or
> deleted in any given period, vacuum-to-vacuum, I guess. What one
> would need to do is calculate how much space a row is taking up on
> disk, and then set the max_fsm_pages to comprise (almost?) enough
> space to keep a map big enough to include all the rows likely to be
> recovered by vacuum. Or am I completely mad? (I know, I know, "Yes"
> is a possible answer there.)
>
> A
>
> --
> ----
> Andrew Sullivan 87 Mowat Avenue
> Liberty RMS Toronto, Ontario Canada
> <andrew@libertyrms.info> M6K 3E3
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom Lane wrote: > Andrew Sullivan <andrew@libertyrms.info> writes: > > But if I understand now what you're saying, it's this: the vacuum > > _does_ get everything, but if the free space map settings are too > > small, the free space map doesn't know about that free space; > > therefore, the space never gets re-used for future data, because as > > far as the postmaster is concerened, the space isn't available. So > > the tables keep growing because they have to append their data at the > > end of the file (because the free space map hasn't reported any free > > space). Run vacuum a few times like this, and there is a substantial > > amount of unused space in the tables, where the free space map > > doesn't have an entry for it. Is that right? > > Well, the design theory was that once you free enough space on a > particular page, it *will* get into the FSM and then will be filled up. > The fact that small amounts of free space don't get re-used quickly > doesn't bother me a whole lot; the table size should still stabilize > at some proportion of free to used space that depends on your vacuum > frequency. > > However, if the FSM is vastly smaller than the number of > actively-changing pages in your database, then that argument breaks down > --- in particular, if the FSM can't even keep track of all the > completely-empty pages in your database then you are going to suffer > progressive leakage. I'm not sure where the problem gets bad enough to > worry about, and have not had time to try to do a probabilistic > analysis. > > If we could determine where trouble sets in, perhaps we could detect the > situation and print warnings during VACUUM? Another possibility is to > auto-size FSM during postmaster startup, perhaps as a fraction of the > total DB size measured by "du". (But that probably doesn't work if > you've been playing symlink games.) In any case we need more knowledge > about appropriate FSM sizes than we have at the moment. > > The whole FSM concept is new in 7.2 and I'm sure it still needs > refinement. Ideas welcome. Not sure if I have said this before, but I think the auto-sizing needs to get some feedback from when empty pages overflow the FSM _and_ when later there is demand for free pages and none exist in the FSM. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073