博客

使用 grafana 监控 sql 执行耗时与次数 数据存储在 influxDB 中

效果

grafana下载



influx db 下载

influx_db 建库

创建 sqlp 数据库

create database sqlp;

添加数据保留策略 这里设置为1天

 create retention policy "1d" on "sqlp"  duration 24h replication 1 DEFAULT ;

java 上报数据

使用 influxdb-java

<dependency>
			<groupId>org.influxdb</groupId>
			<artifactId>influxdb-java</artifactId>
			<version>2.22</version>
</dependency>

数据保存时用表名作为 measurement

并添加一个 sql_type 的 tag (select, insert, update, select count)

可以用 druid filter 统计耗时

@Slf4j
public class MyDruidFilter extends FilterEventAdapter {
    
    // 覆盖执行方法
    @Override
    public boolean preparedStatement_execute(FilterChain chain, PreparedStatementProxy statement) throws SQLException {
        long start = System.currentTimeMillis();
        try {
            this.statementExecuteBefore(statement, statement.getSql());
            boolean firstResult = chain.preparedStatement_execute(statement);
            this.statementExecuteAfter(statement, statement.getSql(), firstResult);
            return firstResult;
        } catch (SQLException var4) {
            this.statement_executeErrorAfter(statement, statement.getSql(), var4);
            throw var4;
        } catch (RuntimeException var5) {
            this.statement_executeErrorAfter(statement, statement.getSql(), var5);
            throw var5;
        } catch (Error var6) {
            this.statement_executeErrorAfter(statement, statement.getSql(), var6);
            throw var6;
        } finally {
            long consumeTime = System.currentTimeMillis() - start;
            SqlMonitor.monitorTime(statement.getSql().toString(), "b_file", consumeTime);
        }
    }
}

monitor time 方法

public static void monitorTime(String sql, String table, Long consumeTimeInMs) {
        sql = sql.replaceAll("\n", "");
        String tag = "default";
        if (sql.startsWith("SELECT COUNT")) {
            tag = "count";
        } else if (sql.startsWith("SELECT")) {
            tag = "select";
        } else if (sql.startsWith("INSERT")) {
            tag = "insert";
        } else if (sql.startsWith("UPDATE")) {
            tag = "update";
        }
        Point point = Point.measurement(table)
            .tag("sql_type", tag)
            .addField("consume_time", consumeTimeInMs)
            .addField("sql_count", 1)
            .build();
        InfluxDBUtil.write(INFLUX_DB, point);
    }

grafana 配置

添加 influxdb 数据源

url 设置为 http://localhost:8086

database 设置为 sqlp

添加面板

执行时间配置如下

执行次数

这里对 sql_count 作 sum 聚合 并 group by 1s 统计出来一共就是 sql 执行 的 qps