【PGCCC】commit_delay 对性能的提升：PostgreSQL 基准测试

原创 于 2025-02-10 16:03:05 发布 · 907 阅读 · 7 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#postgresql #数据库

在这里插入图片描述

通过禁用参数可以来调整事务工作负载synchronous_commit。该措施有惊人效果。但在操作系统崩溃期间丢失已提交事务的可能性使其成为许多应用程序无法启动的因素。因此我决定写下来。

WAL 刷新是事务数据库工作负载的瓶颈

为了确保已提交的事务不会丢失，PostgreSQL 必须确保事务的WAL已刷新到磁盘，然后才能向客户端报告成功。如果数据库工作负载主要由小数据修改组成，则这些事务产生的IOPS可能会使磁盘饱和，即使写入的数据量适中。

参数对commit_delay可以commit_siblings通过减少这些 WAL 刷新所需的 IOPS 数量来缓解瓶颈。

commit_delay和如何commit_siblings工作？

您可以通过将其设置为大于零的值来激活该功能commit_delay。每当事务在提交期间达到将 WAL 刷新到磁盘的点时，它首先检查当前有多少其他事务处于活动状态。如果至少有其他commit_siblings事务处于打开状态并且没有等待锁定，PostgreSQL 不会立即刷新 WAL，而是等待commit_delay几微秒。经过该延迟后，其他一些事务可能已达到准备刷新 WAL 的点。然后，所有这些后端都可以在单个 I/O 操作中执行其 WAL 刷新。

commit_delay调整起来并不容易，因为延迟会使事务耗时更长。另一方面，如果选择的值太低，则在延迟过去时可能没有其他事务准备就绪，并且您无法减少执行的 IOPS 数量。

commit_delay基准设置

基准测试在我的 ASUS ZenBook UX433F 笔记本上运行，该笔记本具有本地 NVME 磁盘、8 个 CPU 核心和 16GB RAM。我设置了sh

