Shulou(Shulou.com)06/01 Report--

This article mainly explains "SpringBoot statistics and how to monitor the operation of SQL", the content of the explanation is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "SpringBoot statistics and how to monitor the operation of SQL"!

1 basic concepts

Druid is the best database connection pool in the Java language.

Although HikariCP is slightly faster, Druid can provide powerful monitoring and extension functions, and it is also Alibaba's open source project.

Druid is a database connection pool developed by Alibaba for monitoring, which surpasses other database connection pools in terms of function, performance and expansibility, including DBCP, C3P0, BoneCP, Proxool, JBoss DataSource and so on.

Druid can well monitor the execution of DB pooled connections and SQL, and is born for monitoring DB connection pooling.

Spring Boot default data sources HikariDataSource and JdbcTemplate have introduced that Spring Boot 2.x uses Hikari data sources by default. It can be said that Hikari and Driud are the best data sources on the current Java Web.

And Druid has deployed more than 600 applications in Alibaba, after several years of severe test of large-scale deployment in the production environment!

Stat:Druid provides a built-in StatFilter for statistical monitoring information.

The WallFilter that wall:Druid defends against SQL injection attacks is through Druid's SQL Parser analysis. The SQL Parser provided by Druid can intercept SQL at the JDBC layer for corresponding processing, such as sub-database, sub-table, audit and so on.

Log4j2: this is the logging function, which can print sql statements to log4j2 for troubleshooting.

2 add dependencies

Pom.xml

Com.alibaba druid-spring-boot-starter 1.1.23 mysql mysql-connector-java runtime org.springframework.boot spring-boot-starter-log4j2 org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.3 com.zaxxer HikariCP 3 configuration related properties

Configure Druid data sources (connection pooling): just as c3p0 and dbcp data sources can set data source connection initialization size, maximum number of connections, wait time, minimum number of connections, etc., Druid data sources can be set in the same way

Configure Druid web to monitor filter (WebStatFilter): this filter is used to count all database information in web application requests, such as sql statements issued, sql execution time, number of requests, requested url address, as well as seesion monitoring, database table visits, and so on.

Configure Druid background management Servlet (StatViewServlet): Druid data source has monitoring function and provides a web interface for users to view. Similar to the default web page provided when installing a router, you need to set the properties of the Druid background management page, such as login account, password, etc.

Note:

The name of the Druid Spring Boot Starter configuration property is fully Druid-compliant, and Druid database connection pooling and monitoring can be configured through the Spring Boot configuration file, or the default value is used if not configured.

Application.yml

