PostgreSQL 教程: 记录慢查询日志

十月 22, 2023

摘要:在本教程中,您将学习如何在 PostgreSQL 中设置慢查询日志记录。

PostgreSQL 允许使用某些参数配置,记录运行时间超过一定时长的查询的日志。这有助于管理员了解需要优化的查询,以提高整体应用程序性能。在本教程中,我们将讨论在 PostgreSQL 中启用记录耗时查询日志所涉及的步骤。

准备工作

需要重点注意的一点是,记录附加的信息到 PostgreSQL 日志,会产生更多的磁盘写入,并使用大量磁盘空间。因此,在启用记录任何大量日志时,监控磁盘使用情况和 IOPS 增加非常重要。将日志和数据分离到不同的磁盘上也很重要。这样,由日志记录产生的写入,就不会导致包含数据库对象的数据目录出现任何 I/O 饱和。

操作步骤

我们将使用以下步骤,启用记录慢查询日志:

1. 确保将logging_collector设置为ON,以启用 PostgreSQL 中的日志记录。可以使用以下步骤,将logging_collector设置为ON

$ psql -c "show logging_collector"
$ psql -c "ALTER SYSTEM SET logging_collector TO 'ON'"

将数据库日志与系统日志分离记录,绝对是明智之举,这样就可以轻松区分内核级消息和 PostgreSQL 事件。因此,我们应该将log_destination参数设置为 ‘stderr’ 而不是 ‘syslog’。

2. 适当地设置log_line_prefix,以便日志文件的每一行中都有足够可见的信息:

$ psql -c "ALTER SYSTEM SET log_line_prefix TO '%t [%p]: [%l-1] user=%u,db=%d'"

3. 将log_min_duration_statement设置成认定为慢查询的时间。我们可以看到各种特定时间范围的示例,如下所示:

以下命令可用于记录运行时间超过 10 毫秒的所有查询:

-- To log all the queries running for more than 10 milliseconds
$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '10ms'"

以下命令可用于记录运行时间超过 15 秒的所有查询:

$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '15s'"

以下命令可用于记录运行时间超过 20 分钟的所有查询:

$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '20min'"

以下命令可用于记录运行时间超过 1 小时的所有查询:

$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '1h'"

4. 重新启动或重新加载配置,以使更改生效。更改logging_collector参数需要重新启动:

可以使用以下命令来执行重新启动:

$ pg_ctl -D $PGDATA restart -mf

以下命令可用于重新加载配置:

$ pg_ctl -D $PGDATA reload

至此,我们已经学会了如何开始记录数据。

怎么做到的…

PostgreSQL 中只有一种类型的日志文件。这意味着,没有单独的日志文件来记录错误或警告,也没有单独的日志文件来记录慢查询。因此,要记录错误或慢查询,必须设置logging_collectorON。否则,我们将不会看到任何信息记录到日志中。一旦确认启用此功能,需要重点确保所有详细信息也是可见的,例如运行 SQL 的应用程序或用户。为此,我们可以使用log_line_prefix参数。可以将其设置为步骤 2 中所示的值,或使用运行时日志记录的配置,如步骤 2 中所示。

要启用慢查询的日志记录,我们必须给参数log_min_duration_statement设置一个时间,超过该时间后的 SQL 查询,就被认为是执行缓慢。这是一个全局设置。如步骤 3 中的示例所示,可以将其设置为几毫秒、几秒、几分钟或几小时。如果只是log_min_duration_statementlog_line_prefix已被更改,我们只需重新加载配置,如步骤 4 所示。

还有更多…

还有更多的日志记录参数,我们可能会想要了解,如下:

  • log_connections:每个新连接(接收和授权)都会记录到 PostgreSQL 日志中。
  • log_disconnections:每次 PostgreSQL 连接断开都会记录到日志中。
  • log_duration:启用记录数据库服务器中运行的每个查询的持续时间。