ClickHouse 实战笔记 第02期：ClickTail+CH 实现 MySQL 慢查询实时展示

马听，多年 DBA 实战经验，对 MySQL、 Redis、ClickHouse 等数据库有一定了解，专栏《一线数据库工程师带你深入理解 MySQL》、《Redis 运维实战》作者。

1 安装 MySQL

2 安装 ClickHouse

3 创建慢查询表

clickhouse-client

CREATE DATABASE IF NOT EXISTS clicktail;

CREATE TABLE IF NOT EXISTS clicktail.mysql_slow_log
(
`_time` DateTime,
`_date` Date default toDate(`_time`),
`_ms` UInt32,
client String,
query String,
normalized_query String,
query_time Float32,
user String,
statement String,
tables String,
schema String,
rows_examined UInt32,
rows_sent UInt32,
lock_time Float32,
connection_id UInt32,
error_num UInt32,
killed UInt16,
rows_affected UInt32,
database String,
comments String,
bytes_sent UInt32,
tmp_tables UInt8,
tmp_disk_tables UInt8,
tmp_table_sizes UInt32,
transaction_id String,
query_cache_hit UInt8,
full_scan UInt8,
full_join UInt8,
tmp_table UInt8,
tmp_table_on_disk UInt8,
filesort UInt8,
filesort_on_disk UInt8,
merge_passes UInt32,
IO_r_ops UInt32,
IO_r_bytes UInt32,
IO_r_wait_sec Float32,
rec_lock_wait_sec Float32,
queue_wait_sec Float32,
pages_distinct UInt32,
sl_rate_type String,
sl_rate_limit UInt16,
hosted_on String,
read_only UInt8,
replica_lag UInt64,
role String
) ENGINE = MergeTree(`_date`, (`_time`, query), 8192);

cat ./mysql_slow_log.sql | clickhouse-client --multiline

4 配置 ClickTail

vim etc/clicktail/clicktail.conf

[Application Options]
APIHost = http://localhost:8123/
[Required Options]
ParserName = mysql
LogFiles = usr/local/mysql/data/mysql-slow.log
Dataset = clicktail.mysql_slow_log
[MySQL Parser Options]
Host = localhost:3306
User = clicktail_r
Pass = IJNbgt666

create user 'clicktail_r'@'localhost' identified with mysql_native_password by 'IJNbgt666';
GRANT SELECT ON *.* TO 'clicktail_r'@'localhost';

5 启动 ClickTail

service clicktail start

6 慢日志分析

6.1 显示最慢的 10 条 SQL

select _time, query, round(query_time, 4) AS latency from mysql_slow_log where query != 'COMMIT' order by query_time DESC LIMIT 10;

6.2 显示锁时间最长的 10 条 SQL

select query,round(query_time, 4) AS latency, round(lock_time, 6) AS lock_time from mysql_slow_log where query != 'COMMIT' and lock_time >0 order by lock_time desc limit 10;

6.3 根据平均耗时查询

select normalized_query, count(*) AS count, round(avg(query_time), 4) AS latency_avg, round(quantile(0.75)(query_time), 4) AS latency_p75, round(quantile(0.99)(query_time), 4) AS latency_p99, round((latency_avg * count)  (max(_time) - min(_time)), 4) AS load from mysql_slow_log where query != 'COMMIT' GROUP BY normalized_query HAVING count > 1 ORDER BY load DESC limit 10;

LIKECOLUMN

悦专栏

在这里，学好编程

做更优秀的 IT人！