0
# Monitoring and Statistics
1

2
Comprehensive performance monitoring and statistics collection framework providing detailed insights into database connections, SQL execution, and system performance with JMX integration and web interfaces.
3

4
## Core Monitoring Services
5

6
### DruidStatService - Main Statistics Service
7

8
```java { .api }
9
// Primary statistics service
10
class DruidStatService {
11
    public static DruidStatService getInstance();
12
    
13
    // REST-style API access
14
    public String service(String url);
15
    
16
    // Configuration
17
    public boolean isResetEnable();
18
    public void setResetEnable(boolean value);
19
    
20
    // JMX integration
21
    public static void registerMBean();
22
    public static void unregisterMBean();
23
}
24
```
25

26
### Statistics API Endpoints
27

28
```java
29
// Available service endpoints via service(String url) method
30

31
// Core statistics
32
"/basic.json"                          // Basic system statistics
33
"/datasource.json"                     // All DataSource statistics  
34
"/sql.json"                           // SQL execution statistics
35
"/sql.json?orderBy=ExecuteCount"      // Ordered SQL statistics
36
"/activeConnectionStackTrace.json"    // Active connection debugging
37

38
// Security and web statistics  
39
"/wall.json"                          // Security wall statistics
40
"/weburi.json"                        // Web URI access statistics
41
"/webapp.json"                        // Web application statistics
42
"/websession.json"                    // Web session statistics
43
"/spring.json"                        // Spring method statistics
44

45
// Management operations
46
"/reset-all.json"                     // Reset all statistics
47
"/log-and-reset.json"                 // Log current stats and reset
48
```
49

50
### DruidDataSourceStatManager - DataSource Statistics Management
51

52
```java { .api }
53
// DataSource statistics manager
54
class DruidDataSourceStatManager {
55
    public static DruidDataSourceStatManager getInstance();
56
    
57
    // DataSource registration
58
    public boolean addDataSource(Object dataSource, String name);
59
    public boolean removeDataSource(Object dataSource);
60
    
61
    // Statistics access
62
    public Set<DruidDataSourceStatValue> getDataSourceList();
63
    public static Set<Object> getDruidDataSourceInstances();
64
    
65
    // Management operations
66
    public void reset();
67
    public void logAndResetDataSource();
68
    public long getResetCount();
69
    
70
    // JMX support
71
    public TabularData getDataSourceList();
72
}
73
```
74

75
## DataSource Statistics
76

77
### JdbcDataSourceStat - Individual DataSource Statistics
78

79
```java { .api }
80
// DataSource-level statistics collector
81
class JdbcDataSourceStat {
82
    // SQL statistics management
83
    public Map<String, JdbcSqlStat> getSqlStatMap();
84
    public JdbcSqlStat getSqlStat(String sql);
85
    public JdbcSqlStat createSqlStat(String sql);
86
    public Map<String, JdbcSqlStat> getSqlStatMapAndReset();
87
    
88
    // Connection statistics
89
    public long getConnectionActiveCount();
90
    public long getConnectionActiveCountMax();
91
    public long getConnectionCloseCount(); 
92
    public long getConnectionCommitCount();
93
    public long getConnectionRollbackCount();
94
    public long getConnectionConnectCount();
95
    public long getConnectionConnectErrorCount();
96
    
97
    // Timing statistics
98
    public long getConnectionConnectMillisTotal();
99
    public long getConnectionConnectMillisMax();
100
    public long getConnectionConnectMillisMin();
101
    public long[] getConnectionHistogramValues();
102
    public long[] getConnectionHoldTimeHistogramValues();
103
    
104
    // Resource management
105
    public long getBlobOpenCount();
106
    public long getClobOpenCount();
107
    public long getReadStringLength();
108
    public long getReadBytesLength();
109
    public long getInputStreamOpenCount();
110
    public long getReaderOpenCount();
111
    
112
    // Management operations
113
    public void reset();
114
    public long getResetCount();
115
}
116
```
117

118
### Connection Statistics Example
119

120
```java
121
import com.alibaba.druid.pool.DruidDataSource;
122
import com.alibaba.druid.stat.JdbcDataSourceStat;
123

124
// Access DataSource statistics
125
DruidDataSource dataSource = getDataSource();
126
JdbcDataSourceStat stat = dataSource.getDataSourceStat();
127

128
// Connection metrics
129
long activeConnections = stat.getConnectionActiveCount();
130
long maxConnections = stat.getConnectionActiveCountMax();
131
long totalConnections = stat.getConnectionConnectCount();
132
long connectionErrors = stat.getConnectionConnectErrorCount();
133

134
// Connection timing
135
long totalConnectTime = stat.getConnectionConnectMillisTotal();
136
long maxConnectTime = stat.getConnectionConnectMillisMax();
137
long[] connectionHistogram = stat.getConnectionHistogramValues();
138

139
// Transaction statistics  
140
long commits = stat.getConnectionCommitCount();
141
long rollbacks = stat.getConnectionRollbackCount();
142
```
143

