前言
該文章介紹Clicktail進(jìn)程拉取到MySQL慢日志文件數(shù)據(jù),并寫(xiě)入到ClickHouse庫(kù)表后,通過(guò)ClickHouse語(yǔ)句分析MySQL慢查詢。
1 查看ClickHouse數(shù)據(jù)
1.1 環(huán)境信息
| 主機(jī)名 | IP | 端口 | 庫(kù)表 |
|---|---|---|---|
| mysql001 | 192.168.6.114 | 3306 | |
| clickhouse001 | 192.168.6.8 | 8123 | clicktail.mysql_slow_log |
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) DESC1.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) DESC2 附錄
參考文章: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/
最后修改時(shí)間:2022-10-17 11:13:11
「喜歡這篇文章,您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容,轉(zhuǎn)載時(shí)必須標(biāo)注文章的來(lái)源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容,歡迎發(fā)送郵件至:contact@modb.pro進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),墨天輪將立刻刪除相關(guān)內(nèi)容。




