网站开发到上线,在线生成小程序,厦门网站建设网站建设,融资渠道ShardingSphere并不负责如何采集、存储以及展示应用性能监控的相关数据#xff0c;而是将SQL解析与SQL执行这两块数据分片的最核心的相关信息发送至应用性能监控系统#xff0c;并交由其处理。 换句话说#xff0c;ShardingSphere仅负责产生具有价值的数据#xff0c;并通过…ShardingSphere并不负责如何采集、存储以及展示应用性能监控的相关数据而是将SQL解析与SQL执行这两块数据分片的最核心的相关信息发送至应用性能监控系统并交由其处理。 换句话说ShardingSphere仅负责产生具有价值的数据并通过标准协议递交至相关系统。
现有问题
现有版本5.2.1不支持对于jdbc模式的直接监控现有pd版本实现双写的重构对于 ShardingSphereStatement、ShardingSpherePreparedStatement是相关监控是否需要重构代码连接池连接信息的监控
方案
针对sql执行情况的监控
针对当前的5.2.1,参考5.3.2进行agent相关代码的编写 升级版本5.3.2 Apache ShardingSphere-JDBC 性能监控实战 trace监控同上现有metrics指标[1]
数据连接池监控
HikariCP 自带
HikariCP 数据库连接池开启prometheus监控_hikaricp开启sql监控_croder的博客-CSDN博客
可观测性-Metrics-数据库连接池HikariCP监控_hikaricp 监控_lakernote的博客-CSDN博客
HikariConfig hikariConfig new HikariConfig();hikariConfig.setJdbcUrl(jdbc:mysql://xxxxxxxx:3306/pegasus_data_demo_00?useUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/ShanghaiuseSSLtruerequireSSLfalse);hikariConfig.setUsername(root);hikariConfig.setPassword(xxxxxxx.);hikariConfig.setDriverClassName(com.mysql.jdbc.Driver);hikariConfig.setAutoCommit(true);hikariConfig.setPoolName(demo-SSSSSS);hikariConfig.setMaximumPoolSize(10);hikariConfig.setMinimumIdle(3);// 创建HikariDataSourceHikariDataSource dataSource new HikariDataSource(hikariConfig);// 设置metric注册器 每10秒打印一次LoggingMeterRegistry loggingMeterRegistry new LoggingMeterRegistry(new LoggingRegistryConfig() {Overridepublic String get(String key) {return null;}Overridepublic Duration step() {return Duration.ofSeconds(10);}}, Clock.SYSTEM);dataSource.setMetricRegistry(loggingMeterRegistry);// 测试 持有3秒连接后才释放Connection connection dataSource.getConnection();TimeUnit.SECONDS.sleep(1000);connection.close();通过注入 Metricslogs 暴露详细 metrics[2]
BUT作者不希望在连接池层面进行日志的输出虽然是一点点的性能
SQL logging · Issue #57 · brettwooldridge/HikariCP Druid 自带监控通过暴露相关的Metrics的数据进行监控 ……
Metrics[1]
指标名称指标类型指标描述build_infoGAUGE构建信息parsed_sql_totalCOUNTER按类型INSERT、UPDATE、DELETE、SELECT、DDL、DCL、DAL、TCL、RQL、RDL、RAL、RUL分类的解析总数routed_sql_totalCOUNTER按类型INSERT、UPDATE、DELETE、SELECT分类的路由总数routed_result_totalCOUNTER路由结果总数(数据源路由结果、表路由结果)jdbc_stateGAUGEShardingSphere-JDBC 状态信息。0 表示正常状态1 表示熔断状态2 锁定状态jdbc_meta_data_infoGAUGEShardingSphere-JDBC 元数据信息jdbc_statement_execute_totalCOUNTER语句执行总数jdbc_statement_execute_errors_totalCOUNTER语句执行错误总数jdbc_statement_execute_latency_millisHISTOGRAM语句执行耗时jdbc_transactions_totalCOUNTER事务总数按 commitrollback 分类
Metrics[2]
指标含义详解hikaricp.connections当前总连接数包括空闲的连接和使用中的连接Connections activeConnection idleConnections会随着连接使用情况变化。hikaricp.connections.active正在使用中活跃连接数会随着连接使用情况变化。hikaricp.connections.idle空闲连接数会随着连接使用情况变化。hikaricp.connections.max最大连接数初始配置。hikaricp.connections.min最小连接数初始配置。hikaricp.connections.pending正在等待连接的线程数量一般来说这里应该都是 0。如果存在这个数据并且时间较长要触发告警视情况加大最大连接数。hikaricp.connections.acquire获取每个连接需要时间单位为 nshikaricp.connections.creation连接创建时间单位为 mshikaricp.connections.timeout创建连接超时次数hikaricp.connections.usage连接从池中取出到返回的时间单位为 ms连接被业务占用时间如果这个时间长的话可能是慢 SQL 或者长事务导致连接被占用问题。
# HELP routed_sql_total Total count of routed SQL
# TYPE routed_sql_total counter
routed_sql_total{typeSELECT,} 28.0
routed_sql_total{typeINSERT,} 62.0
# HELP jdbc_statement_execute_total Total number of statement execute
# TYPE jdbc_statement_execute_total counter
jdbc_statement_execute_total{statement_typeprepared_statement,} 14.0
# HELP parsed_sql_total Total count of parsed SQL
# TYPE parsed_sql_total counter
parsed_sql_total{typeSELECT,} 28.0
parsed_sql_total{typeINSERT,} 62.0
# HELP jdbc_statement_execute_latency_millis Statement execute latency millis histogram
# TYPE jdbc_statement_execute_latency_millis histogram
jdbc_statement_execute_latency_millis_bucket{le1.0,} 0.0
jdbc_statement_execute_latency_millis_bucket{le2.0,} 0.0
jdbc_statement_execute_latency_millis_bucket{le4.0,} 0.0
jdbc_statement_execute_latency_millis_bucket{le8.0,} 0.0
jdbc_statement_execute_latency_millis_bucket{le16.0,} 0.0
jdbc_statement_execute_latency_millis_bucket{le32.0,} 0.0
jdbc_statement_execute_latency_millis_bucket{le64.0,} 2.0
jdbc_statement_execute_latency_millis_bucket{le128.0,} 8.0
jdbc_statement_execute_latency_millis_bucket{le256.0,} 12.0
jdbc_statement_execute_latency_millis_bucket{le512.0,} 13.0
jdbc_statement_execute_latency_millis_bucket{le1024.0,} 14.0
jdbc_statement_execute_latency_millis_bucket{le2048.0,} 14.0
jdbc_statement_execute_latency_millis_bucket{le4096.0,} 14.0
jdbc_statement_execute_latency_millis_bucket{leInf,} 14.0
jdbc_statement_execute_latency_millis_count 14.0
jdbc_statement_execute_latency_millis_sum 2491.0
# HELP routed_result_total Total count of routed result
# TYPE routed_result_total counter
routed_result_total{objectdata_source,namepegasus_data_demo_00,} 118.0
routed_result_total{objectdata_source,namepegasus_data_demo_01,} 56.0
routed_result_total{objecttable,nameuser_info_0000,} 28.0
routed_result_total{objecttable,nameuser_info_0001,} 90.0
routed_result_total{objecttable,nameuser_info_0002,} 28.0
routed_result_total{objecttable,nameuser_info_0003,} 28.0
# HELP build_info Build information
# TYPE build_info gauge
build_info{nameShardingSphere,version5.3.2,} 1.0
# HELP jdbc_statement_execute_created Total number of statement execute
# TYPE jdbc_statement_execute_created gauge
jdbc_statement_execute_created{statement_typeprepared_statement,} 1.691140932075E9
# HELP jdbc_statement_execute_latency_millis_created Statement execute latency millis histogram
# TYPE jdbc_statement_execute_latency_millis_created gauge
jdbc_statement_execute_latency_millis_created 1.691140932077E9
# HELP parsed_sql_created Total count of parsed SQL
# TYPE parsed_sql_created gauge
parsed_sql_created{typeSELECT,} 1.691140931048E9
parsed_sql_created{typeINSERT,} 1.691141080971E9
# HELP routed_result_created Total count of routed result
# TYPE routed_result_created gauge
routed_result_created{objectdata_source,namepegasus_data_demo_00,} 1.691140931237E9
routed_result_created{objectdata_source,namepegasus_data_demo_01,} 1.691140931237E9
routed_result_created{objecttable,nameuser_info_0000,} 1.691140931237E9
routed_result_created{objecttable,nameuser_info_0001,} 1.691140931237E9
routed_result_created{objecttable,nameuser_info_0002,} 1.691140931237E9
routed_result_created{objecttable,nameuser_info_0003,} 1.691140931237E9
# HELP routed_sql_created Total count of routed SQL
# TYPE routed_sql_created gauge
routed_sql_created{typeSELECT,} 1.691140931227E9
routed_sql_created{typeINSERT,} 1.691141080974E9
