印刻数据库

外观

操作系统参数调整 ​

操作系统参数调整是PostgreSQL性能优化的重要组成部分，合理的操作系统配置可以显著提高数据库的性能和稳定性。本文将详细介绍PostgreSQL的操作系统参数调整方法，包括Linux内核参数、文件系统参数、网络参数等的优化配置和最佳实践，帮助DBA在实际生产环境中优化操作系统配置，兼顾不同PostgreSQL版本的特性差异。

Linux内核参数调整 ​

Linux内核参数直接影响操作系统的性能和稳定性，合理调整这些参数可以提高PostgreSQL的性能。不同PostgreSQL版本对内核参数的要求有所不同，特别是在内存管理和I/O处理方面。

内存管理参数 ​

调整内存分配策略 ​

内存分配策略对PostgreSQL的性能影响很大，特别是在高并发场景下：

bash
# /etc/sysctl.conf
# 调整内存分配策略，PostgreSQL 12+推荐使用2
vm.overcommit_memory = 2
# 调整内存过度分配比例，根据系统内存大小调整
vm.overcommit_ratio = 90

# 调整脏页回写策略，减少I/O峰值
# PostgreSQL 14+优化了WAL写入，可适当降低脏页比例
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
# 调整脏页回写间隔（毫秒）
vm.dirty_writeback_centisecs = 500
vm.dirty_expire_centisecs = 2000

# 调整内存页面回收策略
# PostgreSQL 10+建议降低swappiness值，减少交换
vm.swappiness = 10
# 调整VFS缓存压力，PostgreSQL 12+建议降低
vm.vfs_cache_pressure = 50

调整NUMA策略 ​

对于多NUMA节点的系统，调整NUMA策略可以提高内存访问效率：

bash
# /etc/sysctl.conf
# 禁用NUMA内存平衡，PostgreSQL 11+增强了NUMA支持
kernel.numa_balancing = 0

# 或在启动PostgreSQL时使用numactl命令绑定CPU和内存（推荐）
# PostgreSQL 13+在启动脚本中已集成NUMA绑定支持
numactl --cpunodebind=0 --membind=0 pg_ctl start -D /var/lib/postgresql/data

进程管理参数 ​

bash
# /etc/sysctl.conf
# 调整最大文件句柄数，PostgreSQL 14+支持更多并发连接
fs.file-max = 655360

# 调整最大进程数
kernel.pid_max = 65536

# 调整最大线程数
kernel.threads-max = 65536

# 调整共享内存大小，PostgreSQL 9.6+默认使用动态共享内存
# 对于大内存系统，设置足够大的共享内存限制
kernel.shmmax = 4398046511104  # 4TB
kernel.shmall = 1073741824     # 4TB / 4KB
kernel.shmmni = 4096

应用内核参数 ​

修改内核参数后，使用以下命令应用更改：

bash
# 应用内核参数
sysctl -p

# 或逐个应用
sysctl -w vm.overcommit_memory=2

文件系统参数调整 ​

文件系统参数直接影响PostgreSQL的I/O性能，合理调整这些参数可以提高数据库的I/O性能。不同文件系统对PostgreSQL的支持程度有所不同，建议优先选择XFS或EXT4。

文件系统挂载参数 ​

XFS文件系统挂载参数 ​

XFS是PostgreSQL 10+推荐的文件系统，特别是对于大文件系统：

bash
# /etc/fstab
# XFS文件系统挂载参数（PostgreSQL推荐）
/dev/sdb1 /var/lib/postgresql/data xfs defaults,noatime,nodiratime,allocsize=16m 0 2
# WAL目录单独挂载，使用更高性能的配置
/dev/sdc1 /var/lib/postgresql/wal xfs defaults,noatime,nodiratime,allocsize=16m,discard 0 2

EXT4文件系统挂载参数 ​

EXT4也是PostgreSQL常用的文件系统，适用于中小规模部署：

bash
# /etc/fstab
# EXT4文件系统挂载参数（PostgreSQL推荐）
/dev/sdb1 /var/lib/postgresql/data ext4 defaults,noatime,nodiratime,barrier=1,data=ordered 0 2

文件句柄限制 ​

PostgreSQL 12+支持更多并发连接，需要调整文件句柄限制：

bash
# /etc/security/limits.conf
# 调整PostgreSQL用户的文件句柄限制
postgres soft nofile 65536
postgres hard nofile 65536

# 调整PostgreSQL用户的进程数限制
postgres soft nproc 65536
postgres hard nproc 65536

# 对于systemd管理的服务，还需要调整service文件
# 在/usr/lib/systemd/system/postgresql.service中添加
# LimitNOFILE=65536

