Thread: On the _need_ to vacuum...
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
* 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
I am rather staggered by a developer considering it necessary to attempt to cooerce the core development team into including a patch. If the work that Alfred has done is as effective as he claims, then there must be a *REALLY* good reason why it isn't being included. I don't want to start any form of war.... But as a user I'd be interested to know why such a patch would appear to be unacceptable. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
Totally agreed. It could just be from a lack of people's time to do things, or I wonder if Alfred's patch is doing things which might not be beneficial? (Maybe there have been decisions on a better way to get it done, but it just hasn't been implemented yet). I'm curious also. Regards and best wishes, Justin Clift geustace@godzone.net.nz wrote: > > I am rather staggered by a developer considering it necessary to > attempt to cooerce the core development team into including a patch. > > If the work that Alfred has done is as effective as he claims, then > there must be a *REALLY* good reason why it isn't being included. > > I don't want to start any form of war.... > But as a user I'd be interested to know why such a patch would appear > to be unacceptable. > > -- > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Glen Eustace, > GodZone Internet Services, a division of AGRE Enterprises Ltd., > P.O. Box 8020, Palmerston North, New Zealand 5301 > Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
* geustace@godzone.net.nz <geustace@godzone.net.nz> [010428 21:44] wrote: > I am rather staggered by a developer considering it necessary to > attempt to cooerce the core development team into including a patch. I'm assuming you refer to the updated page at: http://people.freebsd.org/~alfred/vacfix/ > If the work that Alfred has done is as effective as he claims, then > there must be a *REALLY* good reason why it isn't being included. The work is not mine. It was contracted by my previous employer that I still maintain a close working relationship with. > I don't want to start any form of war.... > But as a user I'd be interested to know why such a patch would appear > to be unacceptable. I never said anyone accused the patch was "unacceptable" I just said it was never integrated nor brought up to date with the 7.1 branch. I'll update the vacfix page to explain better. I also need to update it to explain that the vacfix is not a cure-all, certain degenerate conditions cause it to perform as bad if not worse than a traditional vacuum. -- -Alfred Perlstein - [alfred@freebsd.org] Represent yourself, show up at BABUG http://www.babug.org/
On Sun, Apr 29, 2001 at 04:33:42PM -0020, geustace@godzone.net.nz wrote:
> If the work that Alfred has done is as effective as he claims, then
> there must be a *REALLY* good reason why it isn't being included.
Isn't 7.1 in a code freeze?
That seems like a *REALLY* good reason not to include such a change at this
moment.
Nothing prevents YOU from using it, of course.
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
On Sat, Apr 28, 2001 at 10:22:53PM -0700, Alfred Perlstein wrote: > * geustace@godzone.net.nz <geustace@godzone.net.nz> [010428 21:44] wrote: > > I am rather staggered by a developer considering it necessary to > > attempt to cooerce the core development team into including a patch. > > I'm assuming you refer to the updated page at: > http://people.freebsd.org/~alfred/vacfix/ > I have to agree with this. Alfred's free to do what he likes. I don't recall that he mentions whether this patch is Open Source. If it isn't, then this is all moot. It can't be included in PostgreSQL because of licensing issues. If it _is_ Open Source, then Alfred is free to charge for it. _However_, he makes the threat of potential legal action if you should broadly disseminate a previously downloaded copy of the patch. That's not only not Open Source, it's ANTI- Open Source. On that basis alone, I would be averse to including it in PostgreSQL. The coercion issue is secondary and childish. Paul
* Paul M Foster <paulf@quillandmouse.com> [010429 10:35] wrote: > On Sat, Apr 28, 2001 at 10:22:53PM -0700, Alfred Perlstein wrote: > > > * geustace@godzone.net.nz <geustace@godzone.net.nz> [010428 21:44] wrote: > > > I am rather staggered by a developer considering it necessary to > > > attempt to cooerce the core development team into including a patch. > > > > I'm assuming you refer to the updated page at: > > http://people.freebsd.org/~alfred/vacfix/ > > > > I have to agree with this. Alfred's free to do what he likes. I don't > recall that he mentions whether this patch is Open Source. It's amazing that someone with your writing skills is so lacking in reading comprehension. The patch is free for inclusion in Postgresql source code, the patch is NOT free to end users unless laudered through the Postgresql source tree. > If it isn't, > then this is all moot. It can't be included in PostgreSQL because of > licensing issues. If it _is_ Open Source, then Alfred is free to charge > for it. _However_, he makes the threat of potential legal action if you > should broadly disseminate a previously downloaded copy of the patch. That has been removed, I will not bother doing so and I'm probably not within my rights to do so, there now you have it, an admittion that I believe I'm not within my rights to own this code as it has been previously released. I do with you'd read on before deciding the mirror the damn thing every which place. > That's not only not Open Source, it's ANTI- Open Source. On that basis > alone, I would be averse to including it in PostgreSQL. The coercion > issue is secondary and childish. You're entitled to your opinions no matter how misformed and foolish they are. There is no coercion involved. I also don't think it helps the project for me to be giving Joe End User patches that can speed up vacuum that basically discourage him from using the latest and greatest version, there's a few problems in the 7.0.3 tree that are fixed in 7.1. 7.1 needs mainstream use. And hey, who knows I may actually recoup my investment that has me seemingly forever stuck with 7.0.3. If someone really need the patches they will just have to pay for them. By not having these patches available for 7.1, large sites such as my previous employer can not even consider testing it. With gracious help from the developers and a lot of work on our part we were able to track down and fix several obscure bugs related to corruption and crashes in 7.0.3, unfortunetly we can't deploy 7.1 so we don't know what's lurking there. Anyhow, I really shouldn't have bothered explaining this to someone that thinks that open source == free beer. I guess it was really for the rest of the people that think I'm being a jerk. -- -Alfred Perlstein - [alfred@freebsd.org] Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom.
[ Charset ISO-8859-1 unsupported, converting... ] > I am rather staggered by a developer considering it necessary to > attempt to cooerce the core development team into including a patch. > > If the work that Alfred has done is as effective as he claims, then > there must be a *REALLY* good reason why it isn't being included. We have tried to get it applied to the main tree but the people needed to merge the patch into 7.1 didn't have the time to get it merged. I expect 7.2 will either have the patch or will remove the need for the patch. -- 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
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.
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)
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.
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.
> 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