20、关键服务监控与PostgreSQL性能优化指南-优快云博客

本文链接：https://blog.youkuaiyun.com/jwt8token/article/details/149548001

关键服务监控与PostgreSQL性能优化指南

在数据库管理和监控领域，对关键服务的有效监控以及对PostgreSQL的性能优化至关重要。本文将详细介绍如何使用Node Exporter、Postgres Exporter收集系统和数据库指标，如何将这些指标集成到Prometheus和Grafana中进行可视化展示，以及如何添加自定义查询和使用PostgreSQL的各种扩展进行性能优化。

1. 使用Node Exporter收集系统指标并集成到Prometheus

Node Exporter是一个强大的工具，可用于收集Linux系统的内核级指标。安装并运行Node Exporter后，我们可以使用 curl 命令在终端验证其导出的指标。接下来，我们要将这些指标集成到Prometheus中，以便进行后续的监控和分析。

准备工作

Node Exporter需在PostgreSQL服务器上运行。
Prometheus服务器必须能够访问Node Exporter暴露指标的端口。

操作步骤

添加PostgreSQL服务器信息到Prometheus配置文件 ：
在监控服务器的 prometheus.yml 文件中添加PostgreSQL服务器的IP地址和Node Exporter使用的端口。示例配置如下：

scrape_configs:
  - job_name: 'prometheus'
    basic_auth:
      username: prom
      password: secret
    static_configs:
    - targets: ['localhost:9090']
    - targets: ['192.169.29.20:9100']

启动Prometheus ：
修改配置文件后，使用以下命令启动Prometheus：

sudo ~/prom/prometheus --config.file ~/prom/prometheus.yml --storage.tsdb.path ~/prom/promdb --web.console.templates ~/prom/consoles --web.console.libraries ~/prom/console_libraries --web.listen-address 192.168.29.10:9090 &

验证新目标是否可见 ：
访问 http://192.168.29.10:9090/targets ，查看是否能看到新添加的目标。

工作原理

通过编辑 prometheus.yml 配置文件，在 scrape_configs 部分添加Postgres节点的IP和端口信息，让Prometheus能够收集和存储Node Exporter的指标。修改配置后，重启Prometheus服务，然后通过浏览器验证新目标是否可见。

2. 使用Postgres Exporter收集PostgreSQL指标

除了系统级指标，我们还需要收集PostgreSQL数据库的特定指标。Postgres Exporter可以帮助我们实现这一目标。

准备工作

拥有一个能够连接到目标数据库的数据库用户。
确保Prometheus服务器能够访问Postgres Exporter暴露指标的端口。
Postgres Exporter的GitHub地址为： https://github.com/wrouesnel/postgres_exporter

操作步骤

安装依赖 ：
在PostgreSQL服务器上安装必要的依赖：

sudo yum install epel-release -y
sudo yum install golang -y

下载Postgres Exporter源码 ：
以Postgres用户身份下载源码：

go get github.com/wrouesnel/postgres_exporter

安装Postgres Exporter ：
进入源码目录并使用 go 进行安装：

cd ${GOPATH-$HOME/go}/src/github.com/wrouesnel/postgres_exporter
go run mage.go binary

验证安装 ：
检查Postgres Exporter的版本：

./postgres_exporter --version

配置pg_hba.conf文件 ：
添加允许Postgres Exporter连接的条目：

echo "host all all 192.168.29.20/32 md5" >> $PGDATA/pg_hba.conf
pg_ctl -D $PGDATA reload

验证连接 ：
使用 psql 命令验证连接是否正常：

psql -d "postgresql://postgres:secret@192.168.29.20:5432/postgres?sslmode=disable"

设置环境变量 ：
设置包含数据库连接URI的环境变量：

export DATA_SOURCE_NAME="postgresql://postgres:secret@192.168.29.20:5432/postgres"

启动Postgres Exporter ：
以守护进程方式启动Postgres Exporter：

./postgres_exporter &

验证指标 ：
使用 curl 命令验证指标是否正常暴露：

curl http://192.168.29.20:9187/metrics

工作原理

Postgres Exporter使用Golang编写，因此需要先安装Golang。下载并安装源码后，通过配置 pg_hba.conf 文件和设置环境变量，确保Exporter能够正常连接到数据库并暴露指标。最后，启动Exporter并使用 curl 验证指标的可用性。

3. 将Postgres Exporter的指标添加到Prometheus

在启动Postgres Exporter后，我们需要告诉Prometheus定期收集并存储这些指标。

准备工作

Postgres Exporter需在Postgres服务器上启动。
Prometheus服务器必须能够访问Postgres Exporter暴露指标的端口。

操作步骤

