Curious (Clojure) Programmer Simplicity matters

Menu

  • Home
  • Archives
  • Tags
  • About
  • My Talks
  • Clojure Tip of the Day Screencast
  • (Open) Source
  • Weekly Bits & Pieces
  • RSS
October 19, 2023

p6spy - Spying on Your Database

Table of Contents
  • Meet p6spy
    • p6spy setup
    • p6spy logging configuration
    • Note about MariaDb
  • CodeScene DB Query Log
  • p6spy log
    • Stacktraces
  • Alternatives
    • Database logs
    • The REPL
  • References

Talking to a relational database with Clojure is relatively easy. There’s a great library next.jdbc for that. In legacy applications, you may encounter an older library, clojure.java.jdbc.

But sometimes, you need to look at the data flowing into/from your database. Or you need to examine the queries your application is making to troubleshoot a performance problem.

Meet p6spy

p6spy is a great tool to intercept and log database queries. It logs all JDBC transactions for any Java/JVM application.

It is as easy to use from Clojure, as it’s from Java.

p6spy setup

First, add p6spy as a dependency:

  • leiningen:

    ;; for logging JDBC queries: https://p6spy.readthedocs.io/en/latest/install.html#generic-instructions
    [p6spy/p6spy "3.9.1"]
  • or deps.edn:

    ;; for logging JDBC queries: https://p6spy.readthedocs.io/en/latest/install.html#generic-instructions
      p6spy/p6spy {:mvn/version "3.9.1"},

Second, use P6SpyDriver instead of your regular JDBC driver. This is how we do it:

(defn- spy-driver
  "If db query logging is enabled, use p6spy driver instead of the original."
  [original-driver]
  (if (:codescene-log-db-query env)
    "com.p6spy.engine.spy.P6SpyDriver"
    original-driver))

;; See https://p6spy.readthedocs.io/en/latest/integration.html#connection-url-way
(defn- spy-url
  "If db query logging is enabled, transforms the ordinary jdbc url like 'jdbc:mysql://...'
  into the one handled by p6spy, e.g. 'jdbc:p6spy:mysql://...'."
  [original-url]
  (cond-> original-url
    (:codescene-log-db-query env) (str/replace-first "jdbc:" "jdbc:p6spy:")))
#_(spy-url "jdbc:mysql://localhost:3306")
;; => "jdbc:p6spy:mysql://localhost:3306"

