FusionReactor and Your ColdFusion Queries

In my last article on FusionReactor, I talked about slow pages and how the tool helps you find them. In that article, I specifically avoided talking about one of the biggest culprits of slow pages – database queries. My history with ColdFusion goes back to about version 2 and even back then database queries were the primary culprit in poorly performing applications.

There are multiple reasons why database queries can be a chokepoint for your application:

 

• A poor connection to the database.
• A poorly configurated database.
• A poorly configurated table.
• A poorly written ColdFusion query.
• And so forth.

In an ideal world, your organization has a DBA (database administrator) who tunes the database and tables and then crafts a beautiful SQL (or stored procedure) you can simply drop into your application. Unfortunately very few of us live in that ideal world. It’s also very easy to simply ignore the problem. SQL, like any language, lets you get stuff done quickly and it can be easy to not consider the performance aspects of your query. Like any poorly written piece of code, a “slightly bad” query in a request can then be joined by another slightly bad one and slowly compound into a poorly performing page.

Being that database activity is such an important part of the performance, it’s no surprise FusionReactor has specific reporting tools focused on just that area. In this post, I’m going to share what that looks like and share some examples of the kind of reports you can find.

JDBC Reports

In my last post, I explained that JDBC stands for Java Database Connectivity. Any time you use a cfquery tag (or queryExecute function), you’re making use of JDBC to allow your ColdFusion templates to speak to a database. Within FusionReactor, you’ll want to start with the JDBC icon on the left:

Under here you’ve got a variety of options:

• JDBC Activity – i.e. what’s going on right now.
• JDBC History – historical activity.
• JDBC Error History – past query errors.
• Activity Graph and Time Graph – a “live” graphical report of JDBC activity.
• Longest Transactions and Slowest Transaction – to be honest, this was a bit confusing. Wouldn’t the longest transaction also be the slowest transaction? The “Longest” report will show the transactions that have taken the longest to execute, no matter how long. The “Slowest” report is a report of all transactions over a particular threshold. So it may be possible that you have nothing there as your ColdFusion queries are performant, but the “Longest” report will still rank them for you.
• Trans. By Mem – reports on queries with high memory usage.
• Stack Trace Filter – it’s possible that multiple different things are making use of your database. The stack trace filter lets you reduce the amount of “noise” you may get from other applications. By default, there’s filters set up for .cfm, .cfc, and .jsp.
• Databases – this gives a really cool report on how you’re using your databases. I’ll be sharing an example of this later.
• Settings – this is where you can configure how FusionReactor logs and monitors your database transactions

As with the previous examples I’ve shown from FusionReactor, take note of the controls on the top right to allow for filtering, reloading, and so forth. What isn’t obvious from the screenshot is that the “All SubFlavors” button actually lets you filter by the type of query, select, insert, and so forth. That’s pretty neat.

The main table of data reports on the app that was being used (I’m just working in my default Lucee home directory) and the SQL that was used. You can see the file name as well as timing information. Note the Time column which shows you how long the particular query took.

Notice how the SQL is reported as well. One of the features of FusionReactor is to automatically replace queryparam values with their ‘real’ values when reporting on the query. You can enable or disable this feature under the “JDBC/Settings” page. While this is a cool feature, it means it’s difficult to see where you’ve forgotten to use queryparams. I’ve reported to the FusionReactor folks that it would be nice if it was obvious when such a replacement has happened, maybe by using bold tags or some such. That way if you a query is not using queryparams it will be easier to find and correct.

The detail view is very deep. Here’s the main tab of information:

There is almost an overwhelming amount of information here, but I’d probably focus mostly on the execution time values under JDBC and the memory section. Here’s the JDBC tab:

As before, there’s a lot of information, but I’d focus on the row count. If you’ve ever seen someone select everything from a table and then use ColdFusion conditionals to restrict what is shown, then you know why this is a problem. The query is returning a mind-boggling twenty-seven thousand rows. There’s no way that was intentional. (Ok, for my test it was, but you get my point.)

