Database connection pool

The idea of connection pooling is to create a “cache pool” that stores a certain number of connections in advance. When a database connection needs to be established, one of the “cache pools” is taken out and then put into the pool.

How does connection pooling manage and allocate connection objects?

Using a “free pool” management has been created but unused connections, when a new connection request, first to “the free pool” in search of free connection object, if there is a free connection object in the pool, took the longest free time to take out the connection object allocation (if the object is valid), if the object is not available, Delete it from the free pool and retest it. If no available connection is detected, the system determines whether the current number of connections reaches the maximum number of connections. If not, a new connection is created. If the number of connections reaches the maximum number of connections, the system enters the waiting state. If an available connection is detected during the waiting time, it is allocated, or null is returned if the timeout has not detected an available connection. The system only counts the connections created. For detecting available connections, threads can be opened (prompt response speed, increase system overhead), or detection can be performed before allocation.

Druid

Durid is an open source JDBC application component of Alibaba, which consists of three parts:

  • DruidDriver: agent Driver that provides plug-in systems based on filter-chian mode
  • DruidDataoSource: An efficient and manageable database connection pool
  • SQLParser: SQL syntax analysis

With Druid connection pool middleware, you can:

  • Druid provides a powerful StatFilter plugin that provides detailed statistics on SQL execution performance, which is useful for online analysis of database access performance
  • Replace the traditional ==DBCP== and ==C3P0== middleware. Provide an efficient, powerful, and monitorable database connection pool
  • Encrypts database passwords. DruidDriver and DruidDataSource support PasswordCallBack.
  • SQL execution logs. Druid provides different logfilters that support == common-logging ==, ==Log4j==, and ==jdkLog==
  • Expand the JDBC. If you have programming requirements for the JDBC layer, you can use the Filter-chain mechanism provided by Druid to write JDBC extensions

Druid document

Druid official documentation

case

Add dependencies to pom.xml

        <! - Druid dependence - >
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>

Copy the code

Modifying data Source Configuration

   datasource:
     name: druidDataSource
     type: com.alibaba.druid.pool.DruidDataSource
     druid:
       driver-class-name: com.mysql.cj.jdbc.Driver
       url: JDBC: mysql: / / 192.168.1.8:3306 / studenms? useUnicode=true&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&characterEncoding=utf-8&serverTimezone=UTC
       username: root
       password: sa
       filters: stat,wall,log4j,config
       max-active: 100
       initial-size: 1
       max-wait: 60000
       min-idle: 1
       time-between-eviction-runs-millis: 60000
       min-evictable-idle-time-millis: 30000
       validation-query: select 'X'
       test-while-idle: true
       test-on-borrow: false
       test-on-return: false
       max-pool-prepared-statement-per-connection-size: 20
       max-open-prepared-statements: 50
       
       Configure the monitoring server
       stat-view-servlet:
         If not, use the default value false
         enabled: true
         login-username: root
         login-password: sa
         reset-enable: false
         url-pattern: /druid/*
Copy the code

Parameter Description:

  • Max-active: indicates the maximum number of connections
  • Initial-size: initializes the number of connections
  • Min-idle: indicates the minimum number of connections
  • Max-wait: indicates the maximum wait time
  • Time-between-eviction -runs-millis: the length of detection interval
  • Min-evictable-idle-time-millis: indicates the minimum lifetime of a connection pool
  • Filters: stat,wall,log4j,config, configures the filers for monitoring statistics interception. If no filers are available, the monitoring screen cannot count them. ‘wall’ is used for the firewall

Druid provides the following filters

Druid-spring-boot-starter simplifies some configurations. If you need to customize the configuration, see druid-spring-boot-starte

test

Enter localhost:8090/druid in the browser. The login page is automatically displayed

Enter login-username and login-password in the monitoring service configuration to access the console page

The test interface

An interface to perform paging queries

Go to the console to view SQL monitoring.

From the SQL monitor, two SQL entries were executed. Call an interface, execute 2 SQL, why?

Mybatis = Count(*) = Count(*) = Count(*) = Count(*) Because the interface uses the PageHelper plug-in, it simplifies the SQL of Mybatis, developers only need to write a SQL query all data, it will automatically intercept, paging, so it is actually executed 2 SQL.Copy the code

Select count(*) takes 14 milliseconds to execute. Druid gives you the “slowest” hint. In fact, this table only has three rows and seven fields. Use select count() with caution.

You can also see url monitoring, which API interfaces are implemented, you can see here

The problem

1. The log4j dependency is missing

  1. The druid monitoring page is blank

Check ==stat-view-servlet== Monitor whether the service configuration is enabled and set enable to true