Druid – Personal article – SegmentFault Think no

In the previous section, we introduced HikariCP, the default data source used by Spring Boot for automated configuration in the JDBC module. In the next section, we’ll look at another widely used open source data source: Druid.

Druid is an open source project produced by Alibaba’s Database division. It is not only a high-performance database connection pool, but also a database connection pool with its own monitoring. While HikariCP is already great, Druid may be more familiar to domestic users. So, how to use Druid in Spring Boot is a basic skill that back-end developers must master.

Configure the Druid data source

This section will be based on code from the Spring Boot 2.x Basics tutorial: Accessing the MySQL database using JdbcTemplate. Therefore, the reader can check out the Chapter3-1 directory from the code repository at the end of this article for practical learning.

Let’s start by configuring the Druid data source for our Spring Boot project:

Step 1: Introduce the Druid’s official Spring Boot Starter package in POM.xml.

< the dependency > < groupId > com. Alibaba < / groupId > < artifactId > druid - spring - the boot - starter < / artifactId > < version > 1.1.21 < / version >  </dependency>Copy the code

Step 2: Configure the database connection information in application.properties.

Druid = spring.datasource. Druid = spring.datasource. Druid = spring.datasource. Druid = spring.datasource. Druid = spring.datasource.

spring.datasource.druid.url=jdbc:mysql://localhost:3306/test
spring.datasource.druid.username=root
spring.datasource.druid.password=
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.DriverCopy the code

Step 3: Configure the Druid connection pool.

As with Hikari, to make good use of a data source, you need to configure its connection pool as follows:

spring.datasource.druid.initialSize=10
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.minIdle=1
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=true
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxOpenPreparedStatements=20
spring.datasource.druid.validationQuery=SELECT 1
spring.datasource.druid.validation-query-timeout=500
spring.datasource.druid.filters=statCopy the code

See the table below for a description of the various connection pool configurations in Druid.

At this point, you’re done switching HikariCP, the default Spring Boot data source, to Druid.

Configure Druid monitoring

If you use Druid, how can you not use the monitoring function of Druid? Now let’s do some more configuration to enable monitoring for Druid.

Step 1: Import the Spring-boot-starter -actuator module in POM. XML

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>Copy the code

Step 2: Add monitoring configuration for Druid in application.properties.

spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=adminCopy the code

The preceding configuration is used to enable the STAT monitoring statistics page and configure monitoring content. The meanings are as follows:

• spring.datasource.druid.stat-view-servlet.url-pattern: Access address rule
• spring.datasource.druid.stat-view-servlet.reset-enable: Indicates whether statistics can be cleared
• spring.datasource.druid.stat-view-servlet.login-username: Indicates the account used to log in to the monitoring page
• spring.datasource.druid.stat-view-servlet.login-password: Password for logging in to the monitoring page

Step 3: For the UserService content we implemented earlier, we create a Controller to invoke the data access operation through the interface:

@Data @AllArgsConstructor @RestController public class UserController { private UserService userService; @PostMapping("/user") public int create(@RequestBody User user) { return userService.create(user.getName(), user.getAge()); } @GetMapping("/user/{name}") public List<User> getByName(@PathVariable String name) { return userService.getByName(name); } @DeleteMapping("/user/{name}") public int deleteByName(@PathVariable String name) { return userService.deleteByName(name); } @GetMapping("/user/count") public int getAllUsers() { return userService.getAllUsers(); } @DeleteMapping("/user/all") public int deleteAllUsers() { return userService.deleteAllUsers(); }}Copy the code

Step 4: complete all of the above configuration, start the application, access to the Druid monitoring page, http://localhost:8080/druid/, you can see the following login page:

Input on the spring. The datasource. The druid. Stat – view – servlet. The login username and spring. The datasource. The druid. Stat – view – servlet. Login password configuration. – After logging in to your account and password, you can see the following monitoring page:

When you enter this side, you can see all kinds of monitoring data for the application side. Here are some of the most commonly used monitoring pages:

Data source: Here you can see the database connection pool information we configured earlier and various indicators of current usage.

SQL monitoring: SQL statements and statistics executed in this data source. On this page, we can easily see the current Spring Boot has executed the SQL, the execution frequency and efficiency of these SQL can be clearly seen. If you don’t see any data here? Remember that we created a Controller earlier and used these interfaces to trigger UserService operations on the database. So, here we can invoke the interface to trigger some operations, so that the SQL monitor page will produce some data:

In terms of monitoring items in the figure, execution time, number of rows read and number of rows updated are all expressed in the form of interval distribution, and the time is distributed into 8 intervals:

• 0-1 Indicates the number of times that the elapsed time ranges from 0 to 1 ms
• 1-10 Times between 1 and 10 milliseconds
• 10-100 Times between 10 and 100 milliseconds
• 100-1,000 Times between 100 and 1000 milliseconds
• 1,000-10,000 Times of taking 1 to 10 seconds
• Times that take 10 to 100 seconds
• 100,000-1,000,000 times between 100 and 1000 seconds
• 1,000,000 – The number of times it took more than 1000 seconds

Log the number of times the elapsed interval has occurred, and by differentiating the distribution, it is easy to see the distribution of excellent, mediocre, and extreme SQL running. Time-sensitive distribution provides the “Distribution when executing +RS”, which combines the execution time and the ResultSet holding time for monitoring. This facilitates the diagnosis of queries that return too many rows.

SQL firewall: This page records the monitoring data of different dimensions from SQL monitoring. It is more used for the statistics of table access dimension and SQL defense dimension.

The function of data records statistics need in spring. The datasource. The druid. Would like to add wall filters attribute record statistics, such as this:

spring.datasource.druid.filters=stat,wallCopy the code

Note: all monitoring information here is for the data source of the application instance, not for the global database level. It can be regarded as application layer monitoring, not middleware layer monitoring.

Code sample

For an example of this article, see the chapter3-3 directory in the repository below:

• Github:github.com/dyc87112/Sp…
• Gitee:gitee.com/didispace/S…

If you found this article good, welcomeStarSupport, your attention is my motivation!

Welcome to pay attention to my official account: Program ape DD, for exclusive learning resources and daily dry goods push.


If you are interested in my topics, you can also follow my blog:
didispace.com