Thread

On the _need_ to vacuum...

Jack Bates <postgres@floatingdoghead.net> — 2001-04-28T03:33:57Z

Hello all:

I am part of a software development team evaluating RDBMSs for inclusion
as a base component of a "messaging" system.  I've been thrashing hard
on PostgreSQL under Solaris 8 and the GNU compiler for a few days now,
and personally, I'm impressed.  Thank you, developers.

The only two major problems I face when considering the use of
PostgreSQL 7.1 as released are:

1) index efficiency appears to drop over relatively short time periods
on highly volatile tables, causing producers to eventually start pulling
away from "more efficient" consumers of data in long-term tests which
include "well-oiled" situations in the load mix.

2) vacuum analyze holds an exclusive table lock for a _significant_
period of time, particularly when vacuuming tables that have been highly
volatile.  

The system we are building needs to have the ability to keep chugging
along 24/7 - without _any_ long lapses of table availability.

Is there any other way to keep this type of table "preened" and
performant without a heavyweight table lock being involved?

If not, please consider this as an item for prioritized future
development.

I thank you in advance for your replies via email or this newsgroup.

--

Jack Bates
Portland, OR, USA
http://www.floatingdoghead.net
My PGP public key: http://www.floatingdoghead.net/pubkey.txt

Re: On the _need_ to vacuum...

Alfred Perlstein <bright@wintelcom.net> — 2001-04-28T20:43:18Z

* Jack Bates <postgres@floatingdoghead.net> [010428 13:31] wrote:
> 
> Hello all:
> 
> I am part of a software development team evaluating RDBMSs for inclusion
> as a base component of a "messaging" system.  I've been thrashing hard
> on PostgreSQL under Solaris 8 and the GNU compiler for a few days now,
> and personally, I'm impressed.  Thank you, developers.
> 
> The only two major problems I face when considering the use of
> PostgreSQL 7.1 as released are:
> 
> 1) index efficiency appears to drop over relatively short time periods
> on highly volatile tables, causing producers to eventually start pulling
> away from "more efficient" consumers of data in long-term tests which
> include "well-oiled" situations in the load mix.
> 
> 2) vacuum analyze holds an exclusive table lock for a _significant_
> period of time, particularly when vacuuming tables that have been highly
> volatile.  
> 
> The system we are building needs to have the ability to keep chugging
> along 24/7 - without _any_ long lapses of table availability.
> 
> Is there any other way to keep this type of table "preened" and
> performant without a heavyweight table lock being involved?
> 
> If not, please consider this as an item for prioritized future
> development.
> 
> I thank you in advance for your replies via email or this newsgroup.

There's a fix for Postgresql 7.0.3 here:

http://www.freebsd.org/~alfred/vacfix

I'm strongly considering taking the patches offline and reselling them
as I seem to be the only source for them nowadays.

-- 
-Alfred Perlstein - [alfred@freebsd.org]
http://www.egr.unlv.edu/~slumos/on-netbsd.html

Why do things slow down without a VACUUM?

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-04-30T01:14:04Z

Hi,

Why do things slow down after a lot of updates without a VACUUM? This
slow-down doesn't seem to happen as much with a lot of inserts.

Don't the indexes know which is the valid updated row? Or does Postgresql
still have to search for it amongst deleted/invalid rows?

I can understand why the database would bloat up and/or slow down slightly
without a vacuum. But why such a significant slowdown? So much so that
people have to vacuum every two hours.

Cheerio,
Link.

Re: Why do things slow down without a VACUUM?

GH <grasshacker@over-yonder.net> — 2001-04-30T02:17:43Z

On Mon, Apr 30, 2001 at 09:14:04AM +0800, some SMTP stream spewed forth: 
> Hi,
> 
> Why do things slow down after a lot of updates without a VACUUM? This
> slow-down doesn't seem to happen as much with a lot of inserts.
> 
> Don't the indexes know which is the valid updated row? Or does Postgresql
> still have to search for it amongst deleted/invalid rows?
> 
> I can understand why the database would bloat up and/or slow down slightly
> without a vacuum. But why such a significant slowdown? So much so that
> people have to vacuum every two hours.


As it seems you know, PostgreSQL "leaves behind" the stagnant rows after
an UPDATE or DELETE; it merely sets a flag (IIRC) to that effect.
As more and more stagnant data accumulates, PostgreSQL has to dig through
more and more data on the disk (or in cache) which (IIRC) causes the
slowdown. (This is also true with regard to indexes/indices and stagnant
data.)

VACUUMS are good. :)

Actually, having to vacuumn is a Very Bad Thing.
Somebody should do something about it, I suppose.
The online (http://www.postgresql.org/) documentation has some
information about why the old data are not cleaned out on the fly.


Cheers,
dan

> 
> Cheerio,
> Link.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Why do things slow down without a VACUUM?

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-04-30T03:23:57Z

At 09:17 PM 29-04-2001 -0500, GH wrote:
>
>As it seems you know, PostgreSQL "leaves behind" the stagnant rows after
>an UPDATE or DELETE; it merely sets a flag (IIRC) to that effect.

OK. I read http://www.ca.postgresql.org/docs/aw_pgsql_book/node110.html

So the stagnant rows are for the other transactions.

I was hoping that there would be a way for queries to find rows quickly,
ignoring stagnant rows. e.g. maybe a subindex pointing to the latest row
with some info so that transactions know whether they should use the latest
or not (Not valid if your transaction started before... - with the usual
rollover issues ;) ). Something like that anyway.

Cheerio,
Link.

Re: Why do things slow down without a VACUUM?

GH <grasshacker@over-yonder.net> — 2001-04-30T03:38:59Z

On Mon, Apr 30, 2001 at 11:23:57AM +0800, some SMTP stream spewed forth: 
> At 09:17 PM 29-04-2001 -0500, GH wrote:
> >
> >As it seems you know, PostgreSQL "leaves behind" the stagnant rows after
> >an UPDATE or DELETE; it merely sets a flag (IIRC) to that effect.
> 
> OK. I read http://www.ca.postgresql.org/docs/aw_pgsql_book/node110.html
> 
> So the stagnant rows are for the other transactions.
> 
> I was hoping that there would be a way for queries to find rows quickly,
> ignoring stagnant rows. e.g. maybe a subindex pointing to the latest row
> with some info so that transactions know whether they should use the latest
> or not (Not valid if your transaction started before... - with the usual
> rollover issues ;) ). Something like that anyway.

You could probably talk to Alfred Perlstein about the work he did on this
subject. Another thread is bickering about a patch that he (and others?
who knows) developed. The availability of this patch is unknown to me,
but its existence is certain.

Good hunting.
dan

Say, wouldn't it sometimes be so much easier if everybody just shut 
the hell up and did something productive? People spend so much time
fighting about stuff, and the root problem is left dangling amid the dust.

*duck and cover*

> 
> Cheerio,
> Link.

Re: Why do things slow down without a VACUUM?

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-04-30T03:48:52Z

> Say, wouldn't it sometimes be so much easier if everybody just shut 
> the hell up and did something productive? People spend so much time
> fighting about stuff, and the root problem is left dangling amid the dust.
> 
> *duck and cover*

We have had an unusual number of bickering episodes lately.  Hopefully
this is just an abnormality.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026