DBPlusEngine-Driver uses Agent to realize log collection function #

Slow Query Log #

Background

The slow query log function records SQL statements that take more than a certain time to execute so that DBAs and developers can easily identify SQL statements that may have problems. It is an essential reference source for database and SQL management.

Parameter Explanation

slow-query-log: Whether to enable the slow query log function, the default value is true.
long-query-time：Slow query time threshold, SQL statements whose execution time exceeds this threshold will be recorded in the slow query log. The configuration unit is milliseconds, and the default value is 5000.

Prerequisite

The agent uses SLF4J for log bridging output, so it is required that the application has SLF4J dependencies and relevant log output configurations.
The slow query log function is implemented based on Agent technology, and the application needs to be configured with javaagent at startup to enable the Agent.

Configuration Example

Agent Configuration

conf/agent.yaml is the Agent configuration file, the default configuration is as follows.

plugins:
  logging:
    BaseLogging:
      props:
        slow-query-log: true
        long-query-time: 5000

Among them, slow-query-log and long-query-time are configured with default values, which means:

Enable slow query log;
When the SQL execution takes more than 5000 milliseconds, record the slow query log.

Application log configuration

Taking the commonly used logback as the log output example, the configuration is as follows.

<configuration>
    <property name="log.context.name" value="project-using-DBPlusEngine-Driver" />
    <property name="log.charset" value="UTF-8" />
    <property name="log.pattern" value="[%-5level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n" />
    <contextName>${log.context.name}</contextName>
    
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder charset="${log.charset}">
            <pattern>${log.pattern}</pattern>
        </encoder>
    </appender>
  
    <logger name="SLOW-QUERY" level="info" additivity="false">
        <appender-ref ref="STDOUT" />
    </logger>
    
    <root>
        <level value="INFO" />
        <appender-ref ref="STDOUT" />
    </root>
</configuration>

Note: The logger name of slow log output is SLOW-QUERY

Slow Query Log Format

db: {database name} query_time: {query time} sql_type: {sql type}

{sql}

db：Database name;
query_time：SQL execution time, in ms;
sql_type：SQL type, (SELECT,INSERT,UPDATE,DELETE,OTHER type);
sql：The specific SQL statement executed;

e.g.

[WARN ] 2023-01-04 14:55:04.035 [http-nio-8888-exec-7] SLOW-QUERY - db: sharding_db query_time: 21 sql_type: SELECT
SELECT  id,user_id,uuid,status,create_time,update_time,is_deleted AS deleted  FROM t_order

Full Audit Log #

Background

Full audit log means that after this function is enabled, the system will record all executed SQL statements, including the database corresponding to the statement, execution time, SQL type and other information, which is convenient for enterprises to perform audit operations.