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)