Обсуждение: 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

Markus Wollny wrote:
>
> 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?

Enable full debugging output in ColdFusion. Do not forget to enable it
in each query statement (use the debug="true" attribute) to get query
stats. Compared to what you see when connecting to Oracle, anything
weird? In the NT Performance Monitor, what is the average request time,
queue time and db time?

If you have queries returning multiple rows, make sure you have a
blockfactor set, this can make up a significant difference. Are you
returning text fields in queries? What if you explicitly cast them to
varchar fields (SELECT textfield::varchar ...)?

What are your settings for connection pooling? How does the bahaviour
change when you change that setting? Are you using cfqueryparam by any
chance? What happens if you change that?

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer fro ColdFusion

http://www.macromedia.com/support/forums/team_macromedia/

On Sat, Jul 06, 2002 at 02:07:34AM +0200, Jochem van Dieten wrote:
> If you have queries returning multiple rows, make sure you have a
> blockfactor set, this can make up a significant difference. Are you
> returning text fields in queries? What if you explicitly cast them to
> varchar fields (SELECT textfield::varchar ...)?

What is the reasoning behind that?

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Neil Conway wrote:
> On Sat, Jul 06, 2002 at 02:07:34AM +0200, Jochem van Dieten wrote:
>
>>If you have queries returning multiple rows, make sure you have a
>>blockfactor set, this can make up a significant difference. Are you
>>returning text fields in queries? What if you explicitly cast them to
>>varchar fields (SELECT textfield::varchar ...)?
>
> What is the reasoning behind that?

No reasoning, just personal experience with ColdFusion 4.5.1 SP2,
PostgreSQL 7.1, the Windows ODBC driver 7.01.00.06 and query timeouts.

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer fro ColdFusion

http://www.macromedia.com/support/forums/team_macromedia/

Le Vendredi 5 Juillet 2002 19:20, Markus Wollny a écrit :
> 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.

IMHO, this is not an ODBC problem.
1) Did you allow enough shared memory to PostgreSQL?
2) If a page takes 50 seconds to load, this is most likely a problem of
long-running queries. For example, if a views containing an ORDER BY
statement is used in an INNER or LEFT JOIN, PostgreSQL is likely to take
seconds to perform the join. So, turn debugging on and try to locate
long-running queries.

Maybe you should post your questions on pgsql-general@postgresql.org where you
are more likely to meet Oracle users.

Cheers,
Jean-Michel POURE

Hello!

Probably not a problem of ODBC itself but more likely due to the change
in architecture in combination with the behaviour of the ColdFusion
server. With Oracle and it's schema-support (I'm looking very much
forward to seeing this feature in PostgreSQL later this year, hopefully)
it was easy for CF to assign the connections, because there only was one
datasource. Now we've got ten datasources altogether which could, I
presume, lead to the effect, that several transactions are carried out
for one datasource, but several others are still on queue for other
datasources, preventing the completion of the current website and
therefore just loading up ColdFusions request-queue in a vicious circle.

We're doing as much as possible to avoid long queries and have been
quite successful after a difficult start - Oracle's optimizer is quite a
bit more good natured and we've been a bit spoiled by it, I think. We've
eliminated all the "killer queries" and now we've got execution times of
just over 2 seconds for some rare big queries, most of the lot is well
below half a second.

We're still trying to find out what the bootleneck is, so thanks for
your hints!

Regards,

    Markus

> -----Ursprüngliche Nachricht-----
> Von: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Gesendet: Montag, 8. Juli 2002 10:50
> An: Markus Wollny; pgsql-odbc@postgresql.org
> Cc: cfdev@oosha.com; pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Performance of ODBC-Driver /w IIS5.0/ColdFusion
>
>
> Le Vendredi 5 Juillet 2002 19:20, Markus Wollny a écrit :
> > 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.
>
> IMHO, this is not an ODBC problem.
> 1) Did you allow enough shared memory to PostgreSQL?
> 2) If a page takes 50 seconds to load, this is most likely a
> problem of
> long-running queries. For example, if a views containing an ORDER BY
> statement is used in an INNER or LEFT JOIN, PostgreSQL is
> likely to take
> seconds to perform the join. So, turn debugging on and try to locate
> long-running queries.
>
> Maybe you should post your questions on
> pgsql-general@postgresql.org where you
> are more likely to meet Oracle users.
>
> Cheers,
> Jean-Michel POURE
>

Hi!

No, hostname_lookup is set false - this db is in a backend plane, so
there's no direct route to the outside internet anyway - hostname lookup
wouldn't make any sense. Our problem seems to lurk somewhere else... But
thank you for your answer!

Regards,

    Markus

> -----Ursprüngliche Nachricht-----
> Von: Fathi Ben Nasr [mailto:fathi.engineer@gnet.tn]
> Gesendet: Dienstag, 8. Januar 2002 08:51
> An: Markus Wollny; cfdev@oosha.com; pgsql-general@postgresql.org
> Cc: pgsql-odbc@postgresql.org
> Betreff: Re: [GENERAL] 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 :
>

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)

Markus,

Have you run Windows Performance Monitor and watched the CF counters?
 They may help pinpoint your problem.  Do you have maintain database
connection selected in your datasources?

-
Rod


Markus Wollny wrote:

>Hi!
>
>No, hostname_lookup is set false - this db is in a backend plane, so
>there's no direct route to the outside internet anyway - hostname lookup
>wouldn't make any sense. Our problem seems to lurk somewhere else... But
>thank you for your answer!
>
>Regards,
>
>    Markus
>
>
>
>>-----Ursprüngliche Nachricht-----
>>Von: Fathi Ben Nasr [mailto:fathi.engineer@gnet.tn]
>>Gesendet: Dienstag, 8. Januar 2002 08:51
>>An: Markus Wollny; cfdev@oosha.com; pgsql-general@postgresql.org
>>Cc: pgsql-odbc@postgresql.org
>>Betreff: Re: [GENERAL] 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 :
>>
>>
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
>
>