View data source metrics

The Performance Monitoring Toolset can monitor a heterogeneous database server environment that consists of all the ColdFusion-supported databases. It helps users in identifying the bottleneck at the database or data source level. It can pin point the ColdFusion queries which are slowing down the entire ColdFusion application, so that developers or database administrators can take the appropriate step to fix these bottlenecks.

Database monitoring is divided into multiple sections, as follows:

• Database overview.
• Data source details page.
• Query details page.
• Actively running queries.
• Database Pool statistics.

The Database overview page contains the following charts:

Average query time

This chart shows the average query execution time per data source. Only five data sources are displayed by default.

You can view the time taken by a query to execute.

Throughput

This chart shows the number of queries executed per unit of time.

Top slow queries –This metric helps in nailing down the query in the ColdFusion application that executes the slowest. You can see a sorted view of all the queries in descending order.

This report helps identifying queries by template name, application name, and line number. On clicking a query, you are redirected to the specific instance of a query that is slow, along with the SQL statement for the query. You drill down to the detailed query page to investigate it further.

To improve performance of these queries, you must tune the queries listed in this table. If the result of a query is static, you can improve performance by caching the query using ColdFusion’s query cache.

You can use the metrics from the sections below to know the performance of your queries.

Query details

The section displays detailed information about the SQL statement, template path, line number, and the time taken to execute the query. The Query detailed page has three tabs:

• Basic info
• Invocations
• References

To know more, click the required query in the Top slow queries chart.

The Basic info tab contains the following:

• Name of the data source.
• Name of the query.
• Time taken to execute.
• The template from where the query was served.
• Application
• Start and end times of query execution.
• Whether the query was cached.
• Query execution status.
• The SQL statement.
• Type of SQL statement, for example, SELECT, UPDATE, and so on.
• The URL of the query.
• The instance from where the query was executed.
• The thread ID of the execution.

The Invocations tab contains the following:

• The SQL statement that executed the queries
• The minimum and maximum execution times of the queries
• The number of times the query is called

The References tab contains the URLs of the queries and the number of times the queries are called.

Data source details: The section contains details about the data source. It has information like database name, database server name, database host name, and database port.

Click any data source for the information.

Most frequently run queries

This chart displays a list of queries that you run most frequently. You cal also see the average time taken by a query to execute, and the number of times the query has been executed.

For more information, click any query.

The Basic Info tab contains the following details:

• Name of the data source that your query uses.
• The name of the query.
• The time taken by the query to execute.
• The template from where the query was served.
• The application that contains the template.
• The start and end times of the query execution.
• The status of the execution.
• The SQL statement.
• The type of SQL statement- SELECT, UPDATE, etc.
• The URL of the query template.
• The ColdFusion instance from where the query was served.
• The Thread ID of the execution.

The Invocations tab contains the following information:

• The SQL statement used in the query.
• The number of times the query was executed.
• The summary statistics of the execution.

The References tab contains the URLs of the queries and the number of times the queries are called.

Query cache

This chart displays the queries that were cached. You can view a list of cached queries or details about an individual query. If the execution time of a query is low, determine if the query needs caching.

To drill down to a more detailed page, click the pie-chart.

Queries per node: This chart shows all the ColdFusion queries executed across all the ColdFusion nodes as part of cluster or group.

You can drill down to more details, as shown below:

Queries per application: This chart shows the distribution of ColdFusion queries executed for various ColdFusion applications.

Click to drill down for more information.

SQL distribution

This chart categorizes all the ColdFusion queries according to the data manipulation language like, select, insert, update, and delete. It helps you to understand the kind of queries that are being used on a ColdFusion data source.

Click to drill down for more information.

CF transaction

This chart shows the distribution of success or failure of a cftransaction tag. This tag allows DBMS for treating multiple database operations into a single transaction. It provides commit and rollback processing of a DBMS. 

Click to drill down for more information.