小型vps服务器postgresql调优指南

3核心 CPU、4GB 内存、60GB NVMe、vps环境 这是基本环境。

特别是 NVMe（极快） 和 60GB 硬盘（空间紧张） 这两个特点，意味着我们的调优方向必须改变：“用 CPU 和 I/O 换空间，同时严格限制内存防止死机”。

3000万行数据对于 TimescaleDB 不算大，但如果不压缩，索引+数据可能很快会占满 60GB（考虑到系统和日志也要占地）。

以下是针对这台 VPS 的 精细化调优方案：

1. 内存与并发调整 (最关键，防止 OOM)

4GB 内存跑 mod_perl + 数据库非常极限。mod_perl 极其吃内存，我们必须把 Postgres 限制得“乖”一点。

修改 postgresql.conf：

Properties

# --- 内存限制 (比之前更保守) ---
# 给 OS 和 mod_perl 留足空间。设为 768MB 比较安全。
shared_buffers = 768MB 

# 你的 API 可能会有很多并发小查询，设太大容易内存溢出。
work_mem = 8MB 

# 维护操作（如建索引）的内存，设小一点。
maintenance_work_mem = 128MB 

# 告诉优化器系统大概有多少缓存可用 (不占用实际内存，仅用于计算代价)
effective_cache_size = 2GB 

# --- CPU 并行 (3核心的限制) ---
# 3核 CPU 不适合大规模并行。
# 限制后台进程总数，避免抢占 Web 服务的 CPU。
max_worker_processes = 6 

# 单个查询最多只能用 1 个额外核心（加上主进程共2核），留 1 核给 API。
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

2. 磁盘 I/O 优化 (发挥 NVMe 优势)

NVMe 的随机读写性能极强，我们可以大胆告诉数据库：“随机读很快，别怕”。

Properties

# --- NVMe 优化 ---
# 告诉数据库随机读和顺序读一样快，这将大大鼓励使用索引。
random_page_cost = 1.1 
effective_io_concurrency = 200

# --- WAL 日志 (空间保护) ---
# 60GB 硬盘比较小，之前的 4GB WAL 可能会占用太多空间。
# 降为 2GB，既保证性能又节省空间。
max_wal_size = 2GB 
min_wal_size = 512MB
checkpoint_completion_target = 0.9

# --- 写入性能 (开发/测试环境神器) ---
# 强烈建议开启，性能提升巨大，风险仅为断电丢失最近 <1秒 数据
synchronous_commit = off 


# 自动清理进程
autovacuum_max_workers = 3      # 原为 10 (对于3核CPU太高了)
autovacuum_naptime = 1min       # 原为 10 (10秒太频繁)

3. TimescaleDB 特有优化 (必须做！)

你在 grep 中看到 timescaledb.max_background_workers = 16，这对于 3核心机器太浪费了，而且会占用共享内存。

Properties

# --- TimescaleDB 调整 ---
# 你的 CPU 只有3核，不需要16个后台工人类。改为 4 个足够处理压缩和保留策略。
timescaledb.max_background_workers = 4

4. 空间大杀器：启用 TimescaleDB 压缩

既然你的磁盘只有 60GB，强烈建议对 3000万遥测数据启用 列式压缩。这通常能将存储空间减少 90% - 95%，而且在做统计分析（如你的三相不平衡查询）时，查询速度会更快（因为 I/O 少了）。

操作步骤（在 psql 中执行）：

  1. 启用压缩特性：

    假设你的超表叫 dms_data_gzdy，按照时间分块。

    SQL

    -- 1. 开启压缩，按 switch_oid 分段（保证同一个设备的记录在一起）
ALTER TABLE dms_data_gzdy SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'switch_oid',
  timescaledb.compress_orderby = 'record_time DESC'
);

  2. 设置自动压缩策略：

    压缩 7 天以前的数据（热数据不压缩，便于写入；冷数据压缩，节省空间）。

    SQL

    SELECT add_compression_policy('dms_data_gzdy', INTERVAL '7 days');

    执行完这个，你的磁盘占用可能会瞬间从几十 GB 掉到 几 GB，非常适合你的 VPS。

5. 针对 mod_perl 的特别提醒

既然内存只有 4GB，除了限制数据库，你必须限制 Apache/mod_perl 的进程数。否则 Apache 会不断生成子进程，直到把内存吃光，然后 Linux OOM Killer 会随机杀掉 Postgres 或 Apache。

建议检查 Apache 配置 (通常在 mpm_prefork.conf 或 mpm_event.conf):

总结行动计划

  1. 修改 postgresql.conf，调低内存（shared_buffers=768MB）、调低并发（max_worker_processes=6）、调低 WAL 大小（2GB）。

  2. 启用 synchronous_commit = off 和 random_page_cost = 1.1 榨干 NVMe 性能。

  3. 在数据库中对遥测表执行 ALTER TABLE ... SET (timescaledb.compress ...) 节省宝贵的 60GB 空间。

  4. 检查 Apache 配置，限制最大连接数，防止内存爆表。

如果16core/16gb vps，考虑如下配置

# ------------------------------------------------------------------------------
# 针对 16GB RAM / 16 Core VDS 的调整
# ------------------------------------------------------------------------------

# --- 内存分配 (大幅提升) ---
# 给数据库 4GB 专用内存，查询速度飞起
shared_buffers = 4GB                  

# 每个查询可以用 32MB 内存做排序，复杂统计更快
work_mem = 32MB                       

# 维护操作（建索引/清理）给够内存，瞬间完成
maintenance_work_mem = 512MB          

# 告诉优化器系统有 10GB+ 的文件缓存可用
effective_cache_size = 10GB           

# --- 并行查询 (利用 16 核心优势) ---
# 允许更多后台进程
max_worker_processes = 16             

# 复杂的分析查询（如三相不平衡）可以同时调用 4 个核心并行计算
max_parallel_workers_per_gather = 4   
max_parallel_workers = 8              

# --- 自动清理 (CPU核心多，可以适当放宽) ---
autovacuum_max_workers = 5            

# --- 写入优化 ---
# 内存大了，WAL 缓冲区也可以大一点
wal_buffers = 64MB                    
# 磁盘空间如果紧张，保持 2GB；如果不紧张，可以设为 4GB 减少检查点频率
max_wal_size = 4GB                    

# --- 其他 ---
# 连接数可以适当增加，mod_perl 进程多也不怕
max_connections = 200