Intermittent JDBC Results
Chris
some details on long running SQL, etc. Though I'm not seeing the
results I expected.
Our site averages about 4-6 requests per second (90% of our requests
are database driven) yet I get only about 1 in 5 requests w/ JDBC data
that can be looked at when drilling down on a request. Most requests
say "None".
Is there a limit to the # of datasources that can be monitored? Is
there anything I am doing wrong when setting fusion reactor up?
Thanks in advance,
Chris
charlie arehart
await an answer from the Intergral folks, I'd propose that you try looking
closely at one of the URLs of the requests you say are lacking the JDBC
info, and confirm that indeed it a) uses a datasource and b) uses one that's
been wrapped. Then, double-check that that DSN is indeed wrapped. It would
be easy for any of these steps to have been missed to cause what you're
seeing.
But if it all checks out, then I'll look forward to hearing what the folks
say.
I'll add, as well, that your settings in the "JDBC settings" (of the
FusionReactor interface) might affect this. If you told it to show only
queries that exceed a certain number of milliseconds, it may be just that
you have none (a good thing). I can't recall if it would still show a count
of all queries to include those that did not exceed that limit. It may be
that it still would but only shows details for those that do.
/charlie
Darren Pywell
There is no limit to the number of data sources that you can monitor.
If requests are saying "None" then the reason tends to be that the
page isn't using the wrapped JDBC datasource but it still using the
original one as Charlie suggests.
Cheers,
Darren
On Jun 27, 6:55 pm, "charlie arehart" <charlie_li...@carehart.org>
wrote:
Chris
said and still not working. The datasource listing says Fusion
Reactor for all datasources, and when I verify any of them, they are
all valid. I've also checked the JDBC settings, and nothing is set
there.
Is there a chance that the database connection is still using the old
datasource without the wrapper? I guess I could try removing the
datasources and adding them again. I'll let you know if I figure it
out.
Thanks.
charlie arehart
things you said. Would hate to see you suffering needlessly. You said "The
datasource listing says Fusion Reactor for all datasources, and when I
verify any of them, they are all valid. I've also checked the JDBC
settings, and nothing is set there."
Just to be clear, simply because the datasource listing "says Fusion
Reactor" and does verify doesn't mean that the DSN is actually using the
FusionReactor (spelled without a space, btw) wrapper. Indeed, when you say
you "checked the JDBC settings" and "nothing is set there", what do you
mean? For each changed DSN, you should see it showing a JDBC URL that starts
with jdbc:fusionreactor:wrapper:{. If that's not there, check out the
FusionReactor JDBC Wrapper Tutorial and User Guides
(http://www.fusion-reactor.com/fr/support.cfm#doc) for more info.
Sorry if any of that is all obvious stuff. Since you go on to say "Is there
a chance that the database connection is still using the old
datasource without the wrapper?", I'm inclined to think you do have it all
right. Still, just pays for us to double check. :-)
As for the effect of datasource changes, they are always immediate in my
experience. No restart or cache refresh is required.
Chris
handful are set up correctly. Im almost positive that my script ran
successfully the first time and all datasources said FusionReactor,
but now they definitely don't. It was done really late at night, so
maybe I saw what I wanted to see in my sleep deprived state.
Anyway...I will rerun my script to convert them all again.
Thanks for the follow up emails. Love the product so far.
On Jun 30, 12:15 pm, "charlie arehart" <charlie_li...@carehart.org>
charlie arehart
that your script didn't run completely :-), it's indeed good to hear that
the help did lead to a diagnosis of the problem. I just tend to trust the
tool first whenever I see such problems. It's nearly always been doing what
it's been told. :-)
Speaking of the script, is that something you might want to share? I'm sure
others would appreciate a tool to help convert a DSN to be wrapped. I've
been meaning to develop one myself (using the Admin API in CF 7 and 8). Even
if you may prefer not to share it, how does yours work?
Before anyone gets too excited about using a tool, though, let Chris'
experience be a lesson: such an automated tool is very powerful, and it
needs to be checked (and double-checked) before and after use. I'd even
recommend taking a backup of the XML files that CF uses for the datasources
(C:\CFusionMX7\lib\neo-query.xml in CF7, and similar in 6, and
C:\ColdFusion8\lib\neo-datasource.xml in CF8, where neo-query.xml has a new
purpose.) If you mess up datasources, people (customers, colleagues, bosses,
admins, etc.) tend to get very upset!
/charlie
-----Original Message-----
From: fusion...@googlegroups.com [mailto:fusion...@googlegroups.com]
On Behalf Of Chris
Sent: Monday, June 30, 2008 2:41 PM
To: FusionReactor
Subject: FusionReactor Group: Re: Intermittent JDBC Results
Chris
list of datasources now say FusionReactor and when I drill down into
any of them, they look correct. Yet, I am still missing JDBC info on
certain requests from certain datasources. Not sure why, but I'll
keep you posted.
As far as the script goes, it's pretty basic. Here it is:
Yes, I always make a backup of the neo-query.xml file before running
this. I've only run it on 7, but it would prob. work on 8 too.
---------------------------------------------------------------------------------
<!--- Depending on # of DS's, this can run for a while ---->
<cfsetting requesttimeout="600">
<cfset URL.cf_password = "password">
<cfset URL.userID = "username">
<!--- Authenticate --->
<cfset cfadmin = createobject("component",
"CFIDE.adminapi.administrator")>
<cfset cfadmin.login("#URL.cf_password#")>
<!--- Create DS object --->
<cfset setDSN = createObject("component","CFIDE.adminapi.datasource")>
<!--- Create a copy, because otherwise you're using the reference
(which is being updated) --->
<cfset dsStruct = structCopy(setDSN.getDatasources())>
<cfloop collection="#dsStruct#" item="dsnRef">
<!--- Create variables --->
<cfset dsnName = dsStruct[dsnRef].name>
<cfset urlString = dsStruct[dsnRef].url>
<cfset hostString = dsStruct[dsnRef].urlMap["host"]>
<!---
name: string, required, name - ColdFusion datasource name.
url: string, required, url - The JDBC Connection URL for this data
source.
class: string, required, class - JDBC class file.
driver: string, optional, driver - JDBC driver.
originaldsn: string, optional, originaldsn - Original ColdFusion
datasource name, if you are renaming this dsn.
port: string, optional, port - port that is used to access the
database server. (default 1433)
username: string, optional, username - Database username.
password: string, optional, password - Database password.
encryptpassword: boolean, optional, encryptpassword - Indicates
whether to encrypt the password when storing it in the neo-query.xml
file:
--->
<cfinvoke component="#setDSN#" method="setOther">
<cfinvokeargument name="name" value="#dsnName#">
<cfinvokeargument name="url" value="jdbc:fusionreactor:wrapper:
{#urlString#};name=#dsnName#">
<cfinvokeargument name="class"
value="com.intergral.fusionreactor.jdbc.Wrapper">
<cfinvokeargument name="driver" value="FusionReactor">
<cfinvokeargument name="host" value="#hostString#">
<cfinvokeargument name="username" value="#URL.userID#">
<cfinvokeargument name="password" value="#URL.password#">
<cfinvokeargument name="port" value="1433">
</cfinvoke>
Updated DSN: <cfoutput>#dsnName#</cfoutput><br />
</cfloop>
-----------------------------------------------------------------------
On Jun 30, 5:38 pm, "charlie arehart" <charlie_li...@carehart.org>
wrote:
charlie arehart
debugging just for those requests and have it report what DSNs are being
used. Then just check that one more time. You say regarding the DSNs that
"when I drill down into any of them, they look correct", but unless you've
really looked at all of them, there's always a chance that some one has
slipped by. This test would help you focus specifically on one being used in
a request not reporting JDBC data.
As for your script to automate wrapping DSNs, thanks for sharing. It may
give folks ideas, but if I can be honest, I'd warn folks against just simply
using it. Study it for ideas, but be aware that it may not suit you.
First, he's presuming use of SQL Server, in setting port 1433. Unless all
your DSNs are using SS and are on that port, this would not be what you'd
want.
Note as well that he's also setting all the DSNs to have a single
username/password, passed in on the URL that calls the page.
Beyond the question of whether you also set userids and passwords on all
your DSNs, let alone setting them all the same, I'd also raise a caution
about using the query string (URL variables) to do that, from 2 aspects of
security.
Besides that querystring data remaining in your browser history (so someone
else on your machine could find it), the query string data also appears in
your web server (and FusionReactor) request logs. If there's any chance that
folks who see that maybe should not know that username/password (or the
cf_password which you also pass in to use for the Admin), it's a security
risk.
One solution is to use a a form which stores it in a session variable. Form
data isn't typically stored in web server (or FR) logs, and it's also not
saved in the browser history.
Not a show-stopper. Just saying it in case others would want to take ideas
from what you did. It's a useful starting point.
In fact, one last concern/observation is that I see you write over all the
current DSNs. I wonder if a better approach would be to use the old ones to
create new ones and leave the old ones as renamed. That way you'd always
have them to fall back on in an emergency. Of course, taking a backup of the
xml file first is nearly as good. Again, just sharing ideas for others, not
knocking what you've chosen to do for yourself.
/charlie
-----Original Message-----
From: fusion...@googlegroups.com [mailto:fusion...@googlegroups.com]
On Behalf Of Chris
Sent: Tuesday, July 01, 2008 10:00 AM
To: FusionReactor
Subject: FusionReactor Group: Re: Intermittent JDBC Results