Обсуждение: Autovacuum / full vacuum

Поиск
Список
Период
Сортировка

Autovacuum / full vacuum

От
Michael Riess
Дата:
hi,

I'm curious as to why autovacuum is not designed to do full vacuum. I
know that the necessity of doing full vacuums can be reduced by
increasing the FSM, but in my opinion that is the wrong decision for
many applications. My application does not continuously
insert/update/delete tuples at a constant rate. Basically there are long
periods of relatively few modifications and short burst of high
activity. Increasing the FSM so that even during these bursts most space
  would be reused would mean to reduce the available memory for all
other database tasks.

So my question is: What's the use of an autovacuum daemon if I still
have to use a cron job to do full vacuums? wouldn't it just be a minor
job to enhance autovacuum to be able to perform full vacuums, if one
really wants it to do that - even if some developers think that it's the
wrong approach?

Mike

Re: Autovacuum / full vacuum

От
Christopher Kings-Lynne
Дата:
> So my question is: What's the use of an autovacuum daemon if I still
> have to use a cron job to do full vacuums? wouldn't it just be a minor
> job to enhance autovacuum to be able to perform full vacuums, if one
> really wants it to do that - even if some developers think that it's the
> wrong approach?

You should never have to do full vacuums...

Chris

Re: Autovacuum / full vacuum

От
Michael Riess
Дата:
Hi,

did you read my post? In the first part I explained why I don't want to
increase the FSM that much.

Mike

>> So my question is: What's the use of an autovacuum daemon if I still
>> have to use a cron job to do full vacuums? wouldn't it just be a minor
>> job to enhance autovacuum to be able to perform full vacuums, if one
>> really wants it to do that - even if some developers think that it's
>> the wrong approach?
>
> You should never have to do full vacuums...
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: Autovacuum / full vacuum

От
Pandurangan R S
Дата:
>> You should never have to do full vacuums...

I would rather say, You should never have to do full vacuums by any
periodic means. It may be done on a adhoc basis, when you have figured
out that your table is never going to grow that big again.

On 1/17/06, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> > So my question is: What's the use of an autovacuum daemon if I still
> > have to use a cron job to do full vacuums? wouldn't it just be a minor
> > job to enhance autovacuum to be able to perform full vacuums, if one
> > really wants it to do that - even if some developers think that it's the
> > wrong approach?
>
> You should never have to do full vacuums...
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: Autovacuum / full vacuum

От
Michael Stone
Дата:
On Tue, Jan 17, 2006 at 11:33:02AM +0100, Michael Riess wrote:
>did you read my post? In the first part I explained why I don't want to
>increase the FSM that much.

Since you didn't quantify it, that wasn't much of a data point. (IOW,
you'd generally have to be seriously resource constrained before the FSM
would be a significant source of memory consumption--in which case more
RAM would probably be a much better solution than screwing with
autovacuum.)

Mike Stone

Re: Autovacuum / full vacuum

От
Alvaro Herrera
Дата:
Michael Riess wrote:
> hi,
>
> I'm curious as to why autovacuum is not designed to do full vacuum.

Because a VACUUM FULL is too invasive.  Lazy vacuum is so light on the
system w.r.t. locks that it's generally not a problem to start one at
any time.  On the contrary, vacuum full could be a disaster on some
situations.

What's more, in general a lazy vacuum is enough to keep the dead space
within manageability, given a good autovacuum configuration and good FSM
configuration, so there's mostly no need for full vacuum.  (This is the
theory at least.)  For the situations where there is a need, we tell you
to issue it manually.

> So my question is: What's the use of an autovacuum daemon if I still
> have to use a cron job to do full vacuums? wouldn't it just be a minor
> job to enhance autovacuum to be able to perform full vacuums, if one
> really wants it to do that - even if some developers think that it's the
> wrong approach?

Yes, it is a minor job to "enhance" it to perform vacuum full.  The
problem is having a good approach to determining _when_ to issue a full
vacuum, and having a way to completely disallow it.  If you want to do
the development work, be my guest (but let us know your design first).
If you don't, I guess you would have to wait until it comes high enough
on someone's to-do list, maybe because you convinced him (or her, but we
don't have Postgres-ladies at the moment AFAIK) monetarily or something.

You can, of course, produce a patch and use it internally.  This is free
software, remember.

--
Alvaro Herrera                           Developer, http://www.PostgreSQL.org
"God is real, unless declared as int"

Re: Autovacuum / full vacuum

От
Christopher Browne
Дата:
> I'm curious as to why autovacuum is not designed to do full vacuum.

Because that's terribly invasive due to the locks it takes out.

Lazy vacuum may chew some I/O, but it does *not* block your
application for the duration.

VACUUM FULL blocks the application.  That is NOT something that anyone
wants to throw into the "activity mix" randomly.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/slony.html
Signs of a Klingon Programmer #11: "This machine is a piece of GAGH! I
need dual Pentium processors if I am to do battle with this code!"

Re: Autovacuum / full vacuum

От
Michael Riess
Дата:
> VACUUM FULL blocks the application.  That is NOT something that anyone
> wants to throw into the "activity mix" randomly.

There must be a way to implement a daemon which frees up space of a
relation without blocking it too long. It could abort after a certain
number of blocks have been freed and then move to the next relation.

Re: Autovacuum / full vacuum

От
"Matthew T. O'Connor"
Дата:
Michael Riess wrote:
> did you read my post? In the first part I explained why I don't want to
> increase the FSM that much.

