在我们的Java代码中诊断常见的数据库性能热点

当我帮助开发人员或架构师分析和优化其Java应用程序的性能时，并不是要调整各个方法以在执行时间上再挤出一两毫秒。 虽然对于某些软件来说，毫秒级优化很重要，但我认为这不是我们应该开始寻找的地方。 我在2015年分析了数百个应用程序，发现大多数性能和可伸缩性问题都涉及不良的体系结构决策，配置错误的框架，不良的数据库访问模式，过多的日志记录和详尽的内存使用情况，从而导致垃圾回收。

对我而言，性能工程与随着时间的推移观察和关联关键体系结构，可伸缩性和性能指标有关，从构建到构建以及在不同的负载条件下，以识别回归或瓶颈。 以以下仪表板为例：

将负载与响应时间和＃SQL执行相关联可以回答几个性能工程根本原因问题！

最上面的图称为“层分解”图，它显示了按逻辑组件（Web服务，数据库访问，业务逻辑，Web服务器等）划分的应用程序的总执行时间。 红色表示在后端Web服务之一中花费的时间。 因此，我们有一个明确的组件热点。 我们还知道Web服务不会承受异常负载，因为第二张图显示了由应用程序处理的请求数量的平线。 一个普遍的发现是，大多数总体响应时间都花在了数据库层上。 但是，这并不一定意味着数据库本身很慢！ 知道低效的数据库访问通常是导致性能下降的原因，所以我总是将SQL执行的数量与之关联。 在这种情况下，响应时间峰值的大部分都具有明显的视觉相关性。

不良的数据库访问模式是我最经常观察到的一种问题模式，其次是服务调用过于闲聊，共享数据访问同步不良，日志过多以及内存泄漏/对象流失过多导致垃圾回收影响或应用程序崩溃。

可用的诊断工具

在本文中，我将重点放在数据库上，因为我确信您所有的应用程序都在遭受其中一种访问模式的困扰！ 您可以使用市场上几乎所有的概要分析，跟踪或APM工具，但是我使用的是免费的Dynatrace Personal License 。 Java还附带了一些出色的工具，例如Java Mission Control 。 许多访问数据的框架（例如Hibernate或Spring）也通常通过日志输出提供诊断选项。

使用这些跟踪工具不需要任何代码更改，因为它们都利用JVMTI （JVM工具接口）来捕获代码级信息，甚至跟踪跨远程层的调用。 这在面向分布式（微）服务的应用程序中非常有用。 只需修改JVM的启动命令行选项即可加载工具。 一些工具供应商提供了IDE集成，您可以简单地说“在XYZ分析启用的情况下运行”。 我有一个简短的YouTube教程，展示了如何跟踪从Eclipse启动的应用程序！

确定数据库性能热点

当事实证明数据库是对应用程序请求的总体响应时间的主要贡献者时，请小心责怪数据库，并指责DBA！ 可能有几种原因会使数据库变得如此繁忙：

• 数据库使用效率低下：错误的查询设计，不良的应用程序逻辑，错误的数据访问框架配置
• 数据库中的设计和数据结构不良：表关系，存储视图速度慢，索引丢失或错误，表统计信息过时
• 不适当的数据库配置：内存，磁盘，表空间，连接池

在本文中，我主要集中于您可以在应用程序方面执行的操作，以最大程度地减少在数据库中花费的时间：

诊断错误的数据库访问模式

诊断应用程序时，我经常检查几种数据库访问模式。 我查看了各个请求，并将其归入以下数据库问题模式类别：

• 过多SQL：执行大量（> 500）不同SQL语句
• N + 1 查询问题 ：多次执行同一条SQL语句（> 20）：
• 慢速单个SQL问题 ：执行单个SQL会贡献> 80％的响应时间
• 数据驱动的问题 ：相同的请求根据输入参数执行不同SQL
• 数据库繁重 ：数据库贡献时间>占总响应时间的60％
• 未准备的语句：执行相同SQL而不准备该语句
• 池耗尽：受高连接获取时间的影响（getConnection时间> executeStatement）
• 无效的池访问：对连接池的访问过多（调用getConnection> executeStatement计数的50％）
• 数据库服务器超载：数据库服务器只是因来自不同应用程序的太多请求而超载

