@杏咲望

图片.png

今天我们来看看性能调优PostgreSQL。PostgreSQL是一个高级的开放源码SQL数据库。建立数据库只是第一步。对于添加的每个表和运行的每个查询，数据库都需要维护和更新，以确保它们继续高效地运行。

尽管PostgreSQL确实为您做了很多优化，但为了有效地运行，它需要从数据库管理员那里了解一些事情。

当涉及到应用程序的性能优化时，有一条规则适用：不要过早优化。首先实现数据库和模式。然后，当您有了最佳的设置之后，您就可以开始使用像retrace这样的工具来监视SQL查询了。这一点很重要，因为性能调整都是为了权衡取舍。您可能会在应用程序的某个部分接受一个运行缓慢的查询，而该部分不经常使用，以便在频繁执行的查询上获得闪电般的快速响应时间。

今天我将引导您完成一些不同的优化。首先，我们将介绍一些可用的基本设置选项：

• 硬件更新您可以对物理服务器进行的更改

• 对现成PostgreSQL配置的配置修改

• vacuum有效设置可以提高性能

一旦建立了系统，我们将看到如何分析和改进您的模式：

*分析查询性能如何分析单个查询

*分析日志如何从系统中获取信息进行分析

*查询性能索引添加索引如何帮助数据库优化其查询计划

第1部分：最佳实践和设置

在本文的第一部分中，我们将介绍如何更改基本的PostgreSQL设置以提高性能。在第2部分中，我将介绍如何优化您的系统细节，例如查询优化。我们开始吧！

增强PostgreSQL硬件

在优化性能方面，最明显的出发点是查看系统本身的硬件。数据库的一些最重要的因素是可用内存、CPU和磁盘空间以及性能。

当涉及到硬件更新时，您应该考虑以下事项：

升级内存。内存是系统用来有效地缓存它经常需要的数据的工具。在以后优化查询时，记住内存是很重要的。缓存越大，数据库到磁盘的时间就越少，这会给性能带来痛苦。PostgreSQL试图在内存中保存最频繁访问的数据，以根据查询的执行方式和您提供的配置来提高性能。但稍后我们将返回到基于内存的性能优化。

将应用程序与数据库分离。注意到内存增加，如果您在与应用程序相同的服务器上运行数据库应用程序，则可能需要考虑将其隔离。如果一个单独的组件正在影响您的度量和环境，那么就很难真正分析和提高您的数据库性能。

数据库配置

PostgreSQL提供了一组默认配置。这个默认配置是为了兼容性而设置的，本质上是一个最佳猜测，它试图适应PostgreSQL的所有可能的用例。幸运的是，这意味着如果您开始调整默认配置，有一些潜在的快速胜利。

在我们深入研究并进行一些您可以进行的最常见的配置优化之前，值得指出的是，有几种可用的工具，例如pgtune，它们尝试为您完成这些配置工作。它们的有效性各不相同，但值得在这些工具的输出和当前数据库配置之间进行增量试验。

PostgreSQL中的数据库配置直接在配置文件（postgresql.conf）中进行，或者通过运行alter system命令进行。如果现在想查看数据库的所有当前配置，只需按如下所示运行show命令：

SHOW ALL

此命令将列出所有现有配置及其设置。同样重要的是要注意，不同的配置只在某些情况下适用，例如数据库重新启动。某些配置更改将需要重新启动服务器；其他更改将需要重新加载配置。

对数据库进行配置更改后，可以通过运行以下命令来查看详细信息，例如是否需要重新启动配置更新：

SELECT * FROM pg_settings WHERE pending_restart = true;

高性能PostgreSQL配置更改

您可能需要修改许多不同的配置，以充分利用PostgreSQL数据库。让我们来介绍一些您可以更改的主要配置，以便从系统中获得更多性能。

最大连接

连接是应用程序与数据库通信的方式。每个连接都需要一些通信颤振和设置才能建立。但是，一旦建立，查询就可以通过线路发送到数据库。PostgreSQL数据库将提供默认数量的连接，您可以更改这些连接。您希望确保您的应用程序没有不必要地连接，因为这样做成本高昂，并且可能影响性能。内存分配是在每个连接的基础上执行的，因此您需要在分配内存和连接数之间取得平衡。