I'm sure he did, but just because you don't have enough FSM space to
capture all everything from your "burst", that doesn't mean that space
can't be reclaimed.  The next time a regular vacuum is run, it will once
again try to fill the FSM with any remaining free space it finds in the
table.  What normally happens is that your table will never bee 100%
free of dead space, normally it will settle at some steady state size
that is small percentage bigger than the table will be after a full
vacuum.  As long as that percentage is small enough, the effect on
performance is negligible.  Have you measured to see if things are truly
faster after a VACUUM FULL?

Matt

Re: Autovacuum / full vacuum

От
Markus Schaber
Дата:
Hi, Matthew,

Matthew T. O'Connor wrote:

> I'm sure he did, but just because you don't have enough FSM space to
> capture all everything from your "burst", that doesn't mean that space
> can't be reclaimed.  The next time a regular vacuum is run, it will once
> again try to fill the FSM with any remaining free space it finds in the
> table.  What normally happens is that your table will never bee 100%
> free of dead space, normally it will settle at some steady state size
> that is small percentage bigger than the table will be after a full
> vacuum.  As long as that percentage is small enough, the effect on
> performance is negligible.

This will work if you've a steady stream of inserts / updates, but not
if you happen to have update bulks that exhaust the FSM capacity. The
update first fills up all the FSM, and then allocates new pages for the
rest. Then VACUUM comes and refills the FSM, however, the FSM does not
contain enough free space for the next large bulk update. The same is
for deletes and large bulk inserts, btw.

So your table keeps growing steadily, until VACUUM FULL or CLUSTER comes
along to clean up the mess.

Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: Autovacuum / full vacuum

От
Michael Riess
Дата:
Hi,

yes, some heavily used tables contain approx. 90% free space after a
week. I'll try to increase FSM even more, but I think that I will still
have to run a full vacuum every week. Prior to 8.1 I was using 7.4 and
ran a full vacuum every day, so the autovacuum has helped a lot.

But actually I never understood why the database system slows down at
all when there is much unused space in the files. Are the unused pages
cached by the system, or is there another reason for the impact on the
performance?

Mike


 >  Have you measured to see if things are truly
> faster after a VACUUM FULL?
>
> Matt
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: Autovacuum / full vacuum

От
"Larry Rosenman"
Дата:
Michael Riess wrote:
> Hi,
>
> yes, some heavily used tables contain approx. 90% free space after a
> week. I'll try to increase FSM even more, but I think that I will
> still have to run a full vacuum every week. Prior to 8.1 I was using
> 7.4 and ran a full vacuum every day, so the autovacuum has helped a
> lot.
>
> But actually I never understood why the database system slows down at
> all when there is much unused space in the files. Are the unused pages
> cached by the system, or is there another reason for the impact on the
> performance?

The reason is that the system needs to LOOK at the pages/tuples to see
if the tuples
are dead or not.

So, the number of dead tuples impacts the scans.

LER

>
> Mike
--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX  78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry.Rosenman@pervasive.com
Web: www.pervasive.com

Re: Autovacuum / full vacuum

От
Christopher Browne
Дата:
> did you read my post? In the first part I explained why I don't want
> to increase the FSM that much.

No, you didn't.  You explained *that* you thought you didn't want to
increase the FSM.  You didn't explain why.

FSM expansion comes fairly cheap, and tends to be an effective way of
eliminating the need for VACUUM FULL.  That is generally considered to
be a good tradeoff.  In future versions, there is likely to be more of
this sort of thing; for instance, on the ToDo list is a "Vacuum Space
Map" that would collect page IDs that need vacuuming so that
PostgreSQL could do "quicker" vacuums...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://cbbrowne.com/info/internet.html
Given  recent  events in  Florida,  the  tourism  board in  Texas  has
developed a new  advertising campaign based on the  slogan "Ya'll come
to Texas, where we ain't shot a tourist in a car since November 1963."

Re: Autovacuum / full vacuum

От
Michael Riess
Дата:
Well,

I think that the documentation is not exactly easy to understand. I
always wondered why there are no examples for common postgresql
configurations. All I know is that the default configuration seems to be
too low for production use. And while running postgres I get no hints as
to which setting needs to be increased to improve performance. I have no
chance to see if my FSM settings are too low other than to run vacuum
full verbose in psql, pipe the result to a text file and grep for some
words to get a somewhat comprehensive idea of how much unused space
there is in my system.

Don't get me wrong - I really like PostgreSQL and it works well in my
application. But somehow I feel that it might run much better ...

about the FSM: You say that increasing the FSM is fairly cheap - how
should I know that?

>> did you read my post? In the first part I explained why I don't want
>> to increase the FSM that much.
>
> No, you didn't.  You explained *that* you thought you didn't want to
> increase the FSM.  You didn't explain why.
>
> FSM expansion comes fairly cheap  ...

Re: Autovacuum / full vacuum

От
Andrew Sullivan
Дата:
On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
> hi,
>
> I'm curious as to why autovacuum is not designed to do full vacuum. I

Because nothing that runs automatically should ever take an exclusive
lock on the entire database, which is what VACUUM FULL does.

> activity. Increasing the FSM so that even during these bursts most space
>  would be reused would mean to reduce the available memory for all
> other database tasks.

I don't believe the hit is enough that you should even notice it.
You'd have to post some pretty incredible use cases to show that the
tiny loss of memory to FSM is worth (a) an exclusive lock and (b) the
loss of efficiency you get from having some preallocated pages in
tables.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun

Re: Autovacuum / full vacuum

От
Michael Riess
Дата:
Hi,

>> hi,
>>
>> I'm curious as to why autovacuum is not designed to do full vacuum. I
>
> Because nothing that runs automatically should ever take an exclusive
> lock on the entire database, which is what VACUUM FULL does.

