ColdFusion MX + PostgreSQL - why slow?

[John H]

Can anyone help with why our pages are slow to load during peak-use
hours? We run a database site getting 4 million hits a month which
becomes painfully slow during peak hours.

The slowness is not reflected in Coldfusion's debug output; for
example the total run-time of 5 heavy queries is typically 1000 ms,
but during peak times the page does not load for 4-10 seconds.
Processor activity at peak times is "busy, but not flat-out" (although
hard to tell, because hyperthreading is activated).

We are using ColdFusion MX 6.1 linked by jdbc to Postgresql 7.3 on
Redhat Linux 3 ES, with Apache 2 web-server. We have a twin Pentium 4
system with 4gig ram housed in a data-centre, so it's probably not
down to hardware or connectivity.
Any tips on...

* Vacuuming (we are doing "vacuum analyze" daily).

* Cold Fusion configuration ( jdbc? Max. number of cached queries?
).

* "postgresql.conf" has been left at its default values.

* Apache configuration (apart from adding mod_jrun) has been left
alone.

Many thanks!

[Christopher Browne]

In the last exciting episode, jo**@lh-int.demon.co.uk (John H) wrote:

Any tips on...

* Vacuuming (we are doing "vacuum analyze" daily).
Possibly needs to be more often, if there are a lot of updates during
the busy times.
* "postgresql.conf" has been left at its default values.

That would be a likely source of problems. The defaults are intended
to allow it to compile and run the regression tests _anywhere_. They
use only minimal amounts of memory for buffer cacheing, and assume
that you'll only have a small amount of filesystem cache, too.

Bump up the shared buffers, as well as the amount of expected
cacheing, and you'll likely see better results.
--
output = reverse("gro.mca" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/sgml.html
THERE'S TOO MUCH BLOOD IN MY CAFFEINE SYSTEM!!