Linux 上的 EPAS和PostgreSQL的常规配置和调优建议

在为 PostgreSQL 设计裸机服务器时，需要考虑几个因素。这些包括CPU、RAM、磁盘，在少数情况下还包括网卡。

CPU

选择正确的CPU可能是PostgreSQL性能的关键点。在处理较大的数据时，CPU速度很重要，但具有较大 L3 缓存的CPU也会提高性能。对于OLTP性能，拥有更多更快的内核将有助于操作系统和PostgreSQL更有效地利用它们。另一方面，使用具有较大L3缓存的 CPU 适用于较大的数据集。

CPU至少有2个高速缓存：L1（又名主高速缓存）和L2（又名二级高速缓存）。L1是最小、最快的高速缓存，嵌入在CPU核心中。L2缓存仅比L1慢一点，但比L1要大。L2用于为L1缓存提供数据。

与每个内核独有的L1和L2高速缓存不同，L3高速缓存是内核之间共享的。与L1和 L2缓存相比，L3缓存的速度较慢，但它是由所有可用内核共享的。但L3缓存仍然比内存 快。拥有更大的L3缓存可以在处理更多数据时提高CPU性能。这也有利于PostgreSQL的并行查询。

内存

操作系统倾向于利用可用内存并尝试缓存尽可能多的数据。更多的缓存最终会带来更少的磁盘I/O和更快的查询时间。选择硬件时，建议首先添加尽可能多的RAM。从成本方面和技术方面来看，将来添加更多RAM会更加昂贵（除非您有带热插拔RAM的系统，否则需要停机）。有多个PostgreSQL参数将根据可用内存进行更改。

磁盘

如果应用程序受I/O限制（读取和/或写入密集型），则选择更快的磁盘会显着提高性能。如NMVe和SSD硬盘。

第一条经验是将WAL磁盘与数据磁盘分开。WAL可能是瓶颈（在写入密集型数据库上），因此将WAL保留在单独且快速的磁盘驱动器上将解决此问题。始终至少使用RAID1，在某些情况下，如果数据库确实写入大量数据，可能需要 RAID 10。

为索引和数据使用单独的表空间和驱动器也会提高性能，尤其是PostgreSQL在SATA 驱动器上运行时。SSD和NVMe驱动器通常不需要这样做。我们建议对数据使用RAID 10。

网卡

尽管网卡看起来与PostgreSQL的性能无关，但当数据大量增长时，更快或绑定的网卡也会加快基础备份的速度。

与裸机相比，虚拟机由于虚拟化层而存在性能缺陷。此外，由于共享资源，可用的CPU和磁盘I/O性能也会减少。

有一些技巧可以让PostgreSQL在虚拟机中可以获得更好的性能：

可以考虑将虚拟机绑定到特定的CPU和磁盘。这将消除（或限制）由于主机上运行其他虚拟机而出现的性能瓶颈。

考虑在安装之前预先分配（pre-allocating）磁盘。这会避免主机在数据库操作期间分配磁盘空间。如果不能这样做（如在云环境），可以在postgresql.conf中更改这两个参数：

建议为EPAS/PG集群设置具有专用IOP的单独挂载点。为了获得更好的性能，建议使用SSD磁盘。

• /pgdata/16: PAS/PG 数据目录的挂载点

  PGDATA目录: /pgdata/16/data

• /pgwaldata/16: WAL挂载点

  WAL目录: pgwaldata/16/wal

根据索引的使用情况以及用户定义表所需的索引数量，根据工作负载，建议为索引设置单独的挂载点。同时建议将PostgreSQL日志目录（默认情况下位于 PGDATA/log 中）保留在具有正确权限的单独安装点上，以使其可供其他进程或用户使用。

建议对PG/EPAS数据目录、WAL以及托管PG/EPAS数据的任何其他安装点使用XFS文件系统。

atime