144
## SQL Execution Statistics  
145

146
### JdbcSqlStat - Individual SQL Statement Statistics
147

148
```java { .api }
149
// SQL statement statistics
150
class JdbcSqlStat {
151
    // Execution counts
152
    public long getExecuteCount();
153
    public long getExecuteSuccessCount();
154
    public long getExecuteErrorCount();
155
    public long getRunningCount();
156
    public long getConcurrentMax();
157
    
158
    // Timing statistics
159
    public long getExecuteMillisTotal();
160
    public long getExecuteMillisMax();
161
    public long getExecuteMillisMin();
162
    public double getExecuteMillisMean();
163
    
164
    // Row operations
165
    public long getFetchRowCount();
166
    public long getFetchRowCountMax();
167
    public long getUpdateCount();
168
    public long getUpdateCountMax();
169
    
170
    // Histogram data
171
    public long[] getHistogramValues();            // Execution time distribution
172
    public long[] getFetchRowCountHistogramValues();
173
    public long[] getUpdateCountHistogramValues();
174
    public long[] getExecuteAndResultHoldTimeHistogramValues();
175
    
176
    // Transaction context
177
    public long getInTransactionCount();
178
    
179
    // Error information
180
    public Throwable getLastError();
181
    public long getLastErrorCount();
182
    public Date getLastErrorTime();
183
    public String getLastErrorMessage();
184
    public String getLastErrorClass();
185
    public String getLastErrorStackTrace();
186
    
187
    // Timing details
188
    public Date getLastTime();
189
    public Date getMaxTimespan();
190
    public long getLastExecuteTimeNano();
191
    
192
    // Data access
193
    public Map<String, Object> getData();
194
    public JdbcSqlStatValue getValue(boolean reset);
195
}
196
```
197

198
### SQL Statistics Usage
199

200
```java
201
import com.alibaba.druid.stat.JdbcSqlStat;
202

203
// Access SQL statistics
204
Map<String, JdbcSqlStat> sqlStats = dataSourceStat.getSqlStatMap();
205

206
for (Map.Entry<String, JdbcSqlStat> entry : sqlStats.entrySet()) {
207
    String sql = entry.getKey();
208
    JdbcSqlStat stat = entry.getValue();
209
    
210
    // Performance metrics
211
    long executions = stat.getExecuteCount();
212
    long errors = stat.getExecuteErrorCount();
213
    long totalTime = stat.getExecuteMillisTotal();
214
    long maxTime = stat.getExecuteMillisMax();
215
    double avgTime = stat.getExecuteMillisMean();
216
    
217
    // Row operations
218
    long rowsFetched = stat.getFetchRowCount();
219
    long rowsUpdated = stat.getUpdateCount();
220
    
221
    // Concurrency
222
    long currentRunning = stat.getRunningCount();
223
    long maxConcurrent = stat.getConcurrentMax();
224
    
225
    // Error information
226
    if (stat.getLastError() != null) {
227
        System.out.println("Last error: " + stat.getLastErrorMessage());
228
        System.out.println("Error time: " + stat.getLastErrorTime());
229
    }
230
}
231
```
232

233
## Statistics Collection Configuration
234

235
### StatFilter - Statistics Collection Filter
236

237
```java { .api }
238
// Statistics collection filter
239
class StatFilter extends FilterEventAdapter implements StatFilterMBean {
240
    // Configuration
241
    public void setSlowSqlMillis(long millis);           // Default: 3000ms
242
    public long getSlowSqlMillis();
243
    public void setLogSlowSql(boolean enabled);          // Default: false
244
    public boolean isLogSlowSql();
245
    public void setMergeSql(boolean enabled);            // Default: false
246
    public boolean isMergeSql();
247
    
248
    // Connection tracking
249
    public void setConnectionStackTraceEnable(boolean enabled);
250
    public boolean isConnectionStackTraceEnable();
251
    
252
    // SQL merging and parameterization
253
    public String mergeSql(String sql);
254
    public String mergeSql(String sql, DbType dbType);
255
    
256
    // Statistics access
257
    public JdbcDataSourceStat getDataSourceStat();
258
    public TabularData getSqlList();
259
    public CompositeData getSqlStat(String sql);
260
    
261
    // Management operations
262
    public void reset();
263
    public long getResetCount();
264
}
265
```
266

267
### StatFilter Configuration Example
268