例1：自家O / R映射器中SQL过多

我拥有的第一个示例来自Web应用程序，该应用程序提供了特定建筑物中会议室的概述。 会议室信息存储在数据库中，每次有人生成报告时都通过自定义数据访问层进行访问。

在分析单个请求时，我总是从查看所谓的事务流开始。 事务流是一个可视化选项，显示了应用程序如何处理请求。 对于我们的会议室概述报告，我们可以看到请求输入到Web服务器层（左），继续进入应用程序服务器层（中），并调用数据库层（右）。 这些层之间的“链接”显示了这些层之间存在多少交互，例如：单个请求执行了多少SQL查询。

该屏幕立即识别出前两个图案以抬起其丑陋的头部； 即Excessive SQLs模式和Database Heavy模式。 让我们来看看：

易于发现过多SQL执行+数据库繁重：24889个SQL！ 花40.27秒（占总时间的66.51％）执行！

查看各个SQL会发现此请求还遭受N + 1查询问题以及低效的池访问 （下面有更多信息）：

这样的错误访问模式无法通过优化索引来增强数据库机器来解决。

我经常看到这个问题。 应用程序逻辑遍历对象列表，但不是在O / R映射框架（例如Hibernate / Spring）中，还是在自编码框架中选择了“延迟加载”方法，而是选择了“延迟加载”方法。上面的例子。 上面的示例使用了一个本地实现，该实现加载每个会议室对象，然后通过单独SQL查询获取每个会议室的所有属性。 这些SQL查询中的每一个都在从池中获取的单独的JDBC连接上执行，然后执行，然后在每个查询完成后返回。 这就解释了每次从池中获取连接时，由Sybase JDBC驱动程序提交的12444调用以设置客户端名称的问题。 oom！ 对于其他可能未调用该set clientname的 JDBC驱动程序，您只需看一下调用getConnection的频率即可，它提供了相同的见解。

对于N + 1查询问题本身：可以通过使用联合查询轻松避免。 在我们的房间和属性示例中，可能类似于：

select r.*, p.*
from meeting_rooms as r
inner join room_properties as p on p.room_id = r.room_id

结果是执行了1个查询，而不是超过12000！ 减少了12000个连接获取和“设置客户端名称”调用。

示例2：过多SQL和错误的Hibernate配置

据我所知，很多人使用Hibernate或其他O / R映射器，我想提醒您，O / R映射器提供延迟加载和急切加载选项以及多层缓存的充分理由。 确保针对您的特定用例正确使用这些功能和选项。

下面是一个示例，其中延迟加载不是一个很好的选择，因为加载2k对象及其属性导致4k + SQL查询。 考虑到将始终需要所有对象，因此最好急于加载这些对象，然后考虑对它们进行缓存（假设它们不经常更改）：

使用Hibernate和Spring等O / R映射器时，请选择正确的加载和缓存选项。 了解他们在幕后所做的事情

大多数O / R映射器通过日志记录为诊断提供了很好的选择。 还请检查其在线社区的最佳做法。 我可以推荐的博客系列来自Alois Reitbauer，他在Hibernate的早期阶段进行了广泛的研究，以强调如何有效地使用缓存和加载选项 。

例3：自定义数据库访问代码中的未准备好的语句

在数据库引擎解析SQL语句并创建用于数据访问的执行计划之后，该结果将存储在数据库的缓存区域中，以供再次使用，而无需重新解析该语句（这消耗了大部分的数据库中的CPU时间）。 用于在缓存中查找查询的键是语句的全文。 这意味着，如果您使用1000条不同的参数值（例如where子句）使用1000次相同的语句，则高速缓存中将有1000条不同的条目。 带有新参数的查询No 1001必须再次进行解析。 这是非常低效的。 因此，我们具有“ Prepared Statements”的概念：一个查询被准备，解析并与变量的占位符一起存储在缓存中。 为了真正执行该语句，它们将被替换为实数值。 但是不需要重新解析该语句，可以从缓存中获取执行计划。