PostgreSQL不依赖数据文件的atime（上次访问文件的时间戳），因此禁用它们将节省CPU周期。在/etc/fstab文件中，设置保存PostgreSQL数据和WAL文件的驱动器的默认值附近添加 noatime。

/dev/mapper/pgdata-01-data pgdata xfs     defaults,noatime 1 1

mount -o remount,noatime,nodiratime pgdata

Read-ahead 预读

增加磁盘提前读取可通过减少对磁盘的请求数来提高I/O吞吐量。这通常设置为128kB，但通常建议在托管数据库或表空间的磁盘上将其设置为4096kB。

可以使用 tuned 守护程序调整预读参数。

I/O调度器

对于CentOS/RHEL7建议使用deadline I/O调度器，对于Rocky/RHEL8使用mq-deadline作为I/O调度器。对于配备SSD或具有自己的重新排序机制的专用IO控制器的系统，建议对RHEL7使用noop，对RHEL8建议使用none。

脏内存

Linux 系统通常将脏内存定义为与总RAM大小的比率。一旦超过这个数量的内存被使用和修改而没有提交，Linux系统将进入顺序访问模式，并将阻塞与脏内存刷新到磁盘无关的所有IO请求。此类事件极具破坏性。由于数据库写入访问模式，随着数据量的增加，发生这种情况可能性大大增加。

在现代硬件和某些虚拟机中，即使1%的系统 RAM 所代表的数据量也可能超过后端存储系统在没有大量IO等待的情况下可以吸收的数据量。这就是为什么现代Linux内核提供了以字节为单位而不是比率来设置脏内存。建议的设置应约为底层存储设备或控制器的缓存大小。在这些值未知的情况下，1GB是合适的默认值。

除了将脏内存视为总可用RAM的比率之外，当前的 Linux 系统还倾向于仅在脏内存超过vm.dirty_background_ratio规定的阈值后才在后台将脏内存写入磁盘。此设置的默认值有所不同，但通常为RAM 的 5-10%，并且不能低于1%。

与vm.dirty_bytes类似，现代内核提供了以字节为单位设置后台写入阈值的能力，而不是使用vm.dirty_background_bytes。这里通常使用脏字节分配大小的1/4值。这使得内核可以在数据达到我们在vm.dirty_bytes 中指定的值之前将数据慢慢传输到磁盘子系统，并且还可以防止由于使用太小的值而导致过多的后台写入。

大多数存储子系统缓存都不会很大，为1-4GB，因此鼓励后台写入，同时避免存储活动开销是一种微妙的平衡行为。将dirty_background_bytes设置为dirty_bytes的1/4是一个简单的初步近似值，稍后可以根据环境进行调整

以下是 EDB 使用上述指南针对 EPAS/PostgreSQL 调整 Linux 操作系统的建议：

mkdir etc/tuned/edb


export DIRTY_BYTES=$[1024*1024*1024]
export DIRTY_BG=$[${DIRTY_BYTES}/4]
export MEM_TOTAL=$(grep MemTotal /proc/meminfo | awk '{print $2}')