# configuration data Source (Druid) # spring: datasource: # JDBC basic configuration # username: xxx password: xxx driver-class-name: com.mysql.cj.jdbc.Driver # mysql8 connection driver url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/ Shanghai platform: mysql # Database type type: com.alibaba.druid.pool.DruidDataSource # specify data source type # connection pool configuration # druid: # configure initialization size, Min and Max initial-size: 5 minIdle: 10 max-active: 20 # configure the time to get the connection wait timeout (in milliseconds) max-wait: 60000 # configure how often to detect Detect idle connections that need to be closed in milliseconds time-between-eviction-runs-millis: 2000 # configure the minimum time for a connection to survive in the pool Unit is millisecond min-evictable-idle-time-millis: 600000 max-evictable-idle-time-millis: 900000 # SQL statement used to test whether a connection is available. The default value is different for each database. This is mysql validationQuery: select 1 # application applies for a connection to the connection pool, and when testOnBorrow is false, the connection pool will determine whether the connection is idle. If so, Then verify whether the connection is available testWhileIdle: true # if true, default is false, when the application requests a connection from the connection pool, the connection pool will determine whether the connection is available testOnBorrow: false # if it is true (default false), when the application uses up the connection, the connection pool will determine whether the connection is still available testOnReturn: false # whether the preparedStatement is cached Also known as PSCache. PSCache has greatly improved the performance of databases that support cursors. For example, oracle poolPreparedStatements: true # must be configured greater than 0 to enable PSCache. When it is greater than 0, poolPreparedStatements will automatically trigger and change to true. # in Druid, there will be no problem that PSCache takes up too much memory under Oracle. # you can configure this value a little larger, such as 100 maxOpenPreparedStatements: 20 # connections within the number of minIdle in the connection pool If the idle time exceeds minEvictableIdleTimeMillis, the keepAlive operation will be performed keepAlive: true # Spring monitoring, and use aop to record the execution time of the specified interface and the number of jdbc. Aop-patterns: "com.springboot.template.dao.*" # enable the built-in filter (the first stat must be Otherwise, the SQL cannot be monitored) # filters: stat,wall,log4j2 # configure the filter filter for monitoring statistics interception: # enable the status monitoring of druiddatasource stat: enabled: true db-type: mysql # enable slow sql monitoring. Slow sql is considered if it exceeds 2 seconds. Record to log log-slow-sql: true slow-sql-millis: 2000 # log monitoring Use slf4j for log output slf4j: enabled: true statement-log-error-enabled: true statement-create-after-log-enabled: false statement-close-after-log-enabled: false result-set-open-after-log-enabled: false result-set-close-after-log-enabled: false # configure WebStatFilter Used to collect data of web associated monitoring # web-stat-filter: enabled: true # launch StatFilter url-pattern: / * # filter all url exclusions: "* .js,*.gif,*.jpg,*.png,*.css,*.ico / druid/* "# exclude some unnecessary url session-stat-enable: true # enable the maximum number of session statistics function session-stat-max-count: 1000 # session. StatViewServlet is configured (monitoring page) by default. Statistics used to display Druid # stat-view-servlet: enabled: true # enable StatViewServlet url-pattern: / druid/* # access path to the built-in monitoring page The home page of the built-in monitoring page is / druid/index.html reset-enable: false # does not allow emptying statistics, recalculate login-username: root # configure monitoring page access password login-password: 123 allow: 127.0.0.1 # allowed access address, if allow is not configured or empty Then all deny: # addresses denied access are allowed. Deny takes precedence over allow. If it is in the deny list, even in the allow list, it will be denied.

The parameters of the above configuration file can be found in com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties and org.springframework.boot.autoconfigure.jdbc.DataSourceProperties

3.1 how to configure Filter

You can use spring.datasource.druid.filters=stat,wall,log4j... To enable the corresponding built-in Filter, but these Filter are the default configuration. If the default configuration does not meet the requirements, you can abandon this approach and configure Filter through the configuration file. Here is an example.

# configure StatFilter spring.datasource.druid.filter.stat.enabled=truespring.datasource.druid.filter.stat.db-type=h3spring.datasource.druid.filter.stat.log-slow-sql=truespring.datasource.druid.filter.stat.slow-sql-millis=2000# configure WallFilter spring.datasource.druid.filter.wall.enabled=truespring.datasource.druid.filter.wall.db-type=h3spring.datasource.druid.filter.wall.config.delete-allow=falsespring.datasource.druid.filter.wall.config.drop-table-allow=false

Configuration support is currently provided for the following Filter, which is configured according to (spring.datasource.druid.filter.*).

StatFilter

WallFilter

ConfigFilter

EncodingConvertFilter

Slf4jLogFilter

Log4jFilter

Log4j2Filter

CommonsLogFilter

Do not want to use the built-in Filters, for the custom Filter configuration to take effect, you need to set the enabled of the corresponding Filter to true, Druid Spring Boot Starter disables StatFilter by default, and you can set its enabled to true to enable it.

4 Monitoring page

(1) after starting the project, visit / druid/login.html to the login page and enter the user name and password to log in.

(2) the data source page is the basic information of the current DataSource configuration. The Filter of the above configuration can be found in it. If Filter is not configured (some information cannot be counted, such as "SQL Monitoring", the SQL execution information related to JDBC cannot be obtained).

(3) SQL monitoring page, which counts the execution of all SQL statements.

(4) URL monitoring page, which counts the access and execution of all Controller interfaces.

(5) Spring monitoring page, which uses aop to record the execution time of the specified interface and the number of jdbc

