問(wèn)題
CM登錄頁(yè)面加載不出或者非常緩慢
現(xiàn)象
查看阿里云冤荆，發(fā)現(xiàn)對(duì)應(yīng)rds負(fù)載很高玷过，對(duì)應(yīng)的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
相關(guān)的數(shù)據(jù)就表是cm中的AUDITS表發(fā)現(xiàn)該表數(shù)據(jù)量比較大人乓，上億笋颤。
第二行audits表.png
解決方法
暫不考慮重啟service cloudera-scm-server restart乳附。
- 查看scm的服務(wù)器日志，沒(méi)有收獲
tail -f /var/log/cloudera-scm-server/cloudera-scm-server.log - 進(jìn)入數(shù)據(jù)庫(kù)伴澄，該表是cm庫(kù)下的赋除。查看表結(jié)構(gòu)，發(fā)現(xiàn)沒(méi)有索引非凌。該表作用是
審核（Audits）- 查詢(xún)或過(guò)濾查詢(xún)?cè)诩荷系牟僮魇录倥缬脩?hù)登錄
https://blog.csdn.net/z644041867/article/details/83011583
所以可以考慮清理數(shù)據(jù)
- 備份數(shù)據(jù)（只備份該表）
mysqldump -hhost -uuser -ppassword <庫(kù)名> [表1,表2...] > xxx.sql - 刪除數(shù)據(jù)
truncate table cm.audits; - 添加聯(lián)合索引
ALTER TABLE AUDITS ADD INDEX idx_audit (ACTING_USER_ID, AUDIT_TYPE, ALLOWED);
Done.
