数据库教程FGMT68：PostgreSQL性能分析与优化调整

## 一、前言与内容大纲介绍

PostgreSQL数据库在生产环境运行一段时间后，随着数据量增长、业务并发升高、SQL语句多样化，性能问题会逐渐显现：某条报表查询越来越慢、批量写入卡顿、数据库CPU飙高、内存换页频繁。作为数据库运维与开发人员，掌握一套系统化的性能分析与优化方法，是保障业务稳定、降低故障率的核心能力。风哥教程本文围绕 PostgreSQL 数据库的性能分析与优化调整展开，全程以实战操作为主，理论为辅，环境按 64G 内存、8 CPU 的主流生产规格进行参数设计与演示。

风哥针对本文的章节规划如下：第一部分讲解优化概述与整体优化方案，建立"分层调优、先诊断后调优"的方法论；第二部分为操作系统层优化调整与建议，包括内核参数、文件系统与I/O调度等；第三部分为存储系统层优化调整与建议，涉及数据目录布局、表空间分离；第四部分为数据库层面常用优化指标分析与参数调整，重点讲解内存、连接、WAL等关键参数的配置方法；第五部分为索引方面优化与经验总结，涵盖索引类型选择、索引失效与索引膨胀处理；第六部分为 SQL 语句层面分析与优化，包含执行计划解读、慢 SQL 定位与优化案例；第七部分为 AWR 性能采集与分析诊断，演示性能数据采集与报告解读；第八部分为性能定期监控分析与问题诊断；最后为自动化调优与顾问建议。

阅读本文前，建议读者已掌握 PostgreSQL 数据库的基本安装与日常管理操作。文中所有演示均在本套风哥教程的演示环境 fgedu-net-cn1、fgedu-net-cn2 两台主机上完成，数据目录统一使用 /fgedudb，数据库实例、数据库名、操作系统用户均统一命名为 fgedudb。下面我们进入正式内容。

## 二、PostgreSQL 优化概述与优化方案

### 2.1 性能优化的层次模型

PostgreSQL 的性能优化不是孤立地调整某一个参数，而是要从整体架构上分层审视。风哥将性能优化划分为五个层次，从底层到上层分别是：硬件与操作系统层、存储系统层、数据库实例层、SQL 语句层、应用架构层。绝大多数性能问题的根因集中在中间三层，也就是存储、实例参数和 SQL 语句，这也是本文的优化重点。

- 硬件与操作系统层：CPU 核数、内存容量、内核参数（内存调度、I/O 调度、透明大页等）。

- 存储系统层：磁盘类型（机械盘、SSD、NVMe）、RAID 级别、文件系统、表空间与数据文件分布。

- 数据库实例层：shared_buffers、work_mem、maintenance_work_mem、effective_cache_size、WAL 相关参数等。

- SQL 语句层：执行计划是否合理、是否走索引、连接方式、统计信息是否准确、是否存在全表扫描。

- 应用架构层：连接池、读写分离、缓存、分库分表等。

### 2.2 优化方法论：先诊断，后调优

风哥在多年的数据库运维中总结出一条铁律：性能优化必须"先诊断，后调优，一次只改一个参数"。切忌在没有量化基线的情况下盲目堆参数，否则容易出现参数之间互相干扰、难以回滚的局面。标准流程如下：

1. 采集基线：通过监控工具采集当前 CPU、内存、磁盘、网络以及数据库关键指标，形成基线。

2. 定位瓶颈：利用 top、vmstat、iostat、sar 以及数据库视图，判断瓶颈位于 CPU、内存、I/O 还是锁竞争。

3. 制定方案：针对瓶颈所在层次制定调整方案，明确预期效果与风险。

4. 实施调优：一次只调整一个参数，记录调整前后的数值对比。

5. 验证效果：通过压测或真实业务验证优化是否达到预期，未达预期则回退并重新诊断。

### 2.3 常用性能指标定义

在正式进入调优之前，需要先明确几个关键性能指标的含义：

- TPS（Transactions Per Second）：每秒事务数，反映数据库事务处理能力。

- QPS（Queries Per Second）：每秒查询数，反映并发查询能力。

- 响应时间（Latency）：单个请求从发出到返回的时间，分为平均响应时间与百分位响应时间。

