This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: [HACKERS] Why Not MySQL? From: "Mitch Vincent" <mitch@huntsvilleal.com> Date: Tue, 2 May 2000 18:28:31 -0400 A very, very good article. I love the comment about MySQL being a filesystem with an SQL interface :-) However.. I'm faced with a huge dilemma. We use PostgreSQL for a fairly large application I wrote, the database is still pretty small, it carries info on about 25-30,000 people and about 5,000 jobs. Recently we've had huge trouble with PostgreSQL -- it seems that every month I stump someone with the obscure things that happen to our data :-) >From corrupted indexes to corrupted system tables, it's almost always unrecoverable. Luckily I always have a backup to restore from and the world goes on... We've only recently started to notice that the backend is slowing down. It seems that with every additional applicant added it get exponentially slower... So, sadly I have to go find another backend for this application -- a commercial one too so we can get "commercial support" (yuck).. So, could you guys suggest some other backends I might look into? I know it's an odd place for me to ask but the flat truth is that I think *I* am to blame for my Postgres troubles and even taking all of the problems into account I think PG is the best damn free RDBMS out there. It's functionality is superior to everyone else's, it's developers are no less than amazing and well -- I trust you guys to give me some honest opinions.. The functionality I need is basically what PG has.. Transactions are a must as well as some sort of sequence -- stability over performance but performance is very important too. It also needs to run native on FreeBSD.. Oracle is out as we use FreeBSD and someone out there decided that they wouldn't support FreeBSD (in the license as well as in the code!).. Thanks guys, especially to all who tried to help in private (Don, Tom -- many others).. -Mitch ==== Subject: Re: [HACKERS] Why Not MySQL? From: Tim Uckun <Malcontent@msgto.com> Date: Tue, 02 May 2000 17:00:45 -0600 > exponentially slower... So, sadly I have to go find another backend for this > application -- a commercial one too so we can get "commercial support" > (yuck).. Actually you might want to give Interbase a try. Version 6 is now in beta and is going to be open sourced. It's been around a while and seems pretty solid. There will be a commercial entity to buy support contracts from and the newsgroups are very helpful. As a bonus there is a realatively large installed user base already and very very nice client side tools available. See http://www.interbase.com/ or http://www.interbase2000.com/ for further details. If I can't get my questions answered about case sensitivity issues here (no help so far) I will most likely to use it myself. > I need is basically what PG has.. Transactions are a must as well as some > sort of sequence -- stability over performance but performance is very > important too. It also needs to run native on FreeBSD.. IB supports transactions and sequences. It's very stable and pretty fast. There was a problem with shared memory in the early beta and earlier releases but it's fixed now. I am pretty sure it runs on FreeBSD but I am not sure if it runs natively or under linux emulation. === Subject: Re: [HACKERS] Why Not MySQL? From: Don Baccus <dhogaza@pacifier.com> Date: Tue, 02 May 2000 15:59:39 -0700 At 06:28 PM 5/2/00 -0400, Mitch Vincent wrote: >So, could you guys suggest some other backends I might look into? I know >it's an odd place for me to ask but the flat truth is that I think *I* am to >blame for my Postgres troubles and even taking all of the problems into >account I think PG is the best damn free RDBMS out there. It's functionality >is superior to everyone else's, it's developers are no less than amazing and >well -- I trust you guys to give me some honest opinions.. The functionality >I need is basically what PG has.. Transactions are a must as well as some >sort of sequence -- stability over performance but performance is very >important too. It also needs to run native on FreeBSD.. First, have you been having the same problems with PG 7.0? I recall that you had it up on a test system but nothing more. It's a pity that you've reached this point, because PG is so much better than it was 18 months ago (and before, of course, I mention that timeframe because that's roughly when I first investigated its suitability for the web toolkit project) and the trajectory is definitely in the right direction. It's also a loss to the development effort, as people with bugs in many ways are more useful than people who have no problems (though of course having no bugs for users to stumble across is the best situation!) Still, I understand the need to solve your problems today, not tomorrow. Interbase is a possible solution. They have a pretty good reputation, and their "super server" (threaded with connections sharing a buffer cache) should scale well. My rough estimate is that they're at about the place PG will be when 7.1 comes out. I don't know if they support FreeBSD, though. Any reason you can't just put up a box with Linux? There's an older version of Sybase available at no charge, again only for Linux, though. === Subject: Re: [HACKERS] Why Not MySQL? From: Hannu Krosing <hannu@tm.ee> Date: Wed, 03 May 2000 07:12:53 +0300 Mitch Vincent wrote: > > A very, very good article. I love the comment about MySQL being a filesystem > with an SQL interface :-) > > However.. I'm faced with a huge dilemma. > > We use PostgreSQL for a fairly large application I wrote, the database is > still pretty small, it carries info on about 25-30,000 people and about > 5,000 jobs. Recently we've had huge trouble with PostgreSQL -- it seems that > every month I stump someone with the obscure things that happen to our data > :-) What version are you using ? > >From corrupted indexes to corrupted system tables, it's almost always > unrecoverable. Luckily I always have a backup to restore from and the world > goes on... We've only recently started to notice that the backend is slowing > down. It seems that with every additional applicant added it get > exponentially slower... So, sadly I have to go find another backend for this > application -- a commercial one too so we can get "commercial support" > (yuck).. Could you be a little more specific on your performance issues ? The usual way to deal wih them is tuning your db structure and/or queries or setting backend options to use more memory for stuff or other such things. If there is something wrong with the structure or queries, then a database switch will help you very little, unless your front-end tool has some special support for _some_ databases and not for others. > So, could you guys suggest some other backends I might look into? The usual - Oracle, Interbase, Informix, DB2, Sybase, Solid The website is usually obtained by putting www inf front and com at the end ;) And let us know of your results. > I know > it's an odd place for me to ask but the flat truth is that I think *I* am to > blame for my Postgres troubles and even taking all of the problems into > account I think PG is the best damn free RDBMS out there. It's functionality > is superior to everyone else's, it's developers are no less than amazing and > well -- I trust you guys to give me some honest opinions.. The functionality > I need is basically what PG has.. Transactions are a must as well as some > sort of sequence -- stability over performance but performance is very > important too. It also needs to run native on FreeBSD.. > > Oracle is out as we use FreeBSD and someone out there decided that they > wouldn't support FreeBSD (in the license as well as in the code!).. Is FreeBSD a religious issue there or can it be negotiated ? === Subject: Re: Corruption (Was: Re: [HACKERS] Why Not MySQL?) From: Vince Vielhaber <vev@michvhf.com> Date: Wed, 3 May 2000 05:30:32 -0400 (EDT) On Tue, 2 May 2000, The Hermit Hacker wrote: > > As Don asks, what happened with the v7.0 trials you were doing? Corrupted > indices, I've seen occasionally in older versions, but I can't recall ever > seeing corrupt system tables ... > > I don't have a GUI browser right, so searching the archives is kinda tough > for me :( Can you refresh my memory for me? There has to be something > logical to this, as to what the cause for the corruption is :( > > >From Don's comment, I take it you are using FreeBSD? Version? Stability > of the machine? Never crashes? > > Version of PostgreSQL? Compile/configure options? Do you have any core > files in your data/base/* hierarchy that would be the result of a backend > crashing? > > I know you are looking at alternatives, but I'm terrible at letting go of > problems :( His description of table corruption and the system running slower and slower sounds like a disk going bad. I've seen it hundreds of times on news machines. Constant retries while trying to write to the disk will give slowdowns. Having data on a spot of the disk that's unreliable will certainly cause data integrity problems. Mitch, have you thoroughly checked the hardware? === Subject: Re: Corruption (Was: Re: [HACKERS] Why Not MySQL?) From: The Hermit Hacker <scrappy@hub.org> Date: Wed, 3 May 2000 09:30:07 -0300 (ADT) On Wed, 3 May 2000, Vince Vielhaber wrote: > On Tue, 2 May 2000, The Hermit Hacker wrote: > > > > > As Don asks, what happened with the v7.0 trials you were doing? Corrupted > > indices, I've seen occasionally in older versions, but I can't recall ever > > seeing corrupt system tables ... > > > > I don't have a GUI browser right, so searching the archives is kinda tough > > for me :( Can you refresh my memory for me? There has to be something > > logical to this, as to what the cause for the corruption is :( > > > > >From Don's comment, I take it you are using FreeBSD? Version? Stability > > of the machine? Never crashes? > > > > Version of PostgreSQL? Compile/configure options? Do you have any core > > files in your data/base/* hierarchy that would be the result of a backend > > crashing? > > > > I know you are looking at alternatives, but I'm terrible at letting go of > > problems :( > > His description of table corruption and the system running slower and > slower sounds like a disk going bad. I've seen it hundreds of times > on news machines. Constant retries while trying to write to the disk > will give slowdowns. Having data on a spot of the disk that's unreliable > will certainly cause data integrity problems. That was one thing I was thinking ... the other was the possibility that he's mount'd async and his machine is rebooting ... *or* he has memory problems causing the shared memory to corrupt, dump the postmaster process which is corrupting his tables ... === Subject: Re: Corruption (Was: Re: [HACKERS] Why Not MySQL?) From: "Mitch Vincent" <mitch@huntsvilleal.com> Date: Wed, 3 May 2000 10:44:28 -0400 > His description of table corruption and the system running slower and > slower sounds like a disk going bad. I've seen it hundreds of times > on news machines. Constant retries while trying to write to the disk > will give slowdowns. Having data on a spot of the disk that's unreliable > will certainly cause data integrity problems. > > Mitch, have you thoroughly checked the hardware? Checked and replaced twice. We're using Ultra 160 SCSI drives now so the speed there isn't a problem I hope. :-) === Subject: Re: Corruption (Was: Re: [HACKERS] Why Not MySQL?) From: The Hermit Hacker <scrappy@hub.org> Date: Wed, 3 May 2000 11:48:03 -0300 (ADT) On Wed, 3 May 2000, Mitch Vincent wrote: > > I don't have a GUI browser right, so searching the archives is kinda tough > > for me :( Can you refresh my memory for me? There has to be something > > logical to this, as to what the cause for the corruption is :( > > Ok, the latest thing was "cannot find attribute 15 of relation pg_am" -- I > got that when I tried to do an query. Someone recently posted similar, and I swore he answered himself with a 'vacuuming fixed the problem' ... but I could be mis-quoting ... > > Version of PostgreSQL? Compile/configure options? Do you have any core > > files in your data/base/* hierarchy that would be the result of a backend > > crashing? > > PG 6.5.3, no core files (this latest time at least, in the past there have > been). > > As far as configure options, nothing, just the default configuration... Okay, with v7.0, I'd recommend adding --enable-debug, so that if a core does creep in, we can analyze it ... My first and foremost recommendation is to upgrade to v7.0 first ... take your test machine and make sure you have no problem with the dump/reload, but v7.0 is, once more, leaps and bounds ahead of v6.5.3 ... no guarantees it will make a difference, but at least it gets you into a release that we're going to be focusing on debugging intensely over the next little while ... === Subject: Re: [HACKERS] Why Not MySQL? From: Tom Lane <tgl@sss.pgh.pa.us> Date: Wed, 03 May 2000 11:21:47 -0400 "Mitch Vincent" <mitch@huntsvilleal.com> writes: >> First, have you been having the same problems with PG 7.0? I recall that >> you had it up on a test system but nothing more. > No, I was afraid to run 7.0 beta on the production server. I do have it on > my development server however and haven't had any problems with it (of > course the devel server is only used by me, the production server is used by > about 600 people)... FWIW, we've fixed a huge number of bugs since 6.5.*. Even the beta versions of 7.0 are more stable than any prior release IMHO (and we've seen no beta test reports that would contradict that). I'd really like === Subject: Re: [HACKERS] Why Not MySQL? From: Tom Lane <tgl@sss.pgh.pa.us> Date: Wed, 03 May 2000 11:28:51 -0400 "Mitch Vincent" <mitch@huntsvilleal.com> writes: >> Could you be a little more specific on your performance issues ? > Well, I'm just noticing that simple select queries are taking 3-5 seconds - > on a table with 63 fields, 10000ish rows of data. The ID fields are > indexed, as well as several always-searched varchar() fields. Hmm. What does EXPLAIN show for the query plan? You might also try turning on execution stats (run psql with PGOPTIONS="-d2 -s" for starters) to see what getrusage() can tell. The results will be in the postmaster log and might look like this: StartTransactionCommand query: SELECT usename, relname, relkind, relhasrules FROM pg_class, pg_user WHERE usesysid = relowner and ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') and relname !~ '^pg_' and (relkind != 'i' OR relname !~ '^xinx') ORDER BY relname ProcessQuery ! system usage stats: ! 0.083256 elapsed 0.040000 user 0.000000 system sec ! [0.080000 user 0.020000 sys total] ! 12/1 [46/11] filesystem blocks in/out ! 0/0 [1/2] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [2/2] messages rcvd/sent ! 8/5 [29/10] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 18 read, 0 written, buffer hit rate = 94.29% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand === Subject: Re: [HACKERS] Why Not MySQL? From: The Hermit Hacker <scrappy@hub.org> Date: Wed, 3 May 2000 12:29:31 -0300 (ADT) On Wed, 3 May 2000, Mitch Vincent wrote: > Here are some typical queries my application might generate. Please, let me > know if you see anything that can be improved! First comment ... Tom Lane always jumps on me on this ... if you are going to send a QUERY to get recommendations on, send in an EXPLAIN on that query also, so that we can see what the backend 'thinks" its going to do ... > select * from applicants as a where a.created::date = '05-01-2000' and > a.firstname ~* '^mitch' limit 10 offset 0 First comment, that Tom can clarify in case I'm wrong ... when I ran UDMSearch under v6.5.3, there was a problem where a LIKE query was causing a query to take forever to complete ... Tom, at the time, got me to change the query so that instead of: url LIKE '%s' it was: (url || '') LIKE '%s' Now, this was in an earlier RC of v7.0 that I had to do this, and Tom made some changes to the following one to 'fix the problem', but my performance went from several *minutes* to several *seconds* of time to complete the exact same query ... > > The usual way to deal wih them is tuning your db structure and/or > > queries or > > setting backend options to use more memory for stuff or other such > > things. > > I'd love some pointers! This machine has lots-n-lots of memory. I'd love to > make postgre use more than normal if it would get me better speed! on my machine (FreeBSD 4.0-STABLE), I'm currently running with a kernel of: options SYSVSHM options SHMMAXPGS=262144 options SHMSEG=32 options SYSVSEM options SEMMNI=40 options SEMMNS=240 options SEMMNU=120 options SEMMAP=120 options SYSVMSG and a -B set to 4096 and -o ' -S 16384 ' ... the -B deals with teh amoun tof shared memory, the -S I'm using only affects stuff like ORDER BY and GROUP BY (allocates up to how much RAM to use on a sort before going to disk ... ===