问题
CM登录页面加载不出或者非常缓慢
现象
查看阿里云，发现对应rds负载很高，对应的SQL为
select dbaudit0_.AUDIT_ID as AUDIT1_0_, dbaudit0_.OPTIMISTIC_LOCK_VERSION as OPTIMIST2_0_, dbaudit0_.ACTING_USER_ID as ACTING3_0_, dbaudit0_.AUDIT_TYPE as AUDIT4_0_, dbaudit0_.CLUSTER_ID as CLUSTER5_0_, dbaudit0_.SERVICE_ID as SERVICE6_0_, dbaudit0_.ROLE_ID as ROLE7_0_, dbaudit0_.COMMAND_ID as COMMAND8_0_, dbaudit0_.USER_ID as USER9_0_, dbaudit0_.HOST_ID as HOST10_0_, dbaudit0_.HOST_TEMPLATE_ID as HOST11_0_, dbaudit0_.CONFIG_CONTAINER_ID as CONFIG12_0_, dbaudit0_.EXTERNAL_ACCOUNT_ID as EXTERNA13_0_, dbaudit0_.CREATED_INSTANT as CREATED14_0_, dbaudit0_.MESSAGE as MESSAGE15_0_, dbaudit0_.IP_ADDRESS as IP16_0_, dbaudit0_.ALLOWED as ALLOWED17_0_
from AUDITS dbaudit0_
where dbaudit0_.ACTING_USER_ID=13 and dbaudit0_.AUDIT_TYPE='AUTHENTICATION' and dbaudit0_.ALLOWED=1
order by dbaudit0_.CREATED_INSTANT DESC limit 2
相关的数据就表是cm中的AUDITS表发现该表数据量比较大，上亿。
第二行audits表.png
解决方法
暂不考虑重启service cloudera-scm-server restart。
- 查看scm的服务器日志，没有收获
tail -f /var/log/cloudera-scm-server/cloudera-scm-server.log - 进入数据库，该表是cm库下的。查看表结构，发现没有索引。该表作用是
审核（Audits）- 查询或过滤查询在集群上的操作事件，例如用户登录
https://blog.csdn.net/z644041867/article/details/83011583
所以可以考虑清理数据
- 备份数据（只备份该表）
mysqldump -hhost -uuser -ppassword <库名> [表1,表2...] > xxx.sql - 删除数据
truncate table cm.audits; - 添加联合索引
ALTER TABLE AUDITS ADD INDEX idx_audit (ACTING_USER_ID, AUDIT_TYPE, ALLOWED);
Done.
