ShardingSphere并不負(fù)責(zé)如何采集、存儲(chǔ)以及展示應(yīng)用性能監(jiān)控的相關(guān)數(shù)據(jù)，而是將SQL解析與SQL執(zhí)行這兩塊數(shù)據(jù)分片的最核心的相關(guān)信息發(fā)送至應(yīng)用性能監(jiān)控系統(tǒng)，并交由其處理。 換句話說(shuō)，ShardingSphere僅負(fù)責(zé)產(chǎn)生具有價(jià)值的數(shù)據(jù)，并通過(guò)標(biāo)準(zhǔn)協(xié)議遞交至相關(guān)系統(tǒng)。

現(xiàn)有問(wèn)題

1. 現(xiàn)有版本5.2.1不支持對(duì)于jdbc模式的直接監(jiān)控
2. 現(xiàn)有pd版本實(shí)現(xiàn)雙寫(xiě)的重構(gòu)對(duì)于 ShardingSphereStatement、ShardingSpherePreparedStatement是相關(guān)監(jiān)控是否需要重構(gòu)代碼
3. 連接池連接信息的監(jiān)控

方案

針對(duì)sql執(zhí)行情況的監(jiān)控

1. 針對(duì)當(dāng)前的5.2.1,參考5.3.2進(jìn)行agent相關(guān)代碼的編寫(xiě)

2. 升級(jí)版本5.3.2

   Apache ShardingSphere-JDBC 性能監(jiān)控實(shí)戰(zhàn)

3. trace監(jiān)控同上，現(xiàn)有metrics指標(biāo)[1]

數(shù)據(jù)連接池監(jiān)控

1. HikariCP 自帶

HikariCP 數(shù)據(jù)庫(kù)連接池開(kāi)啟prometheus監(jiān)控_hikaricp開(kāi)啟sql監(jiān)控_croder的博客-CSDN博客

可觀測(cè)性-Metrics-數(shù)據(jù)庫(kù)連接池HikariCP監(jiān)控_hikaricp 監(jiān)控_lakernote的博客-CSDN博客

HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl("jdbc:mysql://xxxxxxxx:3306/pegasus_data_demo_00?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=true&requireSSL=false");
        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);
        // 創(chuàng)建HikariDataSource
        HikariDataSource dataSource = new HikariDataSource(hikariConfig);
        // 設(shè)置metric注冊(cè)器 每10秒打印一次
        LoggingMeterRegistry loggingMeterRegistry = new LoggingMeterRegistry(new LoggingRegistryConfig() {
            @Override
            public String get(String key) {
                return null;
            }
            @Override
            public Duration step() {
                return Duration.ofSeconds(10);
            }
        }, Clock.SYSTEM);
        dataSource.setMetricRegistry(loggingMeterRegistry);

        // 測(cè)試 持有3秒連接后才釋放
        Connection connection = dataSource.getConnection();
        TimeUnit.SECONDS.sleep(1000);
        connection.close();

通過(guò)注入 Metricslogs 暴露詳細(xì) metrics[2]

BUT：作者不希望在連接池層面進(jìn)行日志的輸出，雖然是一點(diǎn)點(diǎn)的性能

SQL logging · Issue #57 · brettwooldridge/HikariCP

1. Druid

   自帶監(jiān)控，通過(guò)暴露相關(guān)的Metrics的數(shù)據(jù)進(jìn)行監(jiān)控

2. ……文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-668961.html

Metrics[1]

Metrics[2]

# HELP routed_sql_total Total count of routed SQL
# TYPE routed_sql_total counter
routed_sql_total{type="SELECT",} 28.0
routed_sql_total{type="INSERT",} 62.0
# HELP jdbc_statement_execute_total Total number of statement execute
# TYPE jdbc_statement_execute_total counter
jdbc_statement_execute_total{statement_type="prepared_statement",} 14.0
# HELP parsed_sql_total Total count of parsed SQL
# TYPE parsed_sql_total counter
parsed_sql_total{type="SELECT",} 28.0
parsed_sql_total{type="INSERT",} 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{le="1.0",} 0.0
jdbc_statement_execute_latency_millis_bucket{le="2.0",} 0.0
jdbc_statement_execute_latency_millis_bucket{le="4.0",} 0.0
jdbc_statement_execute_latency_millis_bucket{le="8.0",} 0.0
jdbc_statement_execute_latency_millis_bucket{le="16.0",} 0.0
jdbc_statement_execute_latency_millis_bucket{le="32.0",} 0.0
jdbc_statement_execute_latency_millis_bucket{le="64.0",} 2.0
jdbc_statement_execute_latency_millis_bucket{le="128.0",} 8.0
jdbc_statement_execute_latency_millis_bucket{le="256.0",} 12.0
jdbc_statement_execute_latency_millis_bucket{le="512.0",} 13.0
jdbc_statement_execute_latency_millis_bucket{le="1024.0",} 14.0
jdbc_statement_execute_latency_millis_bucket{le="2048.0",} 14.0
jdbc_statement_execute_latency_millis_bucket{le="4096.0",} 14.0
jdbc_statement_execute_latency_millis_bucket{le="+Inf",} 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{object="data_source",name="pegasus_data_demo_00",} 118.0
routed_result_total{object="data_source",name="pegasus_data_demo_01",} 56.0
routed_result_total{object="table",name="user_info_0000",} 28.0
routed_result_total{object="table",name="user_info_0001",} 90.0
routed_result_total{object="table",name="user_info_0002",} 28.0
routed_result_total{object="table",name="user_info_0003",} 28.0
# HELP build_info Build information
# TYPE build_info gauge
build_info{name="ShardingSphere",version="5.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_type="prepared_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{type="SELECT",} 1.691140931048E9
parsed_sql_created{type="INSERT",} 1.691141080971E9
# HELP routed_result_created Total count of routed result
# TYPE routed_result_created gauge
routed_result_created{object="data_source",name="pegasus_data_demo_00",} 1.691140931237E9
routed_result_created{object="data_source",name="pegasus_data_demo_01",} 1.691140931237E9
routed_result_created{object="table",name="user_info_0000",} 1.691140931237E9
routed_result_created{object="table",name="user_info_0001",} 1.691140931237E9
routed_result_created{object="table",name="user_info_0002",} 1.691140931237E9
routed_result_created{object="table",name="user_info_0003",} 1.691140931237E9
# HELP routed_sql_created Total count of routed SQL
# TYPE routed_sql_created gauge
routed_sql_created{type="SELECT",} 1.691140931227E9
routed_sql_created{type="INSERT",} 1.691141080974E9

到了這里，關(guān)于ShardingSphere 可觀測(cè) SQL 指標(biāo)監(jiān)控的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！