- 缓存命中率：Buffer Cache 命中率，衡量共享缓冲区对数据页的复用程度。

- 等待事件：数据库进程在等待何种资源，如等待锁、等待 WAL 刷盘、等待 I/O 等。

> 风哥 itpux-com

明确了这些概念之后，我们进入操作系统层的优化调整。

## 三、操作系统层优化调整与建议

操作系统是数据库运行的底座。64G 内存、8 CPU 的机器上，如果内核参数设置不当，即使数据库参数配置得再合理，性能也会大打折扣。本节针对 fgedu-net-cn1、fgedu-net-cn2 两台主机演示操作系统层的常用优化。

### 3.1 内核参数优化

PostgreSQL 运行依赖 Linux 内核的内存与 I/O 机制，风哥建议重点调整以下内核参数。首先编辑内核参数配置文件：

```bash

vi /etc/sysctl.conf

```

针对 64G 内存的生产环境，推荐的内核参数配置如下：

```ini

# 减少 swap 使用，尽量利用物理内存

vm.swappiness = 10

# 脏页控制，避免一次性大量刷盘导致 I/O 抖动

vm.dirty_ratio = 20

vm.dirty_background_ratio = 5

# 允许共享内存扩大，供 shared_buffers 等使用

kernel.shmmax = 68719476736

kernel.shmall = 16777216

# 文件句柄数提升

fs.file-max = 6815744

fs.aio-max-nr = 1048576

# 网络相关参数，提升并发连接能力

net.core.somaxconn = 4096

net.ipv4.ip_local_port_range = 1024 65535

net.ipv4.tcp_max_syn_backlog = 4096

```

参数修改完成后，执行以下命令使配置立即生效并验证：

```bash

sysctl -p

sysctl vm.swappiness

sysctl kernel.shmmax

```

输出确认 vm.swappiness = 10 即说明内核参数调整成功。

### 3.2 禁用透明大页（Transparent Huge Pages）

PostgreSQL 官方强烈建议在生产环境关闭透明大页 THP。THP 会导致内存页分裂与碎片化，引起数据库性能波动。在 RHEL/CentOS 等发行版上，可以通过 systemd 或 rc.local 在系统启动时关闭。检查当前状态：

```bash

cat /sys/kernel/mm/transparent_hugepage/enabled

cat /sys/kernel/mm/transparent_hugepage/defrag

```

若输出为 [always] madvise，说明 THP 处于开启状态。风哥推荐将 always 改为 never，以 RHEL 系列为例，在 /etc/default/grub 中追加内核参数 transparent_hugepage=never，或通过 systemd 服务在启动时写入：

```bash

vi /etc/systemd/system/disable-thp.service

```

服务内容如下：

```ini

[Unit]

Description=Disable Transparent Huge Pages

After=multi-user.target

[Service]

Type=oneshot

ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled'

ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/defrag'

[Install]

WantedBy=multi-user.target

```

启用并启动服务：

```bash

systemctl daemon-reload

systemctl enable disable-thp.service

systemctl start disable-thp.service

```

再次检查，若输出为 [never]，则说明 THP 已成功关闭。

### 3.3 CPU 与 NUMA 优化

8 CPU 的机器在生产中常开启 NUMA 架构。PostgreSQL 在跨 NUMA 节点访问内存时可能产生性能损耗。风哥建议在启动数据库时通过 taskset 或关闭 NUMA 调度来固定 CPU。可以先检查 NUMA 拓扑：

```bash

numactl --hardware

```

若确认存在多个 NUMA 节点，可在 PostgreSQL 服务启动脚本中绑定 CPU 核，例如：

```bash

numactl --cpunodebind=0 --membind=0 systemctl start postgresql-fgedudb

```

### 3.4 I/O 调度器优化

对于使用 SSD/NVMe 盘的生产环境，风哥建议将 I/O 调度器设置为 none 或 noop，避免多余的调度开销。查看当前调度器：

```bash

cat /sys/block/sda/queue/scheduler

```

通过 udev 规则将 SSD 盘的调度器固定为 none：

```bash

echo 'ACTION=="add|change", KERNEL=="sd*", ATTR{queue/scheduler}="none"' > /etc/udev/rules.d/60-iosched.rules

```

> 风哥教程 113257174

