Re: Why Not MySQL? - Mailing list pgsql-hackers

Okay, single or dual processor machine?  I'm running Dual myself, and for
any largish DB, I would highly recommend that regardless of OS ... at
least if a query starts to hog CPU, you have a second one to use ...

On Wed, 3 May 2000, Mitch Vincent wrote:

> > > 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 ?
> 
> 6.5.3 :-)
> 
> > > >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 ?
> 
> 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.
> 
> Here are some typical queries my application might generate. Please, let me
> know if you see anything that can be improved!
> 
> select * from applicants as a where a.created::date = '05-01-2000' and
> a.firstname ~* '^mitch' limit 10 offset 0
> 
> select * from applicants as a,applicants_states as s where a.firstname ~*
> '^mitch' and s.rstate='AL' and s.app_id=a.app_id limit 10 offset 0
> 
> .... There are 63 fields in the 'applicants' table, all of which are
> searchable. Would it be a good or bad thing to index all fields that are
> searchable?
> 
> > 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!
> 
> > 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.
> 
> PHP/C is what I use to access PostgreSQL.
> 
> > > 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 ?
> 
> Not religious, though I love that OS like I love my right arm.. I am stuck
> with x86 hardware -- that's pretty much it (and eliminates Solaris and
> several other as a possible OS I suppose)..
> 
> THANKS!!
> 
> -Mitch Vincent
> 
> 
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org