269
```java
270
import com.alibaba.druid.filter.stat.StatFilter;
271
import com.alibaba.druid.pool.DruidDataSource;
272

273
// Configure statistics collection
274
DruidDataSource dataSource = new DruidDataSource();
275

276
// Method 1: Via filters string
277
dataSource.setFilters("stat");
278

279
// Method 2: Programmatic configuration
280
StatFilter statFilter = new StatFilter();
281
statFilter.setSlowSqlMillis(5000);           // 5 second slow SQL threshold
282
statFilter.setLogSlowSql(true);              // Log slow SQL statements
283
statFilter.setMergeSql(true);                // Merge similar SQL statements
284
statFilter.setConnectionStackTraceEnable(true); // Capture connection stack traces
285

286
dataSource.getProxyFilters().add(statFilter);
287
```
288

289
## Web Interface Integration
290

291
### StatViewServlet - Web Statistics Interface
292

293
```java { .api }
294
// Web interface servlet
295
class StatViewServlet extends HttpServlet {
296
    // Configuration parameters
297
    // resetEnable - Allow statistics reset via web interface
298
    // jmxUrl, jmxUsername, jmxPassword - Remote JMX connection
299
    // allow, deny - IP access control
300
}
301
```
302

303
### Web Interface Setup
304

305
```xml
306
<!-- web.xml configuration -->
307
<servlet>
308
    <servlet-name>DruidStatView</servlet-name>
309
    <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
310
    <init-param>
311
        <param-name>resetEnable</param-name>
312
        <param-value>true</param-value>
313
    </init-param>
314
    <init-param>
315
        <param-name>loginUsername</param-name>
316
        <param-value>admin</param-value>
317
    </init-param>
318
    <init-param>
319
        <param-name>loginPassword</param-name>
320
        <param-value>password</param-value>
321
    </init-param>
322
</servlet>
323
<servlet-mapping>
324
    <servlet-name>DruidStatView</servlet-name>
325
    <url-pattern>/druid/*</url-pattern>
326
</servlet-mapping>
327
```
328

329
## JMX Integration
330

331
### MBean Registration and Access
332

333
```java { .api }
334
// JMX ObjectNames
335
// com.alibaba.druid:type=DruidStatService
336
// com.alibaba.druid:type=DruidDataSourceStat  
337
// com.alibaba.druid:type=DruidDataSource,id={name}
338
```
339

340
### JMX Usage Examples
341

342
```java
343
import javax.management.MBeanServer;
344
import javax.management.ObjectName;
345
import java.lang.management.ManagementFactory;
346

347
// Access statistics via JMX
348
MBeanServer server = ManagementFactory.getPlatformMBeanServer();
349

350
// DruidStatService MBean
351
ObjectName statServiceName = new ObjectName("com.alibaba.druid:type=DruidStatService");
352
String datasourceStats = (String) server.invoke(statServiceName, "service", 
353
    new Object[]{"/datasource.json"}, new String[]{String.class.getName()});
354

355
// SQL statistics with pagination
356
String sqlStats = (String) server.invoke(statServiceName, "service",
357
    new Object[]{"/sql.json?page=1&perPageCount=100&orderBy=ExecuteCount&orderType=desc"},
358
    new String[]{String.class.getName()});
359

360
// DataSource manager MBean
361
ObjectName managerName = new ObjectName("com.alibaba.druid:type=DruidDataSourceStat");
362
TabularData dataSourceList = (TabularData) server.getAttribute(managerName, "DataSourceList");
363
Long resetCount = (Long) server.getAttribute(managerName, "ResetCount");
364

365
// Reset all statistics
366
server.invoke(managerName, "reset", null, null);
367
```
368

369
## Performance Monitoring
370

371
### Histogram Analysis
372

373
```java
374
// Execution time histogram buckets
375
long[] histogram = sqlStat.getHistogramValues();
376
// Index 0: 0-1ms
377
// Index 1: 1-10ms  
378
// Index 2: 10-100ms
379
// Index 3: 100ms-1s
380
// Index 4: 1s-10s
381
// Index 5: 10s-100s
382
// Index 6: 100s-1000s
383
// Index 7: >1000s
384

385
// Analyze performance distribution
386
long totalExecutions = sqlStat.getExecuteCount(); 
387
long slowExecutions = histogram[4] + histogram[5] + histogram[6] + histogram[7];
388
double slowPercentage = (double) slowExecutions / totalExecutions * 100;
389

390
System.out.println("Slow executions (>1s): " + slowPercentage + "%");
391
```
392

393
### Active Connection Monitoring
394

395
```java
396
// Monitor active connections and detect leaks
397
DruidStatService statService = DruidStatService.getInstance();
398
String activeConnectionsJson = statService.service("/activeConnectionStackTrace.json");
399

400
// Parse JSON to analyze connection stack traces
401
// Identify potential connection leaks and problematic code paths
402
```
403