操作系统层调整完毕，接下来我们看存储系统层的优化。

## 四、存储系统层优化调整与建议

存储系统层的优化目标是降低 I/O 延迟、分散 I/O 压力，从而提升数据库整体吞吐。本节结合 fgedudb 数据目录布局进行演示。

### 4.1 数据目录与 WAL 分离

PostgreSQL 的 WAL 日志写入是频繁的、顺序的小 I/O，而数据文件读取是随机的块 I/O。风哥强烈建议将 WAL 目录（pg_wal）与数据文件放在不同的物理磁盘或 LUN 上，避免相互争抢 I/O 带宽。在 fgedu-net-cn1 上，规划如下：

- 数据文件目录：/fgedudb/fgedudb_data

- WAL 日志目录：/fgedudb/fgedudb_wal

- 表空间目录：/fgedudb/fgedudb_tbs

创建目录并赋予数据库用户权限：

```bash

mkdir -p /fgedudb/fgedudb_data /fgedudb/fgedudb_wal /fgedudb/fgedudb_tbs

chown -R fgedudb:fgedudb /fgedudb

```

在初始化数据库时，可通过 --waldir 参数指定独立的 WAL 目录：

```bash

su - fgedudb

/fgedudb/pgsql/bin/initdb -D /fgedudb/fgedudb_data --waldir=/fgedudb/fgedudb_wal -E UTF8 --locale=C -U fgedudb

```

### 4.2 表空间规划

对于大表、索引和临时数据，可以单独创建表空间，分散 I/O 压力。创建表空间并移动对象的实操如下：

```sql

CREATE TABLESPACE fgedudb_tbs LOCATION '/fgedudb/fgedudb_tbs';

```

将已有表移动到新的表空间：

```sql

ALTER TABLE fgedudb.t_order SET TABLESPACE fgedudb_tbs;

ALTER INDEX fgedudb.idx_order_no SET TABLESPACE fgedudb_tbs;

```

> 风哥数据库教程 itpux-com

存储层优化完成后，我们进入数据库实例层，也就是参数调整的核心内容。

## 五、数据库层面常用优化指标分析与参数调整

数据库实例层的参数调整是性能优化的重中之重。针对 64G 内存、8 CPU 的 fgedu-net-cn1 主机，风哥给出以下核心参数配置。配置文件位于 /fgedudb/fgedudb_data/postgresql.conf。

### 5.1 内存类参数

内存参数决定了数据库能利用多少物理内存来缓存数据。64G 内存下推荐如下：

```ini

# 共享缓冲区，建议为物理内存的 25% 左右

shared_buffers = 16GB

# 单条排序/哈希操作可用内存，不宜过大

work_mem = 64MB

# 维护操作（VACUUM、CREATE INDEX）可用内存

maintenance_work_mem = 2GB

# 操作系统文件缓存估算值，帮助规划器判断是否走索引

effective_cache_size = 48GB

# 预写日志缓冲区

wal_buffers = 64MB

```

修改后重载配置并验证：

```bash

su - fgedudb

/fgedudb/pgsql/bin/pg_ctl reload -D /fgedudb/fgedudb_data

```

通过 SQL 查看当前生效值：

```sql

SHOW shared_buffers;

SHOW work_mem;

SHOW effective_cache_size;

```

### 5.2 连接与并发参数

8 CPU 的机器并发能力有限，连接数过大会造成内存碎片和锁竞争。推荐配置：

```ini

max_connections = 500

superuser_reserved_connections = 3

max_worker_processes = 8

max_parallel_workers = 8

max_parallel_workers_per_gather = 4

max_parallel_maintenance_workers = 4

```

这些参数中 max_connections、max_worker_processes 属于 postmaster 上下文，修改后需要重启数据库才生效：

```bash

/fgedudb/pgsql/bin/pg_ctl restart -D /fgedudb/fgedudb_data

```

### 5.3 磁盘与 WAL 相关参数

```ini

# 检查点相关，降低频繁刷盘带来的 I/O 抖动

checkpoint_timeout = 15min

max_wal_size = 8GB

min_wal_size = 2GB

# 日志刷盘策略，保证 ACID 的前提下可调整

synchronous_commit = on

# 自动清理（VACUUM）相关

autovacuum = on

autovacuum_naptime = 60s

autovacuum_vacuum_scale_factor = 0.1

autovacuum_max_workers = 3

```