最低0.47元/天 解锁文章
【PG基础知识】七 postgreSQL参数解释二 WRITE-AHEAD LOG部分
DBA之路的博客
11-06 1058
postgreSQL参数解释二 WRITE-AHEAD LOG部分
PostgreSQL数据库的性能测试报告生成
AI天才研究院
06-21 1249
本报告系统阐述PostgreSQL性能测试报告的生成方法论，覆盖从测试设计到报告输出的全生命周期。通过结合第一性原理（如Little定律、排队论）与工程实践，构建包含基准测试、负载测试、压力测试的多层次测试框架；定义涵盖吞吐量（QPS）、响应时间（RT）、并发能力（CC）、资源利用率（CPU/Memory/Disk/I/O）的核心指标体系；解析pgBench、sysbench、JMeter等工具的适配场景；最终形成结构化报告模板，包含测试环境、用例设计、结果分析、瓶颈诊断、优化建议五大模块。
PostgreSQL 配置文件详解
技术小站
11-01 2444
如果要查看配置文件中的一些选项，则可以登录psql后 使用 命令来查看； show 选项名; show all； #查看所有数据库参数的值 主要选项： 选项 默认值 说明 是否优化 原因 max_connections 100 允许客户端的最大并发连接数目 否 因为在测试的过程中，100个连接已经足够 fsync on 强制把数据同步更新到磁盘 是 因为系统的IO压力很大，为了更好的测试其他配置的影响，把改参数改为off s...
Postgresql关于wal日志总结，一文搞清楚它的所有概念和相关操作
lusklusklusk的博客
03-19 1万+
官方文档 https://www.postgresql.org/docs/11/wal-intro.html https://www.postgresql.org/docs/11/wal-configuration.html https://www.postgresql.org/docs/11/runtime-config-wal.html https://www.postgresql.org/docs/11/runtime-config-preset.html https://www.postgresql
postgresql.conf commit_delay
baidu_24377669的博客
07-20 294
postgresql.conf commit_siblings
baidu_24377669的博客
07-20 304
【PostgreSQL】性能飞跃：PostgreSQL性能调优与优化策略
weixin_43298211的博客
06-29 1712
通用搜索树（GiST）索引是一种灵活的索引类型，支持多种数据类型和查询类型，包括全文搜索、空间数据查询等。它适用于地理信息系统（GIS）应用、全文搜索等场景，虽然查询效率可能不如特化索引（如 GIN 索引对于全文检索），但其广泛的适用性使其成为处理复杂查询的理想选择。GiST索引通过支持多种查询操作符类，允许用户定义特定的查询条件，从而提高查询效率。适用于那些经常执行精确匹配且不需要排序的场景，但要注意，如果索引列有大量重复值，Hash 索引的效率会降低。解读查询计划，识别慢查询的瓶颈，如全表扫描。
解决git clone时client_loop: send disconnect: Connection reset by peer/s错误
热门推荐
explore_inload的博客
04-05 1万+
为了学习李笑来老师在github上的python内容，先将https://github.com/selfteaching/the-craft-of-selfteaching上的资源fork到自己的账号下，然后再git clone到本地，我用的是SSH协议。 $ git clone git@github.com:username/the-craft-of-selfteaching...
Postgresql实战：使用pg_basebackup或pg_start_backup方式搭建Postgresql主从流复制
lusklusklusk的博客
03-27 2761
Postgresql实战：使用pg_basebackup或pg_start_backup方式搭建Postgresql主从流复制
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 30 章 可靠性和预写式日志_30.3. 异步提交...
weixin_33737774的博客
10-03 153
30.3.异步提交 异步提交是一个允许事务能更快完成的选项，代价是在数据库崩溃时最近的事务会丢失。在很多应用中这是一个可接受的交换。 如前一节所述，事务提交通常是同步的：服务器等到事务的WAL记录被刷写到持久存储之后才向客户端返回成功指示。因此客户端可以确保那些报告已被提交的事务确会被保存，即便随后马上发生了一次服务器崩溃。但是，对于短事务来说这...
进阶数据库系列（二十三）：PostgreSQL 性能优化
民工哥的博客
07-21 2786
点击下方名片，设为星标！回复“1024”获取2TB学习资源！前面介绍了PostgreSQL基于 Patroni 高可用架构部署及故障切换、基于 repmgr 高可用架构实践、基于 pgpool 实现读写分离实践、数据库备份与恢复、主从数据目录同步工具 pg_rewind、数据库作业调度工具等相关的知识点，今天我将详细的为大家介绍 PostgreSQL 性能优化相关知识，希望大家能够从中收获多多...
KingbaseESV8R6延迟提交参数
lyu1026的博客
09-18 117
前言 队列理论在我们生活中的应用随处可见，例如我们去食堂打饭需要排队，我们生活中随处可见排队的场景。 在计算机领域中，性能诊断等地方使用队列理论的案例也很多。服务器硬件分为动态设备和静态设备。CPU和IO子系统属于动态设备，RAM属于静态设备，队列理论只对动态设备适用。 KingbaseES数据库中遇到的队列理论 一个数据库系统的响应时间(DBTIME)由两部分决定，前台进程非空闲等待时间和cp...
PostgreSQL数据库WAL总结
qq_58883939的博客
04-26 3926
简单地说，WAL的核心概念是，对数据文件(表和索引所在的地方)的更改必须在写入了日志文件后这些更改之后才可以写入数据文件，也就是说，描述更改的日志记录被刷新到永久存储之后才可以写数据文件。使用WAL可以显著减少磁盘写操作的数量，因为只需要将日志文件刷新到磁盘以确保提交事务，而不是事务更改的每个数据文件。日志文件是按顺序写入的，因此同步日志的成本要比刷新数据页的成本低得多。备注：数据库将脏数据刷到数据文件上,这个动作是随机I/O,性能比写日志的顺序I/O差太多总结
pg中wal日志参数对提交和未提交事务的控制
weixin_43084715的博客
02-17 1003
脏数据（未提交）和脏页（已经被修改的页） 脏数据是指事务对缓冲池中行记录的修改，并且还没有提交。 注意脏数据和脏页是完全不同的两种概念，脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据不一致。脏页的读取是非常正常的，脏页是数据库实例内存和磁盘异步造成的，这并不影响数据的一致性，脏页最终会被刷新到磁盘中。 wal_buffers：控制wal buffer的大小,默认值为shared_buffers大小的1/32。 wal_writer_delay：控
PostgreSQL 参数调整(性能优化)
大JAVA解决方案
08-05 4105
昨天分别在外网和无外网环境下安装PostgreSQL，有外网环境下安装的相当顺利。但是在无外网环境下就是两个不同的概念了，可谓十有八折。感兴趣的同学可以搭建一下。 PostgreSQL安装完成后第一件事便是做相关测试，然后调整参数。 /*CPU 查看CPU型号*/ cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c /*查看物理CPU个数*/ cat /proc/cpuinfo | grep "physical id" | sor...
linux常用命令分类总结！
Nick Blog
12-21 2248
文章目录一、文件和目录1. cd2. pwd3. ls4. cp5. mv6. rm二、查看文件内容1. cat三、文件搜索1. find四、文件的权限1. chmod2. chown3. chgrp五、文本处理1. grep2. paste3. sort4. comm六、打包和压缩文件七、系统和关机（关机、重启和登出）八、进程相关的命令1. ps2. kill3. killall4. top 一、文件和目录 1. cd 用于切换当前目录，它的参数是要切换到的目录的路径，可以是绝对路径，也可以是相对路径。
5.3 PostgreSQL数据库事务管理
Python老吕的博客
09-23 2239
事务是数据库管理系统中的一个核心概念，它表示一组不可分割的数据库操作序列。这些操作要么全部成功，要么全部不成功，是一个原子性的单元。定义事务是数据库执行过程中的一个逻辑单位，由一系列的数据库操作组成。特性原子性（Atomicity）：事务是最小的工作单元，其操作要么全部完成，要么全部不完成。一致性（Consistency）：事务必须保证数据库从一个一致的状态转移到另一个一致的状态。隔离性（Isolation）：并发执行的事务之间不会互相影响。持久性（Durability）
postgresql server configuration
数据库
05-10 168
1.postgres=# select distinct(context) from pg_settings; context ------------ backend --类似于sighup，更改后不会影响正在连接的会话，修改之后的新会话才会采用修改的值，这类参数很少，如log_connections user --修改当前会话的参数，只在该会话生效 internal --数据库内部...
PostgreSQL配置参数调优
Bangca的博客
07-19 2904
/CPU 查看CPU型号/ cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c /查看物理CPU个数/ cat /proc/cpuinfo | grep “physical id” | sort -u | wc -l /查看逻辑CPU个数/ cat /proc/cpuinfo | grep “processor” | wc -l /查看CPU内核数/ cat /proc/cpuinfo | grep “cpu cores” | uniq /查看单个物理
# global settings pid_dir: '~/.pg_chameleon/pid/' log_dir: '~/.pg_chameleon/logs/' log_dest: file log_level: info log_days_keep: 10 rollbar_key: '' rollbar_env: '' dump_json: No alert_log_collection_enable: false alert_log_kafka_server: 127.0.0.1:9092 alert_log_kafka_topic: my_log # type_override allows the user to override the default type conversion # into a different one. type_override: # "tinyint(1)": # override_to: boolean # override_tables: # - "*" # specify the compress properties when creating tables compress_properties: compresstype: 0 compress_level: 0 compress_chunk_size: 4096 compress_prealloc_chunks: 0 compress_byte_convert: false compress_diff_convert: false # postgres destination connection pg_conn: host: "localhost" port: "5432" user: "usr_replica" password: "never_commit_passwords" database: "db_replica" charset: "utf8" params: # maintenance_work_mem: "1G" # param1: value1 # param2: value2 sources: mysql: readers: 4 writers: 4 retry: 3 db_conn: host: "localhost" port: "3306" user: "usr_replica" password: "never_commit_passwords" charset: 'utf8' connect_timeout: 10 schema_mappings: delphis_mediterranea: loxodonta_africana limit_tables: - delphis_mediterranea.foo skip_tables: - delphis_mediterranea.bar enable_compress: No compress_tables: - delphis_mediterranea.foo grant_select_to: - usr_readonly lock_timeout: "120s" my_server_id: 100 replica_batch_size: 10000 replay_max_rows: 10000 batch_retention: '1 day' copy_max_memory: "300M" copy_mode: 'file' out_dir: /tmp csv_dir: /tmp contain_columns: No column_split: ',' sleep_loop: 1 on_error_replay: continue on_error_read: continue auto_maintenance: "disabled" index_parallel_workers: 2 gtid_enable: false type: mysql skip_events: insert: - delphis_mediterranea.foo # skips inserts on delphis_mediterranea.foo delete: - delphis_mediterranea # skips deletes on schema delphis_mediterranea update: keep_existing_schema: No migrate_default_value: Yes mysql_restart_config: No is_create_index: Yes index_dir: '~/.pg_chameleon/index/' is_skip_completed_tables: No with_datacheck: No slice_size: 100000 csv_files_threshold: csv_dir_space_threshold:
最新发布
07-05
这是 **pg_chameleon** 工具的完整配置文件（YAML 格式），用于从 **MySQL** 迁移数据到 **PostgreSQL**（或 openGauss）。配置分为全局设置、目标库连接、源库连接和迁移规则四大部分，支持离线迁移和实时同步（基于 binlog）。以下是详细解析： --- ### **1. 全局设置（Global Settings）** ```yaml pid_dir: '~/.pg_chameleon/pid/' # 进程 ID 文件存储目录 log_dir: '~/.pg_chameleon/logs/' # 日志文件存储目录 log_dest: file # 日志输出目标（file=文件，stdout=控制台） log_level: info # 日志级别（debug/info/warning/error） log_days_keep: 10 # 日志保留天数 rollbar_key: '' # Rollbar 错误监控服务 API Key（未启用） rollbar_env: '' # Rollbar 环境名称（未启用） dump_json: No # 是否导出迁移过程为 JSON（否） alert_log_collection_enable: false # 是否启用日志告警收集（否） alert_log_kafka_server: 127.0.0.1:9092 # Kafka 服务器地址（用于日志告警） alert_log_kafka_topic: my_log # Kafka 主题名称 ``` #### **关键作用** - 控制工具的运行行为（如日志、进程管理）。 - 支持将日志发送到 Kafka 等外部系统（当前未启用）。 --- ### **2. 类型覆盖（Type Override）** ```yaml type_override: # "tinyint(1)": # MySQL 类型 # override_to: boolean # 覆盖为 PostgreSQL 的 boolean 类型 # override_tables: # 适用表范围 # - "*" # 所有表 ``` #### **作用** - 自定义 MySQL 数据类型到 PostgreSQL 的映射规则。 - **示例场景**：将 MySQL 的 `tinyint(1)`（通常用于布尔值）自动转换为 PostgreSQL 的 `boolean` 类型。 - **当前状态**：配置被注释，未生效。 --- ### **3. 压缩属性（Compress Properties）** ```yaml compress_properties: compresstype: 0 # 压缩类型（0=不压缩） compress_level: 0 # 压缩级别（0-9，0=不压缩） compress_chunk_size: 4096 # 压缩块大小（字节） compress_prealloc_chunks: 0 # 预分配压缩块数量 compress_byte_convert: false # 是否转换字节序 compress_diff_convert: false # 是否转换差分数据 ``` #### **作用** - 配置迁移过程中数据的压缩选项（当前未启用压缩）。 - **适用场景**：迁移大表时减少网络传输量（需 PostgreSQL 支持压缩扩展）。 --- ### **4. PostgreSQL 目标连接（pg_conn）** ```yaml pg_conn: host: "localhost" # PostgreSQL 服务器地址 port: "5432" # PostgreSQL 端口 user: "usr_replica" # 连接用户名 password: "never_commit_passwords" # 连接密码（安全提示：实际使用时应从环境变量或密钥管理服务读取） database: "db_replica" # 目标数据库名 charset: "utf8" # 字符集 params: # 额外连接参数（可选） # maintenance_work_mem: "1G" # 示例：设置维护内存为 1GB # param1: value1 # param2: value2 ``` #### **关键参数** - **`user`/`password`**：需确保该用户有权限在目标库创建表和插入数据。 - **`params`**：可配置 PostgreSQL 的运行时参数（如 `work_mem`、`maintenance_work_mem`）。 --- ### **5. MySQL 源库连接（sources → mysql）** ```yaml sources: mysql: readers: 4 # 读取线程数（并行读取 MySQL 数据） writers: 4 # 写入线程数（并行写入 PostgreSQL） retry: 3 # 操作失败后的重试次数 db_conn: # MySQL 连接配置 host: "localhost" port: "3306" user: "usr_replica" password: "never_commit_passwords" charset: 'utf8' connect_timeout: 10 # 连接超时时间（秒） schema_mappings: # 模式（Schema）映射 delphis_mediterranea: loxodonta_africana # 将 MySQL 的 delphis_mediterranea 映射为 PostgreSQL 的 loxodonta_africana limit_tables: # 仅迁移指定表 - delphis_mediterranea.foo skip_tables: # 跳过指定表 - delphis_mediterranea.bar enable_compress: No # 是否启用数据压缩（当前禁用） compress_tables: # 压缩表列表（需 enable_compress=Yes 才生效） - delphis_mediterranea.foo grant_select_to: # 授权只读用户访问迁移后的表 - usr_readonly lock_timeout: "120s" # 获取锁的超时时间 my_server_id: 100 # MySQL 复制中的服务器 ID（实时同步时使用） replica_batch_size: 10000 # 每次批量复制的行数 replay_max_rows: 10000 # 每次重放的行数 batch_retention: '1 day' # 批量数据的保留时间 copy_max_memory: "300M" # COPY 命令的最大内存使用量 copy_mode: 'file' # COPY 模式（file=通过文件传输，direct=直接传输） out_dir: /tmp # 临时文件输出目录 csv_dir: /tmp # CSV 文件存储目录 contain_columns: No # 是否包含列名在 CSV 文件中 column_split: ',' # CSV 文件的列分隔符 sleep_loop: 1 # 循环间隔（秒） on_error_replay: continue # 重放错误时的行为（continue=继续，abort=终止） on_error_read: continue # 读取错误时的行为 auto_maintenance: "disabled" # 是否自动维护（如真空表） index_parallel_workers: 2 # 创建索引的并行工作线程数 gtid_enable: false # 是否启用 GTID（全局事务标识符） type: mysql # 源数据库类型 skip_events: # 跳过特定事件 insert: - delphis_mediterranea.foo # 跳过对 delphis_mediterranea.foo 的 INSERT 操作 delete: - delphis_mediterranea # 跳过对 delphis_mediterranea 模式的 DELETE 操作 update: # 未配置 UPDATE 跳过规则 keep_existing_schema: No # 如果目标库已存在同名表，是否保留（No=覆盖） migrate_default_value: Yes # 是否迁移列的默认值 mysql_restart_config: No # 是否重启 MySQL 配置（通常不需要） is_create_index: Yes # 是否创建索引 index_dir: '~/.pg_chameleon/index/' # 索引文件存储目录 is_skip_completed_tables: No # 是否跳过已迁移完成的表 with_datacheck: No # 是否进行数据一致性校验 slice_size: 100000 # 数据分片大小（行数） csv_files_threshold: # CSV 文件大小阈值（未配置） csv_dir_space_threshold: # CSV 目录空间阈值（未配置） ``` #### **关键参数解析** 1. **`schema_mappings`**： - 将 MySQL 的 `delphis_mediterranea` 模式映射为 PostgreSQL 的 `loxodonta_africana` 模式。 - **用途**：解决 MySQL 和 PostgreSQL 中模式命名差异的问题。 2. **`limit_tables` 和 `skip_tables`**： - 仅迁移 `delphis_mediterranea.foo` 表，跳过 `delphis_mediterranea.bar` 表。 - **用途**：选择性迁移部分表。 3. **`skip_events`**： - 跳过对 `delphis_mediterranea.foo` 表的 INSERT 操作和对 `delphis_mediterranea` 模式的 DELETE 操作。 - **用途**：在实时同步中排除特定操作（如避免删除生产数据）。 4. **`copy_mode: 'file'`**： - 通过文件传输数据（而非直接网络传输），适用于大表迁移。 - **替代方案**：`copy_mode: 'direct'`（直接传输，速度更快但可能不稳定）。 5. **`batch_retention: '1 day'`**： - 保留批量数据 1 天，用于故障恢复。 - **用途**：避免重复迁移已处理的数据。 --- ### **6. 配置文件的典型用途** 1. **离线迁移**： - 从 MySQL 导出数据，转换语法后导入 PostgreSQL。 - 示例命令： ```bash pg_chameleon migrate --config /path/to/config.yaml --source mysql --target postgres ``` 2. **实时同步**： - 通过解析 MySQL 的 binlog 实现增量同步（需配置 `my_server_id` 和 `gtid_enable`）。 - 示例命令： ```bash pg_chameleon start_replica --config /path/to/config.yaml --source mysql ``` --- ### **7. 常见问题与优化** #### **Q1: 如何启用类型覆盖？** - 取消 `type_override` 部分的注释，并修改为实际需求： ```yaml type_override: "tinyint(1)": override_to: boolean override_tables: - "my_table" # 仅对 my_table 生效 ``` #### **Q2: 如何提升大表迁移性能？** - 调整以下参数： ```yaml sources: mysql: readers: 8 # 增加读取线程 writers: 8 # 增加写入线程 copy_max_memory: "1G" # 加大 COPY 内存 slice_size: 500000 # 增大分片大小 ``` #### **Q3: 如何安全存储密码？** - 避免在配置文件中明文存储密码，可通过以下方式： 1. 使用环境变量： ```yaml pg_conn: password: "${DB_PASSWORD}" # 从环境变量读取 ``` 2. 使用密钥管理服务（如 AWS Secrets Manager）。 #### **Q4: 如何验证配置是否生效？** - 运行测试命令： ```bash pg_chameleon test_connection --config /path/to/config.yaml --source mysql pg_chameleon test_connection --config /path/to/config.yaml --target postgres ``` 输出应显示 `Connection to MySQL successful` 和 `Connection to PostgreSQL successful`。 #### **Q5: 如何跳过所有 UPDATE 操作？** - 在 `skip_events` 中添加： ```yaml skip_events: update: - "*" # 跳过所有 UPDATE 操作 ``` --- ### **8. 完整配置示例（优化版）** ```yaml pid_dir: '/var/run/pg_chameleon' log_dir: '/var/log/pg_chameleon' log_level: debug type_override: "tinyint(1)": override_to: boolean override_tables: - "*" compress_properties: compresstype: 1 # 启用压缩（需 PostgreSQL 扩展支持） compress_level: 3 pg_conn: host: "192.168.1.100" port: "5432" user: "replica_user" password: "${DB_PASSWORD}" # 从环境变量读取 database: "migrated_db" params: maintenance_work_mem: "1GB" sources: mysql: readers: 8 writers: 8 db_conn: host: "192.168.1.200" port: "3306" user: "replica_user" password: "${MYSQL_PASSWORD}" schema_mappings: my_mysql_db: my_postgres_db limit_tables: - my_mysql_db.large_table skip_tables: - my_mysql_db.log_* copy_mode: 'file' copy_max_memory: "500M" slice_size: 1000000 ``` --- ### **总结** | 配置段 | 作用 | |----------------------|----------------------------------------------------------------------| | **Global Settings** | 控制日志、进程管理等工具行为。 | | **Type Override** | 自定义 MySQL 到 PostgreSQL 的数据类型映射。 | | **Compress** | 配置迁移数据的压缩选项（减少网络传输）。 | | **pg_conn** | 定义 PostgreSQL 目标数据库的连接参数和权限。 | | **sources → mysql** | 定义 MySQL 源库的连接参数、迁移规则和性能调优选项。 | ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值