《逆流而上 阿里巴巴技术成长之路》读后记录之一——数据库篇

1、数据库常见问题

对数据库性能的要求，包括提升连接池的性能、单行并发更新能力、降低一些场景下的锁冲突概率、将memcached引擎引入到mysql内核中去。

1.1 数据库异常分析结果

场景：数据库相应慢，出现大量的close wait情况，导致业务系统无法正常使用

• 数据库出现大量close wait情况
  close wait产生的原因在于数据库服务端等待超时后，主动断开连接，如果应用服务端在数据库断开后能够相应及时关闭，就不会积攒大量的close wait，但是应用服务端在当天问题发生时并未马上关闭无用连接，所以大量的close wait状态出现，导致主机资源耗尽。
• 应用服务端为什么大量报错“open too many files”
  应用服务器可以open的文件数量有显示，通常一个进程是1024个默认，总共大概不到100w个，通常不会达到这个阈值，如果出现那可能是文件句柄没有及时释放，由于前面有大量的close_wait的tcp连接，每个连接对应一个套接字（socket），也对应一个文件句柄，这是导致报错的主要原因。
• 数据库服务的耗时是否和数据库主机的CPU利用率有关
• 会不会和主机磁盘IO有关
• 会不会和业务SQL性能有关
  发现是业务sql的性能问题，sql的执行缺乏正确的索引并且并发很高导致的。

解决方案

• 大量的close wait 如何避免
  从业务上下游主链路梳理超时时间参数和sql的超时设置，对其超时标准，统一调优
• 如何避免“open too many files”
  检查文件读写操作，socket通讯是否正常
• 业务如何避免同类SQL执行计划问题发生
  针对Top10 sql集中review执行计划和索引设置，避免出发sal可能的潜在问题
  数据库主机虽然后cpu等系统监控，但是并无执行计划的监控，完善sql执行计划的监控覆盖
• 线上问题及时发现
  依靠监控系统，完善SQL执行计划监控项，添加数据库容量带宽和连接数的监控
• 问题快速定位
  线上问题快读定位和排查能力提升，定位问题需要分阶段进行，首先定位外部系统原因，数据库原因，网络原因还是应用程序原因。

1.2 数据库延迟

某业务系统在生产环境遇到一个诡异的数据库访问现象：通过应用数据库中间件TDDL访问MySQL数据库，偶尔出现超长的访问延迟500ms，导致整个远程服务调用超时，但是绝大部分又不超时5ms。
* 大约0.3%的服务调用出现这种情况
* 几乎不受业务访问压力的影响，包括预发布环境也能重现这种超时
* 所有应用服务器上都能重现

通常情况下，出现超时会有以下几种情况
* 锁等待
有几行数据被锁，造成部分请求长时间等待锁，或者出现非公平的锁等待，插队太多，请求被“饿死”
* 热点数据
部分业务ID返回的数据量很大，或者需要扫描的索引数据很多
* IO抖动
高IO压力下，命中缓存的请求和需要访问磁盘的请求延迟（因为IO排队）差别会很大
* 位置其他问题等

问题分析及定位

定位后发现，延迟的请求都是查询其中两个库，说明这两个库有问题，后来发现数据库实例也没有问题，进一步分析发现同一条TCP连接的请求，要么都慢、要么都快，而很少有因素会影响单个TCP连接的处理效率的。
后面分析发现是MySQL线程池的问题，MySQL线程池做了很多优化，其中一个是对线程池资源进行分组，每个分组至少有2-3个线程，这样每个资源相互隔离，防止慢查询耗尽全部线程资源，导致MySQL无法响应业务请求，阿里内部有DRC，在数据库启动一个长时间运行的Binlog采集线程，持续抓取新生成的MySQL Binlog日志传输到另一端的DRC服务节点。由于DRC发起的连接和普通连接不一样，他请求Binlog Dump任务始终不会结束，这样执行DRC请求的线程就一直无法释放，如果有一个分组分配到2个以上的DRC连接，该分组内就没有空闲线程来处理其他请求了，因此表现高延迟。
那么为什么不是超时而是高延迟呢？由于MySQL线程池有另外一个参数thread_pool_stall_limit避免无限期等待。而这个值默认是500ms

解决方案就是让DRC线程归属到独立的线程池处理，或者绕过线程池，回归到一个线程服务一个连接的模式，或者减少DRC连接的数目作为临时方案。

1.3 AliSQL连接池调优

背景：连接出现大量“unauthenticated user”

原因分析：
MySQL的登录过程如下：
* 1、client建立TCP连接到DB对应的端口
* 2、DB向Client发送握手包
* 3、Client向DB发送验证信息包（用户名、密码）
* 4、DB向Client发送验证确认包

而线程池和MySQL登录请求处理包括下面过程：
1、在MySQL引入线程池之前，在TCP连接建立后，DB会新建一个线程，单独在这个新的FD上等待处理后续所有的登录交互以及query交互过程
2、在mysql引入线程池后，在tcp链接建立后，主线程会根据thread id将FD绑定到对应的group 的Epoll中，由Epoll来监听后续的网络包，如果发现这个session尚未登录，通用的worker会接收处理登录请求（在线程池中一次完整的登录交互相当于一次sql查询交互）
由于线程池中的worker是有限的，通常为320-254之间。
发现原因如下：
在连接过程中，步骤1建立连接后的1.26秒以后，DB发送握手包，期间Client没有断开TCP连接，而且在DB发送握手包之后，Client没有返回ACK or 密码包，这种情况下，会引发DB端线程池空等8s，并最终握手失败，导致线程池worker被耗尽，但是Client在0.5s的等待后，会再次发送新的连接请求，进入线程池的等待队列，引起连接池的奔溃。

解决方案：
1、确保连接重置一定被转发，避免MySQLID无谓等待
2、Client连接池保持存货机制，避免连接风暴
3、MySQLID增加worker数量，提高被连接风暴冲击能力
4、Client增加JDBC的connectTimeout参数，避免无谓重连
5、MySQLID减少connect_timeout时间，避免无谓的等待，改为1s。

1.4 ORM规则变更案例

背景：某次业务发布，select * 导致报错，提示字段不存在

分析：
* select * 严禁使用
* 由于receive_address增加了字段，而分库分表，有的库字段还未更新，导致select * 提示没有这个字段，还有原因是select * 是从数据库中解析出对应的全部字段（取第一个库的第一个表进行解析，解析后会把结果缓存起来，），替换*， 然后解析后的语句进行执行。
* 重点：一定不要使用select * ，还有很多弊端，生产一定不能使用。

1.5 SQL优化经典案例

索引篇：
* 通过explain来查看执行计划
* 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
* 超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致;多表关联查询 时，保证被关联的字段需要有索引。
* 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度。
* 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
* 如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
* 利用延迟关联或者子查询优化超多分页场景。
先快速定位需要获取的 id 段，然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
* SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
* in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控 制在 1000 个之内。
* 不要在where内进行函数运算，无法使用索引且速度很慢

参数优化篇：
* innodb_buffer_pool_size innodb引擎缓冲池的大小，通常配置主机内存70%-80%之间
* tmp_table_size:决定内部临时表的最大值，每个线程都要分配。
* table_open_cache:指定表告诉缓存的大小
* query_cache_size: 默认是关闭的，如果出现checking query cache for query，waiting for query cache lock，stroing result in query cache,则可以关闭该设置

DBA需要深入理解业务，当DBA深入理解业务后，既能站在业务上，又能站在DB角度考虑，这时候再去做优化，往往能够事半功倍。