Performance of ODBC-Driver /w IIS5.0/ColdFusion

Hello!

We've still not been successful in our attempts to migrate from oracle
to PostgreSQL - and we've got ODBC under suspicion to cause a servere
bottleneck. Here's our setup:

Database: SuSE Linux 7.3 (kernel 2.4.10), 4xPIII550 XEON, 2GB RAM, 30GB
SCSI-Hardware-RAID 5 with 5 harddisks, PostgreSQL 7.2.1 configured
--with-odbc.
Three Webservers: Windows 2000 Server, IIS5, ColdFusion 5, 2xPentium
667MHz, 768MB RAM each, SCSI-Hardware-RAID 1 with 3x18GB disks.
As PostgreSQL doesn't support schema as yet, we had to rewrite our whole
architecture using separate databases, each of those being a separate
ODBC-datasource for ColdFusion on our webservers; we've got ten
datasources all together. The complete postgres-data-directory is
approximately 3GB in size, we installed the latest ODBC-driver on the
webservers (psqlodbc-07_02_0001).

Number of processes are limited to 190 on the database, and that seems
to be sufficient, too, with about 120 backends open most of the time.

This setup powers a couple of websites, the biggest of which has got
approximately 10 million page impressions per month; in peak times that
amounts to about 8 pi/s with an average of about 4 queries per page.

The whole thing was running absolutely flawlessly under Oracle 8i on the
very same hardware (except that before starting migration we used to
have only 1GB of RAM on the database machine) and in the very same
combination with ColdFusion. Now we have got loading-times of 30-50
seconds per page and upwards. The database load itself just keeps at
2-5, average idle-process is about 10-20%, there's another 700-800MB of
pyhysical RAM available, no swap-space used at all, network-load is
about 80k/s, so nothing to be afraid of; SQL-code has been optimized for
PostgreSQLs optimizer, so we've located and eliminated almost all of
those queries that cost us high server loads and long execution times.
We have got output of the execution times of each website enabled,
telling us that a certain website needed a script execution time of 5
seconds - and it still needed more than 45 seconds to come up. Same
applied for the ColdFusion Administrator webfrontend - it took ages to
come up as long as we had our main-site running our PostgreSQL-Version.
As soon as we switched back to Oracle 8i, everything was fine again.

The webservers themselves do not seem to be all too busy either, RAM and
processor-time both being always available in satisfying amounts. I
think we may safely rule out insufficient system resources on both
database and application/webserver - there's just nothing there which
could be up to the limit. So it seems to be something of an
architecture-problem, something that seriously affects ColdFusion 5 in
combination with PostgreSQL-ODBC.

Now what I'd need is some advice on how to locate the bottleneck - could
it be ODBC itself? If so, how can we find a remedy? Has anybody got some
similar combination (IIS/CF/ODBC/PGSQL) up and running with similar size
in terms of access and size of the database? Where it it that we're
going wrong? Can we tune some ODBC setting somewhere? How do we
configure ColdFusion correctly to get maximum performance?

Regards,

Markus