Everyone in the project will use the database connection pool to manage database links. The druID database connection pool can be used to monitor the performance of the database connection pool. The druid database connection pool can be used to monitor the performance of the database connection pool. The druid database connection pool can also be used to record some slow SQL records.
Start by introducing the Druid database connection pool in SpringBoot
Introducing the Druid database connection pool in SpringBoot is very simple. Add the relevant configuration to the application.yml configuration file:
Spring: datasource: url: database connection username: database username password: database password driver-class-name: com.mysql.jdbc.driver platform: Mysql type: com. Alibaba. Druid. Pool. DruidDataSource # below to replenish the connection pool Settings, applied to all data sources above # initialization size, minimum, maximum initialSize: 60 minIdle: 120 maxActive: 200 # configuration for connecting waiting timeout time maxWait: how long is the interval to a 60000 # configuration testing, testing needs to be closed free connection, unit is a millisecond timeBetweenEvictionRunsMillis: 60000 # configure a connection in the pool minimum survival time, unit is a millisecond minEvictableIdleTimeMillis: 30000 validationQuery: select 'x' testWhileIdle: TestOnBorrow: false testOnReturn: false # Enable PSCache and specify the size of PSCache on each connection poolPreparedStatements: True maxPoolPreparedStatementPerConnectionSize: # 20 configuration monitoring statistics of intercepting filters, remove the monitor interface after SQL is unable to statistics, "wall" used in a firewall filters: Stat, Wall,log4j # Enable mergeSql via connectProperties; ConnectionProperties: druid.stat. MergeSql =true; Druid.stat. SlowSqlMillis =5000 #useGlobalDataSourceStat: trueCopy the codeThis configuration is generic, just replace the connection pool type with the druid database type
- type: com.alibaba.druid.pool.DruidDataSource
This can be used directly in the introduced ORM framework
Enable druid monitoring in Springboot
There are a number of filters built into Druid. StatFilter is used for monitoring information. There are two ways to enable configuration
Enable druid monitoring in Springboot
It is enabled by default, which is the configuration in the database connection pool above
Filters: stat,wall,log4jCopy the code
Enable druid monitoring in Springboot
Custom configuration to enable StatFilter is also simple
# filters: stat,wall,log4j # StatFilter: stat: enabled true db-type: mysql log-slow-sql: true slow-sql-millis: 1000Copy the codeThese configurations will not be explained in detail, but are literal, slow-SQL-millis, and slow SQL times are in milliseconds
Query the monitoring mode
Through DruidStatManagerFacade. GetInstance (). GetDataSourceStatDataList () method to query the database link pool monitoring information
Code can pass DruidStatManagerFacade. GetInstance () getDataSourceStatDataList () method to query the database link pool monitoring information, You can store this monitoring information through the service interface, write the scheduled task to the database, write the scheduled task to the log and extract it to Kafka, etc., for real-time monitoring.
[ { "ActiveCount": 0, "ActivePeak": 8, "ActivePeakTime": 1586764216671, "BlobOpenCount": 0, "ClobOpenCount": 0, "CommitCount": 67, "ConnectionHoldTimeHistogram": [ 0, 0, 0, 0, 0, 0, 0, 0 ], "DbType": "mysql", "DefaultAutoCommit": true, "DriverClassName": "com.mysql.jdbc.Driver", "ErrorCount": 0, "ExceptionSorterClassName": "com.alibaba.druid.pool.vendor.MySqlExceptionSorter", "ExecuteCount": 91, "FilterClassNames": [], "Identity": 1606272155, "InitialSize": 0, "LogicCloseCount": 93, "LogicConnectCount": 93, "LogicConnectErrorCount": 0, "LoginTimeout": 0, "MaxActive": 8, "MinIdle": 0, "Name": "DataSource-1606272155", "NotEmptyWaitCount": 69, "NotEmptyWaitMillis": 0, "PSCacheAccessCount": 0, "PSCacheHitCount": 0, "PSCacheMissCount": 0, "PhysicalCloseCount": 0, "PhysicalConnectCount": 8, "PhysicalConnectErrorCount": 0, "PoolingCount": 8, "PoolingPeak": 8, "PoolingPeakTime": 1586764216896, "QueryTimeout": 0, "RemoveAbandoned": false, "RollbackCount": 0, "StartTransactionCount": 67, "TestOnBorrow": false, "TestOnReturn": false, "TestWhileIdle": true, "TransactionHistogram": [ 0, 5, 62, 0, 0, 0, 0 ], "TransactionQueryTimeout": 0, "URL": "", "UserName": "", "ValidConnectionCheckerClassName": "com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker", "WaitThreadCount": 0}]Copy the codeThe data is returned in JSON format, so let’s talk about the meaning of each parameter, only the key monitoring fields
| parameter | Value (value in chestnut) | meaning |
|---|---|---|
| ActiveCount | 0 | Number of active connections in the current connection pool |
| ActivePeak | 1 | Peak number of active connections in the connection pool |
| ActivePeakTime | 2020/4/13 affliction | The time when the peak of the active connection pool occurs |
| BlobOpenCount | 0 | Open a Blob number |
| ClobOpenCount | 0 | Clob open number |
| CommitCount | 0 | Submit the number |
| ConnectionHoldTimeHistogram | 0,0,0,0,0,0,0,0 | The connection holds the time distribution, and the distribution interval is [0-1 ms, 1-10 ms, 10-100 ms, 100ms-1s, 1-10 s, 10-100 s, 100-1000 s, >1000 s]. This value is an array, and the meaning of the index bit of the value is as above. The data on the index number represents the number of connections contained in this time interval |
| ErrorCount | 0 | Wrong number |
| ExecuteCount | 0 | Number of executions |
| InitialSize | 60 | The number of initial connections created when the connection pool is established |
| LogicCloseCount | 2 | The total number of logical connection closures generated |
| LogicConnectCount | 2 | The total number of logical connections created |
| LogicConnectErrorCount | 0 | The total number of logical connection errors generated |
| LoginTimeout | 0 | Timeout duration of database client login |
| MaxActive | 200 | The maximum number of active connections in the connection pool |
| MinIdle | 120 | The minimum number of active connections in the connection pool |
| NotEmptyWaitCount | 0 | The maximum number of times to wait to get a connection |
| NotEmptyWaitMillis | 0 | How long to wait, in milliseconds, to obtain a connection |
| PSCacheAccessCount | 0 | PSCache Indicates the total number of PSCache accesses |
| PSCacheHitCount | 0 | PSCache Number of matches |
| PSCacheMissCount | 0 | PSCache number of missed matches |
| PhysicalCloseCount | 0 | Total number of physical shutdowns generated |
| PhysicalConnectCount | 60 | The total number of physical connections created |
| PhysicalConnectErrorCount | 0 | The total number of physical connection failures generated |
| PoolingCount | 60 | Number of connections in the current connection pool |
| PoolingPeak | 60 | The peak number of connections in the connection pool |
| PoolingPeakTime | 2020/4/13 affliction | The time when the number of connection pools peaked |
| QueryTimeout | 0 | Query timeout |
| RollbackCount | 0 | Roll back the number of |
| StartTransactionCount | 0 | Number of transactions started |
| TransactionHistogram | 0,0,0,0,0,0,0,0 | Transaction running time distribution, the distribution interval is [0-10 ms, 10-100 ms, 100-1 s, 1-10 s, 10-100 s, >100 s], this value is an array, the value of the index bit meaning as above, the number of index data represents the number of transactions contained in this time interval |
| TransactionQueryTimeout | 0 | Number of transaction query timeouts |
| WaitThreadCount | 0 | Number of threads currently waiting to acquire connections |
Use druid’s monitoring component to monitor the connection pool of the database. Thank you!