PostgreSQL在Linux上如何调优

一、系统级基础优化

1. 文件系统与I/O调优

• 挂载选项优化：修改/etc/fstab，为PostgreSQL数据目录所在分区添加noatime,nodiratime,async选项（noatime禁用访问时间更新，减少磁盘写入；async异步写入提升吞吐），修改后执行mount -o remount /data生效。
• 磁盘预读调整：通过blockdev --getra /dev/sda查看当前预读扇区数（默认通常为256），使用blockdev --setra 4096 /dev/sda设置为4096扇区（2MB，适合顺序读场景）；永久生效可添加echo 4096 > /sys/block/sda/queue/read_ahead_kb到/etc/rc.local。
• I/O调度器选择：数据库服务器推荐使用deadline调度器（平衡请求响应时间，避免饥饿），执行echo deadline > /sys/block/sda/queue/scheduler临时修改；永久生效需修改GRUB配置（grubby --update-kernel=ALL --args="elevator=deadline"）并重启。
• 透明大页与NUMA关闭：透明大页会导致内存分配延迟，执行echo never > /sys/kernel/mm/transparent_hugepage/enabled和echo never > /sys/kernel/mm/transparent_hugepage/defrag临时关闭；永久生效添加到/etc/rc.d/rc.local。NUMA架构会引发内存分配不均，建议在BIOS中禁用或通过numactl --interleave=all启动PostgreSQL。

2. 内核参数调优

编辑/etc/sysctl.conf，添加/修改以下参数（优化网络与内存管理）：

• vm.swappiness=0：禁用交换分区（避免内存不足时频繁换页，影响性能）；
• vm.overcommit_memory=2：禁止过度内存分配（防止OOM Killer杀死PostgreSQL进程）；
• net.core.wmem_max=873200、net.core.rmem_max=873200：增大socket读写缓冲区；
• net.ipv4.tcp_wmem=8192 436600 873200、net.ipv4.tcp_rmem=32768 436600 873200：优化TCP缓冲区；
• net.core.netdev_max_backlog=1000、net.core.somaxconn=256：增大网络队列长度（应对高并发连接）。
  修改后执行sysctl -p使配置生效。

二、PostgreSQL配置文件优化（postgresql.conf）

1. 内存分配参数

• shared_buffers：设置共享内存缓冲区大小（用于缓存数据和索引），建议为系统内存的25%-40%（如64GB内存设为16GB-25GB），需重启生效。
• work_mem：每个后端进程的排序/哈希操作内存（如ORDER BY、GROUP BY），建议4MB-16MB（避免设置过大导致内存耗尽，可针对复杂查询在会话级别调整）。
• maintenance_work_mem：维护任务（如VACUUM、CREATE INDEX）的内存，建议64MB-256MB（更大的值加速维护操作）。
• effective_cache_size：操作系统缓存的大小（用于查询规划器判断索引有效性），建议为系统内存的50%-80%（如64GB内存设为32GB-64GB）。

2. I/O与检查点参数

• max_wal_size：WAL（预写日志）的最大大小，建议10GB-20GB（增大可延长检查点间隔，减少I/O压力）。
• checkpoint_completion_target：检查点完成时间目标（0-1之间），建议0.8-0.9（让检查点更平缓，减少磁盘I/O峰值）。
• random_page_cost：随机I/O代价（默认4.0），若使用SSD可降低至1.0-2.0（让查询规划器更倾向于使用索引扫描）。

3. 并发与连接参数

• max_connections：最大并发连接数（默认100，过大会导致内存不足），建议根据应用需求调整（如100-500），配合连接池（如PgBouncer）使用更佳。
• max_worker_processes：后台进程最大数量（默认8），建议设置为CPU核心数的1-2倍（支持并行查询）。
• max_parallel_workers_per_gather：每个Gather节点的并行工作线程数（默认2），建议设置为CPU核心数的1-2倍（加速大数据量查询）。

三、数据库维护优化

• 定期VACUUM与ANALYZE：VACUUM清理死行（避免表膨胀），ANALYZE更新统计信息（帮助查询规划器选择最优计划）。可设置autovacuum=on（自动执行），并通过VACUUM VERBOSE监控碎片情况。
• 索引优化：为高频查询的WHERE、JOIN、ORDER BY列创建索引（如B-tree索引适合等值查询，GIN索引适合全文搜索）；定期执行REINDEX重建碎片化索引（如REINDEX TABLE large_table）。
• 查询优化：使用EXPLAIN ANALYZE分析查询计划（找出全表扫描、排序等瓶颈）；避免SELECT *（只查询必要列），减少不必要的JOIN操作。

四、监控与持续优化

• 工具选择：使用pg_stat_activity监控当前连接与查询（如SELECT * FROM pg_stat_activity WHERE state = 'active'）；使用pg_stat_user_tables监控表级统计信息（如扫描次数、更新次数）。
• 第三方工具：部署Prometheus+Grafana监控集群（监控QPS、延迟、缓存命中率等指标）；使用pt-query-digest分析慢查询日志（定位性能瓶颈）。