I thought that vacuum full only locks the table which it currently
operates on? I'm pretty sure that once a table has been vacuumed, it can
be accessed without any restrictions while the vacuum process works on
the next table.

>
>> activity. Increasing the FSM so that even during these bursts most space
>>  would be reused would mean to reduce the available memory for all
>> other database tasks.
>
> I don't believe the hit is enough that you should even notice it.
> You'd have to post some pretty incredible use cases to show that the
> tiny loss of memory to FSM is worth (a) an exclusive lock and (b) the
> loss of efficiency you get from having some preallocated pages in
> tables.

I have 5000 tables and a workstation with 1 GB RAM which hosts an Apache
   Web Server, Tomcat Servlet Container and PostgreSQL. RAM is not
something that I have plenty of ... and the hardware is fixed and cannot
be changed.



Re: Autovacuum / full vacuum

От
Michael Stone
Дата:
On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
>about the FSM: You say that increasing the FSM is fairly cheap - how
>should I know that?

Why would you assume otherwise, to the point of not considering changing
the setting?

The documentation explains how much memory is used for FSM entries. If
you look at vacuum verbose output it will tell you how much memory
you're currently using for the FSM.

Mike Stone

Re: Autovacuum / full vacuum

От
Andrew Sullivan
Дата:
On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
> always wondered why there are no examples for common postgresql
> configurations.

You mean like this one? (for 8.0):

<http://www.powerpostgresql.com/Downloads/annotated_conf_80.html>



> All I know is that the default configuration seems to be
> too low for production use.

Define "production use".  It may be too low for you.

> chance to see if my FSM settings are too low other than to run vacuum
> full verbose in psql, pipe the result to a text file and grep for some

Not true.  You don't need a FULL on there to figure this out.

> about the FSM: You say that increasing the FSM is fairly cheap - how
> should I know that?

Do the math.  The docs say this:

--snip---
max_fsm_pages (integer)

    Sets the maximum number of disk pages for which free space will
be tracked in the shared free-space map. Six bytes of shared memory
are consumed for each page slot. This setting must be more than 16 *
max_fsm_relations. The default is 20000. This option can only be set
at server start.

max_fsm_relations (integer)

    Sets the maximum number of relations (tables and indexes) for
which free space will be tracked in the shared free-space map.
Roughly seventy bytes of shared memory are consumed for each slot.
The default is 1000. This option can only be set at server start.

---snip---

So by default, you have 6 B * 20,000 = 120,000 bytes for the FSM pages.

By default, you have 70 B * 1,000 = 70,000 bytes for the FSM
relations.

Now, there are two knobs.  One of them tracks the number of
relations.  How many relations do you have?  Count the number of
indexes and tables you have, and give yourself some headroom in case
you add some more, and poof, you have your number for the relations.

Now all you need to do is figure out what your churn rate is on
tables, and count up how many disk pages that's likely to be.  Give
yourself a little headroom, and the number of FSM pages is done, too.

This churn rate is often tough to estimate, though, so you may have
to fiddle with it from time to time.

A


--
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to.  That actually seems sort of quaint now.
        --J.D. Baldwin

Re: Autovacuum / full vacuum

От
Andrew Sullivan
Дата:
On Tue, Jan 17, 2006 at 09:09:02AM -0500, Matthew T. O'Connor wrote:
> vacuum.  As long as that percentage is small enough, the effect on
> performance is negligible.  Have you measured to see if things are truly

Actually, as long as the percentage is small enough and the pages are
really empty, the performance effect is positive.  If you have VACUUM
FULLed table, inserts have to extend the table before inserting,
whereas in a table with some space reclaimed, the I/O effect of
having to allocate another disk page is already done.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?
        --attr. John Maynard Keynes

Re: Autovacuum / full vacuum

От
hubert depesz lubaczewski
Дата:
On 1/17/06, Michael Riess <mlriess@gmx.de> wrote:
about the FSM: You say that increasing the FSM is fairly cheap - how
should I know that?

comment from original postgresql.conf file seems pretty obvious:
#max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000       # min 100, ~70 bytes each

basically setting max_fsm_pages to 1000000 consumes 6 megabytes. and i definitelly doubt you will ever hit that high.

depesz

Re: Autovacuum / full vacuum

От
Tom Lane
Дата:
Michael Riess <mlriess@gmx.de> writes:
> I'm curious as to why autovacuum is not designed to do full vacuum.

Locking considerations.  VACUUM FULL takes an exclusive lock, which
blocks any foreground transactions that want to touch the table ---
so it's really not the sort of thing you want being launched at
unpredictable times.

            regards, tom lane

Re: Autovacuum / full vacuum

От
Andrew Sullivan
Дата:
On Tue, Jan 17, 2006 at 03:05:29PM +0100, Michael Riess wrote:
> There must be a way to implement a daemon which frees up space of a
> relation without blocking it too long.

Define "too long".  If I have a table that needs to respond to a
SELECT in 50ms, I don't have time for you to lock my table.  If this
were such an easy thing to do, don't you think the folks who came up
wit the ingenious lazy vacuum system would have done it?

Remember, a vacuum full must completely lock the table, because it is
physically moving bits around on the disk.  So a SELECT can't happen
at the same time, because the bits might move out from under the
SELECT while it's running.  Concurrency is hard, and race conditions
are easy, to implement.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
        --Alexander Hamilton

Re: Autovacuum / full vacuum

