1.系统性能问题的五大特征2.系统性能故障排查策略3.MySQL开发规范及常用调优策略4.MySQL性能管控系统5.未来展望1.系统性能问题的五个特征如果你了解一些方法论，你应该听说过两条原则：一是海恩定律，强调量变导致质变；二是海恩定律。另一个是陈词滥调的墨菲定律，它强调可能出错的事情总会出错。基于这两个原则，我总结了系统性能问题的五个特点。1）系统响应慢无论负载情况如何，系统应用总是极其缓慢，响应时间长。2）时间序列越来越慢，负载稳定，但是系统随着时间的推移越来越慢。达到一定阈值后，系统可能会因大量错误而被锁定或崩溃。3）突发混乱系统运行稳定，某一时刻突然出现大量错误。4）部分功能异常。用户访问某些页面异常。上图右下角的图片是F12访问Google页面的截图。从中可以看出，我们访问谷歌已经超时，无法访问。5）随着负载变化越来越慢，当用户数量增加时，系统速度明显变慢。用户离开系统后，系统恢复到原来的状态。上图左下角的图为CPU占用率，从100%负载恢复正常，然后随着用户数的增加逐渐增加到100%负载。2.系统性能调查策略1.系统性能调查策略方法论系统性能调查策略可归纳为以下两点：1)积极沟通减少影响使用5W1H原则了解问题现象，即什么是问题问题、时间和地点、如何发生以及由谁处理。同时，需要收集现场信息，包括常见的日志信息、流量信息等，并尽量进行全面排查。安抚客户，减少客户影响。由于客户的恐慌性增长，小事件可能导致大事故。基于历史经验的紧急情况。2）大胆推敲，合理举证根据异常信息，要大胆推论，合理举证，避免“我推断是这样，但我不会证明”；进行全链路考虑，避免单点猜测，比如直接判断是数据库有问题，但分析发现其实不是数据库负载问题，而是网络问题或者中间件问题；解决问题必须包括临时解决方案和最终解决方案。使用临时解决方案以最快的方式消除影响，然后对问题做出最终解决方案，避免以后出现类似问题造成的隐患。为此，我通过鱼骨图进一步描述排查问题的方法：1）要消除影响，首先要消除对客户的影响，其次要消除对系统的影响。可以利用历史经验帮助客户或系统避免紧急响应或其他方法。问题。2）采集场景这一步强调日志的完整性。同时，我们需要知道问题发生时的问题数据和系统数据，以便数据重现。3）明确问题范围，判断是个别交易问题还是普遍问题。如果是个别交易问题，我们可以快速定位到当时交易发生了哪些变化；如果是一般性的问题，我们需要判断影响的是哪些客户和客流，这个问题是否会影响到其他方面。4）问题分析问题分析包括两个方面。一是系统级链路分析，从最早的端到端链路进行统一排查；数据库返回并分析整个事务级链接。5）问题解决方案经过前面的一系列步骤，我们终于可以制定出问题的解决方案了。在制定解决方案时，一般会进行数据修复和程序修复，同步验证环境，将修改的部分合并到后续版本中，避免类似问题再次发生。6）问题总结这一步主要是回顾问题，从中寻找优化点，总结问题处理方法的经验教训，然后进行一些横向调研，积累相关经验。下面给大家说说性能问题的排查，包括两个方面：系统环境和运行环境。1）系统环境我们原则上是通过APM工具对系统环境进行监控。业界已经有一些不错的开源监控工具，比如Prometheus、Zabbix等，可以用来监控CPU负载、IO负载、内存负载、网络负载。2）运行环境运行环境的问题大致可以分为以下三类：①数据库日志信息对于MySQL，首先查看它的错误日志，直接通过mysql.err查看当时是什么问题；检查SQL日志（一般是slow-queries.log），原则上超过10秒的事务会反映在这里；然后查看事务日志，通过binlog查看当时的事务状态。如果备库有一些问题重复出现，可以查看主备relaylog，通过relaylog查看备库replay的状态。它类似于甲骨文。可以通过监听日志listener.log和lsnrctlstatus来查看监听器的状态。Oracle中有报警日志，可以通过alert.log查看当时发生的事件。还可以进一步开发AWR报表和ASH报表来监控数据库，这点不如MySQLforOracle。此外，Oracle还提供了一些历史快照信息表，如dba_hist_sqlstat和dba_hist_snapshot，通过这些表你可以获取你需要的任何快照时间的处理信息。最后，通过session信息，可以查看当前session中访问了哪些中间件，以及整个session的状态。性能分析在进行性能分析时，我们可以查看执行计划。对于MySQL，我们可以通过explain语句查看当时的执行计划，是否有索引，索引是否运行良好。对于Oracle，我们可以通过v$sql_plan和dba_hist_sql_plan查看执行计划变化的原因，为执行计划重建索引。此外，我们还需要分析死锁和处理等待事件。②中间件对于中间件，如WAS、Liberty、Tomcat、国内东方通等业界应用比较广泛的中间件，我们可以查看它的一些线程信息。建议大家打3~5个javacores，一般是一分钟打一个，这样可以使用IBM的jca4611.jar工具来对比分析是哪个线程导致的问题，或者是什么情况下线程卡住了。如果涉及OOM（outofmemory），可以打印heapdump信息，然后用IBM的ha457.jar工具分析。我们可以通过GC信息来查看系统是否会因为服务器的fullGC而继续崩溃。如果是这样，我们可以调整vm信息。另外，中间件还会打印一些日志信息，从中可以发现当时出现的问题。最后可以监控一些中间件的资源信息，包括数据库连接池，线程池，还有一些web容器。③如果应用程序发现数据库和中间件都没有问题，再看应用程序。对于前台，看看是不是因为它在前台缓存，没有实时刷新，所以新的请求得到旧的事务，最终出问题。此外，您还可以查看请求的连接数。浏览器中请求的连接数实际上是有限的。请求的连接太多也会导致应用程序出现问题。最后，您可以检查网络传输量大是否是资源过多导致的。这个问题可以通过两种方式解决，一种是资源压缩，一种是将资源部署到CDN上。对于逻辑层，我们可以看是否释放了资源，包括数据库连接、文件读写、sockets、缓存等。然后可以看事务问题，比如事务很久没有结束，这会导致很多线程信息被卡住，循环数据库也会导致事务持续很长时间。最后可以看多线程信息有没有锁等待，有没有数据污染。总结起来，系统性能排查有四个要点：查看完整日志、使用好工具、执行计划、关注逻辑问题。接下来对java中间件和数据库性能两部分进行详细分析：二、java中间件分析1）通过javacore的jca分析，对比了4个javacore文件，发现大部分问题都集中在获取不到connectionpool，也就是连接池都已经被占用，很久没有释放了。这时候可以根据连接池的情况快速定位问题。2）分析oom对象对于oom对象，从上图可以看出，在BankFunctionTypePool中，oom存储了大约1G的空间，也就是说jvm内存已经直接耗尽了。这种情况一般建议结合使用heapdump和javacore进行分析，这样可以快速定位问题。3、数据库相关问题分析对于数据库问题，有以下分析过程。一般出现问题场景后，先通过日志分析判断是否连接不上数据库。如果无法连接数据库，请检查监听状态。如果是Oracle，listener.log没有status日志记录，可以查看lsnrctlstatus，然后配置TNS，启动listener，保证正常访问数据库。如果是MySQL，可以查看mysql.err文件，发现有accessdenied错误。这种情况下，我们必须授权访问并确认防火墙，才能正常访问数据库。如果可以连接数据库，但是数据库的执行时间太长，这种情况应该用下面的方法来解决。如果是Oracle，可以打印问题发生时的AWR报告，定位问题语句（一般关注Logons、Top5events、SQLorderbyElapsedtime等），然后处理问题。进一步排查，可以打印ASH报告，查看历史同期引入问题的变化，从而快速定位一些问题。如果是MySQL，一般先查看mysql.err的错误日志，再查看slow-queries.log。如果需要进一步调查，可以提取performance_schema.events_statements_summary_by_digest表中的数据进行进一步调查。一般来说，数据库相关的问题可以分为以下四种：1）如果出现死锁，需要调整业务逻辑的顺序，进行压力测试，然后验证结束。2）如果没有死锁，只是执行计划有问题，比如全表扫描，在上面加上适当的索引处理。3）如果有指标，需要判断其区分度：如果区分度高，数据变化频繁，需要更新统计信息；如果区分度低，则判断索引是否合适，如果不合适，则重建索引，并选择合适的索引进行处理。4）最后，还要看数据量的大小。如果超过了规范的阈值，就要分库分表分分区策略。调整逻辑后，我们将进行相关的压力测试。当压测满意的时候，验证就完成了，我们就真正的去生产加工了。三、MySQL调优策略1、索引1）一般建议大家查看执行计划。从我目前的分析来看，语句问题占90%以上；2）命中指标不代表ok；3）执行计划至少要达到rangeScanning，一般建议达到reflevel。对于MySQL的执行计划，有id、select_type、table等列，其中我一般关注表中的type，表示访问类型，决定了MySQL在表中查找所需行的方式.我在上图右侧列出了效率：system(nodiskIOrequired)>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL接下来查看key和key_len值而使用索引的字节长度越短越好。可以根据表定义粗略计算出索引的最大可能长度，供复合索引的实际字段使用。检查行后，一般建议行值越小越好。其他如filtered、Extra等也是关键信息，这里不再赘述，大家可以参考上图中的表格。2、分库分表对于分库分表，首先要注意一个问题，单表有多少数据需要分库？阿里手册上写到数据量达到500万要分库分表。据业界介绍，数据量达到2000万条，分为数据库和表。问题的根源是，一位百度DBA做了压力测试后，觉得压到2000万没问题，但超过2000万就会出现性能问题。因此，行业流通的数据量上限为2000万。对于工商银行，MySQL规范建议数据量达到3000万条分库分表。MySQL索引有两种，一种是聚簇索引，即主键索引，索引和数据放在一起。另一个是secondaryIndex，也就是辅助索引。下面简单介绍一些基础知识：MySQL表数据是以页的形式存储的，默认是16k，innodb_page_size的值为16384，除以1024正好是16k。一般索引是B+树，叶子存放数据，非叶子存放主键和指向页码，一般12字节，因为使用bigint会占用8字节，源码在lot0types。h有一个指针FIL_PAGE_OFFSET，占4个字节。所以非叶存储存储大约12个字节。只有15k左右的数据页数据可以存储数据，因为页眉、页目录和页脚也会占用1k的空间。B+树的扇出率很高，15k除以12byte，每个节点可以指向1280片叶子。B+树一般推荐层数为2~4层。保证搜索某个key值，最多2~4个IO就够了。主键索引一般在3级左右。这里还涉及到一个iops的知识，因为你以前用的是机械硬盘，一般一个io操作需要0.01秒，但是现在常用的SSD都是几万个ops，mysql的访问效率比以前高很多。针对以上基础知识，做如下具体说明：数据量=扇出值^(B+树层数-1)*叶节点存储行数比如我们行的行大小约为850Byte字节，而每个叶子节点可以存储18行，数据量约为2900万条，这也是3000万条分库分表边界的来源。百度的行大小是1K，每个叶子节点存储15行数据，数据量大约2400万条，所以行业只有2000万条。阿里也是一样，经过计算强调分库分表数据量超过500万。我们需要理解规范数字背后的含义，这样很多问题就会迎刃而解。此外，服务器配置、数据库版本等因素也会影响查询速度。3、锁问题MySQL官方对锁有比较详细的介绍，常见的有读锁和写锁。读锁包括两种类型的锁：记录锁和间隙锁。我们的ICBC使用READ-COMMITTED来避免间隙锁。通过mysql.err查看日志性能，可以看到有lock_modeX和locksrec但没有gap，这就是recordlock的意思。这里需要注意以下两点：1）锁竞争在5.7版本中，我们从locks和locks_waits表中查看锁，但是8.0版本从infomation_spchema移动到了performance_schema。我们举个例子来说明。事务1是一个start事务，更新同样id=1的值，事务也更新它。50秒后会抛出1205错误，直接显示锁等待超时。我们建议锁等待的超时时间为5-10秒，以免对事务造成较大影响。2）死锁检测死锁检测的本质是一个哲学家问题：两个或多个事务，双方都在等待对方释放自己已经占有的资源，最后造成等待循环，形成死锁。MySQL的实现机制可以看lock0lock.cc，本质上实现了一个深度优先的机制。如果发现循环，则认为是死锁，回滚具有少量undolog的事务。查看mysql.err可以发现第一步检测到死锁，此时事务1会等待另一个记录锁被释放，事务2也会等待事务1的记录锁被释放释放了，最后因为事务2回滚变小了，所以事务2被回滚了。4.GoogleTrends&DB-EnginesMySQL和PostgreSQL都是不错的数据库，但是对于我们国家来说，MySQL在GoogleTrends上更受欢迎，约占189%，PostgreSQL约占11%。当我们搜索关键词时，如何编译MySQL是最受欢迎的，这说明我国对掌握和编译源代码的需求比较迫切。从DB-EnginesRank可以看出，MySQL和Oracle已经旗鼓相当，而PostgreSQL的热度也在逐渐攀升。4.MySQL绩效管控系统下面给大家分享一下我行的绩效管控系统。“免费的午餐不好。”随着MySQL的广泛使用，人们对开发规范的不重视，将导致慢SQL的数量呈爆发式增长。单条慢SQL会导致服务不可用，降低用户幸福指数。为此，我们构建了一个控制系统，以确保开发合规性和性能控制。1.绩效管控体系1）R&Dpipeline（DevOps）+QA定期检查（线下）首先，我们通过R&Dpipeline（DevOos）和QA定期检查来处理整个R&D流程。具体可以分为以下几个环节：设计环节在设计环节，我们建立了设计指南，做了一些元数据管理，开设了能力提升课程，提高大家使用数据库的能力。我们也会推广一些表结构设计工具和元数据管理系统，限制大家处理问题的情况。同时，我们在这个环节设置了权限控制。在开发阶段，我们将一些规范自动化，包括SQL注入检查和SQL编写规则。SonarQube有一个SonarLint插件，可以做服务端同步，也有利于开发过程中的性能控制。在测试阶段，我们通过安全测试、性能测试、混沌测试进行性能控制。发布环节在发布环节，我们的SRE会发布一些态势感知报告，从技术和安全等方面对业务提出有针对性的建议和后续整改措施。运营环节在这个环节，我们会先对慢SQL进行监控和管理，逐步减少大事务数据；可以看到上图中某部门有2个应用，慢SQL个数为12条，最大耗时246秒，平均耗时11.414秒。其次，我们将进行生产案例分析，将相关规则存入知识库，将技术组件放入技术模型。另外，我们也会做一些AIOps根因分析。最后，我们会监控杀掉一些慢SQL，提前杀掉大事务，避免它们对系统造成影响。2）性能运维事件响应和溯源我们会对每一个问题进行反思和溯源，看看哪个环节有问题，哪个环节可以优化。比如判断：语句是否存在未定义的时间范围而丢失？设计功能是否考虑到大表关联的设计缺陷？开发过程中是否存在代码缺陷？检查完开发环节后，我们会检查测试环节是否存在测试用例缺失、测试工具报告缺失等缺陷，最后检查发布环节是否存在发布标准等缺陷。3）能力沉淀最后进行能力沉淀，比如问题的闭环跟踪、横向根因的排查，最终沉淀到知识库、技术组件、度量模型。2.MySQL开发规范1)设计原则在设计上，我们有以下三个原则：①重用原则在系统架构中，应考虑使用同一套数据结构来存储具有相同或相似功能的信息。例如：通用参数表、通用字典表。②前瞻性原则设计应基于完整的产品定义和业务要素，而不是当前特定的功能需求设计表结构；设计应基于完整的生命周期和业务流程设计表结构。例如：事件类表，可以适当增加category和status字段，方便后续扩展。③元数据原则列名应遵循统一的数据标准，即同一类型的字段应对应相同的元数据；字段类型和长度应相同，例如同一产品线下所有表的组织编号应对应相同的元数据；常用字段应建立应用级标准定义，指定元数据，确定字段命名。比如所有表的“最近一次维护时间”字段统一命名为last_modify_time，保证了我们在挖掘数据库和做知识图谱时能够串起整个链路。2)典型规范示例①操作：方法论方法论是万事万物的基石。例如，我们必须为每个表创建一个主键。如果不显示主键，会自动生成一个rowid（6字节）作为隐藏主键，所有表共享这个空间，导致性能下降。②量化：提炼的理性思维。我们建议扫描命中率原则上应该是100:1。从事物的大小来说，我们的要求是10万个，行业一般需要1万个。③避坑：为了避免MySQLBug，大表truncate改为drop+createtable，在5.7很有效，但是公司在8.0修改优化了。针对上面的规范，我们需要让开发者潜移默化的知道发生了什么，为什么会这样，从而避免一些问题的发生。3.质量权限控制自动化基于druid，我们扩展了Sonarqube插件，实现了本地检查规则和云端同步。我们之前制定了大约27条规则，包括一些常见的错误。比如有人在update语句中把set关键字后面的分隔符逗号（“,”）误写成了“and”，导致出现意想不到的结果。4、大事务查杀大事务的相关问题主要有以下几点：binlog的写入、传输、回放慢。之前看过一个应用，备份库24小时都没有播放完毕。万一主库出现问题，就没有办法切换回去了。只能等备份库处理完再切换回去；事务写入被阻止；在主库游戏失败的情况下，是切还是不切？我们行业和行业都采用了自动查杀的方式。在showengineinnodbstatus中，我们可以监控。如果某笔交易未结束，则提示该笔交易更新的记录条数；当超过阈值时，我们可以自动杀死它。对于online和batch来说，阈值是不一样的，所以我们在自动执行kill的时候，一定要避免一刀切的问题。我们当时做了两步操作。第一步是区分在线交易库和批处理库。对于在线仓库，超过三秒的交易可以自动查杀；批量仓库先进行小规模试点，再全面推广。以后要保证MySQL的主动同步不降级，去掉降级时间，但这要靠我们完善的治理，不存在大事务。五、未来展望1.全链路监控希望能实现一套端到端的全链路监控，快速定位到哪个节点有问题。2、进一步发展AIOps希望AIOps进一步发展，达到业界所说的1-5-10目标，1分钟发现，5分钟处理，10分钟恢复。3、掌握源码最后希望大家掌握一些开源组件的源码，做到“他山之石攻玉”，了解其中隐藏的bug风险，对我们有帮助将来维护开源组件。Q&AQ1：贵公司在MySQL调优过程中会使用相关的辅助工具吗？老师可以简单分享一下吗？A1：没有使用辅助工具，我们还是直接通过explain查看执行计划，然后进行一些分析。Q2：MySQL规范在贵公司普及了吗？实施一套规范需要多长时间？A2：我们从2017年开始建立MySQL规范，因为在引入MySQL5.7的时候，我们要建立方法论的基石。我们建立规范后，在SonarQube上构建检测组件，实现访问控制，实现规范。只有规范，没有执行，我们是很难控制的，必须通过硬性的方法来控制。Q3：贵公司使用什么方式监控MySQL？A3：它包括两个级别。第一层，我们在MyBatis上进行了扩展，会对语句进行review，判断语句是否有问题。第二层是监控MySQL的性能模式和信息模式相关表，发现并处理其中的慢SQL。Q4：老师，自动查杀的准确率是多少？A4：自动查杀准确率其实可以达到100%。大额交易可以轻松监控，但很多时候我们不敢查杀。我们将线上交易和批量交易分开后，查杀线上大额交易的准确率相当于100%。Q5：老师能推荐一个好用的开发工具吗？喜欢工作台？这个总公司有什么要求吗？A5：行业里其实有很多工具，比如收费的Navicat，免费的MySQLWorkbench等，我一般Workbench用的多一点，因为我们行业的进口软件是管控的，必须要注册。作者简介：魏亚东：中国工商银行软件开发中心三级经理，高级架构师，杭州研发部数据库专家组组长，开发中心安全组成员，负责技术管理、数据库、安全-相关工作;2009年加入工行软件开发中心，致力于推动管理创新和效率提升，提供全面的技术管控，推动自动化实施，实现业务价值的优质快速交付；同时作为技术专家，为安全生产提供技术支持；负责教育培训、预付费等SaaS产品和数字生态基础（ComposableApplications）等。
