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

In postgresql.conf do you have "hostname_lookup=true" ?
If so you could have a dns problem (to be more accurate a reverse dns
problem) or no dns at all.
So try to disable hostname lookups.
From my experience, dns problems are hard to guess and are the source of
the
biggest connection time problems.

Hope this helps.

Fathi Ben Nasr.

Markus Wollny a écrit :

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

(See attached file: smime.p7s)