(defn get-db-spec []
  (let [host (get-property-value :db-host)
        port (get-property-value :db-port)
        database (get-property-value :db-name)
        engine-specs [{:db-type "h2"
                       :driver-class-name (spy-driver "org.h2.Driver")
                       :jdbc-url (spy-url (format "jdbc:h2:file:%s;MULTI_THREADED=1;MODE=MYSQL" (get-property-value :db-physical-path)))}
                      {:db-type "mysql"
                       :driver-class-name (spy-driver "com.mysql.cj.jdbc.Driver")
                       :jdbc-url (spy-url (format "jdbc:mysql://%s:%s/%s" host (or port "3306") database))}
                       ;; maria-db left out snce it doesn't support p6spy
                      ]
...

p6spy logging configuration

To configure the logging, we need to create the spy.properties file in our "resources" folder. Here’s a sample configuration file that I used:

# https://p6spy.readthedocs.io/en/latest/configandusage.html

# determines if property file should be reloaded
# Please note: reload means forgetting all the previously set settings (even those set during runtime - via JMX)
# and starting with the clean table (default is false)
reloadproperties=true
# determines how often should be reloaded in seconds (default is 60)
reloadpropertiesinterval=10

# driverlist isn't really needed for MySQL but it didn't work for H2 without it
# Note: mariadb doesn't work, even after listing it here.
driverlist=org.h2.Driver,com.mysql.jdbc.Driver,org.mariadb.jdbc.Driver
dateformat=dd-MM-yy HH:mm:ss:SS
logfile=codescene.db.log
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
  # Custom log message format used ONLY IF logMessageFormat is set to com.p6spy.engine.spy.appender.CustomLineFormat
# default is %(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)
customLogMessageFormat=%(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)

# prints a stack trace for every statement logged (default is false)
# very handy for debugging but very verbose
#stacktrace=true

You can find the description of p6spy configuration online: Configuration and Usage

Note about MariaDb

At the moment, it seems that p6spy doesn’t support MariaDB - at least, I couldn’t make it work:

Execution error at com.zaxxer.hikari.util.DriverDataSource/<init> (DriverDataSource.java:110).
Driver org.mariadb.jdbc.Driver claims to not accept jdbcUrl, jdbc:p6spy:mariadb://localhost:3307/codescene?allowMultiQueries=true&serverTimezone=UTC

CodeScene DB Query Log

For the CodeScene On-Prem product, you can enable DB query logging (powered by p6spy) via CODESCENE_LOG_DB_QUERY=true. See Logging database queries (MySQL or embedded H2 db)

You will then find the detailed DB query log in the codescene.db.log file.

p6spy log

Here’s a small excerpt from codescene.db.log (see section CodeScene DB Query Log)

22-12-22 06:59:51:317|0|statement|connection375|SELECT * FROM mail_server_configuration
22-12-22 06:59:51:318|0|statement|connection375|select id, project_id, plan, type, address, user, provider_id        from report_configuration

Stacktraces

Enabling stacktraces in p6spy configuration can be very handy. You can then clearly see where do the queries come from.

You need to enable this in the spy.properties config file:

# prints a stack trace for every statement logged (default is false)
# very handy for debugging but very verbose
stacktrace=true

Then you can see a stacktrace for each logged query:

12-12-22 13:22:48:746|3|statement|connection0|SELECT * FROM codescene_enterprise2.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
java.lang.Exception
        at com.p6spy.engine.common.P6LogQuery.doLog(P6LogQuery.java:126)
        ...
        at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
        at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
        at liquibase.executor.jvm.JdbcExecutor$QueryStatementCallback.doInStatement(JdbcExecutor.java:511)
        ...
        at liquibase.Liquibase.validate(Liquibase.java:2324)
        ...
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:807)
        ...
        at myapp.database.db$migrate.invokeStatic(db.clj:142)
        ...
        at java.lang.Thread.run(Thread.java:748)

Warning: Especially with stacktraces, the db log file can grow large very quickly. Make sure you disable the logging once you are done with investigation.

Alternatives

Database logs

Most database come with their own facilities for detailed query logging. One such option is Slow Query Log for MySQL. Another one is The General Query Log

This is a very useful option and you can safely enable it in production for queries slower than ~1 second. The downside is that it may be hard to correlate the queries to the application code or to discover all the queries (the faster ones) that the app is doing.

On the other hand, it can give you additional information useful for understanding the performance of the queries. So it’s a good idea to use this as a complement to p6spy.

The REPL

Clojure REPL is an invaluable tool and you can use it to manually replicate specific flow (perhaps a route handler) in your application. Capture the data you need in def-s, run the top-level function triggering the flow and observe the generated queries via p6spy.

References

  • next.jdbc

  • clojure.java.jdbc

  • p6spy

    • p6spy - Configuration and Usage

  • CodeScene On-Prem db logging: Logging database queries (MySQL or embedded H2 db)

  • MySQL Query Logging

    • Slow Query Log for MySQL

    • The General Query Log

    • Logging and analyzing slow queries in MySQL

    • Impact of logging on MySQL’s performance


Tags: clojure databases jdbc debugging performance

« DNS: on (not so obvious) recursive name resolution Liquibase: custom database migrations with Clojure (without AOT) »

Copyright © 2025 Juraj Martinka

Powered by Cryogen | Free Website Template by Download Website Templates