checkpoint_segments

检查点是一种定期操作，用于存储有关系统的信息。默认情况下，检查点将在若干段之后运行，但根据您的系统，您可能希望增加此值。稍后我们将在本文中讨论如何注销检查点数据，但是检查点配置很重要，因为它可能是一个非常昂贵的操作。通常认为默认配置太激进，执行检查点的频率太高，因此您可能希望增加该值以减少检查点的频率。

work_mem

正如我前面提到的，内存分配和管理是性能调优PostgreSQL的重要组成部分。如果您的系统进行了很多复杂的排序，增加排序内存可以帮助数据库为您的设置优化其配置。这允许PostgreSQL在执行排序时在内存中缓存更多的数据，而不是对磁盘进行昂贵的调用。

random_page_cost

这个设置本质上是优化器在到达磁盘之前读取内存所花费的时间。只有在您完成了我们即将介绍的其他基于计划的优化（如清空、索引或更改查询和模式）后，才应更改此设置。

这些只是您可以对数据库配置进行的一些优化，但还有很多其他优化。既然您知道了如何调整数据库设置，那么让我们看看另一个需要调查的领域：真空处理。

使用vacuum以避免膨胀

下一个领域是vacuum。真空扫描是指将元组标记为不再需要以便覆盖它们的扫描。如果不这样做，可能意味着您的系统中有死元组。这些死元组通常被称为膨胀。膨胀主要源于正在被删除、更新或插入的记录。

PostgreSQL默认设置vacuum，但正如您可以配置其他设置一样，您也可以通过vacuum设置。甚至可以根据每个表设置真空设置，以实现更细粒度的优化。

vacuum展示命令

VACUUM

如果要查看以前运行VACUUM的历史记录，可以执行以下操作：

SELECT * FROM pg_stat_user_tables

一般来说，经常VACUUM是不够的。更频繁的VACUUM可以将膨胀保持在最低限度，并确保数据库性能保持较高。虽然已经设置了VACUUM，但您可能希望更改设置以使其更有效

第2部分：PostgreSQL分析

前端系统配置将限制性能提高。虽然您可以预先进行一些配置，但是您的许多配置决策将由您的系统的数据和分析驱动。您将希望优化它的设置方式，了解它的查询方式，并基于这些用例进行优化。PostgreSQL和社区为我们提供了一些方便的工具来进行这些优化。

用analyze更新postgres算法数据

在继续之前，了解查询的生命周期是很重要的。生命周期是从最初的请求到数据库发送的响应所发生的事情。PostgreSQL不只是把手伸进一大袋内存中。当您拥有大量数据时，粗取数据会导致性能下降。如果您按顺序扫描数据库（通常称为表扫描）以查找数据，那么您的性能将线性扩展—行越多，性能越慢。但我们可以做得更好。

那么查询的生命周期是什么呢？最初，查询字符串会传输到PostgreSQL。然后分析查询字符串并创建计划。计划真的很重要。计划是PostgreSQL将要采取的步骤来查找请求的数据。如何设置数据库配置、模式和索引（稍后将详细介绍索引）都将影响这些计划的性能。所以，了解计划以及如何优化计划是很重要的。最后，数据库将执行计划并检索数据。

PostgreSQL用于获取数据的数据库计划和它实际获取数据的方式之间可能会出现差异。这是因为PostgreSQL的计划基于不经常更新的度量和统计数据。一些数据必须定期更新，以便用于计划的统计数据是最新的。

这就是分析的切入点。运行analyze命令可以更新这些统计信息，以便postgres有一组关于如何创建计划的新数据。因此，如果要更新表或架构或添加索引，请记住在更新后运行analyze命令，这样更改将生效。

深入了解您的查询性能

接下来要关注的一个明显领域是查询优化。由于许多原因，您正在运行的查询可能效率低下，因此我们需要一种方法来了解这些查询所发生的情况，解释命令就是在这些查询中出现的。

explain为您提供了PostgreSQL将如何执行查询的精确细分。该计划基于有关表的统计信息，并考虑数据库索引等因素，以找到最有效的数据路径。但是“解释”只能猜测并给你一个它认为可以执行的计划。您可以运行explain analyze来获取有关预测计划的信息，还可以更新查询的执行方式。