От
Andrew Sullivan
Дата:
On Tue, Jan 17, 2006 at 04:04:41PM +0100, Michael Riess wrote:
>
> I thought that vacuum full only locks the table which it currently
> operates on? I'm pretty sure that once a table has been vacuumed, it can
> be accessed without any restrictions while the vacuum process works on
> the next table.

Yes, I think the way I phrased it was unfortunate.  But if you issue
VACUUM FULL you'll get an exclusive lock on everything, although not
all at the same time.  But of course, if your query load is like
this

BEGIN;
SELECT from t1, t2 where t1.col1 = t2.col2;
[application logic]
UPDATE t3 . . .
COMMIT;

you'll find yourself blocked in the first statement on both t1 and
t2; and then on t3 as well.  You sure don't want that to happen
automagically, in the middle of your business day.

> I have 5000 tables and a workstation with 1 GB RAM which hosts an Apache
>   Web Server, Tomcat Servlet Container and PostgreSQL. RAM is not
> something that I have plenty of ... and the hardware is fixed and cannot
> be changed.

I see.  Well, I humbly submit that your problem is not the design of
the PostgreSQL server, then.  "The hardware is fixed and cannot be
changed," is the first optimisation I'd make.  Heck, I gave away a
box to charity only two weeks ago that would solve your problem
better than automatically issuing VACUUM FULL.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Information security isn't a technological problem.  It's an economics
problem.
        --Bruce Schneier

Re: Autovacuum / full vacuum

От
Tom Lane
Дата:
Michael Riess <mlriess@gmx.de> writes:
> But actually I never understood why the database system slows down at
> all when there is much unused space in the files.

Perhaps some of your common queries are doing sequential scans?  Those
would visit the empty pages as well as the full ones.

            regards, tom lane

Re: Autovacuum / full vacuum

От
Scott Marlowe
Дата:
On Tue, 2006-01-17 at 09:08, Andrew Sullivan wrote:
> On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
> > always wondered why there are no examples for common postgresql
> > configurations.
>
> You mean like this one? (for 8.0):
>
> <http://www.powerpostgresql.com/Downloads/annotated_conf_80.html>

I have to admit, looking at the documentation, that we really don't
explain this all that well in the administration section, and I can see
how easily led astray beginners are.

I think it's time I joined the pgsql-docs mailing list...

Re: Autovacuum / full vacuum

От
Chris Browne
Дата:
ajs@crankycanuck.ca (Andrew Sullivan) writes:
> On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
>> hi,
>>
>> I'm curious as to why autovacuum is not designed to do full vacuum. I
>
> Because nothing that runs automatically should ever take an exclusive
> lock on the entire database, which is what VACUUM FULL does.

That's a bit more than what autovacuum would probably do...
autovacuum does things table by table, so that what would be locked
should just be one table.

Even so, I'd not be keen on having anything that runs automatically
take an exclusive lock on even as much as a table.

>> activity. Increasing the FSM so that even during these bursts most
>> space would be reused would mean to reduce the available memory for
>> all other database tasks.
>
> I don't believe the hit is enough that you should even notice
> it. You'd have to post some pretty incredible use cases to show that
> the tiny loss of memory to FSM is worth (a) an exclusive lock and
> (b) the loss of efficiency you get from having some preallocated
> pages in tables.

There is *a* case for setting up full vacuums of *some* objects.  If
you have a table whose tuples all get modified in the course of some
common query, that will lead to a pretty conspicuous bloating of *that
table.*

Even with a big FSM, the pattern of how updates take place will lead
to that table having ~50% of its space being "dead/free," which is way
higher than the desirable "stable proportion" of 10-15%.

For that sort of table, it may be attractive to run VACUUM FULL on a
regular basis.  Of course, it may also be attractive to try to come up
with an update process that won't kill the whole table's contents at
once ;-).
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/x.html
"As  long as  each  individual is  facing  the TV  tube alone,  formal
freedom poses no threat to privilege."  --Noam Chomsky

Re: Autovacuum / full vacuum

От
Andrew Sullivan
Дата:
On Tue, Jan 17, 2006 at 09:59:25AM -0600, Scott Marlowe wrote:
> I have to admit, looking at the documentation, that we really don't
> explain this all that well in the administration section, and I can see
> how easily led astray beginners are.

I understand what you mean, but I suppose my reaction would be that
what we really need is a place to keep these things, with a note in
the docs that the "best practice" settings for these are documented
at <some url>, and evolve over time as people gain expertise with the
new features.

I suspect, for instance, that nobody knows exactly the right settings
for any generic workload yet under 8.1 (although probably people know
them well enough for particular workloads).

A


--
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
        --Dennis Ritchie

Re: Autovacuum / full vacuum

От
Markus Schaber
Дата:
Hi, Michael,

Michael Riess wrote:

> But actually I never understood why the database system slows down at
> all when there is much unused space in the files. Are the unused pages
> cached by the system, or is there another reason for the impact on the
> performance?

No, they are not cached as such, but PostgreSQL caches whole pages, and
you don't have only empty pages, but also lots of partially empty pages,
so the signal/noise ratio is worse (means PostgreSQL has to fetch more
pages to get the same data).

Sequential scans etc. are also slower.

And some file systems get slower when files get bigger or there are more
files, but this effect should not really be noticeable here.

HTH,
Markus


--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: Autovacuum / full vacuum

От
Alvaro Herrera
Дата:
Chris Browne wrote:
> ajs@crankycanuck.ca (Andrew Sullivan) writes:
> > On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
> >> hi,
> >>
> >> I'm curious as to why autovacuum is not designed to do full vacuum. I
> >
> > Because nothing that runs automatically should ever take an exclusive
> > lock on the entire database, which is what VACUUM FULL does.
>
> That's a bit more than what autovacuum would probably do...
> autovacuum does things table by table, so that what would be locked
> should just be one table.

