Re: Performance Issues

On Mon, Sep 08, 2003 at 11:43:42AM +0530, Ravi T Ramachandra wrote:
> I recently setup postgres on a Linux box with 4GB Ram and 2.5 GHz
> processor.

Big box.

> We have created a database with 1.5 million rows in a
> table.

Small database.

> When we try to select rows from the table, it is taking enormous
> time with the default configuration.

Problem is very probably default configuration (you didn't
say which postgresql version you had, it would have been
interesting, but no matter, or specify that your performance
results were consistent during several runs without reboot, but
I suppose so).

Default configuration is for a small box, or a small database,
or "extremely conservative", whatever. It's "the size that
works for everybody", for some modest value of "work". That's
very different from "one size fits all", and you'll agree that
postgresql can't expect to have 4 GB of RAM to play with on most
machines.

You want to tune performance :-)

To begin with, you want to bump shared_buffers from 64 or
512 to 4096 at the very least, probably all the way to say
32768 or maybe 65536 or even more, I don't know if there's
an upper limit, soft or hard (I've never had more than 1GB
RAM on a machine so I've never wondered <jealous> :-) ) Note
that "cat /proc/sys/kernel/shmmax" should be more or equal to
shared_buffers*8192, otherwise maybe postmaster won't start,
a quick check says that my untuned Linux is at 4096*8192. To
change that, umm, google, yes:

http://www.lyris.com/lm_help/7.5/tuning_postgresql.html

which has a lot of other tips.

Continuing in that vein yields

http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://developer.postgresql.org/docs/postgres/kernel-resources.html

And more in-depth:

http://candle.pha.pa.us/main/writings/pgsql/performance.pdf
http://www.argudo.org/postgresql/soft-tuning.html

If you still have performance problems after a dose of that
medicine I'll be very surprised :-)

Nathan