1. Introduction to the P6Spy UI Plugin
The P6Spy UI plugin uses the P6Spy library to intercept JDBC calls and display them in a web page. One benefit of P6Spy is that it will display the SQL that was actually run with SQL ? placeholders but also the SQL with the actual values.
Note that the plugin does not require the use of the Grails p6spy plugin and is actually incompatible with it. If you are currently using it, migrate your settings from spy.properties to application.groovy as described in the Configuration section and uninstall it. |
The plugin includes with a controller and a GSP that will display the executed SQL in a DataTable, and also graphs in a second tab. Once the plugin is installed navigate to http://localhost:8080/p6spy to view the queries and graphs, or http://localhost:8080/p6spy/admin for a basic page displaying configuration settings.
1.1. Release history
-
1/04/2016 3.0.0 release
-
1/04/2016 1.0.0 release
-
11/11/2012 Initial 0.1 release
2. Configuration
P6Spy works by intercepting JDBC calls to log the executed SQL and parameters. There are multiple ways to configure this; by default the plugin will update the JDBC URL and driver class name for you, and the P6Spy driver will intercept calls and re-route them to the real driver. This is not possible if you use a JNDI data source, so in that case or if the configuration is more complex you can manually configure the DataSource.
If you cannot or don’t want to use the auto-configuration feature, the first thing you need to do is change the driverClassName
property in DataSource.groovy
(or in the JNDI configuration) for any environments that you want to use the plugin in. Change it to com.p6spy.engine.spy.P6SpyDriver
, e.g.
dataSource {
...
driverClassName = 'com.p6spy.engine.spy.P6SpyDriver'
...
}
Having done this, you need to tell P6Spy what the real driver is. You do not create a spy.properties
file like you usually do when working with P6Spy - instead you store settings in application.groovy. This is more convenient since the plugin has many default values already set, so you only need to set the values that are required (currently just “realdriver” and only if you’re not using auto-configuration) and any overrides or other values that don’t have defaults set. In addition you can also externalize properties and take advantage of other features of setting properties in application.groovy. If you’re using MySQL you would specify the real driver class as
grails.plugin.p6spy.config.realdriver = 'com.mysql.jdbc.Driver'
and if you’re using a different database change the value to the appropriate driver class.
The following table summarizes the various configuration options. Options that start with “config” are passed through to P6Spy, and those starting with “gsp” and “updateDataSource” are plugin-specific.
All must be set if application.groovy or an external config file, and must include the grails.plugin.p6spy.
prefix, e.g.
grails.plugin.p6spy.config.jmx = false
See the P6Spy documentation for more information about the available P6Spy options.
Property | Default | Meaning |
---|---|---|
config.appender |
'grails.plugin.p6spy.ui. MemoryLogger' |
class name of the appender to use |
config.autoflush |
true |
whether to flush per statement |
config.databaseDialectDateFormat |
'dd-MMM-yy' |
SimpleDateFormat format used for logging of PreparedStatement date/time values |
config.dateformat |
none |
SimpleDateFormat format used for logging of the query time (if not set, millseconds since epoch is logged) |
config.driverlist |
none |
comma-separated list of JDBC drivers to load and register |
config.exclude |
none |
if filter is true, a comma-separated list of strings used to define the include/exclude filter rule |
config.excludecategories |
'batch, debug, info, result, resultset' |
comma-separated list of category names to exclude |
config.executionthreshold |
'0' |
if set, only statements that have taken longer than the time specified (in milliseconds) will be logged |
config.filter |
false |
if true, filter what is logged |
config.include |
none |
if filter is true, a comma-separated list of strings used to define include/exclude filter rule |
config.jmx |
true |
whether to expose options via JMX |
config.jmxPrefix |
none |
if jmx is true, the prefix used for the naming pattern |
config.jndicontextcustom |
none |
JNDI config setting if using external JNDI |
config.jndicontextfactory |
none |
JNDI config setting if using external JNDI |
config.jndicontextproviderurl |
none |
JNDI config setting if using external JNDI |
config.modulelist |
'com.p6spy.engine.spy. P6SpyFactory, com.p6spy.engine.logging. P6LogFactory' |
comma-separated list of names of module classes which should be active |
config.outagedetection |
false |
if true, detects long-running statements that may indicate a database outage and logs statements that exceed outagedetectioninterval seconds; when enabled, only long-running statements are logged |
config.outagedetectioninterval |
'30' |
threshold (in seconds) for outage detection |
config.realdatasource |
none |
if not using auto-update, the real JNDI name |
config.realdatasourceclass |
none |
if not using auto-update, the name of the real DataSource class |
config.realdatasourceproperties |
none |
if not using auto-update, optional DataSource url properties |
config.sqlexpression |
none |
if filter is true, a regex defining filter rule for SQL statements |
config.stacktrace |
false |
if true, log a stack trace for every statement logged |
config.stacktraceclass |
none |
if stacktrace is true, a class name which must be present in the stacktrace for the stacktrace to be logged |
gsp.layoutAdmin |
'main' |
the layout to use for admin.gsp |
gsp.layoutIndex |
'p6spy-ui' |
the layout to use for index.gsp |
updateDataSource.autoUpdate |
true |
if true, update the DataSource URL to include “p6spy:” to trigger P6Spy’s auto-update feature |
updateDataSource.driverClassNameProperty |
'driverClassName' |
if autoUpdate is true, the name of the DataSource driver class name property |
updateDataSource.urlProperty |
'url' |
if autoUpdate is true, the name of the DataSource 'url' property |