(6) SQL Firewall Page

Druid provides access to the blacklist and whitelist, so you can clearly see the sql protection.

(7) Session monitoring page

You can see the current session status, creation time, last active time, number of requests, request time and other detailed parameters.

(8) JSONAPI page

Access the monitoring interface of Druid through the form of api, and the api interface returns data in the form of Json.

5 sql monitoring

Configure the filter Druid web Monitoring filter (WebStatFilter) to count all database information in web application requests, such as sql statements issued, sql execution time, number of requests, requested url address, as well as seesion monitoring, database table visits, and so on.

Spring: datasource: druid: # configure WebStatFilter Used to collect data of web associated monitoring # web-stat-filter: enabled: true # launch StatFilter url-pattern: / * # filter all url exclusions: "* .js,*.gif,*.jpg,*.png,*.css,*.ico / druid/* "# exclude some unnecessary url session-stat-enable: true # enable the maximum number of session statistics function session-stat-max-count: 1000 # session. The default is 1006 slow sql records.

Sometimes, the execution of some SQL in the system is very slow. We want to use logging to enable the slow SQL recording function of Druid.

Spring: datasource: druid: filter: stat: enabled: true # enable DruidDataSource status Monitoring db-type: mysql # Database Type log-slow-sql: true # enable slow SQL recording slow-sql-millis: 2000 # default 3000 milliseconds, here more than 2 seconds, that is, slow, recorded to the log

After startup, if a slow SQL is encountered, it will be output to the log

7 spring monitoring

After access, spring monitoring has no data by default.

This requires importing the Starter of SprngBoot's AOP

Org.springframework.boot spring-boot-starter-aop

You need to configure it in application.yml:

Spring monitors AOP pointcuts, such as com.springboot.template.dao.*, configuration with multiple English commas separated

Spring.datasource.druid.aop-patterns= "com.springboot.template.dao.*" 8 to Ad (advertisement)

When you visit the monitoring page, you may see Alibaba's advertisement at the bottom of the page (footer).

Reason: common.js in the jar package of the introduced druid (there is a piece of js code to append the advertisement to the footer of the page)

If you want to get rid of it, there are two ways:

(1) comment this code directly and manually

If you are using Maven, go directly to the local warehouse and look for the jar package

Code to comment:

/ / this.buildFooter ()

Location of common.js:

Composer alibabaUniverse druidAccord 1.1.23 druidMae 1.1.23.jarAccording to supportGetWord ("supportUniverse"); httpAccorder; resourcesGear; JsUniver.js;

(2) filter with filter

Register a filter, filter common.js requests, and replace related ad content with regular expressions

@ Configuration@ConditionalOnWebApplication@AutoConfigureAfter (DruidDataSourceAutoConfigure.class) @ ConditionalOnProperty (name = "spring.datasource.druid.stat-view-servlet.enabled", havingValue = "true" MatchIfMissing = true) public class RemoveDruidAdConfig {/ * method name: removeDruidAdFilterRegistrationBean * method describes removing the advertisement at the bottom of the page * @ param properties com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties * @ return org.springframework.boot.web.servlet.FilterRegistrationBean * / @ Bean public FilterRegistrationBean removeDruidAdFilterRegistrationBean (DruidStatProperties properties) {/ / get the parameter DruidStatProperties.StatViewServlet config = properties.getStatViewServlet () of the web monitoring page / / extract the configuration path of common.js String pattern = config.getUrlPattern ()! = null? Config.getUrlPattern (): "/ druid/*"; String commonJsPattern = pattern.replaceAll ("\ *", "js/common.js"); final String filePath = "support/http/resources/js/common.js" / / create filter to filter Filter filter = new Filter () {@ Override public void init (FilterConfig filterConfig) throws ServletException {} @ Override public void doFilter (ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {chain.doFilter (request, response) / / reset buffer, response header will not be reset response.resetBuffer (); / / get common.js String text = Utils.readFromResource (filePath); / / regular replace banner, remove advertising message text = text.replaceAll at the bottom ("

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.