添加目标到Prometheus配置文件 ：
在 prometheus.yml 文件中添加Postgres服务器和Postgres Exporter使用的端口：

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
    - targets: ['192.168.29.20:9100']
    - targets: ['192.168.29.20:9187']

重新加载或重启Prometheus ：
可以使用 SIGHUP 信号重新加载Prometheus，或者使用以下命令重启：

sudo ~/prom/prometheus --config.file ~/prom/prometheus.yml --storage.tsdb.path ~/prom/promdb --web.console.templates ~/prom/consoles --web.console.libraries ~/prom/console_libraries --web.listen-address 192.168.29.10:9090 &

验证目标 ：
访问 http://192.168.29.10:9090/targets ，查看新目标是否可见。

工作原理

通过在 prometheus.yml 文件的 scrape_configs 部分添加Postgres服务器的IP和端口信息，让Prometheus能够收集Postgres Exporter的指标。重新加载或重启Prometheus后，使用浏览器验证新目标是否正常显示。

4. 在Grafana中导入监控仪表盘

Grafana是一个强大的可视化工具，我们可以使用它来创建各种监控仪表盘。下面介绍如何导入用于监控Linux和PostgreSQL指标的仪表盘。

导入Linux指标监控仪表盘

准备工作 ：
使用Node Exporter设置好PostgreSQL服务器的监控。
Grafana在监控服务器上正常运行。
操作步骤 ：
1. 找到合适的仪表盘 ：访问 https://grafana.com/grafana/dashboards/1860 ，复制仪表盘ID（这里是1860）。
2. 导入仪表盘 ：在Grafana浏览器中点击“Import”，粘贴仪表盘ID，然后点击“Load”导入。
3. 查看指标 ：连接到导入的仪表盘，查看Linux系统的指标。

导入PostgreSQL指标监控仪表盘

准备工作 ：
使用Postgres Exporter设置好PostgreSQL服务器的监控。
Grafana在监控服务器上正常运行。
操作步骤 ：
1. 找到合适的仪表盘 ：访问 https://grafana.com/grafana/dashboards/9628 。
2. 导入仪表盘 ：选择Prometheus作为数据源，导入仪表盘。
3. 查看指标 ：成功导入后，在仪表盘上查看PostgreSQL服务器的指标。

5. 为Postgres Exporter添加自定义查询

默认情况下，Postgres Exporter的查询可能无法满足所有的监控需求。我们可以添加自定义查询，让Exporter暴露这些查询的结果。

准备工作

使用Postgres Exporter设置好PostgreSQL服务器的监控。
Grafana在监控服务器上正常运行。

操作步骤

准备自定义查询文件 ：
创建 custom_queries.yml 文件，并添加自定义查询。示例如下：

pg_database_age:
  query: "SELECT datname, age(datfrozenxid) age FROM pg_database"
  metrics:
    - datname:
      usage: "LABEL"
      description: "Name of the database"
    - age:
      usage: "COUNTER"
      description: "Age of database"
pg_stat_progress_vacuum:
  query: "SELECT current_database() datname, relid::regclass as table, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples FROM pg_stat_progress_vacuum"
  metrics:
    - datname:
      usage: "LABEL"
      description: "Name of the database"
    - table:
      usage: "LABEL"
      description: "Name or oid of the table"
    - phase:
      usage: "LABEL"
      description: "Phase of the vacuum"
    - heap_blks_total:
      usage: "COUNTER"
      description: "Total number of heap blocks"
    - heap_blks_scanned:
      usage: "COUNTER"
      description: "Scanned heap blocks so far"
    - heap_blks_vacuumed:
      usage: "COUNTER"
      description: "Heap blocks vacuumed"
    - index_vacuum_count:
      usage: "COUNTER"
      description: "Number of completed index vacuum cycles."
    - max_dead_tuples:
      usage: "COUNTER"
      description: "Number of dead tuples that we can store before needing to perform an index vacuum cycle"
    - num_dead_tuples:
      usage: "COUNTER"
      description: "Number of dead tuples collected since the last index vacuum cycle."