磁盘I/O参数调整 ​

磁盘I/O是数据库性能的关键瓶颈，合理调整磁盘I/O参数可以提高PostgreSQL的I/O性能。不同存储设备（HDD、SSD、NVMe）需要不同的I/O调度策略。

调整I/O调度器 ​

对于SSD/NVMe设备 ​

PostgreSQL 13+优化了对NVMe SSD的支持，推荐使用mq-deadline调度器：

bash
# 临时调整I/O调度器
echo mq-deadline > /sys/block/nvme0n1/queue/scheduler

# 永久调整I/O调度器，创建udev规则
cat > /etc/udev/rules.d/60-scheduler.rules << EOF
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/rotational}=="0", ATTR{queue/scheduler}="mq-deadline"
ACTION=="add|change", KERNEL=="nvme*", ATTR{queue/scheduler}="mq-deadline"
EOF

对于HDD设备 ​

HDD设备推荐使用bfq或deadline调度器：

bash
# 临时调整I/O调度器
echo bfq > /sys/block/sda/queue/scheduler

# 永久调整I/O调度器，添加到udev规则
ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/rotational}=="1", ATTR{queue/scheduler}="bfq"

调整磁盘队列参数 ​

bash
# 调整队列深度，NVMe SSD可适当增大
# PostgreSQL 14+优化了并行I/O，可提高队列深度
cat /sys/block/nvme0n1/queue/nr_requests  # 查看当前值
echo 256 > /sys/block/nvme0n1/queue/nr_requests

# 调整I/O优先级，为PostgreSQL进程设置高优先级
ionice -c 1 -n 0 -p $(pgrep -d ',' -f postgres)

监控磁盘I/O性能 ​

bash
# 使用iostat监控磁盘I/O，重点关注%util和await
# PostgreSQL 12+优化了I/O统计，可结合pg_stat_database查看
watch -n 1 "iostat -x /dev/nvme0n1 /dev/nvme0n2"

# 使用iotop监控PostgreSQL进程的I/O
iotop -P -p $(pgrep -d ',' -f postgres)

网络参数调整 ​

网络参数直接影响PostgreSQL的网络性能，合理调整这些参数可以提高数据库的网络性能。PostgreSQL 12+增强了网络性能，特别是在高并发连接和大数据包传输方面。

调整网络接口参数 ​

bash
# 调整网络接口的MTU值，对于万兆网络可以使用9000（Jumbo帧）
# PostgreSQL 14+优化了网络包处理，Jumbo帧可提高大查询性能
ip link set eth0 mtu 9000

# 启用网络接口的硬件卸载功能
# 根据网卡支持情况启用，PostgreSQL 12+可更好地利用这些功能
ethtool -K eth0 gro on gso on tso on rx on tx on sg on

调整TCP参数 ​

bash
# /etc/sysctl.conf
# 调整TCP连接参数，PostgreSQL 13+推荐增大
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
# 调整TCP握手重试次数
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 2

# 调整TCP内存参数，PostgreSQL 12+支持更大的TCP缓冲区
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# 启用TCP快速打开，PostgreSQL 11+支持
net.ipv4.tcp_fastopen = 3

# 调整TCP连接超时参数，PostgreSQL 14+优化了连接管理
net.ipv4.tcp_keepalives_idle = 60
net.ipv4.tcp_keepalives_interval = 10
net.ipv4.tcp_keepalives_count = 10
# 调整TIME_WAIT状态超时时间
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_max_tw_buckets = 5000

# 启用TCP高级特性，提高网络性能
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_sack = 1

监控网络性能 ​

bash
# 使用ss监控PostgreSQL连接状态
ss -s state established '( sport = :5432 or dport = :5432 )'

# 使用ping监控网络延迟
ping -c 10 database_host

# 使用iperf3测试网络吞吐量，PostgreSQL 12+支持更大的数据包
iperf3 -c database_host -M 8960  # 使用接近Jumbo帧的大小

防火墙参数调整 ​

防火墙参数直接影响网络连接的性能和安全性，合理调整这些参数可以提高PostgreSQL的网络性能。

调整防火墙规则 ​

bash
# 使用firewalld开放PostgreSQL端口（推荐）
firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --add-port=8008/tcp --permanent  # Patroni监控端口（如需）
firewall-cmd --reload

# 或使用iptables开放PostgreSQL端口
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
iptables-save > /etc/sysconfig/iptables

调整防火墙连接跟踪参数 ​

对于高并发场景，需要调整防火墙连接跟踪参数：

bash
# /etc/sysctl.conf
# 调整连接跟踪表大小，PostgreSQL 14+支持更多并发连接
net.netfilter.nf_conntrack_max = 1000000

