Ubuntu 22.04上PostgreSQL 14集群大数据查询性能调优实战

前阵子有个朋友找我说他们那套跑在Ubuntu 22.04 LTS上的PostgreSQL 14集群平时业务查询挺顺可一到月底跑大数据量的汇总报表单条查询能拖到十几秒主库CPU直接飙到90%以上从库复制延迟也一路走高。我一听就知道这不是硬件不够而是典型的“集群搭起来了但没按大数据查询的场景做优化”。这篇文章就是我复盘这次性能调优全过程的记录。目标很直接在Ubuntu 22.04 LTS上基于PostgreSQL 14的流复制集群把大数据量查询的响应速度和整体稳定性同时做上来。适合正在管理PostgreSQL集群的运维、后端开发、以及对数据库性能调优有需求的技术人员参考。文章会从集群架构选型、系统内核参数、PostgreSQL核心配置、大数据查询优化、高可用实战、故障排查几个维度展开每个参数都会说明为什么这么调踩过的坑也会一并交代。1. 方案选型与整体设计思路1.1 先明确一件事你要的是高可用还是横向扩展PostgreSQL的“集群”这个词在不同人嘴里含义差很多。有人说的是主从复制加自动故障转移的高可用集群有人说的是Citus那种分布式横向扩展集群。这两种对优化策略的影响完全不同如果不先分清后面的参数调优很可能白忙活。场景A主从复制集群。主库处理写入和大部分查询从库承担只读查询并做热备。如果你遇到的是查询慢、主库CPU高、从库延迟大这属于高可用/读写分离集群的优化。场景B分布式扩展集群Citus。适合单机承载不了的数据量级和并发比如几十TB以上的分析型场景。本文主要围绕场景A展开这是多数中小团队部署PostgreSQL集群的首选形态。场景A的优化有两个核心矛盾一个是查询性能一个是数据安全。很多团队只盯着性能参数猛调结果故障转移时丢数据这是本末倒置。1.2 集群组件选型为什么我推荐Patroni etcd HAProxy高可用方案市面上不少repmgr、Patroni、pg_auto_failover、Keepalived加流复制。我实际用过一圈之后稳定可靠的选择是Patroni etcd HAProxy的组合。这里说下为什么不用Keepalived。Keepalived走的是VIP漂移思路实现简单但没有真正解决“数据一致性”的问题。主库挂了之后VIP漂到备库备库如果因为某些原因数据落后于旧主库直接对外提供服务就会丢数据。Patroni通过etcd分布式锁做选主配合同步提交策略能在“可用性”和“数据不丢失”之间做出可控的取舍还天然提供管理API、自动故障转移、手动切换、健康检查接口。HAProxy则负责流量的读写分离和自动摘除故障节点。需要说明的是这套组合虽然稳但确实有学习成本。如果团队没有能力维护etcd和HAProxy可以考虑repmgr——它更简单部署路径更短但自动切换的能力相对弱一些。我的建议是如果业务对可用性要求高、愿意花一到两天做部署和演练直接上Patroni组合如果只是想给单机PostgreSQL加个主备先上repmgr足够了。1.3 容量规划一张纸算清楚内存和并发我调优之前习惯先算一笔账。假设主库机器是64GB内存、16核CPU、NVMe SSD连接数峰值大概100个左右。首先评估内存需求。共享内存shared_buffers通常给物理内存的25%也就是16GB。排序、哈希、聚合这些操作还需要单独的工作内存work_mem如果设置过大100个并发连接每人分到64MB峰值就可能超过10GB而系统还要给OS页缓存和运行程序留空间。所以在分配内存参数时必须先算总账。再评估并行度。16核的机器单个查询开4个并行worker比较合适并行度开满16反而会因为调度开销吃掉收益尤其是一些聚合和排序查询并行收益并不是线性增长的。然后是磁盘。大数据量查询的瓶颈多数在IO。用NVMe SSD时random_page_cost要调低从机械硬盘默认的4降到1.1数据库才会更愿意走索引而不是全表扫描。这套“朴素数学”看起来简单但绝大多数线上问题就是没做这个总账导致——内存被work_mem打爆索引被错误的磁盘成本模型冷落并行参数拍脑袋设置结果CPU跑满、查询反而更慢。2. Ubuntu 22.04系统层优化先把地基打牢2.1 内核参数调整避免操作系统拖后腿Ubuntu 22.04默认内核是5.15已经比较适合跑数据库但默认的内核参数主要面向通用负载不是为数据库优化的。我会调整这几个关键点。vm.swappiness。默认值60会很积极地把内存页换到磁盘数据库进程一旦被换出对延迟的影响是灾难性的。推荐降到10或者1。vm.dirty_ratio和vm.dirty_background_ratio。这两个参数控制脏页的阈值。默认dirty_ratio20、dirty_background_ratio10太高写密集时脏页堆积会导致写停顿。把它调低让后台刷盘更勤快前台就不容易被卡住。vm.overcommit_memory。强烈建议设置为2。默认的启发式overcommit可能导致PostgreSQL申请大块内存时系统假装有内存真正分配时直接OOM杀进程。设置为2后内核按overcommit_ratio默认95严格控制数据库更安全。我直接贴一套实测比较稳的配置cat /etc/sysctl.conf EOF vm.swappiness 10 vm.dirty_ratio 10 vm.dirty_background_ratio 1 vm.dirty_writeback_centisecs 500 vm.dirty_expire_centisecs 3000 vm.overcommit_memory 2 vm.overcommit_ratio 90 vm.zone_reclaim_mode 0 fs.aio-max-nr 1048576 fs.file-max 1000000 EOF sysctl -p关于transparent_hugepage透明大页我建议在Ubuntu 22.04上把它关掉或者至少设为madvise。PostgreSQL对大页的利用方式比较特殊透明大页的碎片整理和分配停顿很容易造成偶发的查询延迟尖刺。关闭方法echo never /sys/kernel/mm/transparent_hugepage/enabled echo never /sys/kernel/mm/transparent_hugepage/defrag这个设置重启会失效想持久化可以在/etc/default/grub的GRUB_CMDLINE_LINUX里加上transparent_hugepagenever然后update-grub。如果不想碰grub用systemd服务在启动时执行这两条命令也行。2.2 文件系统与挂载参数SSD下的细节Ubuntu 22.04默认文件系统ext4装系统可以跑数据库我更推荐xfs。xfs对于大文件、高并发写入场景表现稳定配合NVMe SSD效果更好。如果不想重装系统ext4也完全可用但挂载参数需要调。重点是把atime相关的元数据更新关掉减少不必要的写IO。/dev/nvme0n1p1 /data xfs defaults,noatime,nodiratime 0 0文件系统层面有件事值得单独说IO调度器。NVMe SSD的IO调度器用noneSATA SSD用mq-deadline比较合适。用下面命令验证并调整cat /sys/block/nvme0n1/queue/scheduler echo none /sys/block/nvme0n1/queue/scheduler还有一个容易忽略的点日志和数据库文件尽量不要放在同一个磁盘分区。如果条件允许把PostgreSQL的WAL目录放到单独的NVMe盘上把数据文件放到另一块盘上。这样checkpoint刷盘和日常写入互不干扰IO竞争能明显降下来。3. PostgreSQL 14核心参数调优把每分内存都用在刀刃上3.1 内存三件套shared_buffers / work_mem / maintenance_work_mem先说shared_buffers。PostgreSQL的默认值通常只有128MB对大数据查询来说等于没设。按64GB内存的机器给16GB是比较标准的做法。设到25%左右之后再往上加收益就急速衰减因为数据库自己管理内存的方式远不如系统页缓存高效shared_buffers超过一半物理内存反而会引发双缓存竞争。work_mem是整个内存参数里最危险的。这个参数在每个排序、哈希操作里单独分配。假设并发100个连接每个连接只跑一个需要64MB work_mem的排序操作那么峰值内存就是6.4GB。我一般对OLTP混合场景控制在32MB到64MB如果某类报表查询特别吃排序内存不要全局调大work_mem而是对单个会话用SET LOCAL或者建函数时指定。我踩过全局调大的坑最后是内存报错和OOM风险都回来了。maintenance_work_mem是三个里面最不需要纠结的。设置大一些因为VACUUM、CREATE INDEX、ANALYZE这些维护操作是串行的。2GB起步最大可以给到4GB。对大数据量建索引这个值决定了速度。3.2 并行查询让多核真正干活PostgreSQL 14的并行查询已经成熟默认的max_parallel_workers_per_gather只有2对大数据查询明显保守。我推荐一组适合16核机器的配置max_worker_processes 16 max_parallel_workers 8 max_parallel_workers_per_gather 4 parallel_setup_cost 1000 parallel_tuple_cost 0.1 min_parallel_table_scan_size 8MB min_parallel_index_scan_size 512kB为什么parallel worker设置成8而不是16因为一个查询最多能用max_parallel_workers_per_gather个worker系统同时也要给维护操作和复制进程留资源。全部压满多查询并发时反而线程爆炸。另外要留意并行查询在OLTP场景不是越高越好。小表、低选择性过滤条件、高频点查开并行会白白增加调度开销。建议把min_parallel_table_scan_size设置高一点让优化器只在表足够大时才考虑并行。3.3 IO与成本模型SSD时代必须更新参数PostgreSQL默认的成本模型是按机械硬盘估算的。跑在NVMe上如果不改优化器会经常做出“全表扫描更便宜”的错误判断。关键参数random_page_cost 1.1 effective_io_concurrency 200random_page_cost默认4意味着优化器认为随机读比顺序读贵4倍。SSD上随机读和顺序读差距远没有那么悬殊调到1.1之后索引扫描被选择的概率大幅提高。如果数据库跑在普通SATA盘上别盲目照搬这个值。effective_io_concurrency表示PostgreSQL预取数据时的并发IO请求数。对于NVMe SSD200是个不错的起点。这个参数在大表索引扫描、bitmap heap scan时收益明显。-- 验证当前生效的参数 SHOW random_page_cost; SHOW effective_io_concurrency; SHOW max_parallel_workers_per_gather;3.4 WAL与checkpoint平衡性能与安全先看几个参数max_wal_size 16GB min_wal_size 1GB checkpoint_completion_target 0.9 wal_buffers 16MB synchronous_commit onmax_wal_size控制两个checkpoint之间WAL的最大增长量。设置太小checkpoint会非常频繁每次checkpoint都要刷大量脏页对IO是一场灾难。设置太大崩溃恢复需要重放更多WAL恢复时间变长。对大数据查询场景16GB是一个比较平衡的起步值。checkpoint_completion_target0.9的意思是尽量把checkpoint的刷脏过程分散到下一个checkpoint开始前的90%时间里完成避免集中爆发IO。这个参数配合大max_wal_size能让系统IO曲线变得平滑很多。wal_buffers默认值在PostgreSQL 14里一般按shared_buffers自动推算通常够用但如果遇到高并发小事务写入峰值手动设成16MB可以避免WAL写入等待。有一点特别想提醒不要把synchronous_commit随便改成off。它确实能大幅提升写入性能但也意味着重启或故障时会丢最近的事务很多业务是接受不了的。如果要做这个选择一定要先和业务方确认数据丢失容忍度。3.5 autovacuum长期稳定性的守护神PostgreSQL的MVCC机制决定了每一条UPDATE会留下旧版本DELETE则是标记删除autovacuum负责清理这些死元组。autovacuum一旦跟不上表膨胀会越来越严重查询IO成本持续上升最终稳定性崩掉。大数据量场景我建议加大autovacuum的力度autovacuum on autovacuum_max_workers 4 autovacuum_naptime 30s autovacuum_vacuum_scale_factor 0.05 autovacuum_analyze_scale_factor 0.02 autovacuum_vacuum_cost_limit 2000默认的vacuum_scale_factor是0.2也就是说表增长20%才触发一次vacuum。对大表来说20%是个很大的量级等触发时脏数据已经积压很多。调到0.05会让触发更积极代价是频繁一点的CPU和IO消耗。业务低峰期我会手动补一轮VACUUM (ANALYZE, VERBOSE)大表配合daily cron效果更好。4. 大数据查询的库表设计与SQL优化4.1 分区表一招解决大表全扫描PostgreSQL 10开始支持声明式分区14已经相当成熟范围分区、列表分区、哈希分区都有。我遇到最多的场景是时序数据表比如订单流水、用户行为日志、监控指标。这种表动辄上亿行如果不做分区哪怕有索引查询时也要扫大量无关数据。分区之后优化器可以根据查询条件裁剪分区只扫必要的子表。实际效果往往是数量级的提升。一个常见的范围分区示例CREATE TABLE user_events ( user_id bigint NOT NULL, event_time timestamptz NOT NULL, event_type text ) PARTITION BY RANGE (event_time); CREATE TABLE user_events_p202401 PARTITION OF user_events FOR VALUES FROM (2024-01-01) TO (2024-02-01); CREATE TABLE user_events_p202402 PARTITION OF user_events FOR VALUES FROM (2024-02-01) TO (2024-03-01);配合定期任务自动创建未来分区和删除过期分区的DROP TABLE而不是DELETE——因为DROP分区是元数据操作开销远小于删除千万行。4.2 索引策略不只有B-tree这一种选择大数据查询里B-tree索引依然是最常用的但有些场景它有替代品。BRIN索引。如果表非常大数据物理排列和查询范围高度相关比如按时间顺序插入的时间序列数据BRIN索引非常划算。它不记录每一行的位置只记录每个数据块范围的极值体积比B-tree小几个量级扫描时能一次性跳过大量无关块。例子CREATE INDEX idx_user_events_brin ON user_events USING brin (event_time);这种索引在“某段时间内所有数据”类查询里效果极好但在点查场景可能帮不上忙。所以实践时要先分析查询模式再决定哪种索引。复合索引的字段顺序也值得认真设计。经验法则是等值条件字段放前面范围条件字段放后面。比如WHERE user_id ? AND event_time BETWEEN ? AND ?那么(user_id, event_time)建复合索引效率最高。4.3 用pg_stat_statements打开“慢查询黑盒”很多时候你说不清哪些查询慢只看到整体性能差。这时候pg_stat_statements是最好的起点。它记录每条SQL的调用次数、总耗时、平均耗时、缓冲命中情况能直接告诉你该优化哪条语句。开启步骤在postgresql.conf中加shared_preload_libraries pg_stat_statements pg_stat_statements.max 10000 pg_stat_statements.track all注意shared_preload_libraries必须在实例启动前设置改完需要重启PostgreSQL。建扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;查询SELECT queryid, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;同时开启慢查询日志作为补充log_min_duration_statement 2000 log_line_prefix %t [%p]: [%l-1] user%u,db%d,app%a,client%h log_checkpoints on log_connections on log_disconnections on log_lock_waits onlog_line_prefix这个格式看起来繁琐但排查问题时你能从日志里一眼看出是哪个用户、哪个应用、从哪个客户端IP发起的查询省非常多事。4.4 EXPLAIN ANALYZE 读法让执行计划说实话定位到慢SQL之后用EXPLAIN (ANALYZE, BUFFERS) 跑一遍。注意ANALYZE会真实执行语句生产环境要小心。我看到最多的三个典型问题第一个是Seq Scan全表扫描。如果结果里出现Seq Scan on user_events而表有几千万行基本可以判定索引没选上或没建对。结合filter条件判断是缺索引还是统计信息过期。第二个是Nested Loop在巨大数据集上循环。两个几百万行的表做嵌套循环会执行百万次索引查找。这时考虑换成Hash Join或者Merge Join常见手段是调整join_collapse_limit、from_collapse_limit或者直接改写SQL。第三个是Rows估算偏差巨大。优化器估算返回1000行实际返回1000万行计划自然烂。这种情况多半是统计信息没更新手动跑一下ANALYZE大表或者检查autovacuum的analyze配置是否被关掉了。5. 集群高可用与读写分离实战5.1 Patroni etcd HAProxy的部署要点部署步骤大体分三层etcd集群、Patroni管理的PostgreSQL实例、HAProxy做入口。etcd集群建议用3节点奇数个这样才能形成有效仲裁。etcd的data-dir放到独立磁盘性能不重要数据一致性是关键。PostgreSQL实例侧需要对每个节点部署Patroni。主节点和从节点用同样的配置Patroni通过etcd协调角色。下面是patroni.yml里极简但关键的部分scope: pgcluster namespace: /pg name: pg-01 restapi: listen: 0.0.0.0:8008 connect_address: 192.168.1.10:8008 etcd: hosts: 192.168.1.10:2379,192.168.1.11:2379,192.168.1.12:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 33554432 postgresql: parameters: wal_level: replica max_wal_senders: 10 max_replication_slots: 10 hot_standby: on max_connections: 200 shared_buffers: 16384MB pg_hba: - local all all trust - host all all 0.0.0.0/0 md5 - host replication all 0.0.0.0/0 md5 postgresql: listen: 0.0.0.0:5432 connect_address: 192.168.1.10:5432 data_dir: /var/lib/postgresql/14/main bin_dir: /usr/lib/postgresql/14/bin启动后Patroni会在etcd注册主节点信息可以通过patronictl list查看。HAProxy把读写流量路由到leader把只读流量轮询分发给replica。健康检查指向Patroni的rest api的/primary和/replica端点这样主节点挂了HAProxy几秒内就能把流量切换到新的主库。这套方案能扛住单个节点故障但我要强调一点HAProxy和etcd本身也要高可用不能把它们当成单点。HAProxy可以做keepalived VIPetcd本来就是3节点问题不大。我见过不少团队把Patroni部署了却忘了给HAProxy和etcd做冗余最后反而在这一层出事。5.2 流复制参数与延迟控制流复制的底层参数主要这几个wal_level replica max_wal_senders 10 max_replication_slots 10 wal_keep_size 1024 hot_standby on hot_standby_feedback on synchronous_commit offPG12开始废弃了wal_keep_segments改成wal_keep_size单位是MB。如果希望从库即使短暂断线也能追上来多预留一些WAL。但WAL保留过多会影响主库checkpoint不建议无脑设置很大配合replication slot更靠谱。注意如果使用replication slot槽本身会保证主库不清理掉还没有复制走的WAL但如果从库长期离线主库的WAL会被槽卡住磁盘迟早打爆监控里要盯紧slot状态。hot_standby_feedback on也很重要。它让从库在执行查询时把所需的最小事务信息反馈给主库避免主库VACUUM过早清理掉从库还在使用的元组。否则从库的长事务查询会报“could not serialize access due to concurrent update”或者大量报错。关于synchronous_commit在集群模式中它和复制方式强相关。如果想要严格不丢数据用synchronous_commit on并且在Patroni里配置synchronous_mode: true同时至少要有一个同步从库。想要性能优先可以synchronous_commit off甚至remote_write。这个就是前文说的须和业务确认。5.3 故障转移演练别让“高可用”只停留在文档里高可用方案部署完一定要做故障转移演练。我强烈建议在业务低峰期拉一次主库看整个切换过程是否顺畅顺便测一下从库是否能在切换后自动接管。演练注意点手动用patronictl switchover做优雅切换观察HAProxy是否把写连接切换到新主库。再模拟硬故障kill -9看Patroni能否在TTL内完成选主。切换后检查所有表是否有数据缺失尤其是同步复制模式下观察同步位点有没有完全一致。明确应用侧是否有长连接。如果应用连接池没配好即使数据库切到新主库旧连接依旧会不断报错。这个是最容易被忽略的。我遇到过最尴尬的一种情况切换成功了但应用侧的连接池配置了过长的连接存活时间一直握着旧主库的连接不放导致业务在切换后的几十分钟内都是“能连但报错”状态。所以高可用优化从来不只是数据库侧的事情应用连接池的探活和重连策略也要一起调。6. 监控体系与常见问题排查6.1 一份够用的监控指标清单我会从三个层面盯指标系统层、数据库层、复制层。系统层CPU使用率、内存压力、IO等待、磁盘读写延迟、网络吞吐。数据库层连接数、活跃会话数、锁等待数、slow query数量、hit ratio、checkpoint频率、dead tuple膨胀率。复制层主从延迟字节数、slot状态、WAL堆积量。一个很实用的组合是Prometheus postgres_exporter grafana。postgres_exporter内置了大量PostgreSQL指标基本开箱即用。配合pg_stat_statements的数据能第一时间发现慢查询和异常热点。日常运维我会定时执行一个小查询快速定位复制状态SELECT client_addr, state, sent_lsn, replay_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes FROM pg_stat_replication;如果replay_lag_bytes持续增长说明备库追不上主库需要检查备库IO或主库WAL产生速度。6.2 常见问题速查连接数打满。症状应用报too many connections。先查当前连接数SELECT count(*), state, usename FROM pg_stat_activity GROUP BY 2,3;如果是活动会话堆积用pg_stat_activity找阻塞源头如果只是连接池开的连接太多调max_connections或者应用连接池上限而不是简单调大最大连接数——连接太多上下文切换和内存开销会让数据库更慢。锁等待。症状查询卡住。查pg_locks和pg_stat_activity找到持有锁的进程评估是等锁还是死锁。必要时取消阻塞查询SELECT pg_cancel_backend(pid); 更极端时pg_terminate_backend(pid)。checkpoint过于频繁。看pg_stat_bgwriter的checkpoints_timed和checkpoints_req。如果checkpoints_req占很大比例说明触发的是需求量而不是时间量通常是max_wal_size太小或写入峰值过高。对症就是提高max_wal_size、优化checkpoint_completion_target同时看看是不是有批量写入导致WAL流量猛涨。表膨胀。查询pg_stat_user_tables比较n_live_tup和n_dead_tup。如果dead_tup占比持续超过20%且autovacuum没跟上手动VACUUM (ANALYZE)即可但如果反复膨胀要检查是否有长事务拖住了vacuum的清理进度。复制中断。pg_stat_replication里看到state变成catchup或者连接消失通常是网络抖动、主备wal冲突或复制槽损坏。处理思路优先恢复网络再查主备两端的日志必要时重建备库。症状常用定位手段常见根因连接数打满pg_stat_activity按状态聚合应用连接池配置不合理或慢查询堆积查询卡住不走pg_locks与pg_stat_activity关联锁等待、长事务阻塞从库追不上pg_stat_replication查看replay_lsn备库IO瓶颈或主库写入峰值过大突发IO尖峰pg_stat_bgwriter检查checkpointmax_wal_size过小或checkpoint参数不合理慢查询越跑越慢pg_stat_statements对比趋势统计信息过期、表膨胀、缺索引除了这些我还想提一个非常实用的小技巧用系统级的工具交叉验证。数据库侧看到IO慢那就用iostat直接看await数据看到CPU高用top和pidstat看是不是postgres进程看到网络延迟用tcpdump分析复制流量。数据库的问题很多其实是系统层的问题别只盯着pg_stat_*看。说实话PostgreSQL 14在Ubuntu 22.04 LTS上的性能调优没有太多玄学。把系统内核参数调合理把shared_buffers、work_mem、checkpoint配置算清楚再把autovacuum喂饱然后针对大数据查询做分区和索引设计配合Patroni做高可用最后用pg_stat_statements持续驱动迭代优化。做到这几点绝大多数集群性能问题都能解决。我个人在实际操作中的体会是别指望一劳永逸。每次调优完要把当时的业务快照、参数快照、压测结果都记录下来形成这个集群自己的“性能基线”。等下次业务流量大了、数据又多了一个量级直接对比基线就能快速判断出到底是哪里退化。调优的最难环节不是调参数而是持续监控和敢于验证。希望这篇记录能帮你少走一些弯路。