超级会员免费看
PostgreSQL 扩展与性能调优
技术要求
要进行相关操作，你需要有一个至少配备两个 CPU 和 2GB 内存的 PostgreSQL 服务器，并且能够从 PGDG 仓库和 GitHub 上通过互联网下载 RPM 包。
安装和创建 pg_repack 以在线重建对象
在 PostgreSQL 中，没有单独的 UNDO 存储，UNDO 记录以多版本形式存储在同一个表中。这些 UNDO 记录或死元组可以通过自动清理或手动清理来清除，但除非空页面位于堆的末尾，否则操作系统可能无法回收空间。未调整好的清理设置可能会导致更严重的问题，比如由于数据膨胀，查询可能需要扫描 100 个页面而不是 10 个页面。在这种情况下，唯一的选择就是重建表。虽然可以使用 VACUUM FULL 来重建表，但它会对表加排他锁，不允许对表进行读写操作，这在生产数据库中通常是要避免的。因此，我们可以使用 pg_repack 扩展来在线重建数据库对象，如表格和索引。
pg_repack （https://github.com/reorg/pg_repack）是一个开源扩展，它可以帮助管理员或开发人员执行以下操作：
- 在线重建表（在线 VACUUM FULL ）
- 在线重建表的索引
- 在线将表或索引迁移到另一个表空间
- 在线对表进行聚类（按聚类索引排序）
准备工作
要使用 pg_repack 在线重建表，表必须在非空列上有主键或唯一总索引（但不能是函数索引）。 pg_repack 的 RPM 和 deb 包可在 PGDG 仓库中获取，并且这些包是针对每个 PostgreSQL 版本特定的。
安装步骤
在 CentOS 上安装 pg_repack
- 若 PGDG 仓库不存在，则进行安装：
$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
你可以访问 此链接 找到适合你 Linux 发行版的 RPM 包。
2. 使用 YUM 安装 pg_repack ：
$ sudo yum install pg_repack13
- 使用版本和帮助选项验证安装（不应出现任何错误）：
$ pg_repack --version
$ pg_repack --help
在 Ubuntu 上安装 pg_repack
- 若 PGDG 仓库不存在，则使用以下命令进行安装：
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
- 使用 apt 安装
pg_repack：
$ apt install postgresql-13-repack
- 使用版本和帮助选项验证安装（不应出现任何错误）。如果
PATH未设置，可能需要使用 PostgreSQL 二进制文件的完整路径：
$ pg_repack --version
$ pg_repack --help
工作原理
此安装过程针对 CentOS/RedHat 和 Ubuntu/Debian 发行版。分为两个部分，第一部分给出了在 CentOS 上安装 pg_repack 的步骤，第二部分给出了在 Ubuntu 上的安装步骤。可以从 PGDG 仓库下载并安装最新的包，使用 YUM 或 APT 进行安装。需要注意的是，CentOS 和 Ubuntu 上的包名不同，在 CentOS 上安装 pg_repack13 包，而在 Ubuntu 上安装 postgresql-13-repack 包。安装完成后，可以使用版本和帮助命令行选项来验证安装是否成功。
在线重建表的操作步骤
准备工作
要使用 pg_repack ，必须在包含要重建表的数据库中安装并创建该扩展：
$ psql -d percdb -c "CREATE EXTENSION pg_repack"
要重建的表必须在非空列上有主键或唯一索引。在对数据库服务器进行任何维护之前，建议进行有效的完整备份，以便进行时间点恢复。
操作步骤
- 使用
--dry-run验证表是否可以使用pg_repack重建：
$ pg_repack --dry-run -d database_name --table schemaname.tablename
示例输出：
$ pg_repack --dry-run -d percdb --table public.pgbench_accounts
INFO: Dry run enabled, not executing repack
INFO: repacking table "public.pgbench_accounts"
- 当干运行成功后，对表执行
pg_repack：
$ pg_repack -d percdb --table public.pgbench_accounts
示例输出：
INFO: repacking table "public.pgbench_accounts"
- 若要使用并行进程运行
pg_repack，可使用-j选项。以下语法使用四个并行进程：
$ pg_repack -d percdb -j 4 --table public.pgbench_accounts
示例输出：
NOTICE: Setting up workers.conns
INFO: repacking table "public.pgbench_accounts"
工作原理
要在线重建表，前提是必须在包含该表的数据库中创建 pg_repack 扩展。创建后，可以先进行干运行，以查看表是否适合使用 pg_repack 进行在线重建。如果干运行成功，可以使用单个进程或多个并行进程来更快地完成重建。但要确保生产服务器上有空闲的 CPU 资源。
在线重建表的索引
在 PostgreSQL 中，如果没有进行适当的清理或定期的索引维护，索引可能会变得非常臃肿。虽然可以使用 CONCURRENTLY 选项在线创建索引，但切换索引到其原始名称或重建主键索引或唯一索引可能需要额外的注意。使用 pg_repack 可以轻松完成这些操作。
准备工作
目前对哪些索引可以或不可以重建没有限制，但在使用 pg_repack 重新打包特定索引或表的所有索引之前，建议进行干运行测试。
操作步骤
重建表的所有索引
- 使用
-x参数进行干运行验证：
$ pg_repack --dry-run -d migops -t public.emp -x
示例输出：
INFO: Dry run enabled, not executing repack
INFO: repacking indexes of "public.emp"
INFO: repacking index "public.idx_1"
INFO: repacking index "public.idx_2"
INFO: repacking index "public.idx_3"
INFO: repacking index "public.idx_4"
- 移除
--dry-run参数，执行索引重建：
$ pg_repack -d migops -t public.emp -x
示例输出：
INFO: repacking indexes of "public.emp"
INFO: repacking index "public.idx_1"
INFO: repacking index "public.idx_2"
INFO: repacking index "public.idx_3"
INFO: repacking index "public.idx_4"
重建特定索引
- 使用
-i参数指定要重建的索引，并进行干运行：
$ pg_repack --dry-run -d migops -i public.pgbench_accounts_pkey
示例输出：
INFO: Dry run enabled, not executing repack
INFO: repacking index "public.pgbench_accounts_pkey"
- 当干运行成功后，执行索引重建：
$ pg_repack -d migops -i public.pgbench_accounts_pkey
示例输出：
INFO: repacking index "public.pgbench_accounts_pkey"
工作原理
使用 pg_repack 重建表的索引非常简单。要重建表的所有索引，可以指定 -x 参数和表名；要重建特定索引，只需指定索引名（包含模式前缀）。在进行重建之前，建议先进行干运行。
在线将表或索引移动到另一个表空间
随着时间的推移，表或数据库的大小可能会变得非常大，包含数据目录的磁盘可能会因 IOPS 饱和。因此，我们希望将选定的表或索引分散到另一个磁盘的不同表空间中。虽然可以使用 ALTER TABLE 来实现，但这不是一个在线操作。所以，我们可以使用 pg_repack 在线将表或索引移动到另一个表空间。
准备工作
要使用 pg_repack 在线将表或索引移动到另一个表空间，需要在包含该表或索引的数据库中创建 pg_repack 扩展。如果表没有主键或非空唯一键，则无法在线移动该表。目标表空间必须提前创建， pg_repack 不会创建表空间。如果目标表空间不存在，可以手动创建：
$ sudo mkdir -p /tblspc_dir/new_tblspc
$ sudo chown -R postgres:postgres /tblspc_dir/new_tblspc
$ sudo chmod -R 700 /tblspc_dir/new_tblspc
$ psql -c "CREATE TABLESPACE new_tblspc LOCATION '/tblspc_dir/new_tblspc'"
操作步骤
移动表到另一个表空间
- 使用以下命令进行干运行并在线移动表：
$ pg_repack --dry-run -d migops -t public.pgbench_accounts --tablespace=new_tblspc
示例输出：
INFO: Dry run enabled, not executing repack
INFO: repacking table "public.pgbench_accounts"
- 使用以下命令仅移动表而不移动其索引到新表空间：
$ pg_repack -d migops -t public.pgbench_accounts --tablespace=new_tblspc
示例输出：
INFO: repacking table "public.pgbench_accounts"
- 使用以下命令将表及其索引一起移动到新表空间：
$ pg_repack -d migops -t public.pgbench_accounts -S --tablespace=new_tblspc
示例输出：
INFO: repacking table "public.pgbench_accounts"
移动索引到另一个表空间
- 进行干运行：
$ pg_repack --dry-run -d migops -i public.idx_1 --tablespace=new_tblspc
示例输出：
INFO: Dry run enabled, not executing repack
INFO: repacking index "public.idx_1"
- 执行索引移动：
$ pg_repack -d migops -i public.idx_1 --tablespace=new_tblspc
示例输出：
INFO: repacking index "public.idx_1"
工作原理
可以使用 --tablespace 标志或 -s 来指定表或索引要在线移动到的表空间。在移动表时，默认情况下表的索引不会移动到新表空间，除非指定 -S 选项。对于索引，只需指定索引名和表空间名即可轻松将其移动到另一个表空间。
使用 pg_prewarm 预热缓存
数据库集群重启后，可能需要一些时间才能将整个缓存加载到 PostgreSQL 缓冲区缓存或操作系统缓冲区缓存中。有时，我们可能希望在执行某些特殊报告任务之前按需将某些表加载到缓存中。为了帮助将表或特定的页面集缓存到操作系统缓存或数据库缓冲区缓存（共享缓冲区）中，有一个名为 pg_prewarm 的扩展。
pg_prewarm 是一个函数，它可以接受五个参数：
pg_prewarm(regclass, mode text default 'buffer', fork text default 'main', first_block int8 default null, last_block int8 default null) RETURNS int8
各参数详细说明如下：
| 参数 | 说明 |
| ---- | ---- |
| regclass | 完全限定的表名（模式名.表名） |
| mode | 可以选择以下三种预热方法之一：
- prefetch：向操作系统发出异步预取请求，缓存在操作系统中。
- read：同步预取，缓存在操作系统中。
- buffer：将页面读入共享缓冲区，默认方法。 |
| fork | 要预热的关系分支，默认是 main ，可以取值为 main 、 fsm 和 vm |
| first_block | 要预热的第一个块的编号，默认是 NULL ，表示第 0 个块 |
| last_block | 要预热的最后一个块的编号，默认是 NULL ，表示最后一个块 |
如果获取的块数无法容纳在所需的缓存中，则编号较低的块可能会被逐出。
准备工作
pg_prewarm 是 contrib 模块中的一个扩展，在 Ubuntu 上需要安装 postgresql-contrib-13 包，在 CentOS/Red Hat 上需要安装 postgresql13-contrib 包。
操作步骤
- 在包含要缓存表的数据库中创建扩展：
$ psql -d migops -c "CREATE EXTENSION pg_prewarm"
- 使用
pg_prewarm预热public.pgbench_accounts表：
$ psql -d migops -c "select pg_prewarm('public.pgbench_accounts')"
示例输出：
pg_prewarm
------------
16394
(1 row)
- 如果希望在重启后自动将缓存预热到关机时的状态，可以将
pg_prewarm包含在shared_preload_libraries中。否则，继续执行步骤 2：
$ psql -c "ALTER SYSTEM SET shared_preload_libraries TO pg_prewarm"
# Changes to shared_preload_libraries requires a restart of the PostgreSQL cluster
$ pg_ctl -D $PGDATA restart -mf
- 检查当
pg_prewarm被添加到shared_preload_libraries时，数据目录中是否存在autoprewarm.blocks文件。如果PGDATA环境变量未设置，需要将$PGDATA替换为数据目录的完整路径：
$ ls -alrth $PGDATA/autoprewarm.blocks
示例输出：
-rw-------. 1 postgres postgres 5.2K Aug 24 03:08 /var/lib/pgsql/12/data/autoprewarm.blocks
会启动一个后台工作进程，每隔 pg_prewarm.autoprewarm_interval 秒将共享缓冲区的内容转储到 autoprewarm.blocks 文件中：
migops=# select name, setting from pg_settings where name like 'pg_prewarm%';
示例输出：
name | setting
---------------------------------+---------
pg_prewarm.autoprewarm | on
pg_prewarm.autoprewarm_interval | 300
(2 rows)
$ ps -eaf | grep prewarm
示例输出：
postgres 5637 5629 0 02:58 ? 00:00:00 postgres: autoprewarm master
工作原理
pg_prewarm 可以手动预热缓存，也可以让它自动进行。前两个步骤展示了如何在数据库中创建 pg_prewarm 扩展以及如何手动缓存表。如果希望在崩溃或重启后让 PostgreSQL 自动将共享缓冲区的内容转储以预热缓存，可以将 pg_prewarm 加载到 shared_preload_libraries 中。在这种情况下，会启动一个后台工作进程将共享缓冲区的内容转储到 autoprewarm.blocks 文件中。可以使用两个参数来修改自动预热的频率和行为。
使用 plprofiler 调优函数或存储过程
plprofiler 是一个扩展或工具，可用于对 PostgreSQL 中的函数进行广泛的性能分析。作为扩展启用后，可以收集数据库中执行的所有 PL/pgSQL 代码的分析数据（按需但非自动）。这些分析数据存储在内部哈希表中，并可以使用外部工具以 HTML 报告的形式呈现。
plprofiler 有两种操作模式：
- 会话级别 ：在将 Oracle 数据库或其他数据库迁移到 PostgreSQL 时，或者在通过函数开发新的应用程序功能时，开发人员可能希望查看每行代码的执行时间。这有助于开发人员调优耗时的查询或调整可能导致函数执行延迟的条件逻辑。为此，可以在会话级别启用 plprofiler 并执行函数，这也可以称为本地数据。
- 全局级别 ：如果开发人员或管理员希望捕获全局执行的每个函数的分析数据，则需要进行全局级别的性能分析。可以生成 HTML 报告，显示在监控期间全局执行的每个函数的每行代码的执行时间。
后续将介绍如何从源代码构建 plprofiler 以及如何使用它。
构建和使用 plprofiler
从源代码构建 plprofiler
要从源代码构建 plprofiler ，通常需要以下步骤：
1. 获取源代码 ：从官方仓库或可靠的代码源下载 plprofiler 的源代码。
2. 配置编译环境 ：确保系统中安装了必要的编译工具，如 gcc 、 make 等，以及 PostgreSQL 的开发头文件。
3. 编译和安装 ：进入源代码目录，执行以下命令进行编译和安装：
$ make
$ make install
使用 plprofiler
会话级别使用
- 启用 plprofiler ：在会话中启用
plprofiler扩展：
LOAD 'plprofiler';
- 执行函数并收集数据 ：执行需要分析的函数：
-- 假设存在一个名为 test_function 的函数
SELECT test_function();
- 生成报告 ：使用外部工具（如专门的脚本）从内部哈希表中提取分析数据并生成 HTML 报告。
全局级别使用
- 配置全局启用 ：在
postgresql.conf文件中添加以下配置：
shared_preload_libraries = 'plprofiler'
- 重启数据库 ：使配置生效：
$ pg_ctl -D $PGDATA restart
- 监控和生成报告 ：在监控期间，
plprofiler会自动收集所有函数的分析数据。在需要时，使用外部工具生成 HTML 报告。
总结
本文介绍了多个用于 PostgreSQL 扩展与性能调优的工具和方法，下面通过一个流程图来总结整个调优过程：
graph LR
classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px;
classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;
A([开始]):::startend --> B(安装和使用 pg_repack):::process
B --> B1(安装 pg_repack):::process
B1 --> B11(在 CentOS 安装):::process
B1 --> B12(在 Ubuntu 安装):::process
B --> B2(在线重建表):::process
B --> B3(在线重建索引):::process
B --> B4(在线移动表或索引到表空间):::process
A --> C(使用 pg_prewarm 预热缓存):::process
C --> C1(安装和创建扩展):::process
C --> C2(手动或自动预热):::process
A --> D(使用 plprofiler 调优函数):::process
D --> D1(构建 plprofiler):::process
D --> D2(会话级别使用):::process
D --> D3(全局级别使用):::process
B --> E(验证和测试):::process
C --> E
D --> E
E --> F([结束]):::startend
通过使用 pg_repack ，可以在不影响数据库正常读写的情况下重建表、索引以及移动表和索引到其他表空间，有效解决数据膨胀和磁盘 IO 饱和等问题。 pg_prewarm 能够帮助我们在需要时快速将表加载到缓存中，提高查询性能。而 plprofiler 则提供了详细的函数性能分析，有助于开发人员和管理员找出性能瓶颈并进行优化。
在实际应用中，建议根据具体的业务需求和数据库状况选择合适的工具和方法。例如，在生产环境中进行表重建时，优先考虑使用 pg_repack 进行在线操作，避免长时间的停机。在进行复杂函数开发时，使用 plprofiler 进行性能分析，确保函数的高效执行。同时，定期使用 pg_prewarm 预热常用表，提高系统的响应速度。
总之，合理运用这些扩展和工具，可以显著提升 PostgreSQL 数据库的性能和稳定性，为业务的顺利运行提供有力支持。
扫一扫
378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
