背景
前几天晚上对主库上的两张大表进行分表工作，因为所分的新表也是在源库上，同库取同库写，比较担心高QPS会对实时业务和定时job有影响，所以一直在看zabbix性能图、show processlist查看在线进程、还要不断地拉取慢sql，看分表sql有没有出现堵塞的情况。
以上三个步骤可以拆开看：
zabbix整体性能：zabbix对整个数据库的监控已经非常全面的，各项重要指标均已监控，个别指标指数过高的话也会触发微信告警。所以zabbix整一块的东西还是比较放心的；
processlist进程：processlist主要是看有什么sql正在执行，目前我们也对processlist中running的线程进行了监控。若running数大于50，就触发微信告警，然后zabbix调用远程脚本，记录告警时正在跑的sql，方便事后查看。所以这块也比较放心；
慢sql分析：这个目前没有什么好办法，因为慢sql是一个文件，需要取值得话，只能对文件进行解析，还要加上所需要的时间区间，才能获取到想要慢sql日志。日常的慢sql分析工作也是去解析文件，较为繁琐；
综上，慢sql是目前数据库分析最为繁琐的一个环节，是否有好的方案让慢sql分析变得更为便捷呢？
方案设想
首先第一步，需要把存入慢sql日志文件的内容解析出来，然后为了日后的查询与统计，肯定需要把数据存入数据库中。
脉脉公司的方案是原始慢sql文件 -> flume interceptor解析日志及 sqltext -> hdfs ，然后通过hive去查询。
因为日志文件过多，查询效果不理想，然后再前面的基础上把数据从 hdfs 存到 clickhouse 中，通过 clickhouse 去查询。
综合网上开源软件和脉脉的方案，确定了以下最适合我们自己的方案：
mysql slow log -> clicktail -> clickhouse
clicktail: 开源的日志解析工具，clicktail比flume轻量化很多，易于配置和部署，还能直接接入clickhouse，https://github.com/Altinity/clicktail
方案实现
安装clickhouse
yum install yum-utils
rpm --import https://repo.yandex.ru/clickhouse/CLICKHOUSE-KEY.GPG
yum-config-manager --add-repo https://repo.yandex.ru/clickhouse/rpm/stable/x86_64
yum install clickhouse-server clickhouse-client
/etc/init.d/clickhouse-server start
clickhouse-client
如果clickhouse不是部署在本机上，需要打开clcikhouse的远程访问权限
vim etc/clickhouse-server/config.xml
<listen_host>0.0.0.0</listen_host> #改成0.0.0.0
安装clicktail
方法一
curl -s https://packagecloud.io/install/repositories/Altinity/clickhouse/script.rpm.sh | bash
yum install clicktail
方法二
go get github.com/Altinity/clicktail
GOPATH=/usr/local go get github.com/Altinity/clicktail
GOPATH=/usr/local go build
GOPATH=/usr/local go install
在clickhouse创建新库新表
cat schema/db.sql | clickhouse-client --multiline
cat schema/mysql.sql | clickhouse-client --multiline
使用clciktail
clicktail -p mysql -f mysql/log/slow.log -d clicktail.mysql_slow_log --api_host=http://172.16.150.223:8123/ --backfill
# --api_host ClickHouse server (default: http://localhost:8123/)
# --backfill 导入文件数据，如果需要把数据实施解析到clickhouse，不用加这个参数
查看数据库
clickhouse-client
ClickHouse client version 19.17.4.11 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 19.17.4 revision 54428.
localhost :) show databases;
SHOW DATABASES
┌─name──────┐
│ clicktail │
│ default │
│ system │
└───────────┘
3 rows in set. Elapsed: 0.004 sec.
localhost :) use clicktail
USE clicktail
Ok.
0 rows in set. Elapsed: 0.002 sec.
localhost :) show tables;
SHOW TABLES
┌─name────────────┐
│ mysql_audit_log │
│ mysql_slow_log │
└─────────────────┘
3 rows in set. Elapsed: 0.004 sec.
localhost :) select * from mysql_slow_log1 limit 2 \G
SELECT *
FROM mysql_slow_log1
LIMIT 2
Row 1:
──────
_time: 2019-05-30 09:25:29
_date: 2019-05-30
_ms: 222198
client: localhost []
query: SELECT CONCAT(table_schema, '/', table_name), engine FROM information_schema.tables WHERE engine NOT IN ( 'MyISAM', 'InnoDB', 'CSV', 'MRG_MYISAM' ) AND table_schema NOT IN ( 'performance_schema', 'information_schema', 'mysql' )
normalized_query: select concat(table_schema, ?, table_name), engine from information_schema.tables where engine not in ( ?, ?, ?, ? ) and table_schema not in ( ?, ?, ? )
query_time: 0.140509
user: root
statement:
tables:
schema:
rows_examined: 102
rows_sent: 0
lock_time: 0.03368
connection_id: 7495
error_num: 0
killed: 0
rows_affected: 0
database:
comments:
bytes_sent: 0
tmp_tables: 0
tmp_disk_tables: 0
tmp_table_sizes: 0
transaction_id:
query_cache_hit: 0
full_scan: 0
full_join: 0
tmp_table: 0
tmp_table_on_disk: 0
filesort: 0
filesort_on_disk: 0
merge_passes: 0
IO_r_ops: 0
IO_r_bytes: 0
IO_r_wait_sec: 0
rec_lock_wait_sec: 0
queue_wait_sec: 0
pages_distinct: 0
sl_rate_type:
sl_rate_limit: 0
hosted_on:
read_only: 0
replica_lag: 0
role:
按需统计
导入慢sql日之，就可以按照自己所需的维度去统计sql。
每日的慢sql量：
localhost :) select _date,count(_time) from mysql_slow_log where _date > '2019-10-31'and _date < '2019-11-28' group by _date;
SELECT
_date,
count(_time)
FROM mysql_slow_log
WHERE (_date > '2019-10-31') AND (_date < '2019-11-28')
GROUP BY _date
┌──────_date─┬─count(_time)─┐
│ 2019-11-01 │ 1100 │
│ 2019-11-02 │ 797 │
│ 2019-11-03 │ 829 │
│ 2019-11-04 │ 1090 │
│ 2019-11-05 │ 1108 │
│ 2019-11-06 │ 1223 │
│ 2019-11-07 │ 1093 │
│ 2019-11-08 │ 1046 │
│ 2019-11-09 │ 782 │
│ 2019-11-10 │ 776 │
│ 2019-11-11 │ 1046 │
│ 2019-11-12 │ 1083 │
│ 2019-11-13 │ 1221 │
│ 2019-11-14 │ 1078 │
│ 2019-11-15 │ 1115 │
│ 2019-11-16 │ 813 │
│ 2019-11-17 │ 806 │
│ 2019-11-18 │ 1140 │
│ 2019-11-19 │ 1132 │
│ 2019-11-20 │ 1263 │
│ 2019-11-21 │ 1159 │
│ 2019-11-22 │ 1396 │
│ 2019-11-23 │ 840 │
│ 2019-11-24 │ 819 │
│ 2019-11-25 │ 1257 │
│ 2019-11-26 │ 1240 │
│ 2019-11-27 │ 2982 │
└────────────┴──────────────┘
27 rows in set. Elapsed: 0.010 sec. Processed 31.35 thousand rows, 181.40 KB (3.08 million rows/s., 17.80 MB/s.)前一日各系统超过10秒的sql数量：
localhost :) select database,count(_time) from mysql_slow_log where _date='2019-11-27' and query_time>10 group by database;
SELECT
database,
count(_time)
FROM mysql_slow_log
WHERE (_date = '2019-11-27') AND (query_time > 10)
GROUP BY database
┌─database──┬─count(_time)─┐
│ │ 297 │
│ cXXX │ 4 │
│ basXXX │ 1 │
│ merXXX │ 105 │
│ accXXX │ 2 │
│ traXXX │ 28 │
│ marXXX │ 3 │
└───────────┴──────────────┘
7 rows in set. Elapsed: 0.009 sec. Processed 12.66 thousand rows, 58.01 KB (1.45 million rows/s., 6.65 MB/s.)
可以从很多维度去统计分析，而且再复杂的统计相应速度也非常快，非常方便。
方案扩展
除慢sql外，审计日志会记录数据库中的每个操作，是否也能将审计日志的数据存入列式数据库中？
审计日志分析好处
审计日志落库后，我们可以按我们自定义的条件取出数据，不需要再去单独解析审计日志，方便统计工作。
方便了使用后，监控审计日志还能做什么：
1. 高频sql解析
对于某些高qps的sql，我们可以通过提取出sql中的关键变量，并统计其分布情况来分析sql的访问热度。有这些结论支撑后，可以判断这些sql是否能通过缓存来减少qps并提高效率。
2. 业务新sql发现
新业务上线，新的 sql 是不会通知DBA的，所有如果有些不合理的 sql 刚上线的时候，运维人员也不知道。有监控的话，比较上线前后几天的 sql 及其变化情况，就可以知道新上了哪些 sql（因为有些sql可能执行需要一点几秒，没有被记录到慢sql的统计范围，所以分析慢sql无法发现这些问题）。
3. 可下线的表或字段
有些老表或字段可能没人知道他们是干什么的，到底还需不需要使用，这时候分析 clcikhouse 里的 sql 历史记录，筛选出没有读写操作的表，那这些表或者字段就是可以下线的。
4. 权限细分
审计日志 audit log，会记录sql的使用人及对应使用ip，所以可以把每一个业务所属的账号权限记录下来，形成独立的账号权限。这样可以安全地细各个账号专属的权限，有利于数据库安全。
审计日志注意点
审计功能看起来功能很强大，那为什么大部分公司都不会开启mysql审计功能呢。
原因就是审计功能会影响数据库的性能，每一次操作都要落盘写入audit log中，数据库最不希望见到的就是和磁盘io的交互，每次操作后都有额外的io开销，在一些数据库资源紧张的场景下审计日志确实难以让人接受。
到底要不要用，还是要看公司实际的业务场景。
