Re: hardware - generic

> I greatly increased the shmmax to approx 448MB (machine has 521K installed)
> max_connections = 30
> I've changed the shared_buffers to 32768.
> work_mem to 2048.
> maintenance_work_mem to 32768
> max_fsm_page to 40_000

These parameter setting seem a bit aggressive due to the limited amount of ram that you have.
According to:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf

Having shared buffers set too hi can actually hurt preformance. Here is the advice for setting an
appropriate value for shared_buffers:

There is one way to decide what is best for you. Set a high value of this parameter and run the
database for typical usage. Watch usage of shared memory using ipcs or similar tools. A
recommended figure would be between 1.2 to 2 times peak shared memory usage

> It's generally running with about 16K of swap in use, but I can't say what it is
> that is swapped out.

I would reduce your memory setting enough to ensure that you are not using swap. Swapping is a
sure fire way to kill preformance. On a smaller bugzilla server that I set up at work use a
throw-away computer, I reduced query times to 50%-70% by reducing memory usage until swap was no
longer needed. This was a 200Mhz PII with 128Mb RAM 2Gb 5Krpm Harddrive. Running both apache and
postgresql. I reduced apaches connections to 5 and postgresqls connections to 20 and turned down
the shared buffers to about 100.

> vmstat is pretty quiet. Nothing out of the ordinary that would say it's running
> poorly. si/so are 0 99% of the time.

Well if disk writes are not hurting you, I guess the next step would be to determine which queries
are running to slow. Speed improvements could be gained by restructuring sql syntax to the
planner chose better plans or you may need to add a few indexes.

To get started, you should turn on duration_logging in your postgresql conf and turn statement
logging. After a day or two you can scan your log files for quieries that you feel are running
too slow. The next step would be to run an "EXPLAIN ANALYZE" on these exact queries to see the
query plan and estimates used in your query. You can post the explain analyze result here, or the
preformance list to get feedback to see what can be done to reduce query time.

I hope this helps. Also, be sure to carefully ready the above article as it will really aid you
in configuring your postgresql.conf file with reasonable settings.

Regards,

Richard Broersma Jr.