Skip site navigation (1) Skip section navigation (2)

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

From: Fathi Ben Nasr <fathi(dot)engineer(at)gnet(dot)tn>
To: Markus Wollny <Markus(dot)Wollny(at)computec(dot)de>, cfdev(at)oosha(dot)com,pgsql-general(at)postgresql(dot)org
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Performance of ODBC-Driver /w IIS5.0/ColdFusion
Date: 2002-07-08 05:50:56
Message-ID: 200207080852.g688q6v08466@smtp.planet.tn (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-odbc

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)

Attachment: =?iso-8859-1?Q?smime.p7s?=
Description: application/octet-stream (1.9 KB)

In response to

pgsql-odbc by date

Next:From: Jean-Michel POUREDate: 2002-07-08 08:50:16
Subject: Re: Performance of ODBC-Driver /w IIS5.0/ColdFusion
Previous:From: Jochen WestlandDate: 2002-07-07 21:45:21
Subject: HELP!! #DEFINE NAMEDATALEN 128 in postgres_ext.h causes ODBC-ERROR(s) ??

pgsql-general by date

Next:From: Curt SampsonDate: 2002-07-08 06:17:36
Subject: Re: I am being interviewed by OReilly
Previous:From: Robert L MathewsDate: 2002-07-08 05:46:12
Subject: Re: I am being interviewed by OReilly

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group