The final tab, Relations, gives you a good look at the query within the page it was working. For example, this page had multiple coldFusion queries and you can see how they impacted the overall total page performance.

Finding Query Errors

Let’s now take a look at how FusionReactor reports errors. To test, I ran two requests with simple SQL errors, one trying to get a field that didn’t exist and one against a table that didn’t exist. Here’s how the main error history page shows the results.

For the most part, this is fairly similar to the history report, except now you can get a short report of the error. As a test, I shut down my MySQL server and ran my request again. As expected, I got an error. However, that error does not show up in this report. It does show up under “Request/Error History” though. My guess is that since Lucee couldn’t ever speak to MySQL, a JDBC transaction was not made. That makes sense to me, but just keep in mind that you may want to check both error reports when hunting down issues.

The detail view is the same as before but with a new tab called “Error Details”:

As always, I find the stack trace a bit hard to parse, but the error details on top seem nice and clear to me. Notice the debug button on the right. This allows you to add a breakpoint for future errors like this. I’m going to talk about FusionReactor’s debugging features later.

JDBC Graphs

FusionReactor offers two reports of JDBC activities. The first is just raw activity (Activity Graph) while the second (Time Graph) reports on how long queries are taken. Both default to a “live” view but also let you look at specific time ranges. Here’s an example of both, but note the graphs are a bit boring for me as this is just my local laptop.

Finding Those Bad ColdFusion Queries

As I explained above, FusionReactor provides two reports to help you find slow queries. “Longest Transactions” will simply list out all your recorded transactions sorted by time. “Slowest Transactions” focuses on queries that are slower than a defined threshold. You can set that threshold in the settings panel, under “History”. The default value is 3 seconds. There are quite a few interesting things you can tweak in the settings panel so I recommend taking a look at it. For my laptop, with again a simple test and not “real” data, here’s the “Longest Transactions” view:

Your Database Use

The final thing I want to show is probably the coolest. The “Databases” report gives you a report on how you are using each of the datasources on your server. It breaks it down by type of operations as well as table usage. It also reports on the queries sent to the datasource.

This is just freaking cool as heck to me. While I think most of us will deal with one database per server, larger more complex applications could be dealing with numerous databases. A report like this could help you figure out if one, or more, are perhaps being used rarely enough to be decommissioned or transitioned to one main database. The table report could be really useful too. I’ve dealt with projects hitting a database with a huge amount of tables. This can give you an idea of what’s actually being used.

Summary

As I said in the beginning, database issues tend to be the number one culprit when it comes to poorly performing ColdFusion queries on sites. I think the JDBC set of features in FusionReactor will probably be your top tool for helping improve your applications. I know this is where I’d start looking!

Thanks to our special guest author Raymond Camdem for writing this article for us.

Blog : www.raymondcamden.com
Twitter: raymondcamden

The post FusionReactor and Your ColdFusion Queries appeared first on FusionReactor Observability & APM.

FusionReactor and Your ColdFusion Queries

In my last article on FusionReactor, I talked about slow pages and how the tool helps you find them. In that article, I specifically avoided talking about one of the biggest culprits of slow pages – database queries. My history with ColdFusion goes back to about version 2 and even back then database queries were the primary culprit in poorly performing applications.

There are multiple reasons why database queries can be a chokepoint for your application:

• A poor connection to the database.
• A poorly configurated database.
• A poorly configurated table.
• A poorly written ColdFusion query.
• And so forth.

In an ideal world, your organization has a DBA (database administrator) who tunes the database and tables and then crafts a beautiful SQL (or stored procedure) you can simply drop into your application. Unfortunately very few of us live in that ideal world. It’s also very easy to simply ignore the problem. SQL, like any language, lets you get stuff done quickly and it can be easy to not consider the performance aspects of your query. Like any poorly written piece of code, a “slightly bad” query in a request can then be joined by another slightly bad one and slowly compound into a poorly performing page.