404
## Statistics Reset and Management
405

406
### Reset Operations
407

408
```java
409
// Reset individual DataSource statistics
410
JdbcDataSourceStat stat = dataSource.getDataSourceStat();
411
stat.reset();
412

413
// Reset all DataSource statistics via manager
414
DruidDataSourceStatManager.getInstance().reset();
415

416
// Reset via StatService
417
DruidStatService statService = DruidStatService.getInstance();
418
if (statService.isResetEnable()) {
419
    statService.service("/reset-all.json");
420
    statService.service("/log-and-reset.json");  // Log before reset
421
}
422
```
423

424
### Periodic Statistics Collection
425

426
```java
427
import java.util.concurrent.ScheduledExecutorService;
428
import java.util.concurrent.Executors;
429
import java.util.concurrent.TimeUnit;
430

431
// Automated statistics collection
432
ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
433

434
scheduler.scheduleAtFixedRate(() -> {
435
    DruidStatService statService = DruidStatService.getInstance();
436
    
437
    // Collect current statistics
438
    String datasourceStats = statService.service("/datasource.json");
439
    String sqlStats = statService.service("/sql.json?orderBy=ExecuteCount");
440
    
441
    // Send to monitoring system
442
    sendToMonitoringSystem(datasourceStats, sqlStats);
443
    
444
    // Optional: Reset statistics after collection
445
    if (shouldReset()) {
446
        statService.service("/reset-all.json");
447
    }
448
}, 0, 60, TimeUnit.SECONDS);
449
```
450

451
## Complete Monitoring Setup Example
452

453
```java
454
import com.alibaba.druid.pool.DruidDataSource;
455
import com.alibaba.druid.filter.stat.StatFilter;
456
import com.alibaba.druid.stat.DruidStatService;
457

458
public class DruidMonitoringConfiguration {
459
    
460
    public static DruidDataSource createMonitoredDataSource() {
461
        // Create DataSource with monitoring
462
        DruidDataSource dataSource = new DruidDataSource();
463
        dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
464
        dataSource.setUsername("dbuser");
465
        dataSource.setPassword("dbpass");
466
        
467
        // Configure StatFilter
468
        StatFilter statFilter = new StatFilter();
469
        statFilter.setSlowSqlMillis(3000);        // 3 second slow SQL threshold
470
        statFilter.setLogSlowSql(true);           // Log slow SQL
471
        statFilter.setMergeSql(true);             // Merge similar queries  
472
        statFilter.setConnectionStackTraceEnable(true); // Debug connection leaks
473
        
474
        dataSource.getProxyFilters().add(statFilter);
475
        
476
        // Enable reset functionality
477
        DruidStatService.getInstance().setResetEnable(true);
478
        
479
        // Register MBeans for JMX access
480
        DruidStatService.registerMBean();
481
        
482
        return dataSource;
483
    }
484
    
485
    public static void printStatistics(DruidDataSource dataSource) {
486
        JdbcDataSourceStat stat = dataSource.getDataSourceStat();
487
        
488
        // DataSource metrics
489
        System.out.println("Active Connections: " + stat.getConnectionActiveCount());
490
        System.out.println("Total Connections: " + stat.getConnectionConnectCount());
491
        System.out.println("Connection Errors: " + stat.getConnectionConnectErrorCount());
492
        System.out.println("Commits: " + stat.getConnectionCommitCount());
493
        System.out.println("Rollbacks: " + stat.getConnectionRollbackCount());
494
        
495
        // SQL statistics
496
        Map<String, JdbcSqlStat> sqlStats = stat.getSqlStatMap();
497
        System.out.println("Unique SQL statements: " + sqlStats.size());
498
        
499
        for (Map.Entry<String, JdbcSqlStat> entry : sqlStats.entrySet()) {
500
            JdbcSqlStat sqlStat = entry.getValue();
501
            if (sqlStat.getExecuteCount() > 0) {
502
                System.out.printf("SQL: %s%n", entry.getKey());
503
                System.out.printf("  Executions: %d, Errors: %d%n", 
504
                    sqlStat.getExecuteCount(), sqlStat.getExecuteErrorCount());
505
                System.out.printf("  Avg Time: %.2fms, Max Time: %dms%n",
506
                    sqlStat.getExecuteMillisMean(), sqlStat.getExecuteMillisMax());
507
            }
508
        }
509
    }
510
}
511
```
512

513
This comprehensive monitoring framework provides detailed visibility into database performance, connection usage, SQL execution patterns, and system health, enabling effective performance tuning, troubleshooting, and capacity planning.