Re: Performance tips

Andrew Perrin <andrew_perrin(at)unc(dot)edu> writes:

> The computer is a 1Ghz PIII (IBM NetVista) running debian linux
> (woody) and PostgreSQL 7.1.3. There's 512M of RAM in it, and top shows
> that swap rarely gets used, so one possibility is to try to have pg keep
> more workspace in RAM at once. I could also potentially buy more RAM for
> the machine.

Do try to keep it out of swap, but you may have scope for increasing
the number of shmem buffers. More RAM will always help, as will
getting more and faster disks and spreading the I/O load over them.
Take a look at 'vmstat' output and your CPU usage while you're running
a query to see where your bottlenecks might be.

Unless you have enough RAM to cache the whole thing, a database is
usually I/O bound, which means your disk subsystem is probably a good
place to improve.

Also: VACUUM ANALYZE (are you running it)? Does EXPLAIN show
reasonable plans for all your queries?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863