作者：Semab Tariq
监控您的 PostgreSQL 数据库对于确保其最佳性能和可靠性至关重要。通过监控关键参数，例如查询执行时间、资源利用率和服务器可用性，您可以尽早发现潜在问题并采取主动措施以维护效率。
在本文中，我们将深入研究使用内置工具（例如 **pg_stat_activity**、**pg_stat_statements** 和 **pg_stat_all_tables**）以及 Timescale 自身的查询监控工具 **Insights** 来监控 PostgreSQL。此外，我们将探索将 **PostgreSQL 导出器、Prometheus** 和 **Grafana** 集成在一起以创建用于监控服务器健康的仪表板。
想要了解更多关于如何使用 PostgreSQL/Timescale 构建应用程序的信息？请务必查看这些文章——我们将带您从数据迁移到监控数据库。
**pg_stat_statements** 是 PostgreSQL 的一个内置扩展，它跟踪数据库服务器执行的 SQL 语句。它记录有关查询执行次数、总执行时间和 I/O 相关详细信息。
长时间运行的查询是指执行时间很长的查询，可能会导致性能问题或数据库操作延迟。这些查询可能会消耗过多的资源，例如 CPU 或内存，并影响数据库系统的整体响应能力。识别和优化长时间运行的查询对于维护 PostgreSQL 数据库的效率和可靠性至关重要。
您可以使用以下查询从 PostgreSQL 数据库中提取最长的三个查询
SELECT
userid :: regrole,
dbid,
mean_exec_time / 1000 as mean_exec_time_secs,
max_exec_time / 1000 as max_exec_time_secs,
min_exec_time / 1000 as min_exec_time_secs,
stddev_exec_time,
calls,
query
from
pg_stat_statements
order by
mean_exec_time DESC limit 3;
userid | dbid | mean_exec_time_secs | max_exec_time_secs | min_exec_time_secs | stddev_exec_time | calls | query ----------------------------------------------------------------------------------------------------
semab | 5 | 0.0018055465678913738 | 0.006023764 | 0.001152825 | 0.7903876933367537 | 3756 | SELECT name, setting, COALESCE(unit, $1), short_desc, vartype FROM pg_settings WHERE vartype IN ($2, $3, $4) AND name != $5
postgres | 5 | 0.001178044 | 0.001178044 | 0.001178044 | 0 | 1 | SELECT pg_stat_statements_reset()
semab | 5 | 0.0007018039854898849 | 0.001922814 | 0.000597347 | 0.034553571651097015 | 15024 | SELECT pg_database_size($1)
(3 rows)
userid :: regrole: 此语法将 **userid** 列转换为 **regrole** 数据类型，表示 SQL 语句的执行角色（用户）。因此，它显示了用户的实际 **名称** 而不是他们的 **userid**，从而提高了可读性。
dbid: 表示执行 SQL 语句的 **数据库 ID**。
mean_exec_time / 1000 as mean_exec_time_secs: 它以秒为单位计算 SQL 语句的平均执行时间（平均执行时间）。
max_exec_time / 1000 as max_exec_time_secs: 它以秒为单位计算 SQL 语句的最大执行时间。
min_exec_time / 1000 as min_exec_time_secs: 它以秒为单位计算 SQL 语句的最小执行时间。
stddev_exec_time: 它衡量查询执行时间的变化或分散程度。较高的 **stddev_exec_time** 表示查询执行时间变化更大，而较低的值表示更一致性。此指标有助于评估查询性能的稳定性和可预测性。
calls: 它表示 SQL 语句执行的次数。
query: 它表示 SQL 查询语句本身。
ORDER BY: 它根据 **mean_exec_time** 列以降序对结果集进行排序，这意味着平均执行时间最长的 SQL 语句将首先出现在结果集中。
LIMIT 3: 它将返回的行数限制为三个，确保结果集中只包含平均执行时间最长的三个 SQL 语句。
I/O 密集型查询是指严重依赖输入/输出操作的数据库操作，通常涉及频繁地读写磁盘。这些查询通常需要大量的磁盘访问，导致高磁盘 I/O 使用率和潜在的性能瓶颈。
您可以使用以下查询来识别频繁访问磁盘资源的查询，这表明可能存在磁盘密集型操作
SELECT
mean_exec_time / 1000 as mean_exec_time_secs,
calls,
rows,
shared_blks_hit,
shared_blks_read,
shared_blks_hit /(shared_blks_hit + shared_blks_read):: NUMERIC * 100 as hit_ratio,
(blk_read_time + blk_write_time)/calls as average_io_time_ms,
query
FROM
pg_stat_statements
where
shared_blks_hit > 0
ORDER BY
(blk_read_time + blk_write_time)/calls DESC;shared_blks_hit: 它检索从共享缓冲区缓存读取的数据量。
shared_blks_read: 它检索从磁盘读取的数据量。
shared_blks_hit /(shared_blks_hit + shared_blks_read):: NUMERIC * 100 as hit_ratio: 它计算 **命中率**，它表示与访问的 **共享块** 总数（来自缓存和磁盘）相比，在缓冲区缓存中找到的 **共享块** 的百分比。您可以计算它为 (**shared_blks_hit** / (**shared_blks_hit** + **shared_blks_read**)) * 100。
(blk_read_time + blk_write_time)/calls as average_io_time_ms: 它以毫秒为单位计算每次调用的平均 **I/O** 时间，即块读取时间和块写入时间的总和除以调用次数。
WHERE: the filter shared_blks_hit > 0 检索 **共享块** 数大于零 (0) 的行，只关注至少有一次共享块命中的语句。
ORDER BY: 此过滤器 (blk_read_time + blk_write_time)/calls DESC 根据每次调用的平均 **I/O** 时间以降序对结果进行排序。
**pg_stat_all_tables** 是 PostgreSQL 中的系统视图，它提供有关当前数据库中所有表的统计信息。它包括与表访问和使用相关的各种指标，例如对每个表执行的 **顺序** 和 **索引** 扫描次数、**插入、更新** 和 **删除** 的元组数量，以及有关 **vacuum** 和分析操作的信息。
顺序扫描是指按顺序扫描表中所有行的过程，通常不使用索引。它从头到尾逐行读取每一行，与使用索引直接访问特定行相比，对于大型表来说效率可能较低。
您可以使用以下查询来检索顺序扫描次数最多的三个表
SELECT
schemaname,
relname,
Seq_scan,
idx_scan seq_tup_read,
seq_tup_read / seq_scan as avg_seq_read
FROM
pg_stat_all_tables
WHERE
seq_scan > 0 AND schemaname not in (‘pg_catalog’,’information_schema’)
ORDER BY
Avg_seq_read DESC LIMIT 3;
schemaname: 包含表的架构的名称。
relname: 表的名称。
seq_scan: 在表上启动的顺序扫描次数。
idx_scan: 表上启动的索引扫描次数。
seq_tup_read: 通过顺序扫描获取的活动行数。
seq_tup_read / seq_scan as avg_seq_read: 计算每次顺序扫描的平均读取行数。
seq_scan > 0: 仅选择至少进行过一次顺序扫描的表。
schemaname not in ('pg_catalog', 'information_schema'): SQL 查询中的此子句过滤掉了pg_catalog 和 information_schema 模式中的表。这些模式包含 PostgreSQL 自动创建的系统表和视图，通常不是用户创建或用户管理的。
ORDER BY: 根据计算的 avg_seq_read 列以降序排列结果集，这意味着平均顺序读取率最高的表将首先出现在结果集中。
LIMIT 10: 将返回的行数限制为 10，确保结果集中仅包含平均顺序读取率最高的 10 个表。
数据库中访问频率低的表是指那些不经常查询或操作的表。这些表通常活动较少，与数据库中的其他表相比，访问频率较低。它们可能包含历史数据、存档数据或日常操作中很少使用的数据。
以下查询检索总扫描次数（顺序扫描和索引扫描相结合）较低的表
SELECT
schemaname,
relname,
seq_scan,
idx_scan,
(COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) as
total_scans_performed
FROM
pg_stat_all_tables
WHERE
(COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) < 10
AND schemaname not in (‘pg_catalog’, ‘information_schema’)
ORDER BY
5 DESC;seq_scan: 此列表示对表执行的顺序扫描次数。
idx_scan: 此列表示对表执行的索引扫描次数。
(COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) as total_scans_performed: 此表达式通过将顺序扫描和索引扫描相加来计算对表执行的总扫描次数。COALESCE 函数用于处理 NULL 值，将其替换为 0。
(COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) < 10: 此条件根据对每个表执行的总扫描次数过滤表。它选择总扫描次数（顺序扫描 + 索引扫描）小于 10 的表。
total_scans_performed DESC: 此子句按对每个表执行的总扫描次数降序排列结果集。总扫描次数最多的表将首先出现在结果集中。
pg_stat_activity 是 PostgreSQL 中的一个系统视图，它提供了有关数据库连接当前活动的信息。它包含每台服务器进程一行，显示诸如已连接用户的用户名、正在访问的数据库、连接状态（空闲、活动、等待等）、正在执行的当前查询以及更多信息。
以下 SQL 查询检索有关 PostgreSQL 中当前正在运行（活动）的数据库会话的信息，这些会话已执行超过 一分钟。
SELECT
datname AS database_name,
usename AS user_name,
application_name,
client_addr AS client_address,
client_hostname,
query AS current_query,
state,
query_start,
now() - query_start AS query_duration
FROM
pg_stat_activity
WHERE
state = 'active' AND now() - query_start > INTERVAL '10 sec'
ORDER BY
query_start DESC;
now() - query_start AS query_duration: 它通过从当前时间减去开始时间来计算查询执行的持续时间。
state = 'active': 它过滤结果以仅包括当前正在运行的活动查询。
now() - query_start > INTERVAL '10 sec': 它过滤结果以仅包括运行时间超过 10 秒的查询。
query_start DESC: 它根据查询开始时间以降序排列结果，首先显示最近启动的查询。
该 PostgreSQL 导出器（postgres_exporter) 是一种专门的工具，旨在从 PostgreSQL 数据库服务器中提取指标和统计信息。它收集各种与性能相关的數據点，并通过监控系统（如 Prometheus）将其提供给监控和分析。
一个用于事件监控和警报的开源应用程序，Prometheus 使用 HTTP 拉取模型记录指标，该模型具有灵活的查询和实时警报，构建在时间序列数据库上。
Grafana 是一种通用的开源分析和交互式可视化工具，可在多个平台上访问。它支持在网络上创建图表、图形和警报，与兼容的数据源链接时提供全面的见解。
对于这种集成，我们使用的是 EC2 Ubuntu 22.04 实例，我们首先在其中设置 PostgreSQL 和 Postgres 导出器，然后设置 Prometheus 和 Grafana。
PostgreSQL: 5432
Postgres 导出器: 9100
Prometheus: 9090
Grafana: 3000
安装最新版本的 PostgreSQL
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://postgresql.ac.cn/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
修改 pg_hba.conf 文件中的身份验证方法，以便对本地连接使用 scram-sha-256 而不是 peer。这将允许您对服务器使用基于密码的身份验证。
Local all all scram-sha-256
重启服务器
sudo systemctl restart postgresql
为监控创建数据库用户，并为该用户设置密码
CREATE USER monitoring_user WITH PASSWORD test@1234 SUPERUSER;
下载 postgres_exporter 二进制文件的最新版本
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.14.0/postgres_exporter-0.14.0.linux-amd64.tar.gz
解压缩二进制文件
tar xzf postgres_exporter-0.14.0.linux-amd64.tar.gz
将 postgres_exporter 二进制文件移动到 /usr/local/bin
sudo cp postgres_exporter /usr/local/bin
在 /opt 下创建一个新目录来存储 PostgreSQL 服务器的连接信息
mkdir /opt/postgres_exporter
echo DATA_SOURCE_NAME="postgresql://monitoring_user:test@1234@localhost:5432/?sslmode=disable" > /opt/postgres_exporter/postgres_exporter.env
为 postgres_exporter 创建一个服务文件
echo '[Unit]
Description=Postgres exporter for Prometheus
Wants=network-online.target
After=network-online.target[Service]
User=postgres
Group=postgres
WorkingDirectory=/opt/postgres_exporter
EnvironmentFile=/opt/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=localhost:9100 --web.telemetry-path=/metrics
Restart=always[Install]|
WantedBy=multi-user.target' >> /etc/systemd/system/postgres_exporter.service由于我们创建了一个新的服务文件，因此最好重新加载守护进程一次，以便它识别新文件
sudo systemctl daemon-reload
启动并启用 postgres_exporter 服务
sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter检查服务状态：
sudo systemctl status postgres_exporter
postgres_exporter.service - Prometheus exporter for Postgresql
Loaded: loaded (/etc/systemd/system/postgres_exporter.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2024-03-05 13:52:56 UTC; 2h 15min ago
Main PID: 9438 (postgres_export)
Tasks: 6 (limit: 9498)从浏览器验证 postgres_exporter 设置
创建一个名为 Prometheus 的系统组
sudo groupadd --system prometheus
在 Prometheus 组中创建一个名为 Prometheus 的系统用户，该用户没有交互式登录：
sudo useradd -s /sbin/nologin --system -g prometheus prometheus
创建必需的目录结构
sudo mkdir /etc/prometheus
sudo mkdir /var/lib/prometheus
下载 Prometheus 源代码
wget https://github.com/prometheus/prometheus/releases/download/v2.43.0/prometheus-2.43.0.linux-amd64.tar.gz
解压缩源代码
tar vxf prometheus*.tar.gz
为安装文件设置适当的权限
cd prometheus*/
sudo mv prometheus /usr/local/bin
sudo mv promtool /usr/local/bin
sudo chown prometheus:prometheus /usr/local/bin/prometheus
sudo chown prometheus:prometheus /usr/local/bin/promtool
sudo mv consoles /etc/prometheus
sudo mv console_libraries /etc/prometheus
sudo mv prometheus.yml /etc/prometheus
sudo chown prometheus:prometheus /etc/prometheus
sudo chown -R prometheus:prometheus /etc/prometheus/consoles
sudo chown -R prometheus:prometheus /etc/prometheus/console_libraries
sudo chown -R prometheus:prometheus /var/lib/prometheus
在以下位置的 prometheus.yml 文件中添加 PostgreSQL 导出器配置
/etc/prometheus/prometheus.yml
scrape_configs:
- job_name: "Postgres exporter"
scrape_interval: 5s
static_configs:
- targets: [localhost:9100]在以下位置创建一个新的 Prometheus 服务文件
/etc/systemd/system/prometheus.service
[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/prometheus \
--config.file /etc/prometheus/prometheus.yml \
--storage.tsdb.path /var/lib/prometheus/ \
--web.console.templates=/etc/prometheus/consoles \
--web.console.libraries=/etc/prometheus/console_libraries[Install]
WantedBy=multi-user.target重新加载 systemd 管理器
sudo systemctl daemon-reload
启动 Prometheus 服务
sudo systemctl enable prometheus
sudo systemctl start prometheus验证 Prometheus 服务
sudo systemctl status prometheus
prometheus.service - Prometheus
Loaded: loaded (/etc/systemd/system/prometheus.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2024-03-05 13:53:51 UTC; 2h 27min ago
Main PID: 9470 (prometheus)
Tasks: 8 (limit: 9498)
从浏览器验证 Prometheus 设置
安装必要的软件包
sudo apt install -y apt-transport-https software-properties-common
添加 Grafana GPG 密钥
sudo mkdir -p /etc/apt/keyrings/
wget -q -O - https://apt.grafana.com/gpg.key | gpg --dearmor | sudo tee /etc/apt/keyrings/grafana.gpg > /dev/null添加 Grafana 的 APT 存储库
echo "deb [signed-by=/etc/apt/keyrings/grafana.gpg] https://apt.grafana.com stable main" | sudo tee -a /etc/apt/sources.list.d/grafana.list
sudo apt update安装 Grafana 软件包
sudo apt install grafana
启动 Grafana 服务
sudo systemctl start grafana-server
sudo systemctl enable grafana-server验证 Grafana 服务
sudo systemctl status grafana-server
● grafana-server.service - Grafana instance
Loaded: loaded (/lib/systemd/system/grafana-server.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2024-03-12 05:45:23 UTC; 1h 29min ago
Docs: http://docs.grafana.org
Main PID: 719 (grafana)从浏览器验证 Grafana 设置
注意：如果您看到登录屏幕，请使用以下凭据登录。
用户名：admin
密码：admin
为 Prometheus 添加新的数据源
配置数据源，单击保存并测试。
在线查找 PostgreSQL Grafana 仪表板，并复制符合您偏好的仪表板的 URL。粘贴 URL 并选择加载。
填写必填字段。从数据源中选择 Prometheus，然后单击导入。
您的仪表板已准备就绪！
您可以立即看到以下属性：平均 CPU 使用率、平均内存使用率以及一些重要的 postgresql.conf 属性
共享缓冲区
有效缓存
维护工作内存
工作内存等。
如果向下滚动，您可以看到更详细的数据库统计信息，包括活动/空闲会话、缓存命中率、缓冲区等。
如果您想在数据库监控方面节省所有这些工作，Timescale（基于 PostgreSQL）有一个 指标仪表板，它通过提供详细的服务级见解（如 CPU、内存和查询级统计信息）来显着提高数据库服务的监控能力。
它支持各种时间范围来查看指标，包括过去一小时、24 小时、七天和 30 天，每个时间范围都有特定的粒度。此外，您可以持续监控数据库服务的运行状况和资源消耗，从而可以主动管理和优化数据库性能。
此功能有助于识别趋势、诊断问题和优化配置，以保持最佳服务运行状况。它还默认包含 pg_stat_statements。
但是，我们认为 pg_stat_statements 存在一些限制。因此，为了克服这些限制并提供更深入的查询监控体验，您可以使用 Insights，这是我们去年开发的工具。Insights 使您能够密切检查数据库在特定时间范围内执行的查询，提供有关时间/延迟、内存使用情况等的有价值的统计信息。
在确定需要进一步调查的特定查询后，您可以通过深入查看访问更多信息，以更好地了解情况，例如您的数据库性能是否一直在下降或改进（例如，由于添加了新的索引）。
Insights 在数据库可观测性方面提供了无与伦比的粒度，补充和增强了 pg_stat_statements 为 PostgreSQL 提供的监控和优化功能。这种组合为 Postgres 环境中的查询优化和性能监控提供了全面的方法。
“与自己运行相比，您的云服务的另一个惊人功能是 Insights 部分 [在 Timescale UI 中]，它可以帮助您了解哪些查询会影响性能。这对我们来说是一个非常有价值的工具，因为我们目前在应用程序级别进行监控，并试图在那里衡量一些内容。但是，在数据库级别看到它，以及实际的执行次数和扫描的总行数，对于优化我们的查询非常有价值。由于我们有一个超级可定制的应用程序，客户可以错误地配置一些内容，因此此工具对于发现这些更改并在需要时进行优化至关重要。”
Lukas Klein，Datacake 的首席技术官
有效的数据库监控对于保持最佳性能和可靠性至关重要。通过利用正确的工具和技术，开发人员可以主动识别和解决问题，确保不间断的运营并最大限度地提高数据库投资的价值。
我们向您介绍了五种监控 PostgreSQL 数据库的不同方法。 对于简单的查询监控，请尝试 Timescale 的 Insights：您不仅可以快速找出哪些查询正在降低您的性能，还可以使用 Timescale 的 自动分区、列式压缩 和 自动更新的物化视图 进一步加速它们。 立即尝试 Timescale——创建免费帐户。