Being that database activity is such an important part of the performance, it’s no surprise FusionReactor has specific reporting tools focused on just that area. In this post, I’m going to share what that looks like and share some examples of the kind of reports you can find.

[/vc_column_text][us_separator size=”small”][vc_column_text]

JDBC Reports

In my last post, I explained that JDBC stands for Java Database Connectivity. Any time you use a cfquery tag (or queryExecute function), you’re making use of JDBC to allow your ColdFusion templates to speak to a database. Within FusionReactor, you’ll want to start with the JDBC icon on the left:

[/vc_column_text][us_separator size=”small”][us_image image=”57374″][us_separator size=”small”][vc_column_text]

Under here you’ve got a variety of options:

• JDBC Activity – i.e. what’s going on right now.
• JDBC History – historical activity.
• JDBC Error History – past query errors.
• Activity Graph and Time Graph – a “live” graphical report of JDBC activity.
• Longest Transactions and Slowest Transaction – to be honest, this was a bit confusing. Wouldn’t the longest transaction also be the slowest transaction? The “Longest” report will show the transactions that have taken the longest to execute, no matter how long. The “Slowest” report is a report of all transactions over a particular threshold. So it may be possible that you have nothing there as your ColdFusion queries are performant, but the “Longest” report will still rank them for you.
• Trans. By Mem – reports on queries with high memory usage.
• Stack Trace Filter – it’s possible that multiple different things are making use of your database. The stack trace filter lets you reduce the amount of “noise” you may get from other applications. By default, there’s filters set up for .cfm, .cfc, and .jsp.
• Databases – this gives a really cool report on how you’re using your databases. I’ll be sharing an example of this later.
• Settings – this is where you can configure how FusionReactor logs and monitors your database transactions

[/vc_column_text][us_separator size=”small”][us_image image=”57375″][us_separator size=”small”][vc_column_text]

As with the previous examples I’ve shown from FusionReactor, take note of the controls on the top right to allow for filtering, reloading, and so forth. What isn’t obvious from the screenshot is that the “All SubFlavors” button actually lets you filter by the type of query, select, insert, and so forth. That’s pretty neat.

The main table of data reports on the app that was being used (I’m just working in my default Lucee home directory) and the SQL that was used. You can see the file name as well as timing information. Note the Time column which shows you how long the particular query took.

Notice how the SQL is reported as well. One of the features of FusionReactor is to automatically replace queryparam values with their ‘real’ values when reporting on the query. You can enable or disable this feature under the “JDBC/Settings” page. While this is a cool feature, it means it’s difficult to see where you’ve forgotten to use queryparams. I’ve reported to the FusionReactor folks that it would be nice if it was obvious when such a replacement has happened, maybe by using bold tags or some such. That way if you a query is not using queryparams it will be easier to find and correct.

The detail view is very deep. Here’s the main tab of information:

[/vc_column_text][us_separator size=”small”][us_image image=”57378″][us_separator size=”small”][vc_column_text]

There is almost an overwhelming amount of information here, but I’d probably focus mostly on the execution time values under JDBC and the memory section. Here’s the JDBC tab:

[/vc_column_text][us_separator size=”small”][us_image image=”57380″][us_separator size=”small”][vc_column_text]

As before, there’s a lot of information, but I’d focus on the row count. If you’ve ever seen someone select everything from a table and then use ColdFusion conditionals to restrict what is shown, then you know why this is a problem. The query is returning a mind-boggling twenty-seven thousand rows. There’s no way that was intentional. (Ok, for my test it was, but you get my point.)

The final tab, Relations, gives you a good look at the query within the page it was working. For example, this page had multiple coldFusion queries and you can see how they impacted the overall total page performance.

[/vc_column_text][us_separator size=”small”][us_image image=”57384″][us_separator][vc_column_text]

Finding Query Errors

Let’s now take a look at how FusionReactor reports errors. To test, I ran two requests with simple SQL errors, one trying to get a field that didn’t exist and one against a table that didn’t exist. Here’s how the main error history page shows the results.

