Setting Up P6Spy With Alfresco

Prior to Alfresco 5.0, we were able to use log4j to enable SQL statement logging. While this technique is no longer possible, we can use something like p6spy. This consists of a JDBC driver that can log all interactions with your database and pass the actual JDBC calls through to your real JDBC driver.

While we tend to simply enable this for a short time to get a view into database interactions, it is possible to leave it in place at all times with logging disabled. Then you can either trigger logging with a change to the config file or via JMX.

We have observed a roughly 2% performance drop with this in place while logging is happening—of course your mileage may vary. With logging disabled, we would not expect any significant reduction in performance. Again, you need to test this for yourself with an eye to how your performance is being affected.

In the following instructions, we will focus on an Alfresco 5.0.2 instance that is using Oracle. This system was already up and running (i.e. ODBC drivers were in place, etc) prior to our configuration. Some items may need to be changed for different databases.

1. Downloaded the P6Spy distribution and extract the contents to a temporary directory. Throughout the rest of the instructions, the files included in this temporary directory will be referenced by name only.
2. Copy the p6spy*.jar file and the spy.properties to alfresco-install-dir/tomcat/shared/lib.
3. Configure the class name of the real JDBC driver in spy.properties.

   driverlist=oracle.jdbc.OracleDriver

4. In alfresco-global.properties:Comment out old db.driver and replace with P6Spy driver:

   #db.driver=oracle.jdbc.OracleDriver
   db.driver=com.p6spy.engine.spy.P6SpyDriver

   Comment out old db.url and replace with P6Spy adjusted db.url:

   #db.url=jdbc:oracle:thin:@${db.host}:${db.port}/${db.sid}
   db.url=jdbc:p6spy:oracle:thin:@${db.host}:${db.port}/${db.sid}
   

After you have completed the installation, a log file called spy.log will be created in the current working directory when Alfresco runs (typically alfresco-install-dir). This log file will contain a list of the various database statements executed. You can alter the location of this log file as well as what gets logged by editing spy.properties.

The default log format will be:

%(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)