# 调整连接跟踪超时时间
net.netfilter.nf_conntrack_tcp_timeout_established = 3600
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120

系统服务优化 ​

禁用不必要的系统服务 ​

禁用不必要的系统服务可以释放系统资源，提高PostgreSQL的性能：

bash
# 禁用不必要的系统服务
systemctl disable firewalld.service  # 如已使用外部防火墙
systemctl disable NetworkManager.service  # 如使用静态IP
systemctl disable tuned.service  # 如手动优化系统参数

# 启用必要的系统服务
systemctl enable sshd.service
systemctl enable systemd-logind.service
systemctl enable systemd-journald.service
systemctl enable systemd-timesyncd.service

优化系统日志 ​

系统日志配置会影响系统性能，特别是在高负载场景下：

bash
# /etc/systemd/journald.conf
# 优化系统日志配置
Storage=auto
Compress=yes
Seal=yes
SplitMode=uid
SyncIntervalSec=5m  # 降低同步频率，PostgreSQL 12+建议5分钟
RateLimitInterval=30s
RateLimitBurst=1000
SystemMaxUse=100M  # 限制日志大小
SystemKeepFree=1G
RuntimeMaxUse=100M
RuntimeKeepFree=1G

系统监控与预警 ​

建立系统监控和预警机制，及时发现和解决系统问题，确保数据库的稳定运行。

监控指标 ​

系统组件关键监控指标PostgreSQL版本建议
内存内存使用率、交换分区使用率、缓存命中率9.6+
磁盘磁盘使用率、I/O吞吐量、I/O延迟（await）、IOPS、%util9.6+
网络网络带宽使用率、网络延迟、丢包率、连接数9.6+
进程PostgreSQL进程CPU使用率、内存使用率9.6+
文件系统文件系统使用率、inode使用率、挂载状态9.6+

监控工具 ​

  • 开源工具：Prometheus + Grafana（推荐）、Zabbix、Nagios
  • PostgreSQL集成工具：pg_stat_statements、pg_stat_database、pg_stat_bgwriter
  • 系统内置工具：top、vmstat、iostat、ss、free、df

预警设置 ​

  • 设置CPU使用率超过80%时预警
  • 设置内存使用率超过90%时预警
  • 设置磁盘使用率超过85%时预警
  • 设置I/O延迟（await）超过1ms（SSD）或10ms（HDD）时预警
  • 设置网络延迟超过50ms时预警
  • 设置PostgreSQL连接数超过最大连接数的80%时预警
  • 设置文件系统inode使用率超过90%时预警

系统调优最佳实践 ​

针对不同场景的系统调优 ​

OLTP场景 ​

  • CPU：优先选择高频率、中等核心数量的CPU，PostgreSQL 14+对OLTP场景的CPU利用率有显著优化
  • 内存：配置较大的shared_buffers和work_mem，PostgreSQL 12+建议shared_buffers为系统内存的15-25%
  • 存储：使用NVMe SSD存储，配置RAID 10，PostgreSQL 14+优化了WAL写入
  • 网络：使用万兆或更高网络，启用Jumbo帧
  • 内核参数：优化内存分配和I/O调度，降低swappiness值

OLAP场景 ​

  • CPU：优先选择多核CPU，PostgreSQL 13+增强了并行查询能力
  • 内存：配置较大的shared_buffers和work_mem，PostgreSQL 11+支持更多并行工作进程
  • 存储：使用高容量的NVMe SSD或存储阵列，PostgreSQL 12+优化了并行I/O
  • 内核参数：优化内存管理和NUMA策略，启用超线程

系统调优步骤 ​

  1. 监控当前系统性能：使用工具监控系统的CPU、内存、磁盘、网络等性能指标
  2. 识别性能瓶颈：分析监控数据，结合PostgreSQL日志和pg_stat_statements识别瓶颈
  3. 调整系统参数：根据性能瓶颈，调整相应的系统参数，每次只调整一个参数
  4. 测试调优效果：使用pgbench等工具测试调优效果，PostgreSQL 14+增强了pgbench的测试能力
  5. 持续监控和调整：持续监控系统性能，根据实际负载调整系统参数

系统调优注意事项 ​

  • 不要盲目调整系统参数，先理解参数的含义和影响
  • 不同PostgreSQL版本对系统参数的要求不同，参考官方文档
  • 对于生产环境，在测试环境验证调优效果后再应用到生产环境
  • 记录调优前后的性能指标，便于比较调优效果
  • 定期检查系统参数，确保参数配置符合当前系统负载
  • 结合PostgreSQL配置参数进行调优，系统参数和数据库参数需要协同优化