[/vc_column_text][us_separator size=”small”][us_image image=”57387″][us_separator size=”small”][vc_column_text]

For the most part, this is fairly similar to the history report, except now you can get a short report of the error. As a test, I shut down my MySQL server and ran my request again. As expected, I got an error. However, that error does not show up in this report. It does show up under “Request/Error History” though. My guess is that since Lucee couldn’t ever speak to MySQL, a JDBC transaction was not made. That makes sense to me, but just keep in mind that you may want to check both error reports when hunting down issues.

The detail view is the same as before but with a new tab called “Error Details”:

[/vc_column_text][us_separator size=”small”][us_image image=”57390″][us_separator size=”small”][vc_column_text]

As always, I find the stack trace a bit hard to parse, but the error details on top seem nice and clear to me. Notice the debug button on the right. This allows you to add a breakpoint for future errors like this. I’m going to talk about FusionReactor’s debugging features later.

[/vc_column_text][us_separator size=”small”][vc_column_text]

JDBC Graphs

FusionReactor offers two reports of JDBC activities. The first is just raw activity (Activity Graph) while the second (Time Graph) reports on how long queries are taken. Both default to a “live” view but also let you look at specific time ranges. Here’s an example of both, but note the graphs are a bit boring for me as this is just my local laptop.

[/vc_column_text][us_separator size=”small”][us_image image=”57392″][us_separator size=”small”][us_image image=”57395″][us_separator][vc_column_text]

Finding Those Bad ColdFusion Queries

As I explained above, FusionReactor provides two reports to help you find slow queries. “Longest Transactions” will simply list out all your recorded transactions sorted by time. “Slowest Transactions” focuses on queries that are slower than a defined threshold. You can set that threshold in the settings panel, under “History”. The default value is 3 seconds. There are quite a few interesting things you can tweak in the settings panel so I recommend taking a look at it. For my laptop, with again a simple test and not “real” data, here’s the “Longest Transactions” view:

[/vc_column_text][us_separator size=”small”][us_image image=”57398″][us_separator size=”small”][vc_column_text]

Your Database Use

The final thing I want to show is probably the coolest. The “Databases” report gives you a report on how you are using each of the datasources on your server. It breaks it down by type of operations as well as table usage. It also reports on the queries sent to the datasource.

[/vc_column_text][us_separator size=”small”][us_image image=”57401″][us_separator size=”small”][vc_column_text]

This is just freaking cool as heck to me. While I think most of us will deal with one database per server, larger more complex applications could be dealing with numerous databases. A report like this could help you figure out if one, or more, are perhaps being used rarely enough to be decommissioned or transitioned to one main database. The table report could be really useful too. I’ve dealt with projects hitting a database with a huge amount of tables. This can give you an idea of what’s actually being used.

[/vc_column_text][us_separator size=”small”][vc_column_text]

Summary

As I said in the beginning, database issues tend to be the number one culprit when it comes to poorly performing ColdFusion queries on sites. I think the JDBC set of features in FusionReactor will probably be your top tool for helping improve your applications. I know this is where I’d start looking!

Thanks to our special guest author Raymond Camdem for writing this article for us.

Blog : www.raymondcamden.com
Twitter: raymondcamden

[/vc_column_text][/vc_column][/vc_row]

The post FusionReactor and Your ColdFusion Queries appeared first on FusionReactor Observability & APM.

Getting Started with FusionReactor (for ColdFusion Devs)

As a ColdFusion developer, you may know that it’s running as a J2EE server but also may have zero to no idea what that means in a practical sense. ColdFusion has always been easy to use, especially for developers from a non-traditional background, and this sometimes means there’s aspects of the platform that aren’t quite as easy to understand as others. A great example of this are the things that are more Java-centric. FusionReactor integrates with your ColdFusion server from a Java-perspective, which means it maybe uses terms that may be unfamiliar to the developer who only knows CFML.