数据库访问框架通常足够智能，可以准备语句，但是在自定义代码中，我经常看到开发人员疏忽大意，例如以下示例，其中实际上只准备了一小部分SQL执行：

通过比较SQL执行次数和SQL准备执行次数来警惕未经准备的数据库访问

如果您开发自己的代码，请仔细检查您是否在适当地调用prepareStatement。 例如，如果您多次调用查询，通常最好使用PreparedStatement。 如果使用框架访问数据，请始终仔细检查这些框架在做什么以及提供了哪些配置选项来优化和执行生成SQL。 最简单的方法是监视executeStatement与prepareStatement的执行次数。 如果对每个SQL查询都执行此操作，则可以轻松找到优化热点。

示例＃4：持久的后端SQL报告的池大小调整效率不高

我经常看到以默认连接池大小（每个池10或20个连接）运行的应用程序。 通常，开发人员不会感到需要优化池的大小，因为他们通常不会进行必要的大规模负载测试，也不知道预期会有多少用户使用该新功能，或者它意味着什么后果，用于并行数据库访问。 或者，从预生产到生产部署的整个过程中，池配置都会“丢失”，然后默认设置恢复为应用服务器的默认设置。

通过JMX指标可以轻松监视连接池利用率。 每个应用程序服务器（Tomcat，JBoss，WebSphere等）都公开了这些指标，尽管有些要求您明确启用此功能。 下面显示了群集中运行的四个WebLogic服务器的池利用率。 您可以看到三个应用程序服务器的“活动数据库连接数”已达到最大值：

确保正确设置连接池的大小，并且不要使用与预期的负载行为不符的默认设置运行

此问题的根本原因不是总体流量激增。 从本文开始使用“ 负载与响应时间”与“数据库计数”仪表板就可以看出，没有额外的流量高峰。 原来，他们安排了每天2PM之后不久运行的报表，并执行了几个运行时间很长的UPDATE语句-所有这些语句都在不同的连接上。 这阻塞了所有连接几分钟，并导致“正常”流量遇到性能问题，因为这些请求无法获得与数据库的任何连接：

执行的个别SQL在几分钟内阻塞了多个连接，从而导致连接池耗尽的问题

如果您知道某些请求会长时间保留连接，则应该：

• 将它们放在单独的服务器上，以免影响其他任何人，
• 将它们安排在没有人受到影响的时间，或者
• 增加连接池的大小，以为常规流量提供足够的连接。

但是首先请确保优化这些查询。 分析SQL查询执行计划，以识别当前花费的时间。 这些天，大多数现代APM工具为您提供了检索SQL语句的执行计划的选项。如果不可用，最简单的方法是使用数据库的命令行工具，或者只是咨询DBA来为您生成它。

通过学习SQL查询执行计划来优化SQL语句

执行计划显示了数据库引擎如何处理SQL语句。 SQL变慢的原因可能多种多样。 这不仅是缺少索引或索引使用不当，而且是设计和结构或联接查询的许多次。 如果您不是SQL专家，请与您的DBA或SQL专家联系，以获取关键帮助。

负载测试和生产监控技巧和窍门

除了查看单个请求并确定这些模式之外，我还查看应用程序负载时的长期趋势。 除了我在一开始就向您展示的仪表板之外，我还确定了数据驱动的行为更改并验证数据缓存是否正常工作。

检查＃1：由于数据缓存的帮助，对数据库的访问应减少

下面显示了一个仪表板，其中显示了SQL执行平均数（绿色）与SQL执行总数（蓝色）的图表。 在持续两个小时的持续高负载性能测试中，我希望平均数会略有减少，总曲线会变平。 为什么？ 因为我假设我们从数据库中获得的大多数数据都是静态的，或将被缓存在不同的层中：

如果您的应用程序行为不同，则可能是数据驱动的性能问题或缓存问题

如果您在应用程序中遇到经典的N + 1查询问题（如我之前所展示的那样），那么随着最终用户生成数据将更多数据添加到数据库中，您的应用程序将显示平均SQL数量增加，因为这些查询将返回更多数据！ 所以–最好注意那些数字！