当你有了数据库试图执行的计划后，你就可以开始分析它了。有几种工具可以让工作更轻松，例如pev，它允许您可视化和理解explain命令的输出。

作为高质量性能数据源的日志

假设您有一个正在运行的数据库，并且希望调试应用程序中的缓慢性能。一种方法是通过日志来实现。日志是应用程序在执行操作时留下的简短信息语句。然后，您可以收集这些操作，并在日志管理工具（如retrace）中对它们进行分析，以了解系统的行为。

默认情况下，应用程序不会记录所有数据。这是因为日志记录的增加也会影响数据库性能。因此，在我们介绍如何更改日志设置以分析性能时，请记住日志设置本身实际上会影响性能。

日志会发送到系统中的一个文件，具体取决于您的配置。当您找到日志所在的位置时，您可以使用诸如retrace之类的工具来分析这些日志。RETRACE将告诉您一些统计信息，例如最频繁运行的查询、查询平均花费的时间等。这些聚合度量可以让您更好地了解系统中哪些地方可能存在性能瓶颈。

所以，现在我们看到了了解系统性能的日志有重要，我们该如何设置或更改它们呢？

log_line_prefix

这是数据库日志的格式。对于像pgbadger这样的工具，它们需要丰富的数据，还需要知道数据格式。日志行前缀告诉PostgreSQL以什么格式发送日志数据。如果您想进行任何有意义的日志分析，您需要将这个值设置为与用于分析日志的工具兼容。

log_statement

这是您正在操作的日志级别。日志级别通常是指日志中需要的详细级别。例如，您想要绝对所有的日志，还是只想要某个关键类型的日志？Log_语句有几个不同的设置，包括：

log_checkpoints

正如我们在配置设置中讨论的，PostgreSQL中的检查点是定期操作，用于存储有关系统的数据。如果日志检查点过多，则会导致性能下降。如果您怀疑可能是这种情况，通过启用日志检查点，您将能够看到有关这些检查点的丰富数据，例如它们运行的频率以及触发它们的内容。

logging_connection

您还可能希望了解有关连接的信息。如果只有一个应用程序连接到您的数据库，但是您看到了许多并发连接，那么可能是出了问题。淹没数据库的连接太多也可能意味着请求无法到达数据库，并可能影响应用程序的最终用户。

既然我们已经有了很多关于我们的系统的数据，那么我们有什么工具来改进我们的系统结构以提高性能呢？

使用数据库索引加快查询速度

在没有索引的世界中，对数据库的每个请求都将导致对整个表进行全面扫描，以查找相关的结果。对于大型数据集，这可能非常慢，这就是数据库索引的来源。就像在书中一样，索引会向数据库引擎提供有关系统中您要查找的数据的大致位置的信息。为了正确索引我们的系统，我们需要了解我们的数据以及如何访问它。这就是为什么我们有可观测性和监控工具很重要，比如追溯设置来帮助我们。然而，索引并不是免费的；如果您更新了一本书的内容，那么每次更改内容时都必须更新索引。

PostgreSQL有几种不同类型的索引。它们之间的区别在于它们都使用不同的算法。可用的索引有B-树（默认索引）、哈希、gist、sp gist和gin。PostgreSQL将在创建主键或唯一键约束时创建隐式索引。但是，您必须手动添加其余的索引。

索引的基本语法是：

CREATE INDEX index_name ON table_name;

但是，在某些用例中不应该使用索引，例如，当使用索引的开销超过了算法的好处时，例如在一个小表上。但是执行了大批量更新的表也可能会看到性能问题。在恢复索引之前，在更新时临时删除这些表上的索引可能是有意义的。

闪电般快的PostgreSQL

希望这能让您了解如何开始性能调优Postgres数据库。现在，您应该能够使用explain分析查询，提取日志以获得进一步的洞察力，并修改索引以提高性能。另外，您应该能够调整您的数据库配置以匹配使用，这样您就可以充分利用它。

记住，调整数据库需要时间和练习。所以要有耐心，并保持好奇，了解更多关于PostgreSQL系统的信息，以获得最佳的性能结果。