案例分析 ​

OLTP场景系统调优 ​

问题描述 ​

电商系统的PostgreSQL 12数据库出现性能瓶颈，CPU使用率高，查询响应时间长，I/O延迟高。

系统配置 ​

  • CPU：16核心，3.2GHz
  • 内存：64GB
  • 存储：SATA SSD
  • 网络：1GbE
  • 操作系统：CentOS 7

调优方案 ​

  1. 调整内核参数：
bash
# /etc/sysctl.conf
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.swappiness = 10
vm.vfs_cache_pressure = 50
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
fs.file-max = 655360
  1. 调整文件系统挂载参数：
bash
# /etc/fstab
/dev/sdb1 /var/lib/postgresql/data xfs defaults,noatime,nodiratime,allocsize=16m 0 2
/dev/sdc1 /var/lib/postgresql/wal xfs defaults,noatime,nodiratime,allocsize=16m 0 2  # WAL分离
  1. 调整I/O调度器：
bash
echo mq-deadline > /sys/block/sdb/queue/scheduler
echo mq-deadline > /sys/block/sdc/queue/scheduler
echo 256 > /sys/block/sdb/queue/nr_requests
echo 256 > /sys/block/sdc/queue/nr_requests
  1. 调整PostgreSQL配置参数：
ini
# postgresql.conf
shared_buffers = 16GB  # 系统内存的25%
work_mem = 64MB
maintenance_work_mem = 2GB
effective_cache_size = 48GB  # 系统内存的75%
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10

调优效果 ​

  • 查询响应时间从100ms减少到10ms
  • 并发处理能力从1000 TPS提高到5000 TPS
  • CPU使用率从90%降低到40%
  • I/O延迟从5ms降低到0.5ms

OLAP场景系统调优 ​

问题描述 ​

数据分析系统的PostgreSQL 13数据库出现性能瓶颈，查询执行时间长，I/O吞吐量不足。

系统配置 ​

  • CPU：32核心，2.8GHz
  • 内存：128GB
  • 存储：HDD
  • 网络：1GbE
  • 操作系统：CentOS 8

调优方案 ​

  1. 调整内核参数：
bash
# /etc/sysctl.conf
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
vm.dirty_background_ratio = 10
vm.dirty_ratio = 20
vm.swappiness = 5
vm.vfs_cache_pressure = 100
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
fs.file-max = 655360
kernel.numa_balancing = 0  # 禁用NUMA平衡
  1. 调整文件系统挂载参数：
bash
# /etc/fstab
/dev/sdb1 /var/lib/postgresql/data xfs defaults,noatime,nodiratime,allocsize=64m 0 2
  1. 调整I/O调度器：
bash
echo bfq > /sys/block/sdb/queue/scheduler
echo 256 > /sys/block/sdb/queue/nr_requests
  1. 调整PostgreSQL配置参数：
ini
# postgresql.conf
shared_buffers = 32GB  # 系统内存的25%
work_mem = 256MB  # 提高工作内存
maintenance_work_mem = 4GB
effective_cache_size = 96GB  # 系统内存的75%
max_parallel_workers = 32  # 启用并行查询
max_parallel_workers_per_gather = 16

调优效果 ​

  • 查询执行时间从30秒减少到3秒
  • 数据加载速度从100MB/s提高到1GB/s
  • I/O吞吐量从500MB/s提高到5GB/s
  • 并发查询能力从10提高到100

总结 ​

操作系统参数调整是PostgreSQL性能优化的重要组成部分，合理调整系统参数可以显著提高数据库的性能和稳定性。本文介绍了Linux内核参数、文件系统参数、磁盘I/O参数、网络参数等的优化配置和最佳实践，包括：

  1. 内存管理参数：调整内存分配策略和NUMA策略，优化内存使用
  2. 进程管理参数：调整文件句柄数和共享内存大小，支持更多并发连接
  3. 磁盘I/O参数：优化I/O调度器和队列参数，提高I/O性能
  4. 网络参数：调整TCP连接参数和网络接口参数，提高网络性能
  5. 防火墙参数：优化防火墙规则和连接跟踪参数，平衡安全性和性能
  6. 系统服务优化：禁用不必要的系统服务，优化系统日志
  7. 系统监控与预警：建立监控机制，及时发现和解决问题

通过不断优化操作系统配置，结合PostgreSQL版本特性，DBA可以提高PostgreSQL数据库的性能，满足业务需求，为业务发展提供有力支持。在实际生产环境中，建议根据具体的业务场景和系统配置，结合监控数据进行针对性的调优。