Even a database-wide vacuum does not take locks on more than one table.
The table locks are acquired and released one by one, as the operation
proceeds.  And as you know, autovacuum (both 8.1's and contrib) does
issue database-wide vacuums, if it finds a database close to an xid
wraparound.

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"Las mujeres son como hondas:  mientras más resistencia tienen,
 más lejos puedes llegar con ellas"  (Jonas Nightingale, Leap of Faith)

Re: Autovacuum / full vacuum

От
Scott Marlowe
Дата:
On Tue, 2006-01-17 at 11:16, Andrew Sullivan wrote:
> On Tue, Jan 17, 2006 at 09:59:25AM -0600, Scott Marlowe wrote:
> > I have to admit, looking at the documentation, that we really don't
> > explain this all that well in the administration section, and I can see
> > how easily led astray beginners are.
>
> I understand what you mean, but I suppose my reaction would be that
> what we really need is a place to keep these things, with a note in
> the docs that the "best practice" settings for these are documented
> at <some url>, and evolve over time as people gain expertise with the
> new features.
>
> I suspect, for instance, that nobody knows exactly the right settings
> for any generic workload yet under 8.1 (although probably people know
> them well enough for particular workloads).

But the problem is bigger than that.  The administrative docs were
obviously evolved over time, and now they kind of jump around and around
covering the same subject from different angles and at different
depths.  Even I find it hard to find what I need, and I know PostgreSQL
administration well enough to be pretty darned good at it.

For the beginner, it must seem much more confusing.  The more I look at
the administration section of the docs, the more I want to reorganize
the whole thing, and rewrite large sections of it as well.

Re: Autovacuum / full vacuum

От
Andrew Sullivan
Дата:
On Tue, Jan 17, 2006 at 11:43:14AM -0500, Chris Browne wrote:
> ajs@crankycanuck.ca (Andrew Sullivan) writes:
> > Because nothing that runs automatically should ever take an exclusive
> > lock on the entire database,

> That's a bit more than what autovacuum would probably do...

Or even VACUUM FULL, as I tried to make clearer in another message:
the way I phrased it suggests that it's a simultaneous lock on the
entire database (when it is most certainly not).  I didn't intend to
mislead; my apologies.

Note, though, that the actual effect for a user might look worse
than a lock on the entire database, though, if you conider
statement_timeout and certain use patterns.

Suppose you want to issue occasional VACCUM FULLs, but your
application is prepared for this, and depends on statement_timeout to
tell it "sorry, too long, try again".  Now, if the exclusive lock on
any given table takes less than statement_timeout, so that each
statement is able to continue in its time, the application looks like
it's having an outage _even though_ it is actually blocked on
vacuums.  (Yes, it's poor application design.  There's plenty of that
in the world, and you can't always fix it.)

> There is *a* case for setting up full vacuums of *some* objects.  If
> you have a table whose tuples all get modified in the course of some
> common query, that will lead to a pretty conspicuous bloating of *that
> table.*

Sure.  And depending on your use model, that might be good.  In many
cases, though, a "rotor table + view + truncate" approach would be
better, and would allow improved uptime.  If you don't care about
uptime, and can take long outages every day, then the discussion is
sort of moot anyway.  And _all_ of this is moot, as near as I can
tell, given the OP's claim that the hardware is adequate and
immutable, even though the former claim is demonstrably false.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun

Re: Autovacuum / full vacuum

От
Chris Browne
Дата:
alvherre@alvh.no-ip.org (Alvaro Herrera) writes:
> Chris Browne wrote:
>> ajs@crankycanuck.ca (Andrew Sullivan) writes:
>> > On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
>> >> hi,
>> >>
>> >> I'm curious as to why autovacuum is not designed to do full vacuum. I
>> >
>> > Because nothing that runs automatically should ever take an exclusive
>> > lock on the entire database, which is what VACUUM FULL does.
>>
>> That's a bit more than what autovacuum would probably do...
>> autovacuum does things table by table, so that what would be locked
>> should just be one table.
>
> Even a database-wide vacuum does not take locks on more than one table.
> The table locks are acquired and released one by one, as the operation
> proceeds.  And as you know, autovacuum (both 8.1's and contrib) does
> issue database-wide vacuums, if it finds a database close to an xid
> wraparound.

Has that changed recently?  I have always seen "vacuumdb" or SQL
"VACUUM" (without table specifications) running as one long
transaction which doesn't release the locks that it is granted until
the end of the transaction.
--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/spiritual.html
"My nostalgia for Icon makes me forget about any of the bad things.  I
don't have much nostalgia for Perl, so its faults I remember."
-- Scott Gilbert comp.lang.python

Re: Autovacuum / full vacuum

От
Tom Lane
Дата:
Chris Browne <cbbrowne@acm.org> writes:
> alvherre@alvh.no-ip.org (Alvaro Herrera) writes:
>> Even a database-wide vacuum does not take locks on more than one table.
>> The table locks are acquired and released one by one, as the operation
>> proceeds.

> Has that changed recently?  I have always seen "vacuumdb" or SQL
> "VACUUM" (without table specifications) running as one long
> transaction which doesn't release the locks that it is granted until
> the end of the transaction.

You sure?  It's not supposed to, and watching a database-wide vacuum
with "select * from pg_locks" doesn't look to me like it ever has locks
on more than one table (plus the table's indexes and toast table).

            regards, tom lane

Re: Autovacuum / full vacuum

От
"Jim C. Nasby"
Дата:
On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
> Well,
>
> I think that the documentation is not exactly easy to understand. I
> always wondered why there are no examples for common postgresql
> configurations. All I know is that the default configuration seems to be
> too low for production use. And while running postgres I get no hints as
> to which setting needs to be increased to improve performance. I have no

There's a number of sites that have lots of info on postgresql.conf
tuning. Google for 'postgresql.conf tuning' or 'annotated
postgresql.conf'.

> chance to see if my FSM settings are too low other than to run vacuum
> full verbose in psql, pipe the result to a text file and grep for some
> words to get a somewhat comprehensive idea of how much unused space
> there is in my system.
>
> Don't get me wrong - I really like PostgreSQL and it works well in my
> application. But somehow I feel that it might run much better ...
>
> about the FSM: You say that increasing the FSM is fairly cheap - how
> should I know that?

decibel@phonebook.1[16:26]/opt/local/share/postgresql8:3%grep fsm \
postgresql.conf.sample
#max_fsm_pages = 20000                  # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000               # min 100, ~70 bytes each
decibel@phonebook.1[16:26]/opt/local/share/postgresql8:4%
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Autovacuum / full vacuum

От
Michael Riess
Дата:
> There's a number of sites that have lots of info on postgresql.conf
> tuning. Google for 'postgresql.conf tuning' or 'annotated
> postgresql.conf'.

I know some of these sites, but who should I know if the information on
those pages is correct? The information on those pages should be
published as part of the postgres documentation. Doesn't have to be too
much, maybe like this page:

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

But it should be part of the documentation to show newbies that not only
the information is correct, but also approved of and recommended by the
postgres team.

Re: Autovacuum / full vacuum

От
"Mindaugas"
Дата:
> >> Even a database-wide vacuum does not take locks on more than one table.
> >> The table locks are acquired and released one by one, as the operation
> >> proceeds.
>
> > Has that changed recently?  I have always seen "vacuumdb" or SQL
> > "VACUUM" (without table specifications) running as one long
> > transaction which doesn't release the locks that it is granted until
> > the end of the transaction.
>
> You sure?  It's not supposed to, and watching a database-wide vacuum
> with "select * from pg_locks" doesn't look to me like it ever has locks
> on more than one table (plus the table's indexes and toast table).

  Are there some plans to remove vacuum altogether?

  Mindaugas


Re: Autovacuum / full vacuum

От
Alvaro Herrera
Дата:
Mindaugas wrote:
> > >> Even a database-wide vacuum does not take locks on more than one table.
> > >> The table locks are acquired and released one by one, as the operation
> > >> proceeds.
> >
> > > Has that changed recently?  I have always seen "vacuumdb" or SQL
> > > "VACUUM" (without table specifications) running as one long
> > > transaction which doesn't release the locks that it is granted until
> > > the end of the transaction.
> >
> > You sure?  It's not supposed to, and watching a database-wide vacuum
> > with "select * from pg_locks" doesn't look to me like it ever has locks
> > on more than one table (plus the table's indexes and toast table).
>
>   Are there some plans to remove vacuum altogether?

No, but there are plans to make it as automatic and unintrusive as
possible.  (User configuration will probably always be needed.)

--
Alvaro Herrera                           Developer, http://www.PostgreSQL.org
FOO MANE PADME HUM

Re: Autovacuum / full vacuum

От
Chris Browne
Дата:
mind@bi.lt ("Mindaugas") writes:
>> >> Even a database-wide vacuum does not take locks on more than one
>> >> table.  The table locks are acquired and released one by one, as
>> >> the operation proceeds.
>>
>> > Has that changed recently?  I have always seen "vacuumdb" or SQL
>> > "VACUUM" (without table specifications) running as one long
>> > transaction which doesn't release the locks that it is granted
>> > until the end of the transaction.
>>
>> You sure?  It's not supposed to, and watching a database-wide
>> vacuum with "select * from pg_locks" doesn't look to me like it
>> ever has locks on more than one table (plus the table's indexes and
>> toast table).
>
>   Are there some plans to remove vacuum altogether?

I don't see that on the TODO list...

http://www.postgresql.org/docs/faqs.TODO.html

To the contrary, there is a whole section on what functionality to
*ADD* to VACUUM.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/finances.html
"There are two types of hackers working on Linux: those who can spell,
and those who can't.  There  is a constant, pitched battle between the
two  camps."
--Russ Nelson (Linux Kernel Summary, Ver. 1.1.75 -> 1.1.76)

Re: Autovacuum / full vacuum (off-topic?)

От
Michael Crozier
Дата:
On Wednesday 18 January 2006 08:54 am, Chris Browne wrote:
> To the contrary, there is a whole section on what functionality to
> *ADD* to VACUUM.

Near but not quite off the topic of VACUUM and new features...

I've been thinking about parsing the vacuum output and storing it in
Postgresql.  All the tuple, page, cpu time, etc... information would be
inserted into a reasonably flat set of tables.

The benefits I would expect from this are:

* monitoring ability - I could routinely monitor the values in the table to
warn when vacuum's are failing or reclaimed space has risen dramatically.  I
find it easier to write and maintain monitoring agents that perform SQL
queries than ones that need to routinely parse log files and coordinate with
cron.

* historical perspective on tuple use - which a relatively small amount of
storage, I could use the vacuum output to get an idea of usage levels over
time, which is beneficial for planning additional capacity

* historical information could theoretically inform the autovacuum, though I
assume there are better alternatives planned.

* it could cut down on traffic on this list if admin could see routine
maintenance in a historical context.

Assuming this isn't a fundamentally horrible idea, it would be nice if there
were ways to do this without parsing the pretty-printed vacuum text (ie,
callbacks, triggers, guc variable).

I'd like to know if anybody does this already, thinks its a bad idea, or can
knock me on the noggin with the pg manual and say, "it's already there!".

Regards,

 Michael


Re: Autovacuum / full vacuum

От
"Jim C. Nasby"
Дата:
On Wed, Jan 18, 2006 at 03:09:42PM +0100, Michael Riess wrote:
> >There's a number of sites that have lots of info on postgresql.conf
> >tuning. Google for 'postgresql.conf tuning' or 'annotated
> >postgresql.conf'.
>
> I know some of these sites, but who should I know if the information on
> those pages is correct? The information on those pages should be
> published as part of the postgres documentation. Doesn't have to be too
> much, maybe like this page:
>
> http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
>
> But it should be part of the documentation to show newbies that not only
> the information is correct, but also approved of and recommended by the
> postgres team.

Actually, most of what you find there is probably also found in
techdocs. But of course it would be better if the docs did a better job
of explaining things...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Autovacuum / full vacuum (off-topic?)

От
"Jim C. Nasby"
Дата:
On Wed, Jan 18, 2006 at 11:15:51AM -0800, Michael Crozier wrote:
> I've been thinking about parsing the vacuum output and storing it in
> Postgresql.  All the tuple, page, cpu time, etc... information would be
> inserted into a reasonably flat set of tables.
<snip>
> Assuming this isn't a fundamentally horrible idea, it would be nice if there
> were ways to do this without parsing the pretty-printed vacuum text (ie,
> callbacks, triggers, guc variable).

The best way to do this would be to modify the vacuum code itself, but
the issue is that vacuum (or at least lazyvacuum) doesn't handle
transactions like the rest of the backend does, so I suspect that there
would be some issues with trying to log the information from the same
backend that was running the vacuum.

> I'd like to know if anybody does this already, thinks its a bad idea, or can
> knock me on the noggin with the pg manual and say, "it's already there!".

I think it's a good idea, but you should take a look at the recently
added functionality that allows you to investigate the contests of the
FSM via a user function (this is either in 8.1 or in HEAD; I can't
remember which).
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Autovacuum / full vacuum (off-topic?)

От
Chris Browne
Дата:
crozierm@conducivetech.com (Michael Crozier) writes:

> On Wednesday 18 January 2006 08:54 am, Chris Browne wrote:
>> To the contrary, there is a whole section on what functionality to
>> *ADD* to VACUUM.
>
> Near but not quite off the topic of VACUUM and new features...
>
> I've been thinking about parsing the vacuum output and storing it in
> Postgresql.  All the tuple, page, cpu time, etc... information would
> be inserted into a reasonably flat set of tables.
>
> The benefits I would expect from this are:
>
> * monitoring ability - I could routinely monitor the values in the
> table to warn when vacuum's are failing or reclaimed space has risen
> dramatically.  I find it easier to write and maintain monitoring
> agents that perform SQL queries than ones that need to routinely
> parse log files and coordinate with cron.
>
> * historical perspective on tuple use - which a relatively small
> amount of storage, I could use the vacuum output to get an idea of
> usage levels over time, which is beneficial for planning additional
> capacity
>
> * historical information could theoretically inform the autovacuum,
> though I assume there are better alternatives planned.
>
> * it could cut down on traffic on this list if admin could see
> routine maintenance in a historical context.
>
> Assuming this isn't a fundamentally horrible idea, it would be nice
> if there were ways to do this without parsing the pretty-printed
> vacuum text (ie, callbacks, triggers, guc variable).
>
> I'd like to know if anybody does this already, thinks its a bad
> idea, or can knock me on the noggin with the pg manual and say,
> "it's already there!".

We had someone working on that for a while; I don't think it got to
the point of being something ready to unleash on the world.

I certainly agree that it would be plenty useful to have this sort of
information available.  Having a body of historical information could
lead to having some "more informed" suggestions for heuristics.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/unix.html
Bad command. Bad, bad command! Sit! Stay! Staaay...

Re: Autovacuum / full vacuum (off-topic?)

От
Michael Crozier
Дата:
On Wednesday 18 January 2006 14:52 pm, Jim C. Nasby wrote:
> I think it's a good idea, but you should take a look at the recently
> added functionality that allows you to investigate the contests of the
> FSM via a user function (this is either in 8.1 or in HEAD; I can't
> remember which).

I will look at this when time allows.  Perhaps there is a combination of
triggers on stat tables and asynchronous notifications that would provide
this functionality without getting too deep into the vacuum's transaction
logic?

Were it too integrated with the vacuum, it would likely be too much for
contrib/, I assume.


thanks,

  Michael


Re: Autovacuum / full vacuum (off-topic?)

От
Michael Crozier
Дата:
> We had someone working on that for a while; I don't think it got to
> the point of being something ready to unleash on the world.

Interesting.  I will dig around the mailing list archives too see how they
went about it... for my own curiosity if nothing else.   If you happen to
know offhand, I'd appreciate a link.

Regards,

 Michael

Re: Autovacuum / full vacuum (off-topic?)

От
"Jim C. Nasby"
Дата:
On Wed, Jan 18, 2006 at 03:36:04PM -0800, Michael Crozier wrote:
>
> On Wednesday 18 January 2006 14:52 pm, Jim C. Nasby wrote:
> > I think it's a good idea, but you should take a look at the recently
> > added functionality that allows you to investigate the contests of the
> > FSM via a user function (this is either in 8.1 or in HEAD; I can't
> > remember which).
>
> I will look at this when time allows.  Perhaps there is a combination of
> triggers on stat tables and asynchronous notifications that would provide
> this functionality without getting too deep into the vacuum's transaction
> logic?

You can't put triggers on system tables, at least not ones that will be
triggered by system operations themselves, because the backend bypasses
normal access methods. Also, most of the really interesting info isn't
logged anywhere in a system table; stuff like the amount of dead space,
tuples removed, etc.

> Were it too integrated with the vacuum, it would likely be too much for
> contrib/, I assume.

Probably.

A good alternative might be allowing vacuum to output some
machine-friendly information (maybe into a backend-writable file?) and
then have code that could load that into a table (though presumably that
could should be as simple as just a COPY).
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Autovacuum / full vacuum (off-topic?)

От
Mark Kirkwood
Дата:
Jim C. Nasby wrote:

>
> I think it's a good idea, but you should take a look at the recently
> added functionality that allows you to investigate the contests of the
> FSM via a user function (this is either in 8.1 or in HEAD; I can't
> remember which).

AFAICS it is still in the patch queue for 8.2.

It's called 'pg_freespacemap' and is available for 8.1/8.0 from the
Pgfoundry 'backports' project:

http://pgfoundry.org/projects/backports

Cheers

Mark


Re: Autovacuum / full vacuum (off-topic?)

От
Bruce Momjian
Дата:
Verified.  I am working toward getting all those patches applied.

---------------------------------------------------------------------------

Mark Kirkwood wrote:
> Jim C. Nasby wrote:
>
> >
> > I think it's a good idea, but you should take a look at the recently
> > added functionality that allows you to investigate the contests of the
> > FSM via a user function (this is either in 8.1 or in HEAD; I can't
> > remember which).
>
> AFAICS it is still in the patch queue for 8.2.
>
> It's called 'pg_freespacemap' and is available for 8.1/8.0 from the
> Pgfoundry 'backports' project:
>
> http://pgfoundry.org/projects/backports
>
> Cheers
>
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
  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

Re: Autovacuum / full vacuum (off-topic?)

От
"Jim C. Nasby"
Дата:
BTW, given all the recent discussion about vacuuming and our MVCC,
http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
should prove interesting. :)
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Autovacuum / full vacuum (off-topic?)

От
"Joshua D. Drake"
Дата:
Jim C. Nasby wrote:
> BTW, given all the recent discussion about vacuuming and our MVCC,
> http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
> should prove interesting. :)
>
Please explain... what is the .asp extension. I have yet to see it
reliable in production ;)




--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/


Re: Autovacuum / full vacuum (off-topic?)

От
"Jim C. Nasby"
Дата:
On Fri, Jan 20, 2006 at 09:31:14AM -0800, Joshua D. Drake wrote:
> Jim C. Nasby wrote:
> >BTW, given all the recent discussion about vacuuming and our MVCC,
> >http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
> >should prove interesting. :)
> >
> Please explain... what is the .asp extension. I have yet to see it
> reliable in production ;)

I lay no claim to our infrastructure. :)
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Autovacuum / full vacuum (off-topic?)

