Thread

Autovacuum / full vacuum

Michael Riess <mlriess@gmx.de> — 2006-01-17T10:18:59Z

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 <chriskl@familyhealth.com.au> — 2006-01-17T10:29:37Z

> 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 <mlriess@gmx.de> — 2006-01-17T10:33:02Z

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 <pandurangan.r.s@gmail.com> — 2006-01-17T11:35:20Z

>> 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 <mstone+postgres@mathom.us> — 2006-01-17T12:08:05Z

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 <alvherre@alvh.no-ip.org> — 2006-01-17T13:19:58Z

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

Chris Browne <cbbrowne@acm.org> — 2006-01-17T13:59:28Z

> 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

Chris Browne <cbbrowne@acm.org> — 2006-01-17T14:04:06Z

> 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 <mlriess@gmx.de> — 2006-01-17T14:05:29Z

> 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 <matthew@zeut.net> — 2006-01-17T14:09:02Z

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 <schabi@logix-tt.com> — 2006-01-17T14:30:14Z

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 <mlriess@gmx.de> — 2006-01-17T14:33:18Z

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

Michael Riess <mlriess@gmx.de> — 2006-01-17T14:50:38Z

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 <ajs@crankycanuck.ca> — 2006-01-17T14:56:49Z

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 <mlriess@gmx.de> — 2006-01-17T15:04:41Z

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 <mstone+postgres@mathom.us> — 2006-01-17T15:07:32Z

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 <ajs@crankycanuck.ca> — 2006-01-17T15:08:00Z

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 <ajs@crankycanuck.ca> — 2006-01-17T15:09:48Z

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 <depesz@gmail.com> — 2006-01-17T15:10:34Z

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 <tgl@sss.pgh.pa.us> — 2006-01-17T15:13:41Z

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 <ajs@crankycanuck.ca> — 2006-01-17T15:13:57Z

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 <ajs@crankycanuck.ca> — 2006-01-17T15:19:44Z

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 <tgl@sss.pgh.pa.us> — 2006-01-17T15:36:11Z

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 <smarlowe@g2switchworks.com> — 2006-01-17T15:59:25Z

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 <cbbrowne@acm.org> — 2006-01-17T16:43:14Z

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 <ajs@crankycanuck.ca> — 2006-01-17T17:16:56Z

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 <schabi@logix-tt.com> — 2006-01-17T17:25:06Z

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 <alvherre@alvh.no-ip.org> — 2006-01-17T17:30:47Z

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 <smarlowe@g2switchworks.com> — 2006-01-17T17:31:43Z

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 <ajs@crankycanuck.ca> — 2006-01-17T18:13:50Z

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 <cbbrowne@acm.org> — 2006-01-17T18:40:46Z

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 <tgl@sss.pgh.pa.us> — 2006-01-17T19:32:52Z

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 Nasby <jnasby@pervasive.com> — 2006-01-17T22:28:12Z

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 <mlriess@gmx.de> — 2006-01-18T14:09:42Z

> 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 <mind@bi.lt> — 2006-01-18T15:50:49Z

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

Chris Browne <cbbrowne@acm.org> — 2006-01-18T16:54:21Z

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

Alvaro Herrera <alvherre@alvh.no-ip.org> — 2006-01-18T16:55:21Z

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 (off-topic?)

Michael Crozier <crozierm@conducivetech.com> — 2006-01-18T19:15:51Z

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 (off-topic?)

Chris Browne <cbbrowne@acm.org> — 2006-01-18T22:04:51Z

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

Jim Nasby <jnasby@pervasive.com> — 2006-01-18T22:48:56Z

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 Nasby <jnasby@pervasive.com> — 2006-01-18T22:52:57Z

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

Michael Crozier <crozierm@conducivetech.com> — 2006-01-18T23:36:04Z

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 <crozierm@conducivetech.com> — 2006-01-18T23:41:33Z

> 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 Nasby <jnasby@pervasive.com> — 2006-01-18T23:44:40Z

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 <markir@paradise.net.nz> — 2006-01-19T23:10:32Z

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 <pgman@candle.pha.pa.us> — 2006-01-19T23:12:36Z

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 Nasby <jnasby@pervasive.com> — 2006-01-20T17:12:14Z

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 <jd@commandprompt.com> — 2006-01-20T17:31:14Z

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 Nasby <jnasby@pervasive.com> — 2006-01-20T17:34:06Z

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 <jd@commandprompt.com> — 2006-01-20T17:37:50Z

> 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 Nasby <jnasby@pervasive.com> — 2006-01-20T17:44:16Z

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

Thomas H. <me@alternize.com> — 2006-01-20T17:46:45Z

> 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 Nasby <jnasby@pervasive.com> — 2006-01-20T19:02:36Z

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