[/vc_column_text][us_separator size=”small”][vc_column_text]

And yes, you can, and should, consider learning more about Java, but at the same time, we don’t always have the opportunity to pick up a new language! You may need to get things working now and what I’d like to do in this article is help you, the CFML developer, better understand how FusionReactor reports issues and get you to a point where you can quickly identify (and fix!) your problems.

[/vc_column_text][us_separator size=”small”][/vc_column][vc_column width=”1/2″][vc_column_text]

My assumption is that you’ve already installed FusionReactor. If not, head over to the downloads and grab a copy. You can get your trial key there and use it to test and evaluate the product. As I’m writing this for CFML developers who aren’t familiar with Java, I strongly urge you to use the “Automated Installers”. I’m testing on a Windows machine with Lucee but obviously you can use Adobe’s ColdFusion product as well. I’m also assuming you’ve set up a FusionReactor instance pointing to your ColdFusion server so you can start monitoring and debugging. Once you have, you can open that instance.

[/vc_column_text][/vc_column][vc_column width=”1/2″][us_image image=”56760″ onclick=”lightbox”][/vc_column][/vc_row][vc_row height=”small”][vc_column][vc_column_text]

Let’s Break Something!

There’s a heck of lot to FusionReactor but in this article I’m going to focus on just one particular aspect – errors. Luckily, I’m a born error creator. One of the best. I should probably get endorsed on LinkedIn for writing buggy code. Thankfully that’s made me something of an expert in debugging a file and figuring out what went wrong. That’s rather easy while I’m writing and testing code. It isn’t necessarily helpful when the code is in production and being used by the public.

[/vc_column_text][us_separator size=”small”][vc_column_text]

Let’s consider a simple template that seems to be bug free.

<h1>Debug Testing</h1> url.name = #url.name# len is #len(url.name)#

[/vc_column_text][us_separator size=”small”][vc_column_text]

This script simply outputs the value of a variable passed in the query string, `name`, and then reports the length of the value. Given that the file is named `ray.cfm`, you can test this like so:

<p> http://127.0.0.1:8888/ray.cfm?name=raymond </p>

[/vc_column_text][us_separator][us_image image=”56766″][us_separator][/vc_column][vc_column width=”1/3″][vc_column_text]

Which gives you:

[/vc_column_text][/vc_column][vc_column width=”2/3″][us_image image=”56763″ onclick=”lightbox”][/vc_column][vc_column][us_separator size=”small”][vc_column_text]

Ok, astute readers will recognize the issue with this code. What happens when you don’t pass the `name` value in the query string?

[/vc_column_text][/vc_column][vc_column][us_separator size=”small”][vc_column_text]

In this case it’s probably obvious what you did wrong. This is easy enough to fix by either setting a default or adding logic to redirect when the value isn’t defined, but what if you miss this and deploy the code to production?

Assuming you’ve done that and got a report from your users (or even better, your manager at 2AM on Saturday), let’s quickly see how FusionReactor can help you find this issue.

[/vc_column_text][/vc_column][/vc_row][vc_row height=”small”][vc_column][vc_column_text]

To the Requests, Batman!

Alright, so you’ve got a report about something going wrong. In a perfect world, that report includes the URL, query string, what the user was doing, the phase of the moon, and more. Also know that ColdFusion has excellent built-in error handling that can send you detailed reports… if you added that to your project.

But let’s pretend you have nothing, or something as useless as this:

“Hey, the web site is broke.”

Let’s begin by looking at the history of requests in the FusionReactor instance. In the left hand menu, mouse over Requests and select History:

[/vc_column_text][us_image image=”56769″][us_separator size=”small”][vc_column_text]

There’s a lot to this table, but let’s focus on a few things in a somewhat descending order of importance:

