yhzhu的随手记
0%

Sharding-JDBC(ShardingSphere)SQL监控实践

发表于

最近我们进行了一个项目的重构，其中一个重要目标是减少数据库访问的负载。由于核心数据库被多个应用程序访问，并且数据库服务器只能对整体数据库流量进行监控，我们需要在应用程序端监控不同应用程序的SQL请求，并将其与现有的指标集成在一起。

需求分析

我们的需求是监控应用程序对数据库的SQL执行情况，并将其与现有的指标集成。我们希望监控以下指标（但不限于）：

  • SQL语句的执行时间
  • SQL语句的执行次数
  • SQL语句的执行结果
  • SQL语句的主库和备库路由

方案设计

我们现有数据库相关技术栈包括MySql, Sharding-JDBC,HikariDataSource和MyBaties；在方案设计阶段，我们考虑了以下三种方案：

ShardingSphere的metrics

ShardingSphere的agent供了SQL监控的功能，具体可参考ShardingSphere的可观测性然而，该代理会暴露一个独立的监控指标页面，无法直接集成到我们现有的监控指标页面中。我们需要单独采集ShardingSphere代理的监控指标，并将其与现有指标进行整合，这增加了一定的复杂性，因此我们放弃了该方案。

Mybaties的拦截器

使用MyBatis的拦截器实现SQL监控非常简单，只需实现org.apache.ibatis.plugin.Interceptor接口即可。以下是一个示例代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@Intercepts({
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
    @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})})
public class SqlMetricsInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
        /** 上报sql metrics**/
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    @Override
    public void setProperties(Properties properties) {
    }
}

在容器启动后，我们将该拦截器注入到MyBatis的org.apache.ibatis.session.SqlSessionFactory中：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
 @Autowired
private ObjectProvider<Interceptor> interceptorsProvider;

@Autowired
private ObjectProvider<SqlSessionFactory> sqlSessionFactories;

@Override
public void afterPropertiesSet() throws Exception {
    for (SqlSessionFactory factory : sqlSessionFactories) {
        for (Interceptor interceptor : interceptorsProvider) {
            factory.getConfiguration().addInterceptor(interceptor);
        }
    }
}

通过以上步骤，我们可以在现有的监控指标页面中看到采集到的与SQL相关的监控指标。但是，该方案无法区分主库和备库的SQL。

JDBC Statement

我们知道，最终的SQL执行是通过JDBC的Statement完成的，因此我们可以直接在JDBC的Statement执行SQL的地方上报监控指标。我们可以自定义Statement来执行SQL，并同时上报SQL的监控指标。以下是类关系图示例：

在DalStatement和PreparedStatement 重载executeQuery和executeUpdate 方法增加相关监控指标，具体代码可参考ydal-spring-boot-starter

这样可以输出类似如下的metrics上报到prometheus

1
2
3
4
5
6
sql_execute_time_seconds_count{route="slave",datasource="order",type="select",} 82.0
sql_execute_time_seconds_sum{route="slave",datasource="order",type="select",} 12.373654891
sql_execute_time_seconds_count{route="master",datasource="order",type="select",} 14.0
sql_execute_time_seconds_sum{route="master",datasource="order",type="select",} 167.934782891
sql_execute_time_seconds_count{route="master",datasource="order",type="update",} 1.0
sql_execute_time_seconds_sum{route="master",datasource="order",type="update",} 0.493257718

通过这种方式，我们可以在现有的监控指标页面中查看采集到的SQL监控指标，并区分主库和备库的SQL。 详细代码：https://github.com/yinghuzhu/ydal-spring-boot-starter

总结

通过本次SQL监控的实践，我们成功地集成了SQL监控功能到我们的应用程序中，并将其与现有的指标集成在一起。这使得我们能够更好地了解应用程序对数据库的访问情况，并及时发现潜在的性能问题和瓶颈。我们选择了自定义的JDBC Statement来实现SQL监控，并通过上报监控指标的方式将其集成到现有的监控系统中。这种方案简单且有效，使我们能够对SQL执行进行精确监控，并根据需要进行进一步的优化和调整。尽管我们在本次实践中取得了成功，但我们也意识到SQL监控仅仅是性能优化和数据库访问管理的一部分。在未来的项目中，我们将继续探索更多的数据库访问优化技术，并进一步提高应用程序的性能和可伸缩性。