pg_tables_info:
  query: "select current_database() datname, pn.nspname as schema_name, pc.relname as table_name, age(pc.relfrozenxid) as xid_age,(pg_table_size(pc.oid)) as table_size, pt.seq_scan,pt.idx_scan, pt.n_tup_ins,pt.n_tup_upd,pt.n_tup_del, pt.n_live_tup,pt.n_dead_tup,pt.vacuum_count,pt.autovacuum_count,pt.analyze_count,pt.autoanalyze_count FROM pg_class pc JOIN pg_namespace pn ON pn.oid = pc.relnamespace JOIN pg_stat_user_tables pt ON pc.oid = pt.relid where pc.relkind = 'r'"
  metrics:
    - datname:
      usage: "LABEL"
      description: "Name of the database"
    - schema_name:
      usage: "LABEL"
      description: "Name of the schema"
    - table_name:
      usage: "LABEL"
      description: "Name of the schema"
    - xid_age:
      usage: "COUNTER"
      description: "Age of the table"
    - table_size:
      usage: "COUNTER"
      description: "Size of the table"
    - seq_scan:
      usage: "COUNTER"
      description: "Number of sequence scans of this table"
    - idx_scan:
      usage: "COUNTER"
      description: "Number of index scans of this table"
    - n_tup_ins:
      usage: "COUNTER"
      description: "Number of inserts to this table"
    - n_tup_upd:
      usage: "COUNTER"
      description: "Number of updates on this table"
    - n_tup_del:
      usage: "COUNTER"
      description: "Number of deletes on this table"
    - n_live_tup:
      usage: "COUNTER"
      description: "Number of live tupes in this table"
    - n_dead_tup:
      usage: "COUNTER"
      description: "Number of dead tupes in this table"
    - vacuum_count:
      usage: "COUNTER"
      description: "Number of manual vacuumes on this table"
    - autovacuum_count:
      usage: "COUNTER"
      description: "Number of automatic vacuumes on this table"
    - analyze_count:
      usage: "COUNTER"
      description: "Number of manual analyzes on this table"
    - autoanalyze_count:
      usage: "COUNTER"
      description: "Number of automatic analyzes on this table"

启动Exporter并包含自定义查询 ：
设置环境变量并启动Exporter：

export DATA_SOURCE_NAME="postgresql://postgres:secret@192.168.29.20:5432/postgres"
cd /var/lib/pgsql/go/src/github.com/wrouesnel/postgres_exporter
./postgres_exporter --extend.query-path=/var/lib/pgsql/go/src/github.com/wrouesnel/postgres_exporter/custom_queries.yaml &

工作原理

通过在 custom_queries.yml 文件中添加自定义查询，然后在启动Exporter时指定该文件的路径，让Exporter能够暴露这些自定义查询的结果。这样，我们就可以监控更多的数据库指标。

6. PostgreSQL扩展与性能调优

PostgreSQL提供了许多有用的扩展，可以帮助管理员和开发人员进行日常维护和性能调优。以下是一些常见的扩展及其用途：
- pg_repack ：用于在线重建表和索引，避免锁表。
- pg_prewarm ：用于预热缓存，提高查询性能。
- plprofiler ：用于对函数或存储过程进行性能调优。
- pg_stat_statements ：用于捕获需要调优的SQL语句。
- EXPLAIN ：用于查看查询的执行计划，帮助优化查询性能。

这些扩展在日常的数据库管理和性能优化中起着重要的作用。管理员可以根据具体需求选择合适的扩展来提高数据库的性能和稳定性。

通过以上步骤，我们可以实现对PostgreSQL服务器的全面监控和性能优化。从系统指标的收集到数据库指标的监控，再到自定义查询的添加和性能扩展的使用，每一个环节都为数据库的高效运行提供了有力支持。希望本文能帮助你更好地管理和优化PostgreSQL数据库。

关键服务监控与PostgreSQL性能优化指南

7. 安装和使用pg_repack进行在线对象重建

pg_repack是一个非常实用的PostgreSQL扩展，它允许我们在线重建表和索引，而不会对数据库的正常运行造成太大影响。以下将详细介绍其安装和使用方法。

安装pg_repack

要安装pg_repack，通常需要从源码进行编译安装。以下是大致的步骤：
1. 下载源码 ：从pg_repack的官方仓库下载最新的源码包。
2. 编译和安装 ：进入源码目录，执行以下命令进行编译和安装：

make
make install

使用pg_repack在线重建表

使用pg_repack重建表可以帮助我们清理表中的碎片，提高查询性能。具体步骤如下：
1. 确定要重建的表 ：选择需要重建的表，例如 your_table 。
2. 执行重建命令 ：使用以下命令重建表：

pg_repack -U your_user -d your_database -t your_table

其中， your_user 是数据库用户， your_database 是数据库名， your_table 是要重建的表名。

使用pg_repack在线重建索引

除了重建表，pg_repack还可以在线重建索引。步骤如下：
1. 确定要重建的索引 ：选择需要重建的索引，例如 your_index 。
2. 执行重建命令 ：使用以下命令重建索引：

pg_repack -U your_user -d your_database -i your_index

其中， your_user 是数据库用户， your_database 是数据库名， your_index 是要重建的索引名。

使用pg_repack在线移动表或索引到其他表空间

