1、CloudDBA 智能诊断平台如何帮助客户定位和解决问题阿里云 ApsaraDB 内核服务 雷尚顺（西扬）AgendaMore than just cloud为什么需要 CloudDBA系统架构SQL 优化建议在线诊断离线诊断为什么需要CloudDBA?为客户节省成本More than just cloud客户将有限的资源集中在业务上为客户提供最好的技术数据库平台需要大量的时间去定位数据库问题成立优化小组专门解决性能问题为了解决性能问题单纯的扩充资源80%20%CloudDBA 系统架构More than just cloudKnowledgeBaseMySQLPostGreSQLApsara

2、DBKFK/JStormLog AgentErrorLogSlow LogAudit LogCPU/IOPS/Status/.Offline data repositoryOffline diagnosisTop SQLAnalysisTrxAnalysisSQLReviewDeadlockAnalysisSQLServerSQL AdvisorRule EngineOnline diagnosisRealtime Event&adviceSlaveDelayConfigTuningActiveSessionLock&TrxResourceInference EngineRuleStartco

3、nd1executorsAdviceGeneratorcond2实时诊断规则引擎More than just cloudRule Engine1.以最小代价及时的探测出有用的状态变化信息2.选择最佳推理模型1.实时状态变化探测2.数据库经验帮助探测过程快速收敛实时诊断知识库&推理引擎More than just cloud1.DBA专家知识的经验积累2.准确的问题定位以及有针对性的建议DBAExpertKnowledgeBaseInference EngineRuleStartexecutorsKnowledgeModeling toolmanagementinterfaceCustomers

4、 MySQL实时诊断系统资源More than just cloud系统状态活跃会话事务和锁其它实时诊断More than just cloud样例输出：离线分析审计日志在离线分析中至关重要More than just cloud1.全量记录数据库执行过的SQL2.AliSQL的一个功能，对数据库不会产生性能影响3.审计日志只有在得到客户授权的前提下才能分析4.分析自动化，无人工干预，不会人为的提取客户业务逻辑Top SQLAnalysisTrxAnalysisSQLReviewDeadlockAnalysis离线分析事务分析More than just cloudSQL review1.Un

5、committed transactions2.Long transactions3.Long interval between transaction SQLs4.Big transactions1.How many types of SQLs2.How many types of Transactions3.SQLs or sequence in transaction is expected or not4.Scan rows,return rows,elapsed time&SQL advice离线分析Top SQLsMore than just cloud1.找出Top SQLs是性

6、能优化的第一步2.TOP SQLs功能提供多维度的资源/SQL执行的性能消耗对比3.很好的解释客户经常提出的问题：“为什么我的应用逻辑没有改变，但是CPU打满了“SQL 优化建议More than just cloud帮助数据库优化器寻找最佳执行路径Query Rewriter What to doQuery Optimizer How to doParserSemanticscheckingQueryRewriteQueryOptimizerCompilerMore than just cloudselect name from(select name,actionfrom mytest t

7、 where t.user=hello)t where t.action=pushselect listSubqueryfrom listwhere listselect name,action from mytest t where t.user=helloselect listfrom listwhere listTable ScanProjectFilterProjectFilterProjectfrom mytest tselect name,actiont.user=helloselect name,actiont.action=pushselect name不同视角来看SQL语句P

8、arserSemanticscheckingQueryRewriteQueryOptimizerCompilerSQL 优化建议自动探测不能使用索引的条件More than just cloudParserSemanticscheckingQueryRewriteQueryOptimizerCompiler1.Like expression with leading wildcardsfirst_name LIKE concat(%,lei);2.Column as function argument UPPER(first_name)3.Implicit conversion due to

9、data type mismatcha=1234.Character set/collation mismatcht1.utf8_string=t2.utf8_bin_stringSQL 优化建议SQL Advisor基于最小代价规则创建索引More than just cloudParserSemanticscheckingQueryRewriteQueryOptimizerCompilerTable ScanFilterProjectTableIndex ScanFetchProject1.Selectivity2.Access method(nested loop only for MySQL)3.Join order4.Sort5.Covered index6.Rewritten Advice:select name from mytest where user=hello and action=pushIndex Advice:ALTER TABLE mytest ADD INDEX rds_idx_1(user,action)SQL AdvisorMore than just cloudExample