ClickHouse分析MySQL慢查询

前言

该文章介绍Clicktail进程拉取到MySQL慢日志文件数据，并写入到ClickHouse库表后，通过ClickHouse语句分析MySQL慢查询。

1 查看ClickHouse数据

1.1 环境信息

1.2 登录ClickHouse

[ root@clickhouse001:~ ]# clickhouse-client --port=9000 --multiline
ClickHouse client version 21.12.3.32 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.3 revision 54452.

clickhouse001 :) 

1.3 查看慢日志数据

clickhouse001 :) select count(*) from clicktail.mysql_slow_log;

SELECT count(*)
FROM clicktail.mysql_slow_log

Query id: 173bb6dc-e8df-4349-9274-ebfb3df23370

┌────count()─────┐
│      2239476260         │
└─────────── ─┘

1 rows in set. Elapsed: 0.010 sec.

1.3 查看慢日志明细

查看最近24小时，clickhouse001机器上数据库上，执行时间大于1秒的慢查询语句。

SELECT
  _time AS "执行完成时间",
  _date,
  query AS "SQL",
  normalized_query,
  hosted_on AS "主机名",
  replaceAll(replaceAll(client, '[', ''), ']', '') AS "客户端",
  user AS "用户名",
  query_time AS "执行耗时（秒）",
  lock_time AS "锁等待耗时（秒）",
  rows_examined AS "扫描行",
  rows_sent AS "返回行"
FROM
  clicktail.mysql_slow_log
WHERE
  hosted_on = 'clickhouse001'
  AND _time > (now() - 86400)
  AND user != 'zabbix_monitor'
  AND query_time >= 1;

1.4 查看慢日志统计

查看最近24小时，clickhouse001机器上数据库上，慢查询统计语句，并按照总执行时间降序排列。

SELECT
  normalized_query AS "SQL模板",
  hosted_on AS "主机名",
  user AS "用户名",
  COUNT(1) AS "执行次数",
  avg(query_time) AS "平均执行时间（秒）",
  max(query_time) AS "最大执行时间（秒）",
  (avg(query_time) * COUNT(1)) AS "总执行时间（秒）",
  avg(lock_time) AS "平均锁等待时间（秒）",
  max(lock_time) AS "最大锁等待时间（秒）",
  avg(rows_examined) AS "平均扫描行",
  max(rows_examined) AS "最大扫描行",
  avg(rows_sent) AS "平均返回行",
  max(rows_sent) AS "最大返回行"
FROM
  clicktail.mysql_slow_log
WHERE
  hosted_on = 'clickhouse001'
  AND _time > (now() - 86400)
  AND user != 'zabbix_monitor'
GROUP BY
  normalized_query,
  hosted_on,
  user
ORDER BY
  max(query_time) DESC

1.5 查看慢日志统计

展示最近24小时，每分钟慢查询的执行次数，最大执行时间等信息，主要用于redash折线图展示。

SELECT
  date_trunc('minute', _time) as time, --时间
  COUNT(1) as query_num,               --执行次数
  sum(query_time) as query_time_sum,   --总执行时间（秒）
  avg(query_time) as query_time_avg,   --平均执行时间（秒）
  quantile(0.5)(query_time) AS query_time_avg_p50,  --平均执行时间-50%
  quantile(0.95)(query_time) AS query_time_avg_p95, --平均执行时间-95%
  quantile(0.99)(query_time) AS query_time_avg_p99, --平均执行时间-99%
  stddevSamp(query_time) AS stddev,                 --均方差
  (query_time_avg * query_num) / (max(_time) - min(_time)) AS load,  --平均执行时间-负载
  max(query_time) as query_time_max   --最大执行时间
FROM
  clicktail.mysql_slow_log
WHERE
  hosted_on = 'clickhouse001'
  AND _time > (now() - 86400)
  AND user != 'zabbix_monitor'
GROUP BY
  date_trunc('minute', _time)
ORDER BY
  date_trunc('minute', _time) DESC

2 附录

参考文章：https://www.percona.com/blog/2018/02/28/analyze-raw-mysql-query-logs-clickhouse/

参考文章：https://www.percona.com/blog/2018/04/18/why-analyze-raw-mysql-query-logs/