* Status Code: When a request is made over the web, a server returns a code that represents how it was handled. `200` represents a good response. `500` is a bad response. (And note how FusionReactor is flagging that already!) There’s a great many different status codes out there and you should take a look at them sometime.
* URL: This tells you the URL of the request and normally, but not always, will give you an idea of the file that was requested. Many people will use URL Rewriting to “hide” the fact that ColdFusion is running. In this case it’s pretty obvious: `http://127.0.0.1:8888/ray.cfm`. Given that the URL path is `/ray.cfm` I can figure out that it’s the `ray.cfm` in my web root. But you can’t always count on it being that easy. Also note that the error in this view is Java-related: `lucee.runtime.exp.ExpressionException`. Don’t worry – we’re going to dig into this.

That was the general request view, but most likely you want to focus in on just the errors. In that same left-hand nav, you can do so by selecting: Requests, Error History:

[/vc_column_text][us_image image=”56771″][us_separator size=”small”][vc_column_text]

This is showing the same information, just filtered to requests with errors.

[/vc_column_text][/vc_column][/vc_row][vc_row height=”small”][vc_column][vc_column_text]

Let’s Get the Error

Alright, so you’ve found a request with an error, how do we diagnose it? On the right hand side is a “book” icon which will load details. Let’s do that and see what we get.

[/vc_column_text][us_image image=”56774″][us_separator size=”small”][vc_column_text]

Holy. Crap.

Overwhelming, right? Everything there is useful, but there’s a lot you don’t need right now in order to get to the issue at hand. Begin by clicking “Error Details” under “Transaction Details”

[/vc_column_text][us_separator size=”small”][us_image image=”56777″][us_separator size=”small”][vc_column_text]

The first two blocks give you a good idea of what’s going on. `key [NAME] doesn’t exist` is a clue that your code tried to use a variable that didn’t exist. If the `key` aspect doesn’t make sense, it may be helpful to remember that ColdFusion variables are scopes, or structs, and when you access `url.something`, you’re trying to use a particular key in that scope.

Alright, so we’re closer. Now our particular ColdFusion file is very short but imagine a much larger template, how would we find where the error was thrown?

The bottom part of the template is the Java Stack Trace…

[/vc_column_text][us_image image=”56780″][us_separator size=”small”][vc_column_text]

The stack trace can be overwhelming. In this case, you can slowly read line by line looking for a line mentioning a CFM file:

ray_cfm$cf.call(/ray.cfm:5)

In this case, `ray.cfm` is the file name (pretty obvious) and `5` is the line number:

url.name = #url.name#

In theory, you’re done, but FusionReactor can help you a bit more. In the left hand navigation, look for the Debug menu and select Sources.

[/vc_column_text][us_separator size=”small”][us_image image=”56784″][us_separator size=”small”][vc_column_text]

Out of the box, FusionReactor doesn’t have any defined sources, so simply click the “Add” button and specify your web root. (Note that you can have more than one, and most likely you’ll have one root directory per application.)

[/vc_column_text][us_separator size=”small”][us_image image=”56786″][us_separator size=”small”][vc_column_text]

In the screenshot above I’ve simply specified my Lucee root.

When you’ve done that go back to the Requests view, find that broken request, and get to the stack trace again.

Now if you click on the method call, which is `ray_cfm$cf.call`, FusionReactor will actually be able to show you the file itself!

[/vc_column_text][us_separator size=”small”][us_image image=”56787″][us_separator size=”small”][vc_column_text]

Notice how FusionReactor even highlights the line in the file!

[/vc_column_text][us_separator size=”small”][vc_column_text]

But Wait – There’s More!

FusionReactor contains a huge set of tools, but in this article we focused in specifically on helping diagnose an error in production. In the next one we’ll start expanding our use of FusionReactor and look for more performance related issues.

Thanks to our special guest author Raymond Camdem for writing this article for us.

Blog : www.raymondcamden.com
Twitter: raymondcamden

[/vc_column_text][/vc_column][/vc_row]

The post Getting Started with FusionReactor (for ColdFusion Devs) appeared first on FusionReactor Observability & APM.