### 5.4 日志与慢 SQL 记录参数

定位性能问题离不开慢 SQL 日志。推荐开启慢查询记录：

```ini

logging_collector = on

log_directory = '/fgedudb/fgedudb_log'

log_min_duration_statement = 1000

log_statement = 'none'

log_line_prefix = '%m [%p] %q%u@%d '

```

log_min_duration_statement = 1000 表示将执行超过 1000 毫秒的 SQL 记录到日志中，这是风哥日常定位慢 SQL 最常用的手段之一。

> 网上搜索风哥教程可以学习全套数据库教程

### 5.5 参数修改后的验证方法

参数修改后必须通过压测验证效果。这里使用 pgbench 进行简单验证。先初始化测试数据：

```bash

/fgedudb/pgsql/bin/pgbench -i -s 100 -h 127.0.0.1 -U fgedudb fgedudb

```

执行压测：

```bash

/fgedudb/pgsql/bin/pgbench -c 32 -j 8 -T 120 -h 127.0.0.1 -U fgedudb fgedudb

```

压测结果中重点观察 TPS 与每秒处理事务数的变化，用于对比参数调整前后的性能差异。

> 上51CTO搜索风哥可以学习全套数据库教程

数据库层面参数调整完毕，下面进入索引方面的优化。

## 六、索引方面优化与经验总结

索引是 SQL 查询性能提升的关键。索引用得好，查询走索引秒级返回；索引用不好，全表扫描卡顿明显。本节结合 fgedudb 库中的订单表 t_order 演示索引优化。

### 6.1 索引类型的选择

PostgreSQL 提供了多种索引类型，需要根据业务特点选择。常用的有：

- B-tree：默认类型，适用于等值与范围查询。

- Hash：适用于等值查询。

- GIN：适用于数组、全文检索等多值类型。

- GiST：适用于地理位置、范围类型。

- BRIN：适用于数据天然有序且体量巨大的表，索引体积极小。

- 表达式索引：对函数或表达式建立索引。

- 部分索引：只对满足条件的行建立索引。

### 6.2 索引设计实战

对经常按 order_no 等值查询、按 create_time 范围查询的 t_order 表，风哥给出如下建索引操作：

```sql

-- 等值查询索引

CREATE INDEX idx_order_no ON fgedudb.t_order(order_no);

-- 范围查询索引

CREATE INDEX idx_order_ctime ON fgedudb.t_order(create_time);

-- 复合索引，注意字段顺序，区分度高的放前面

CREATE INDEX idx_order_user_time ON fgedudb.t_order(user_id, create_time);

-- 部分索引，仅对有效订单建立索引

CREATE INDEX idx_order_active ON fgedudb.t_order(order_no) WHERE status = '1';

```

### 6.3 索引失效场景分析

索引并非建了就一定能用上。常见导致索引失效的场景包括：

- 在索引列上使用函数，如 WHERE upper(order_no) = 'AB'，此时可用表达式索引解决。

- 对索引列做隐式类型转换。

- 统计信息过期导致规划器低估行数而选择全表扫描。

- 小表上全表扫描成本本就低于索引扫描，属正常行为。

针对函数导致索引失效的情况，可以建立表达式索引：

```sql

CREATE INDEX idx_order_upper_no ON fgedudb.t_order(upper(order_no));

```

### 6.4 索引膨胀检测与重建

随着数据增删改，索引会出现膨胀，占用过多空间且扫描变慢。通过 pg_stat_user_indexes 视图可以判断索引有效性：

```sql

SELECT relname, indexrelname, idx_scan, idx_tup_read

FROM pg_stat_user_indexes

ORDER BY idx_scan;

```

idx_scan 为 0 的索引说明从未被使用，可以考虑删除。索引膨胀情况可以通过 pgstattuple 扩展检测：

```sql

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstatindex('idx_order_no');

```

对于膨胀明显的索引，使用 REINDEX 进行重建，可采用并发重建方式避免锁表：

```sql

REINDEX INDEX CONCURRENTLY idx_order_no;

```

> 风哥 itpux-com

索引优化告一段落，接下来是 SQL 语句层面的分析与优化。

