SQL statement blocking P6SPY

Software introduction

P6Spy is an open source framework that can be used to intercept and modify data operation statements in applications. Through P6Spy, we can intercept SQL statements, which is equivalent to a recorder of SQL statements, so we can use it for relevant analysis, such as performance analysis.

P6SPY provides the following functions:

Record the execution timestamp of the SQL statement. Record SQL statement type Record SQL statements with and without parameters Control the execution time of SQL statements according to the configured time, and output the SQL statements beyond the time to the log file

Software use

1. Prepare the jar package of p6spy

pom.xml Introduce dependency in file

<!-- Console SQL Log printing plug-in -->

2. Configure data source agent in Spring boot

application.yml Add Spring's data source configuration to the file

      # Whether to turn on SQL log output? It is recommended to turn off the production environment, with performance loss
      p6spy: true

3. Create a new one spy.properties

Create a new file in the resource directory spy.properties

# p6spy���ã��ĵ� https://p6spy.readthedocs.io/en/latest/configandusage.html
# ʹ����־ϵͳ��¼ sql
# �Զ�����־��ӡ
# �Ƿ����� SQL��¼
# �� SQL��¼��׼ 2 ��
# ��������
# ���� QRTZ�IJ���ӡ
exclude=QRTZ,select 1


# Specify the log interception module of the application, the default is com.p6 spy.engine.spy .P6SpyFactory 

# Real JDBC driver. Multiple comma separated drivers are empty by default

# Whether to automatically refresh the default flash

# Configure SimpleDateFormat date format to be empty by default

# Print stack trace information default flash

# If stacktrace=true, you can specify a specific class name to filter.

# Monitor whether the property profile is reloaded

# Property profile reload interval in seconds default 60s

# Specify the appender of Log. The value is:

# Specify the default file name for Log spy.log

# Specifies whether to add Log every time. If it is set to false, the default value is true

# Specify log output style as com.p6 by default spy.engine.spy . appender.SingleLineFormat  , single line output unformatted statement
# You can also use com.p6 spy.engine.spy . appender.CustomLineFormat  From the defined output style, the default value is% (currenttime) |% (executiontime) |% (category) | (connection) (connectionid) |% (sqlsingleline)
# The available variables are:
#   %(connectionId)            connection id
#   %(currentTime) current time
#   %(executionTime) execution time
#   %(category) execution grouping
#   %(effectiveSql) SQL newline submitted
#   %(effectiveSqlSingleLine) submitted SQL does not wrap
#   %(sql) the actual SQL statement executed. The placeholder has been replaced
#   %The real SQL statement executed by (sqlSingleLine) has replaced the placeholder display

# Date type field the date format used for logging is dd-MMM-yy by default

# The date format used by the boolean type field for logging. The default Boolean optional value is numeric

# Whether to expose attribute default true through jmx

# If jmx is set to true, specify that the prefix is empty by default when exposing properties through jmx
# com.p6spy(.<jmxPrefix>)?:name=<optionsClassName>

# Display nanosecond default false or not

# Actual data source JNDI
# Actual data sourcedatasource class

# The configuration parameters carried by the actual data source are specified as k=v and divided by semicolon

# jndi data source configuration 
# Set the NamingContextFactory of the JNDI data source. 
# Set the URL of the provider of the JNDI data source. 
# Set up some custom information for JNDI data sources, separated by semicolons. 

# Whether to enable log filtering is false by default. The precondition for this configuration to take effect is to configure include/exclude/sqlexpression

# List of table names included in filtering Log, comma separated and empty by default
# The list of table names excluded during Log filtering, separated by commas, is empty by default

# SQL regular expression name when filtering Log is empty by default

#Display the list of categories queued when filtering Log specified. Values: error, info, batch, debug, statement,
#commit, rollback, result and resultset are valid values
# (default info,debug,result,resultset,batch)

# Filter binary fields or not
# (default is false)

# P6Log module execution time setting, integer value (in milliseconds), Log only after this time. 0 by default

# Whether P6 output module records statement running for a long time is false by default
# outagedetection=true|false
# P6output module execution time setting, integer value (in seconds), Log only after this time. Default 30s
# outagedetectioninterval=integer time (seconds)

4. Custom p6spy format output message class

 * Custom p6spy sql output format
 * @author Admin
public class P6spySqlFormatConfigure implements MessageFormattingStrategy {

    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        return StringUtils.isNotBlank(sql) ? DateUtil.formatFullTime(LocalDateTime.now(), DateUtil.FULL_TIME_SPLIT_PATTERN)
                + " | time consuming " + elapsed + " ms | SQL sentence:" + StringUtils.LF + sql.replaceAll("[s]+", StringUtils.SPACE) + ";" : StringUtils.EMPTY;

5. Setting of printing log

Create a new logback file in the resource directory- spring.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true" scanPeriod="60 seconds" debug="false">
    <property name="log.path" value="log" />
    <property name="log.maxHistory" value="15" />
    <property name="log.colorPattern" value="%magenta(%d{yyyy-MM-dd HH:mm:ss}) %highlight(%-5level) %yellow(%thread) %green(%logger) %msg%n"/>
    <property name="log.pattern" value="%d{yyyy-MM-dd HH:mm:ss} %-5level %thread %logger %msg%n"/>

    <!--Output to console-->
    <appender name="console" class="ch.qos.logback.core.ConsoleAppender">

    <!--output to a file-->
    <appender name="file_info" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
        <filter class="ch.qos.logback.classic.filter.LevelFilter">

    <appender name="file_error" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
        <filter class="ch.qos.logback.classic.filter.LevelFilter">

    <root level="debug">
        <appender-ref ref="console" />

    <root level="info">
        <appender-ref ref="file_info" />
        <appender-ref ref="file_error" />

Tags: Programming SQL Spring xml JDBC

Posted on Wed, 27 May 2020 07:33:48 -0700 by FeralReason