Re: DB Performance

From: "Steve Wolfe" <nw(at)codon(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: DB Performance
Date: 2002-11-01 18:53:57
Message-ID: 002901c281d8$09faddf0$d281f6cc@WEASEL
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

> I need to find a way to increase performance on my server.

> We are running apache, mod-perl, sendmail, and postgres on our server.
The
> machine is a dual 900Mhz processor with 2 gigs of ram, and fast 10k raid
> drives.

> What else can I do to help performance?
> Will a beowulf cluster help to increase performance?

Moving the web serving off of the DB machine will probably help, but
there's a chance you'll need more horsepower still. There are several
things that could be your bottleneck - CPU power, I/O, etc.. When your DB
machine is under heavy load, is the CPU utilized completely? If not, then
you may very well be running into I/O problems between the memory and the
bus.

Remember that in a dual P3 setup (this is a P3, right?), you've got a
133 MHz memory and front-side bus, but both processers are fighting for
that, giving each one an effective 66 MHz. That's not very much. Moving
to an architecture with more bandwidth would probably improve things
significantly. In our case, moving from a quad Xeon 700 to a dual Athlon
MP2000+ dropped our system loads in *half*. That's attributable not only
to the increase in CPU power, but also to the doubling of the FSB/memory
bandwidth. I've often wondered how a P4 system, with the 400 MHz or 566
MHz FSB would work as a database server, but haven't had the chance to
play with any.

After all of the hardware advice, you could also do some profiling of
your queries, and try and optimize them better. I've seen very simple
optimizations drop query costs by a factor of nearly 100 before - and if
those types of queries are being executed very often, that can add up to a
very large increase in performance.

You could also look into connection pooling. In our environment, which
may be similar to yours, we make a very large number of database
connections throughout the day. Some are for big, nasty reporting-type
queries, many are for small, fairly trivial queries. In rudimentary
tests, connection pooling increased our throughput by anywhere from 50% to
600%.

steve

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Robert Treat 2002-11-01 19:54:05 Re: [pgsql-performance] Is dump-reload the only cure?
Previous Message Andrew Sullivan 2002-11-01 18:27:26 Re: HA for high insert volume