## 七、SQL 语句层面分析与优化经验

SQL 语句的优化直接决定单个查询的性能。本部分演示如何使用执行计划定位慢 SQL，并针对典型场景进行优化。

### 7.1 慢 SQL 定位

首先通过慢查询日志确认哪些 SQL 需要优化。在 fgedu-net-cn1 上查看日志：

```bash

tail -f /fgedudb/fgedudb_log/postgresql-*.log

```

也可以利用 pg_stat_statements 扩展统计 Top 慢 SQL。先在配置中启用：

```ini

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000

pg_stat_statements.track = all

```

重启数据库后创建扩展：

```sql

CREATE EXTENSION pg_stat_statements;

```

查询执行时间最长的 Top 10 SQL：

```sql

SELECT query, calls, total_exec_time, mean_exec_time, rows

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 10;

```

### 7.2 执行计划解读与 EXPLAIN ANALYZE

对慢 SQL 使用 EXPLAIN ANALYZE 获取真实执行计划：

```sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 

SELECT order_no, user_id, amount 

FROM fgedudb.t_order 

WHERE create_time >= '2025-01-01' AND create_time < '2025-02-01';

```

输出中需要重点关注以下几个关键字：

- Seq Scan：全表扫描，数据量大时通常是性能瓶颈。

- Index Scan / Index Only Scan：走索引，通常较优。

- Bitmap Heap Scan：位图堆扫描，多条件场景下常见。

- Nested Loop / Hash Join / Merge Join：连接方式，Nested Loop 在小表驱动大表时较优。

- 实际行数与预估行数严重不符：说明统计信息过期，需要 ANALYZE。

### 7.3 统计信息更新

当执行计划中预估行数与实际行数偏差大时，需要更新统计信息：

```sql

ANALYZE fgedudb.t_order;

```

建议开启自动统计信息收集，相关参数：

```ini

autovacuum_analyze_threshold = 50

autovacuum_analyze_scale_factor = 0.1

```

### 7.4 典型 SQL 优化案例

**案例一：全表扫描改为索引扫描**

原 SQL 对 order_no 等值过滤，但 order_no 未建索引，导致 Seq Scan。优化方法为建立索引：

```sql

CREATE INDEX idx_order_no ON fgedudb.t_order(order_no);

```

重新执行 EXPLAIN ANALYZE，确认执行计划由 Seq Scan 变为 Index Scan。

**案例二：避免函数作用于索引列**

原 SQL 为 WHERE substr(order_no,1,4)='2025'，索引失效。改为范围条件：

```sql

SELECT * FROM fgedudb.t_order 

WHERE order_no >= '202501' AND order_no < '202502';

```

**案例三：减少 SELECT * 与排序开销**

避免取出不必要的大字段，并针对排序字段建立索引：

```sql

SELECT order_no, amount FROM fgedudb.t_order ORDER BY create_time DESC LIMIT 100;

-- 配套索引

CREATE INDEX idx_order_ctime ON fgedudb.t_order(create_time DESC);

```

### 7.5 生产环境应急问题快速处理

在生产环境中，慢 SQL 往往伴随高并发一起出现，需要快速止血。风哥给出几种应急处理方法：

1. 使用 pg_terminate_backend 终止长时间运行且占用资源的会话：

```sql

SELECT pid, state, now()-query_start AS duration, query 

FROM pg_stat_activity 

WHERE state = 'active' AND now() - query_start > interval '300 seconds';

SELECT pg_terminate_backend(pid) FROM pg_stat_activity 

WHERE state = 'active' AND now() - query_start > interval '300 seconds';

```

2. 排查锁等待，定位阻塞会话：

```sql

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid

FROM pg_stat_activity blocked

JOIN pg_stat_activity blocking ON blocking.pid = ANY(blocked.pg_blocking_pids())

WHERE blocked.state = 'active';

```

3. 快速清理膨胀表，使用 VACUUM FULL 回收空间：

```sql

VACUUM FULL VERBOSE fgedudb.t_order;

```

> 风哥教程 113257174

SQL 层面优化完成，下面我们进入性能数据的采集与分析诊断。

## 八、AWR 性能采集与分析诊断