От
"Joshua D. Drake"
Дата:
> I lay no claim to our infrastructure. :)
>
Can I quote the: Pervasive Senior Engineering Consultant on that?

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/


Re: Autovacuum / full vacuum (off-topic?)

От
"Jim C. Nasby"
Дата:
On Fri, Jan 20, 2006 at 09:37:50AM -0800, Joshua D. Drake wrote:
>
> >I lay no claim to our infrastructure. :)
> >
> Can I quote the: Pervasive Senior Engineering Consultant on that?

Sure... I've never been asked to consult on our stuff, and in any case,
I don't do web front-ends (one of the nice things about working with a
team of other consultants). AFAIK IIS will happily talk to PostgreSQL
(though maybe I'm wrong there...)

I *have* asked what database is being used on the backend though, and
depending on the answer to that some folks might have some explaining to
do. :)

*grabs big can of dog food*
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Autovacuum / full vacuum (off-topic?)

От
Дата:
> Sure... I've never been asked to consult on our stuff, and in any case,
> I don't do web front-ends (one of the nice things about working with a
> team of other consultants). AFAIK IIS will happily talk to PostgreSQL
> (though maybe I'm wrong there...)

iis (yeah, asp in a successfull productive environement hehe) & postgresql
works even better for us than iis & mssql :-)

- thomas



Re: Autovacuum / full vacuum (off-topic?)

От
"Jim C. Nasby"
Дата:
On Fri, Jan 20, 2006 at 06:46:45PM +0100, me@alternize.com wrote:
> >Sure... I've never been asked to consult on our stuff, and in any case,
> >I don't do web front-ends (one of the nice things about working with a
> >team of other consultants). AFAIK IIS will happily talk to PostgreSQL
> >(though maybe I'm wrong there...)
>
> iis (yeah, asp in a successfull productive environement hehe) & postgresql
> works even better for us than iis & mssql :-)

Just last night I was talking to someone about different databases and
what-not (he's stuck in a windows shop using MSSQL and I mentioned I'd
heard some bad things about it's stability). I realized at some point
that asking about what large installs of something exist is pretty
pointless... given enough effort you can make almost anything scale. As
an example, there's a cable company with a MySQL database that's nearly
1TB... if that's not proof you can make anything scale, I don't know
what is. ;)

What people really need to ask about is how hard it is to make something
work, and how many problems you're likely to keep encountering.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461