cat<<EOF>/etc/tuned/edb/tuned.conf
[main]
summary=Tuned profiles for EnterpriseDB Postgres Advanced Server
[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
[disk]
readahead=4096
[sysctl]
vm.overcommit_memory=2
vm.overcommit_kbytes=${MEM_TOTAL}
vm.swappiness=1
vm.dirty_bytes=${DIRTY_BYTES}
vm.dirty_background_bytes=${DIRTY_BG}
[vm]
transparent_hugepages=never
EOF


systemctl enable --now tuned
tuned-adm profile edb

初始化 EPAS/PG 集群

从以下 initdb 命令开始引导 EPAS PGDATA 文件夹：

PGSETUP_INITDB_OPTIONS="-X /pgwaldata/16/wal --pgdata=/pgdata/16/data" 
/usr/edb/as16/bin/edb-as16-setup initdb

systemctl edit edb-as-16

[Service]
Environment=PGDATA=/pgdata/16/data

systemctl enable --now edb-as-16

sudo -u enterprisedb rmdir /var/lib/edb/as16/data
sudo -u enterprisedb ln -s /pgdata/16/data /var/lib/edb/as16/data
systemctl enable --now edb-as-16

最大连接数

max_connections的最佳最大数量大约是CPU核心数量的4倍。该公式通常给出一个非常小的数字，这在大多数现实情况下并不实用，因此建议使用公式：GREATEST(4xCPU cores,100)。超出此数字，应使用连接池程序，例如 pgbouncer。

密码加密

建议使用 scram-sha-256 作为参数。

shared_buffers共享缓冲区

这个参数的变动幅度最大。一些工作负载在非常小的值（例如1GB或2GB）下效果最佳，即使在非常大的数据库容量情况下也是如此。其他工作负载则需要较大的值。一个合理的起始点是使用总内存的四分之一，更细致的版本则通过以下伪代码来考虑内存变动的更多情况：

base = RAM / 4


if RAM < 3 GB:
   base = base * 0.5
else if RAM < 8 GB:
   base = base * 0.75
else if RAM > 64 GB:
   base = greatest(16 GB, RAM / 6)


shared_buffers = least(base, 64 GB)

这说明了这样一个事实：较低内存的系统在处理用户会话的同时，对专用于Postgres 共享缓冲区的大量内存的容忍度较低。具有更高RAM服务器可以吸收按比例调整的共享缓冲区设置。然而，超过 64GB，由于维护如此大的连续内存分配相关的开销，回报会递减。

work_mem工作内存

work_mem 的建议起始点是 ((Total RAM - shared_buffers)/(16 x CPU cores))。

maintenance_work_mem维护工作内存

这决定了用于维护操作（如VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 和数据加载操作）的最大内存量。在执行此类活动时，可能会增加数据库服务器上的 I/O，因此为它们分配更多内存可能会导致这些操作更快完成。15%x(Total RAM - shared_buffers)/autovacuum_max_workers 的计算值大于1GB 是一个好的开始。

effective_io_concurrency

该参数用于某些操作期间的预读，应设置为用于存储数据的磁盘数量。然而，通过使用该数字的倍数，性能会有改善。对于固态硬盘，建议将此值设置为200。

wal_compression

当此参数打开时，PostgreSQL 服务器会在 full_page_writes 打开时或在基本备份期间压缩写入 WAL 的全页映像。将此参数设置为“on”

wal_log_hints

为了使用 pg_rewind，需要此参数。 将其设置为“on”。

注意：如果 Postgres 实例启用了校验和，则意味着此设置始终处于激活状态。

wal_buffers

这控制了后端在同步之前放置 WAL 数据的可用内存空间量。默认情况下，每个 WAL 段为 16MB，因此缓冲一个段在内存方面非常便宜。据观察，较大的缓冲区大小可能对测试性能产生非常积极的影响。将此参数设置为64MB。

checkpoint_timeout

较长的超时会减少 WAL 总量，但会导致崩溃恢复时间更长。建议的值至少为 15 minutes，但最终，业务需求的 RPO 决定了该值应该是多少。

checkpoint_completion_target

这决定了 PostgreSQL 旨在完成检查点的时间量。这意味着检查点不需要导致 I/O 峰值，而是旨在将写入分布在特定时间段内。建议值为0.9。 如果使用的 PostgreSQL 版本大于13，则默认值为 0.9

max_wal_size

如果您的磁盘空间有限，请将 max_wal_size 设置为尽可能高的值，这样可以避免空间不足的风险，并留出一点空间。如果您的 WAL 位于专用磁盘分区上（始终建议这样做），那么为了安全起见，这可能是分区大小的 50-75%”(r)。

为了更精确地调整max_wal_size，建议您在pg_stat_bgwriter视图中监控checkpoints_timed和checkpoints_req值。如果max_wal_size太小，请求的检查点与定时检查点的比率将会上升，表明检查点正在发生，因为没有足够的空间来容纳到达下一个定时检查点所需的WAL段。

由于偶尔的活动高峰而有一些请求的检查点是完全可以的，但这不应该成为常态。根据需要增加 max_wal_size 以最大限度地减少请求的检查点数量，而不会耗尽可用磁盘空间。

archive_mode 归档模式

由于更改此项需要重新启动，因此应将其设置为“on”。

archive_command 归档命令

如果 archive_mode 打开，则需要有效的archive_command。在准备好配置归档之前，POSIX系统上的默认值为“:to be configured”。

random_page_cost

如果使用SSD盘，建议值为1.1。

effective_cache_size

是shared_buffers和Linux缓冲区高速缓存的总和（如 free 命令的 buff/cache 列中所示）。

cpu_tuple_cost

指定查询期间处理每一行的相对成本。目前设置为 0.01，但这可能低于最佳值，应增加到 0.03 以获得更实际的成本计算。

logging_collector 日志收集器

如果 log_destination 包含 stderr 或 csvlog，则此参数应为 on。

log_directory 日志目录

如果logging_collector打开，则应将其设置为数据目录之外的某个位置。 这样，日志就不再是基础备份的一部分。

log_checkpoints

这应该设置为on

log_min_duration_statement

尽早识别性能不佳的查询非常重要，因此建议将此参数设置为1s以记录运行一秒或更长时间的任何查询。这只是一个起点。如果这太冗长，请设置较高的值并修复较慢的查询，然后再降低该值。

对于事务性工作负载，我们通常认为运行时间超过250毫秒的查询很慢。对于业务分析等其他工作负载，即使运行5秒的查询也可能不会被认为是慢的。

log_line_prefix

前缀至少应包含时间、进程 ID、行号、用户和数据库以及应用程序名称。

建议值：“%m [%p]: u=[%u] db=[%d] app=[%a] c=[%h] s=[%c:%l] tx=[%v:%x]”

log_lock_waits

设置为on。该参数对于诊断慢速查询至关重要。

log_statement

设置为“ddl”。除了留下基本的审计跟踪之外，有助于确定灾难性人为错误发生的时间，例如删除错误的表。

log_temp_files

设置为0。记录创建的所有临时文件，表明 work_mem 调整不正确。

log_connections

设置为on。将记录所有连接。它对于安全目的很有用，而且还可以确定是否需要连接池或连接池是否已正确设置。

log_disconnections

设置为on。将记录所有连接。它对于安全目的很有用，而且还可以确定是否需要连接池或连接池是否已正确设置。

timed_statistics (EPAS)

控制动态运行时仪表工具架构 (DRITA) 功能的计时数据收集。设置为打开时，会收集计时数据。将此参数设置为打开。

log_autovacuum_min_duration

监视自动清理活动。建议值：0。

autovacuum_max_workers

这是autovacuum的worker数量。默认值为3，需要重新启动数据库才能更新。请注意，每张表只能有一个worker在操作。因此，增加worker只会有助于并行且更频繁地跨表进行清理。默认值较低，因此建议将此值增加到5。

autovacuum_vacuum_cost_limit

为了防止由于 autovacuum 导致数据库负载过重，Postgres施加了I/O配额。因此，每次读/写都会导致该配额耗尽，一旦耗尽，autovacuum就会休眠一段固定的时间。此配置增加了配额限制，从而增加了vacuum可以执行的I/O量。默认值较低，建议将此值增加到5000。

idle_in_transaction_session_timeout

事务中保持空闲的会话可以持有锁并防止出现vacuum。建议值：10 minutes。

lc_messages

日志分析器只能理解未翻译的消息。将其设置为“C”。

shared_preload_libraries

添加 pg_stat_statements 开销低，价值高。这是推荐的，但可选。

-----------------------------------------------------------------

<引用> General configuration and tuning recommendations for EDB Postgres Advanced Server and PostgreSQL on Linux 

https://www.enterprisedb.com/blog/general-configuration-and-tuning-recommendations-edb-postgres-advanced-server-and-postgresql