OK, here's the longer note with some thoughts.
Executive summary
It may be that you're dealing with a host who simply doesn't
understand CF very well, so their recommendation to look at your code
regarding connections may be dodgy.
But there are indeed settings in the CF Admin that could be
affecting this, which haven't been discussed in this thread yet. Also, if
you use USERNAME/PASSWORD attributes in CFQUERY, that could affect pool
reuse (and from that standpoint, then, they could be right in their
recommendation).
First, as for their recommendation to "revise your code and ensure that you
are utilising connection pooling", it could be that your host is more
familiar with .NET, Java, or other languages where one does indeed control
pooling manually (in code). As has been said, though, in CFML we do not
typically and instead rely on the engine to do it, as per our Admin console
settings for the DSN. (Only rarely might one bother to manipulate the
pooling process in CFML code.)
But there are indeed settings in the CF Admin that can affect this. No one
has mentioned, but what are your settings related to connections for the
DSNs in question? First and foremost, what about "maintain connections"? Do
you have that enabled? If not, then you would indeed use more connections
than needed. I do realize some argue against enabling it--but that will
cause higher connection use and prevent pool reuse.
Perhaps you have enabled that setting: how might one generate a lot of
connections even so? Well, do you use the username and password attributes
on CFQUERY? If so, that would prevent reuse of a connection by someone using
a different username/password. If you had no choice in that matter, there is
another setting to limit how many connections you do create.
In fact, even if you don't use per-user authentication of CFQUERY, that
setting could help you.
It's the DSN connection setting for "limit connections" which, along with
its "restrict connections to" value. That would seem just the solution here
to restrict how many you create. Still, I saw Sean's later note that this is
a rather high-volume site, so you will want to be careful in evaluating (and
understanding) the impact of any such changes. You seem to be asking for
ideas, though, which is why I write.
Steven Erat of Adobe did a nice blog entry on this, including some case
examples and recommendations, back in 2005 at:
http://www.talkingtree.com/blog/index.cfm/2005/3/14/ConnPooling1
Note also that there are two other DSN settings related to connections:
"timeout" and "interval" options (as opposed to "login timeout"). Perhaps
those need to be tweaked.
Finally, take note if you may have any values in the connection string which
might have an impact on connection pooling? There are some.
You had mentioned in an earlier note that you "might hit up the host for
more info and check they have their CF Admin settings correct." Let's hear
what you have.
If it's just that your site is a higher volume CF site than your host is
used to, and none of the above help, then perhaps there's nothing to be done
other than to negotiate with them to let you use more connections, or change
plans, or change hosts.
Hope that's helpful to you and others.
/charlie
-----Original Message-----
From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of silverbeetle
Sent: Sunday, September 23, 2007 7:46 PM
To: cfaussie
Subject: [cfaussie] Re: Connection Pooling
Hey Barry
Well - could well be a scapegoat. i think my code is pretty clean and
efficient.
I dont have any long running queries apart from a few admin related ones.
Some of the nav is generated from queries but this is all cached to avoid
database getting hammered with each users request.
So i guess i was looking here to check that isn't some cf code related
connection pooling technique that i'm not aware of and that they indeed are
just passing the buck. Wouldnt be the first time...
Cheers
Chris
On Sep 21, 2:56 pm, "Barry Beattie" <[EMAIL PROTECTED]> wrote:
> Chris,
>
> are you sure they're not just picking on you and treating you as a
> scapegoat for some other problem?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~----------~----~----~----~------~----~------~--~---