检查2：按类型识别SQL访问模式

与上面示例4中的情况类似，其中背景报告在下午2点开始启动，我一直在寻找随时间推移进行整体SQL访问的模式。 我查看了总执行时间，还查看了SELECT，INSERT，UPDATE和DELETE的执行计数。 这使我能够确定一天中是否有某些特定活动正在进行，例如后台作业更新大量数据。

通过查看SELECT，INSERT，UPDATE和DELETE的总时间和执行计数，了解您的应用程序的数据库访问行为

执行批量更新的批处理作业可能需要一段时间，尤其是对于具有大量行的表。 如果为此目的锁定了整个表，请注意，在该表上执行更新的其他请求（即使是在单行上）也必须等待，直到释放该锁为止。 考虑在没有其他用户在线时运行这些作业，或者考虑使用其他锁定逻辑逐行锁定，更新和释放。

检查＃3：数据库实例的状况如何？

在本文中，我试图强调数据库性能问题通常与缓慢的数据库服务器无关，但更可能与数据库访问模式不良的应用程序代码（ N + 1查询问题，未准备好的语句等 ）或配置有关。问题（ 池访问效率低下，数据驱动问题等）。

但是完全忽略数据库是不明智的，因此我总是喜欢检查关键的数据库性能指标。 大多数数据库通过特殊的系统表提供出色的性能信息。 例如，Oracle提供了几个v $表和视图来访问关键的数据库性能指标（＃会话，等待时间，在解析中花费的时间，执行…）或诸如表锁或慢速SQL之类的信息，这些信息来自使用该应用程序的各种应用程序共享数据库实例。

这是我查看的两个仪表板，用于快速检查状态。 您可以看到完全从以下性能表中提取的指标：

找出数据库是否正常，或者是否可能受到共享此数据库实例的所有应用程序的过多负载的影响。

检查是否有任何当前正在执行SQL正在影响服务器，从而影响您的应用程序，例如：通过表锁

在持续集成中自动执行DB指标检查

在为您提供有关关键数据库指标和用例的一系列新想法之前，我想将差距缩小到我们都应该考虑的主题：自动化！

我建议不要在执行单元，集成，REST API或任何其他类型的功能测试时，在持续集成工具中查看这些指标，而不是手动进行所有这些检查。 如果您已经投资了一个测试套件来验证您的新REST API或新功能的功能，那么为什么不为每个构建中的每个测试执行捕获这些指标呢？ 这种方法将带来以下好处：

1. 专注于这些指标的代码审查，而不是遍历每行代码
2. 引入任何上述行为的代码签入通知

这是一个截图，用于跟踪每个测试和每个构建的这些指标，并在行为发生变化时发出警报。 将此与您的构建管道集成在一起，以防万一代码更改产生了不良影响，并得到通知-然后立即对其进行修复，而不必在生产中发布它时等待崩溃的系统：

将这些度量标准放入您的持续集成中，并通过注意度量标准行为的变化自动识别这些模式！

不仅仅是数据库

在本文中，我们重点介绍了与数据库相关的热点。 但是在我的工作中，我也看到其他领域的许多问题。 在2015年，我看到了从整体到（微）服务迁移项目的大量问题，并且与我们在这里看到的模式类似，例如N + 1查询问题，其中每个用例调用后端服务被调用数百次。 。 大多数情况下，问题是由于不良的界面设计造成的，并且没有考虑到一旦在Docker容器或云环境中执行了一次本地方法，将会发生什么情况。 网络突然开始起作用，包括您放在网络上的有效负载以及您必须处理的新连接池（线程和套接字）。 但这是另一回事了。 请继续关注并“愿指标与您同在” ：-)

在我们的下一篇文章中，我们将介绍定位常见的微服务性能反模式 。

翻译自: https://www.infoq.com/articles/Diagnosing-Common-Java-Database-Performance-Hotspots/?topicPageSponsorship=c1246725-b0a7-43a6-9ef9-68102c8d48e1