如何使用AI发现和诊断由于数据库调用产生的性能问题

引言

作为一个APM产品的布道者，我在不少项目中都会和客户的开发者或架构师一起尝试使用APM一起分析和优化Java应用程序的性能问题，我发现大部分的性能问题，往往并不是因为在某一个具体方法上缓慢的一两毫秒，更多的是因为糟糕的架构设计，不合适的框架配置，错误的数据库访问模式，乱打日志以及内存过度消耗导致的GC异常。下面我将重点介绍如何使用AI发现和诊断由于数据库调用产生的性能问题。

一.常见的数据库问题大概有以下几种可能：

  • 一次请求SQL大量执行：
    • N+1查询问题（N+1 Query）：多次（30次或者更多）执行相同SQL。
    • 过多的SQL执行（Excessive SQLs）：执行大量（大于500）不同的SQL语句
  • 单一SQL执行缓慢：
    • 单一SQL执行缓慢（Slow Single SQL）：某个单一的SQL语句执行时间占据了请求的响应时间的80%以上。
  • 数据库负载较大：
    • 数据库繁忙（Database Heavy）：数据库执行的总体时间占据总体响应时间的80%以上
    • 数据库服务服务器超负荷（Overloaded Database Server）：来自各个应用的请求过多，造成了数据库服务器超负荷
  • 数据库连接池相关问题：
    • 连接池资源用尽（Pool Exhaustion）：由于连接获取时间过长所导致（getConnection的时间超过了executeStatement） *低效的连接池访问（Inefficient Pool Access）：对连接池的访问次数过多（对getConnection的调用超过了executeStatement调用次数的50%）

我遇到过的用户案例分析：

  1. 问题类型：一次请求SQL大量执行（现象）–数据库繁忙（现象）-N+1 Query （原因）

  • 问题发现：

    • 由总览分析发现，用户主要耗时集中在数据库： Sj21
    • 在现场分析用户最缓慢的几个web事务，观察他们这几个web事务对应的breakdown，统计发现一次请求平均调用数据库的次数多达1000次占据了请求的响应时间占比到90%以上，其中最慢的请求调用数据库达上万次。 Sj22

  • 问题调查和分析：这是一个给BD使用的内部应用，这类web事务都是在查询和统计当前时间段内该区域所有的外卖订单的相关信息，开发在做这个查询的时候，先查询了所有该区域内的商户对象，然后根据商户一个一个的查询出来他们当前的订单详情，是一个典型的N+1问题。

  • 针对这个问题可能给出的建议： 对于N+1次查询问题本身来说，使用连接查询就可以轻易地避免这一问题。在这个商户与属性的示例中，可以使用以下连接查询：

    select r., p.
    from shanghu_names as r
    inner join shanghu_properties as p on p.shanghu_id = r.shanghu_id

结果就是整个执行过程只产生了1次查询执行，不再是1000多次了！ 2. 问题类型：一次请求SQL大量执行（现象）–低效的连接池访问（原因）–ORM框架配置（解决方案）

  • 问题发现：同样是在用户现场发现几个web事务有大量的数据库调用，虽然数据库调用数量比较多，但是每次请求的1000+次数据库调用的总执行时间并不是很长（大概300ms左右），但是请求总体比较缓慢（平均响应时间在4s，break down table中的database占比在10%上）。 Sj23 (大量的相同SQL被执行)
  • 问题调查： 这次直接交流的是对应的开发部门，开发部门表示他们使用的ORM去调用数据库，业务与上一个案例类似。
  • 问题分析： 经过分析trace，发现虽然每一次sql执行都不慢（5ms左右），但是由于每次SQL查询都要向连接池获取一个新的连接，查询后再释放，导致对于连接词访问次数过多，从而引起访问缓慢。
  • 针对这个问题可能给出的建议： 应用的逻辑需要对某个对象列表进行迭代，但它并没有选择使用“即时加载”（Eager Loading）方式，则是使用了“延迟加载”（Lazy Loading）方式。考虑到我们总是需要获取所有对象，那么更好的方式是“即时加载”（Eager Loading）这些对象，然后考虑对他们进行缓存，前提是这些对象不会变更得十分频繁：（Hibernate之加载策略(延迟加载与即时加载)，来进行这种类型的查询。该用户使用了“延迟加载”（Lazy Loading）方式，它会加载每个对象，并通过独立的SQL查询语句获取每个对象的全部属性。每个SQL查询都是在一个向连接池获取的JDBC连接中执行的，然后在每个查询完成之后都会返回。查看一下调用trace中getConnection的出现次数，直接反映出了这个问题。

  • 问题类型：连接池资源用尽（原因）

  • 问题发现： 某一时间应用整体响应时间飙升，APM软件发出报警。

  • 问题调查和分析：

    • 从web事务角度分析，发现所有缓慢trace中的getconnection时间的占比较高。 Sj24
    • 从数据库角度分析，发现当时update类型操作有一个明显的突起 Sj25
    • 查看当时候比较缓慢的upadate操作，发现都是由于 当时突然开始运行的 一个后台任务发起的。 Sj26
    • 最后定位问题： 由于定时的后台任务突然发起的upadate操作，导致数据库连接池用尽，导致其他请求无法正常获取数据库连接导致的。

  • 类似的场景：

    • 某票务网站的硬件设备会定期请求后台，大约每分钟固定请求1000次，正常阶段slect和update操作的SQL运行都正常，放票阶段由于大量update操作导致数据库锁表，导致用户无法正常打开购票页面。
    • 某运营商网站，有一个非常缓慢的报表查询类接口，由于不明原因，短时间内对这个接口有大量访问，导致数据库连接资源用尽，用户其他请求无法得到响应。

  • 针对这类问题可能给出的建议：

    • 将这些请求发送至独立的服务器上，避免影响其他使用者。
    • 重新设定其执行时间，只在不会影响到其他人的时间段才执行,比如凌晨。
    • 增加连接池大小，确保在正常的访问量下有足够的连接可用。

  • 问题类型：单一SQL执行缓慢

  • 问题发现：通过分析web事物的breakdown table，发现请求中最耗时的是对某一个表的select. Sj27

  • 针对这类问题可能给出的建议： Sj28 通过分析SQL查询执行计划，可以帮组DBA优化那些耗时较长的SQL。