审计日志是RDS安全策略中非常重要的一环,它采集了数据库中所有的访问请求,包括常见的insert,update,delete,select,alter,drop,create语句, 还有一些比如set,commit,rollback命令语句。有了这些日志后可以帮助我们进行问题回溯,分析问题。下面这则案例讲述如何使用审计日志来分析只读实例延迟问题,如果没有审计日志我们很难想象该问题该如何解决。 ## 问题描述: 一客户使用了2个RDS只读节点来承担业务的读流量,两个RDS的资源规格和业务流量完全一样,但是离奇的发现两个只读中有一个实例出现了延迟,让用户百思不得其解: ![](https://oss.showapi.com/doc/2096/30/073122ab-5f88-4bfc-8d42-751c7b64463e.jpg) 只读Slave1出现延迟(图一): ![](https://oss.showapi.com/doc/2096/30/ef44f12b-9883-4505-844f-2a7d44b7d24c.jpg) 只读Slave2正常同步(图二): ![](https://oss.showapi.com/doc/2096/30/3f436c3e-e458-4b67-8457-4e122c5d9470.jpg) ## 分析: 1. 从上图监控可以看出延迟的曲线是一条直线,出现这样的直线通常是数据库的复制线程被block,导致slave一直无法完成与主库的数据同步,  比如:主库的一个超大事务或DDL传到备库。 2. 但是两个只读实例中只有一个只读实例出现延迟(图二),那么需要看一下出现延迟的时候只读实例当时的线程状态,以此来判断出问题所在。 ## 问题排查: * 检查延迟只读实例slave2在延迟期间的数据库快照,发现数据库中有大量的Waiting for table metadata lock 等待  (出现延迟时候可以使用show processlist将数据库的线程状态保存下来):  表:xxx_user  SQL: ~~~ SELECT user_id ...... FROM xxx_user where latest_time >= DATE_FORMAT('2016/06/15 23:40:09.000000000','%Y-%m-%d %k:%i:%s') ~~~ ![](https://oss.showapi.com/doc/2096/30/4054f8f8-ca0d-443f-aec9-b5b44e366c7d.jpg) ### 分析: 1. 出现select等待MDL锁的原因通常是该表上有DDL操作,在DDL操作的过程中会加上一个MDL锁,但是如果该表上有大查询,大事务或者未提交的事务,则会导致DDL操作无法获得MDL锁,进而阻塞住该表上的所有查询。 2. 由于当前实例是只读实例,所以DDL操作来源于主库,所以我们看一下主库是否真正在该表上有DDL操作。 * 从后端审计日志中果然发现在2016-06-17 00:41:17 时主库做了一次DDL操作,但是该DDL操作执行非常快,那么很有可能DDL传递到只读节点的时候,由于只读节点上该表有一个未提交的事务或者查询,导致DDL操作被blcok。 ![](https://oss.showapi.com/doc/2096/30/86483397-0edf-4f4c-b153-ef9fe3c4cb4b.jpg) * 在诊断快照中发现了一个事务在2016-06-16 19:17:09 就已经开始了,所以该事务很有可能就是导致DDL无法获取MDL锁的根源,那我们看一下这个事务做了哪些事情,线程id:1435503。 ![](https://oss.showapi.com/doc/2096/30/f730b524-81c3-4257-b2bb-bd34ef1dbba4.jpg) * 查看审计日志: 通过查看审计日志发现,线程id:1435503最后一次设置autocommit=0,时间是在16-06-16 14:10:03,之后对xx_user这个表执行了一个select,时间是在2016-06-16 19:17:09,但是一直没有提交,所以就是这个语句拿着MDL锁,直到2016-06-16 05:08:42 被kill后才被释放。该线程kill掉之后只读节点的延迟迅速下降。 ![](https://oss.showapi.com/doc/2096/30/32132405-6405-4a49-bdcc-7f2a52673f94.jpg) ![](https://oss.showapi.com/doc/2096/30/c13c0110-9edf-47ce-af7c-89108f74f0dc.jpg) ![](https://oss.showapi.com/doc/2096/30/a59c3e76-d519-4404-9298-998e7b153be3.jpg) ## 总结: 1. 此次slave延迟的原因为只读节点有未提交的事务导致主库的DDL被阻塞,所以在日常做DDL的过程中一定要观察数据库中是否存在大查询,大事务或者未提交的事务。 2. 善于使用show processlist来保存数据库的快照,如果该问题出现在自建数据库中,也是需要按照上述的方法进行排查,但是如果没有RDS的审计日志,排查问题起来会非常麻烦,可以通过审计日志去发现RDS中执行过的是有SQL,RDS不是黑盒子。 3. 只读实例延迟排查常见思路:一看资源是否达到瓶颈;二看线程状态是否有锁;三判断是否存在大事务或未提交的事务。 4. 要注意set autocommit=0的使用,一定要在语句结束后显式commit掉,不然会导致数据库中存在长时间未提交的事务,进而引发很多潜在的问题。 5. 为了审计以及排查问题方便,建议打开审计日志。