clickhouse分析mysql慢查询 -m6米乐安卓版下载

前言

该文章介绍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/