JDBCMetrics - get information from your JDBC driver

Using JDBCMetrics you can get hold of the following information from your driver:

By only setting up the JDBCMetricsDriver (meaning configure your driver):

• The total number of database reads
• The total number of database writes
• Number of reads per second (per minute, 5 minutes & 15 minutes)
• Number of writes per second (per minute, 5 minutes & 15 minutes)
• Average/median/percentile query times for reads & for writes

By also setting up the JDBCMetricsFilter you will get:

• The number of database reads created for a specific HTTP request
• The number of database writes created for a specific HTTP request
• The time spent in the database for reads & writes per request
• Statistics about reads & read time per request (average, median, percentile etc)
• Statistics about writes & write time per request (average, median, percentile etc)

##Background## In 99% of the projects we have worked with, when the shit hits the fan the problem is the interaction with the database. We want a super easy way of knowing what actually is happening between the application server & the database.

##Setup##

1. Add the jar
2. Setup the driver
3. Setup the filter (optional)
4. Setup a reporter
5. Choosing registry (optional)

###Add the jar In your pom.xml file add:

<dependency>
 <groupId>com.soulgalore</groupId>
 <artifactId>jdbcmetrics</artifactId>
 <version>2.0.1</version>
 <scope>runtime</scope>
</dependency>

###Setup the driver Depending on your current setup, this need to be done in different ways.

####Using DataSource####

The idea is to change the existing data sources name and create a new JDBCMetrics data source that wraps/proxies the existing and name it with the real data sources original name. You feed it with either the real data source or it's new name (like "java:/comp/env/jdbc/testDS" or "jdbc/testDS"). This can be done as contructor parameters or by setters named "dataSource" or "referenceName". The classes are com.soulgalore.jdbcmetrics.DataSource, ConnectionPoolDataSource, XADataSource.

You can also use the factory com.soulgalore.jdbcmetrics.DataSourceFactory with the params "referenceName" (like "java:/comp/env/jdbc/testDS" or "jdbc/testDS") and "className" which is the type to use (as above, defaults to the DataSouce class if left out).

If you need to pass along the resources in web.xml, don't forget to add the real one with the new name.

If your existing data source can be configured with any jdbc driver, you can use the "Using DriverManager" method instead.

####Using DriverManager####

The driver is automatically registered in DriverManager (as of JDBC4 in java6). If you need to register it manually either set the JVM parameter -Djdbc.drivers=com.soulgalore.jdbcmetrics.Driver or load the driver in your code like Class.forName("com.soulgalore.jdbcmetrics.Driver");

Then configure the jdbc url/connect string. If your existing connect string looks like this: jdbc:mysql://localhost:3306/test_db
Prefix it with jdbcmetrics: like this jdbc:jdbcmetrics:mysql://localhost:3306/test_db

Specify the underlaying driver, your regular driver, in the url like this jdbc:jdbcmetrics?driver=com.mysql.jdbc.Driver:mysql://localhost:3306/test_db

JDBCMetricsDriver will then instantiate the specified driver to use it underneath. If you omit the driver param JDBCMetricsDriver will try to match the url to a driver registered in DriverManager.

Setup the filter (optional)

Add the filter in your web.xml file (make sure it run early in the chain):

<filter>
<filter-name>JDBCMetricsFilter</filter-name>
<filter-class>
com.soulgalore.jdbcmetrics.filter.JDBCMetricsFilter
</filter-class>
<init-param>
<param-name>use-headers</param-name>
<param-value>true</param-value>
</init-param>
<init-param>
<param-name>request-header-name</param-name>
<param-value>jdbcmetrics</param-value>
</init-param>
</filter>

<filter-mapping>
	<filter-name>JDBCMetricsFilter</filter-name>
	<url-pattern>/*</url-pattern>
</filter-mapping>

###Reporters### JDBCMetrics uses the great Metrics as metric backend, that have the following different ways of reporting:

• JMX (not recommended for production)
• console
• CSV - which periodically appends to a set of .csv files in a given directory.
• Servlet - which will return all metrics as JSON.
• Ganglia - read more about Ganglia here.
• Graphite - read more about Graphite here.

Click here for documentation of how to setup the reporters.

And here's a real world example of setting up an metrics servlet:

First add it the servlet to your pom.xml file:

<dependency>
    <groupId>io.dropwizard.metrics</groupId>
    <artifactId>metrics-servlets</artifactId>
    <version>3.1.0</version>
</dependency>

Then set it up in your web.xml:

<servlet>
	<servlet-name>MetricsServlet</servlet-name>
	<servlet-class>com.codahale.metrics.servlets.AdminServlet</servlet-class>
	<init-param>
		<param-name>show-jvm-metrics</param-name>
		<param-value>false</param-value>
	</init-param>
</servlet>

<servlet-mapping>
	<servlet-name>MetricsServlet</servlet-name>
	<url-pattern>/jdbcmetrics</url-pattern>
</servlet-mapping>

###Choose registry### By default, a new MetricRegistry is used. If you wish to obtain a specific MetricRegistry from SharedMetricRegistries, for example to add other metrics to it, you may either:

• Set the System property com.soulgalore.jdbcmetrics.MetricRegistry to the name of the registry you wish to use
• Set a property called metricRegistry.name in a file called jdbcmetrics.properties at the root of the classpath.

The system property takes precedence over the properties file.

Fetching info from individual request(s)

You can get information on how many database reads & writes your request generates by two different ways: Either it is logged to your log system or you can get it as response headers when you access the page.

Response headers

To get the information back as response headers, you need to turn on that functionality in the filter config by setting use-headers to true. The reason for why this is not default behaviour is that it will wrap the response and not flush the resoonse until everything is finished, so if you have some smart flushing content early thing, the filter will remove that functionality (so for some systems, this is not good running in production).

You can configure the request header name that will trigger the response, by default it is jdbcmetrics and you configure that in web.xml and the JDBCMetrics servlet filter.

By sending a header like: jdbcmetrics=yes you will get two response headers: nr-of-reads & nr-of-writes holding the values of the reads & writes.

Getting the log

The log logs at debug level to com.soulgalore.jdbcmetrics.filter.JDBCMetricsFilter and uses SLF4j. To setup it up to support your current logging framework, checkout the documentation.

A log entry will look something like this:

URL: http://www.example.com reads:10 writes:1

License

Copyright 2014 Magnus Lundberg & Peter Hedenskog

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.