有时候，我们可能需要将表或索引移动到其他表空间以优化存储。使用pg_repack可以在线完成这个操作，步骤如下：
1. 确定目标表空间 ：选择要移动到的目标表空间，例如 your_tablespace 。
2. 执行移动命令 ：使用以下命令将表或索引移动到目标表空间：

pg_repack -U your_user -d your_database -t your_table -s your_tablespace

其中， your_user 是数据库用户， your_database 是数据库名， your_table 是要移动的表名， your_tablespace 是目标表空间名。

8. 使用pg_prewarm预热缓存

pg_prewarm是一个用于预热缓存的扩展，它可以将表和索引的数据加载到缓存中，从而提高后续查询的性能。以下是使用pg_prewarm的步骤：

启用pg_prewarm扩展

在使用pg_prewarm之前，需要先在数据库中启用该扩展：

CREATE EXTENSION pg_prewarm;

预热表数据

要预热表数据，可以使用以下SQL语句：

SELECT pg_prewarm('your_table');

其中， your_table 是要预热的表名。

预热索引数据

要预热索引数据，可以使用以下SQL语句：

SELECT pg_prewarm('your_index', 'buffer');

其中， your_index 是要预热的索引名。

9. 使用plprofiler对函数或存储过程进行性能调优

plprofiler是一个用于对函数或存储过程进行性能调优的扩展。它可以帮助我们找出函数或存储过程中的性能瓶颈。以下是使用plprofiler的步骤：

启用plprofiler扩展

在使用plprofiler之前，需要先在数据库中启用该扩展：

CREATE EXTENSION plprofiler;

配置plprofiler

在启用扩展后，需要对plprofiler进行一些配置。例如，可以设置采样间隔等参数。

分析函数或存储过程

使用plprofiler分析函数或存储过程的性能，例如：

SELECT plprofiler('your_function');

其中， your_function 是要分析的函数或存储过程名。

10. 使用pg_stat_statements捕获需要调优的语句

pg_stat_statements是一个用于捕获SQL语句执行统计信息的扩展。它可以帮助我们找出需要调优的SQL语句。以下是使用pg_stat_statements的步骤：

启用pg_stat_statements扩展

在使用pg_stat_statements之前，需要先在数据库中启用该扩展：

CREATE EXTENSION pg_stat_statements;

配置pg_stat_statements

在启用扩展后，需要对pg_stat_statements进行一些配置。例如，可以设置统计信息的存储方式等。

查看统计信息

使用以下SQL语句查看SQL语句的执行统计信息：

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC;

这个查询将按照总执行时间降序排列，显示每个SQL语句的执行次数、总执行时间、返回行数以及缓存命中率等信息。

11. 使用EXPLAIN查看查询执行计划

EXPLAIN是PostgreSQL中一个非常重要的工具，它可以帮助我们查看查询的执行计划，从而找出查询中的性能瓶颈。以下是使用EXPLAIN的步骤：

基本用法

要查看一个查询的执行计划，只需要在查询前加上 EXPLAIN 关键字，例如：

EXPLAIN SELECT * FROM your_table WHERE your_column = 'your_value';

这个查询将显示查询的执行计划，包括每个操作的成本、行数等信息。

详细执行计划

如果需要更详细的执行计划，可以使用 EXPLAIN ANALYZE ，例如：

EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'your_value';

EXPLAIN ANALYZE 不仅会显示执行计划，还会实际执行查询并显示每个操作的实际执行时间和行数。

12. 总结

通过前面介绍的各种方法，我们可以实现对PostgreSQL服务器的全面监控和性能优化。从使用Node Exporter和Postgres Exporter收集系统和数据库指标，到将这些指标集成到Prometheus和Grafana进行可视化展示；从添加自定义查询以满足特定的监控需求，到使用各种PostgreSQL扩展进行在线对象重建、缓存预热、函数调优和查询分析，每一个步骤都为数据库的高效运行提供了有力支持。

在实际应用中，我们可以根据数据库的具体情况和需求，选择合适的监控和优化方法。例如，如果数据库中存在大量的碎片，可以使用pg_repack进行在线重建；如果查询性能较低，可以使用EXPLAIN查看执行计划并进行优化。同时，持续监控数据库的性能指标，及时发现和解决问题，也是确保数据库稳定运行的关键。

以下是一个简单的流程图，展示了从监控到优化的整体流程：

graph LR
    A[系统和数据库指标收集] --> B[指标集成到Prometheus和Grafana]
    B --> C[可视化展示和监控]
    C --> D{是否发现问题}
    D -- 是 --> E[分析问题]
    E --> F[选择优化方法]
    F --> G[执行优化操作]
    G --> C
    D -- 否 --> C

希望本文介绍的内容能够帮助你更好地管理和优化PostgreSQL数据库，提高数据库的性能和稳定性。