前言

該文章介紹Clicktail進(jìn)程拉取到MySQL慢日志文件數(shù)據(jù)，并寫(xiě)入到ClickHouse庫(kù)表后，通過(guò)ClickHouse語(yǔ)句分析MySQL慢查詢。

1 查看ClickHouse數(shù)據(jù)

1.1 環(huán)境信息

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 查看慢日志數(shù)據(jù)

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 查看慢日志明細(xì)

查看最近24小時(shí)，clickhouse001機(jī)器上數(shù)據(jù)庫(kù)上，執(zhí)行時(shí)間大于1秒的慢查詢語(yǔ)句。

SELECT
  _time AS "執(zhí)行完成時(shí)間",
  _date,
  query AS "SQL",
  normalized_query,
  hosted_on AS "主機(jī)名",
  replaceAll(replaceAll(client, '[', ''), ']', '') AS "客戶端",
  user AS "用戶名",
  query_time AS "執(zhí)行耗時(shí)（秒）",
  lock_time AS "鎖等待耗時(shí)（秒）",
  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 查看慢日志統(tǒng)計(jì)

查看最近24小時(shí)，clickhouse001機(jī)器上數(shù)據(jù)庫(kù)上，慢查詢統(tǒng)計(jì)語(yǔ)句，并按照總執(zhí)行時(shí)間降序排列。

SELECT
  normalized_query AS "SQL模板",
  hosted_on AS "主機(jī)名",
  user AS "用戶名",
  COUNT(1) AS "執(zhí)行次數(shù)",
  avg(query_time) AS "平均執(zhí)行時(shí)間（秒）",
  max(query_time) AS "最大執(zhí)行時(shí)間（秒）",
  (avg(query_time) * COUNT(1)) AS "總執(zhí)行時(shí)間（秒）",
  avg(lock_time) AS "平均鎖等待時(shí)間（秒）",
  max(lock_time) AS "最大鎖等待時(shí)間（秒）",
  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 查看慢日志統(tǒng)計(jì)

展示最近24小時(shí)，每分鐘慢查詢的執(zhí)行次數(shù)，最大執(zhí)行時(shí)間等信息，主要用于redash折線圖展示。

SELECT
  date_trunc('minute', _time) as time, --時(shí)間
  COUNT(1) as query_num,               --執(zhí)行次數(shù)
  sum(query_time) as query_time_sum,   --總執(zhí)行時(shí)間（秒）
  avg(query_time) as query_time_avg,   --平均執(zhí)行時(shí)間（秒）
  quantile(0.5)(query_time) AS query_time_avg_p50,  --平均執(zhí)行時(shí)間-50%
  quantile(0.95)(query_time) AS query_time_avg_p95, --平均執(zhí)行時(shí)間-95%
  quantile(0.99)(query_time) AS query_time_avg_p99, --平均執(zhí)行時(shí)間-99%
  stddevSamp(query_time) AS stddev,                 --均方差
  (query_time_avg * query_num) / (max(_time) - min(_time)) AS load,  --平均執(zhí)行時(shí)間-負(fù)載
  max(query_time) as query_time_max   --最大執(zhí)行時(shí)間
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/