Postgres优化数据库服务端性能 -- 未完待续

总述

优化数据库服务端性能

硬件层面主要包括CPU、I/O、内存及网络等影响因素。
软件层面的影响因素则要复杂得多，主要包括操作系统、数据库中间件、文件系统等。值得一提的是，数据库本身的配置参数优化有时会产生预想不到的效果

标准？优化？

SmartX 携手 openGauss 社区发布联合方案评测与性能最佳实践

硬件

服务器基本知识

系统各层次性能概况

服务器主板组成

服务器架构图

系统后视图

CPU

lscpu

查看linux系统CPU型号

睿频、超频、主频、、、

I/O

列举 HDD SSD NVMe 的 io和iops 的指标

内存

内存型号

网络

服务器网卡介绍

软件

针对操作系统 (OS)、文件系统和 PostgreSQL (PG) 数据库的具体优化建议，可以从以下几个方面着手：

1. 操作系统优化

• CPU调度与多核处理优化：

  • 使用合适的调度算法，如 CFS（完全公平调度器）以更好地利用多核处理器，尤其是在高并发应用场景中，确保任务均匀分配给不同核心。
  • 通过 taskset 命令将计算密集型进程绑定到特定CPU核心，避免CPU资源争用。
  • 配置 CPU频率调节，选择性能模式（Performance）来提高CPU的最大频率，减少延迟。

• 内存管理：

  • 调整 vm.swappiness 参数以优化虚拟内存的使用，减少系统过度使用交换空间（swap）。
  • 配置合理的 overcommit_memory 和 overcommit_ratio 参数，避免内存超额分配导致的性能问题。
  • 使用 hugepages 来提高大内存页面的性能，尤其是在内存要求高的应用中。

• 磁盘I/O优化：

  • 配置磁盘的 I/O调度器，在Linux中常用的调度器有 deadline（低延迟）、cfq（公平调度）和 noop（不做任何调度）。根据具体应用选择最合适的调度器。
  • 使用 SSD 或 NVMe 存储设备，显著提升磁盘读写性能。
  • 启用 disk write-back cache，提高磁盘写入性能。

• 系统资源监控与自动化调优：

  • 定期使用 top、vmstat、iostat 等工具检查系统资源利用情况，及时发现瓶颈。
  • 配置自动化脚本，通过 cron 定期清理缓存文件、日志文件，释放系统资源。

2. 文件系统优化

• 选择合适的文件系统：

  • 如果磁盘频繁进行大文件存储、读取或写入操作，使用 XFS 或 ext4 文件系统，这两者都支持高效的并发读写。
  • 对于大文件系统，ZFS 文件系统可以通过其数据压缩功能和内存管理技术，减少磁盘的占用，并提高存储效率。

• 减少磁盘碎片：

  • 对于传统硬盘（HDD）使用 e4defrag 工具进行碎片整理，对于固态硬盘（SSD），由于没有机械运动部件，不需要进行碎片整理，但定期的TRIM操作可以提高SSD性能。
  • 定期通过fsck检查文件系统的一致性。

• 文件系统缓存优化：

  • 调整 vm.dirty_background_ratio 和 vm.dirty_ratio 参数来优化系统写缓存行为，避免因缓存过多导致性能下降。
  • 使用 tmpfs 将临时文件存储到内存中，减少磁盘I/O。

• 启用磁盘缓存：

  • 在硬盘驱动器上启用写回缓存，改善随机写入性能。

• 压缩和归档策略：

  • 对于不常用的文件，可以使用 btrfs 或 ZFS 等文件系统的内建压缩功能来节省磁盘空间。

3. PostgreSQL (PG) 数据库优化

• 查询性能优化：

  • 合理使用索引：避免无效索引，确保对查询条件（WHERE）和连接条件（JOIN）字段创建索引。对于大表的查询，使用 partial index 或 BRIN index 来优化查询性能。
  • 使用EXPLAIN分析查询：通过 EXPLAIN ANALYZE 来分析查询计划，识别性能瓶颈，如全表扫描、连接顺序不佳等，并调整查询方式。
  • 优化JOIN操作：避免在大数据集上使用多表嵌套查询，改用 JOIN 或 CTE（公用表表达式）来简化复杂查询。

• 数据库连接池：

  • 使用数据库连接池（如 PgBouncer 或 pgpool-II）来管理数据库连接，减少数据库连接和关闭的开销。
  • 配置合理的连接池参数，如 max_connections，确保连接池的大小与实际并发请求匹配。

• 内存管理与缓存：

  • 调整 work_mem 和 shared_buffers：根据可用内存调整这两个参数，以提升内存使用效率。shared_buffers 用于缓存查询的数据块，work_mem 控制排序和哈希表的内存。
  • 使用 effective_cache_size 来指示操作系统缓存的大小，帮助查询优化器做出更合适的决策。

• 表结构与数据存储优化：

  • 对于大表，使用 表分区（partitioning）技术来优化查询性能。例如，可以按照时间戳、ID范围等字段进行分区，减少查询时的数据扫描量。
  • 对大表的数据进行定期归档，删除过时或不常用的数据，减少主表的大小。

• 批量操作优化：

  • 使用批量插入（COPY 命令），避免一次插入单条记录导致的性能下降。
  • 对于大量更新，尽量使用 UPDATE 而不是删除再插入，以减少数据锁定和IO开销。

• 日志与检查点优化：

  • 配置合理的 checkpoint_timeout 和 checkpoint_completion_target，优化事务提交时的日志写入频率。
  • 设置适当的 log_statement 和 log_duration 来记录慢查询和高耗时操作，帮助后续进行优化。

通过以上措施，系统性能可以得到有效提升。在实施过程中，务必进行压力测试和性能监控，以确保优化措施的有效性。