AWR 是自动工作负载仓库（Automatic Workload Repository）的简称，源自 Oracle，用于周期性采集性能快照并生成诊断报告。PostgreSQL 没有内置原生 AWR，但风哥在本文中演示通过扩展与脚本实现等效的周期性采集与报告分析。

### 8.1 采集环境准备

在 fgedu-net-cn1 上启用 pg_stat_statements 作为 SQL 负载数据源（前面已配置），并准备独立的快照表用于存储周期采集数据：

```sql

CREATE SCHEMA fgedu_awr;

CREATE TABLE fgedu_awr.snap (

    snap_id SERIAL PRIMARY KEY,

    snap_time TIMESTAMP DEFAULT now(),

    tps NUMERIC,

    cache_hit_ratio NUMERIC,

    db_size BIGINT,

    active_sessions INTEGER

);

```

### 8.2 周期采集脚本

编写一个 Shell 脚本在操作系统层面周期采集数据库指标并写入快照表。脚本内容如下：

```bash

#!/bin/bash

# 采集脚本：/fgedudb/scripts/awr_collect.sh

export PGHOST=127.0.0.1

export PGUSER=fgedudb

export PGPASSWORD='fgedudb_password'

/fgedudb/pgsql/bin/psql -d fgedudb -c "

INSERT INTO fgedu_awr.snap(tps, cache_hit_ratio, db_size, active_sessions)

SELECT 

  (SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname='fgedudb'),

  (SELECT round(sum(blks_hit)*100.0/sum(blks_hit+blks_read),2) 

     FROM pg_stat_database WHERE datname='fgedudb'),

  (SELECT pg_database_size('fgedudb')),

  (SELECT count(*) FROM pg_stat_activity WHERE state='active');

"

```

通过 crontab 设置每 15 分钟采集一次：

```bash

crontab -e -u fgedudb

*/15 * * * * /bin/bash /fgedudb/scripts/awr_collect.sh >> /fgedudb/scripts/awr_collect.log 2>&1

```

### 8.3 报告生成与分析

查询两个快照之间的指标差异，即形成一段时间的性能增量报告：

```sql

SELECT 

  (SELECT tps FROM fgedu_awr.snap WHERE snap_id=100) AS tps_begin,

  (SELECT tps FROM fgedu_awr.snap WHERE snap_id=120) AS tps_end,

  (SELECT tps FROM fgedu_awr.snap WHERE snap_id=120) - 

  (SELECT tps FROM fgedu_awr.snap WHERE snap_id=100) AS tps_diff,

  (SELECT cache_hit_ratio FROM fgedu_awr.snap WHERE snap_id=120) AS hit_ratio

;

```

分析要点：缓存命中率应长期保持在 99% 以上，若低于 95% 则需要排查 shared_buffers 是否偏小或存在大量随机读；TPS 若呈持续下降趋势，需要结合慢 SQL 与锁等待进一步诊断。

> 风哥数据库教程 itpux-com

## 九、性能定期监控分析与问题诊断

性能优化不是一次性的，必须建立定期监控与巡检机制，才能在问题萌芽时及时发现。本节演示基于 fgedu-net-cn1、fgedu-net-cn2 的日常监控方法。

### 9.1 操作系统级监控

定期使用 sar、vmstat、iostat 观察资源走势：

```bash

# 每 2 秒采样 10 次，观察内存与CPU

vmstat 2 10

# 观察磁盘 I/O 使用率与等待时间

iostat -x 1 10

# 查看历史负载，用于回看问题时段

sar -q

sar -r

```

### 9.2 数据库级监控

通过系统视图实时掌握数据库运行状态：

```sql

-- 数据库整体状态

SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit

FROM pg_stat_database;

-- 当前活跃会话

SELECT pid, usename, state, wait_event_type, wait_event, query

FROM pg_stat_activity WHERE state = 'active';

-- 表级 I/O 统计

SELECT relname, seq_scan, idx_scan, seq_tup_read, idx_tup_fetch

FROM pg_stat_user_tables ORDER BY seq_scan DESC;

```

### 9.3 日常巡检脚本

风哥推荐将常见检查项固化为巡检脚本，定期执行。示例脚本片段：

```bash

#!/bin/bash

# 巡检脚本：/fgedudb/scripts/check_pg.sh

export PGHOST=127.0.0.1

export PGUSER=fgedudb

echo "===== 数据库连接数 ====="

/fgedudb/pgsql/bin/psql -d fgedudb -c "SELECT count(*) FROM pg_stat_activity;"

echo "===== 缓存命中率 ====="

/fgedudb/pgsql/bin/psql -d fgedudb -c "SELECT round(sum(blks_hit)*100.0/sum(blks_hit+blks_read),2) AS hit_ratio FROM pg_stat_database;"

echo "===== 最长事务 ====="

/fgedudb/pgsql/bin/psql -d fgedudb -c "SELECT pid, now()-xact_start AS xact_age FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 5;"

echo "===== 死锁与等待事件 ====="

/fgedudb/pgsql/bin/psql -d fgedudb -c "SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE wait_event IS NOT NULL GROUP BY 1,2;"

```

设置每周执行一次并输出结果到日志，形成周度巡检报告：

```bash

crontab -e -u fgedudb

0 2 * * 0 /bin/bash /fgedudb/scripts/check_pg.sh > /fgedudb/scripts/check_report_$(date +%Y%m%d).log 2>&1

```

### 9.4 常见问题诊断要点

风哥针对 PostgreSQL 高频故障整理出以下诊断要点：

- CPU 飙高：优先检查是否出现大量全表扫描或哈希连接，结合 pg_stat_statements 定位慢 SQL。

- 内存不足/换页：检查 shared_buffers 是否过大，结合 swappiness 与脏页参数调整。

- I/O 瓶颈：检查 WAL 目录是否与数据文件争用磁盘，结合 iostat 的 await、util 判断。

- 锁等待：通过 pg_locks 与 pg_stat_activity 定位阻塞链。

- 事务回卷风险：长期不清理导致 xid 回卷，务必保证 autovacuum 正常并关注 age。

```sql

-- 查看事务年龄，防范事务回卷

SELECT datname, age(datfrozenxid) AS age

FROM pg_database ORDER BY age DESC;

```网上搜索风哥教程可以学习全套数据库教程

## 十、自动化调优与顾问建议

在人工调优经验沉淀的基础上，风哥建议引入自动化手段提升效率。主要包括：

- 参数基线管理：将调优后的参数固化到配置文件并纳入版本管理，避免变更失控。

- 自动化采集：通过定时任务与脚本实现指标、慢 SQL、锁等待的自动采集。

- 阈值告警：对 CPU、内存、命中率、长事务等关键指标设置阈值，触发自动告警。

- 定期报告：每周自动生成性能健康报告，供运维与开发复盘。上51CTO搜索风哥可以学习全套数据库教程

自动化建议并不复杂，核心在于把风哥本文所讲的操作系统、存储、参数、索引、SQL 各层的优化经验固化为可重复执行的流程。

## 十一、风哥针对本文总结

风哥针对本文做如下总结：PostgreSQL 的性能分析与优化调整是一项系统性工程，必须按照"先诊断、后调优、一次只改一个参数"的原则推进。本文以 64G 内存、8 CPU 的 fgedu-net-cn1、fgedu-net-cn2 环境为例，依次讲解了优化概述与方案、操作系统层内核与透明大页优化、存储系统层目录与表空间分离、数据库层内存与并发参数调整、索引类型选择与膨胀处理、SQL 执行计划分析与慢 SQL 优化、AWR 周期采集与报告解读、日常监控巡检以及自动化调优建议。

在整个过程中，读者应当掌握几个核心能力：能够使用系统工具与数据库视图快速定位瓶颈所在的层次；能够针对内存、CPU、I/O、锁等待制定对应的调整方案；能够通过 EXPLAIN ANALYZE 与 pg_stat_statements 定位并优化慢 SQL；能够建立周期性的采集与巡检机制，将性能问题消灭在萌芽状态。数据目录统一规划为 /fgedudb，实例与账号使用 fgedudb，主机规划为 fgedu-net-cn1、fgedu-net-cn2，均可直接复用到生产部署中。

性能优化没有一劳永逸的银弹，业务在变、数据在变，优化也需要持续迭代。希望读者把风哥本文的方法论落到日常运维中，形成自己的性能分析与调优能力，从而让 PostgreSQL 数据库持续稳定高效地运行。