PostgreSQL配置优化

PostgreSQL 部署及配置参数优化与常见问题汇总 本文全面介绍了PostgreSQL数据库的部署、配置优化与运维管理方案。内容涵盖单机二进制/源码安装、主从集群部署指导,重点讲解了关键参数调优(内存、WAL、并行查询等)和性能优化技巧(索引策略、查询优化、表分区)。针对常见问题如性能下降、连接耗尽、数据库膨胀等提供了具体解决方案,并详细阐述了物理/逻辑备份恢复策略。最后给出了安全配置建议,包括密码策略、网络访问控制、审计日志和SSL加密设置。文章还包含监控指标说明和定期维护任务清单,为构建高可用、高性能的PostgreSQL数据库环境提供了完整的技术参考。 阅读详情

 

  • PostgreSQL配置优化
      • 硬件和系统配置
      • 测试工具
      • 配置文件
      • 主要选项
      • 测试数据
      • 总结

 

硬件和系统配置

操作系统Ubuntu13.04
系统位数64
CPUIntel(R) Core(TM)2 Duo CPU
内存4G
硬盘Seagate ST2000DM001-1CH164
测试工具PostgreSQL-9.1.11

 

测试工具

工具名称pgbench
数据量200W(整个数据库大小约为300M)
模拟客户端数4
线程数4
测试时间60秒
  • 准备命令:pgbench -i -s 20 pgbenchdb
  • 测试命令:pgbench -r -j4 -c4 -T60 testdb

 

配置文件

默认的配置配置文件是保存在/etc/postgresql/VERSION/main目录下的postgresql.conf文件

  • 如果想查看参数修改是否生效,可以用psql连接到数据库后,用<show 选项名> 来查看。
  • 如果要修改shared_buffers, 在ubuntu下可能需要执行命令<sysctl -w>Managing Kernel Resources

 

主要选项

选项默认值说明是否优化原因
max_connections100允许客户端连接的最大数目否因为在测试的过程中,100个连接已经足够
fsyncon强制把数据同步更新到磁盘是因为系统的IO压力很大,为了更好的测试其他配置的影响,把改参数改为off
shared_buffers24MB决定有多少内存可以被PostgreSQL用于缓存数据(推荐内存的1/4)是在IO压力很大的情况下,提高该值可以减少IO
work_mem1MB使内部排序和一些复杂的查询都在这个buffer中完成是有助提高排序等操作的速度,并且减低IO
effective_cache_size128MB优化器假设一个查询可以用的最大内存,和shared_buffers无关(推荐内存的1/2)是设置稍大,优化器更倾向使用索引扫描而不是顺序扫描
maintenance_work_mem16MB这里定义的内存只是被VACUUM等耗费资源较多的命令调用时使用是把该值调大,能加快命令的执行
wal_buffer768kB日志缓存区的大小是可以降低IO,如果遇上比较多的并发短事务,应该和commit_delay一起用
checkpoint_segments3设置wal log的最大数量数(一个log的大小为16M)是默认的48M的缓存是一个严重的瓶颈,基本上都要设置为10以上
checkpoint_completion_target0.5表示checkpoint的完成时间要在两个checkpoint间隔时间的N%内完成是能降低平均写入的开销
commit_delay0事务提交后,日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling是能够一次写入多个事务,减少IO,提高性能
commit_siblings5设置触发commit_delay的并发事务数,根据并发事务多少来配置是减少IO,提高性能

 

测试数据

  • 测试的数据是运行3次,取平均值。
  • 关闭fsync是为了更好的体现出其他参数对PostgreSQL的影响。
参数修改值事务总数tps(包括建立连接)tps(不包括建立连接)
默认设置 8464140.999792141.016182
fsyncoff925711479.9697551480.163355
shared_buffers1GB1000551635.7592751635.977823
work_mem10MB1012091665.8048121666.04082
effective_cache_size2GB982091636.7331521636.970271
maintenance_work_mem512MB929301548.0292331548.223108
checkpoint_segments321959823265.9953266.471064
checkpoint_completion_target0.91943903239.4064933239.842596
wal_buffer8MB1986393310.2414583310.724067
恢复fsyncoff11157185.883542185.909849
commit_delay && commit_siblings10 && 411229187.103538187.131747

 

总结

 事务总数tps(包括建立连接)tps(不包括建立连接)
优化前8464140.999792141.016182
优化后(fsync=on)11229187.103538187.131747
优化后(fsync=off)1986393310.2414583310.724067

在fsync打开的情况下,优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势,如果到实际测试中,提升应该不止30%。
测试的过程中,主要的瓶颈就在系统的IO,如果需要减少IO的负荷,最直接的方法就是把fsync关闭,但是这样就会在掉电的情况下,可能会丢失部分数据。

作者:roddick621 发表于2013-12-26 9:21:16 原文链接

阅读:0 评论:0 查看评论

如何优化 PostgreSQL 中的存储引擎配置? PostgreSQL 使用的是多版本并发控制(MVCC)机制,这就像是一个时间穿梭机,让不同的事务能够看到数据库在不同时间点的“快照”,从而实现并发操作而互不干扰。曾经参与过一个金融交易系统的优化项目,由于交易的实时性要求非常高,我们对关键表的索引进行了精心设计,并通过调整共享缓冲区和有效_io_concurrency 等参数,使得系统在高并发情况下依然能够快速响应,保障了交易的顺利进行。比如,对于一个在线交易系统,要求数据库的响应时间极短,可能需要更注重索引的优化和内存的配置; 阅读详情

相关推荐

麒麟银河操作系统V10部署ffmpeg(也能用于Linux系统)

部署ffmpeg用来处理视频的各种操作,如编码解码,格式转换,剪辑,处理实时视频流,音频流,加滤镜等

zhouwenxing666的博客 9043

PostgreSQL参数优化

PostgreSQL中与内存有关的配置参数如下。shared_buffers:共享缓存区的大小,相当于Oracle数据库中的SGA,主要做数据块的缓存。work_mem:为每个进程单独分配的内存,主要用于排序、HASH等操作。maintence_work_mem:为每个进程单独分配的内存,主要是进行维护操作时需要的内存,如VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY等操作需要的内存。

ciqingloveless的博客 4587

Visual Studio报错TRK0005找不到CL.exe?三步彻底解决环境变量问题

本文详细解析了Visual Studio报错TRK0005找不到CL.exe的根本原因,并提供了三步彻底解决环境变量问题的方案。从验证安装完整性到配置永久环境变量,再到注册表级修复,帮助开发者建立健壮的开发环境配置体系,确保编译链稳定运行。

weixin_30839881的博客 459

PostgreSQL数据库的部署与优化

(5)开发接口:PostgreSQL 提供了丰富的编程接口和很多扩展机制,MySQL 也提供了许多编程接口和外部库连接的支持,Oracle 支持丰富的编程语言如 Java、C++、C# 等,并且提供较为完善的相关开发文档和开发支持友好。(3)数据处理和分析:PostgreSQL 的高级查询分析器和支持复杂查询的计算引擎,以及许多第三方扩展,可以帮助用户快速高效地对数据进行分析和查询,支持复杂的数据处理,如分组、过滤、联合查询、递归查询等。然而,过多或不必要的索引可能会导致性能下降,因此需要进行权衡和优化。

Katie_ff的博客 3525

Ubuntu系统下主板GPU温控pwm风扇

在Linux系统下——以Ubuntu为例,配置主板sys_fan等接口连接pwm温控风扇转速根据NVIDIA GPU温度,为服务器GPU等无主动散热显卡提供散热。

罗思哲的博客 5040

保姆级避坑指南:用IWR6843ISK毫米波雷达实现区域检测,从软件安装到可视化调试全流程

本文提供IWR6843ISK毫米波雷达区域检测的全流程实战指南,涵盖环境搭建、固件烧录、可视化工具配置及性能优化等关键步骤。重点解决软件版本兼容性、烧录失败等常见问题,帮助开发者高效实现精准区域检测,避免开发过程中的常见陷阱。

weixin_42653691的博客 376

PostgreSQL性能调优 postgresql.conf参数调优

PostgreSQL性能调优 调整硬件配置 在大容量的数据库中,适当的硬件配置也是提高性能的一个途径。 1.存储器 目前的电脑内存是大增了,8GB也是主流了,内存增大肯定会比内存小的时候性能要高。但比如数据库是几百 GB的 时候,怎么也不可能只通过内存就可以解决了。数据量大的时候,高速的存储介质也是非常重要的,主要用途不同对存储介质的要求也不一样。 1)OLAP(联机分析处理) 行扫描的查询为主体的场合,大量的数据通过 IO流来交换,这个时候IO控制器的性能就比较重要了。增加硬盘的场合RAID

秋̶᭄ꦿ攻城狮࿆ྂ 3125

Ubuntu 24.04 LTS安装WPS Office 2025冬季版全攻略(附字体缺失解决方案)

本文提供了在Ubuntu 24.04 LTS系统上安装WPS Office 2025冬季版的完整指南,详细对比了dpkg、Snap和GUI三种安装方式的优劣。针对安装后常见的中文字体缺失、思源黑体粗细异常等核心问题,给出了从原理分析到手动补充字体库、修改字体配置文件的深度解决方案,确保WPS Office在Linux环境下获得最佳使用体验。

weixin_29284201的博客 620

PostgreSQL 性能优化全方位指南:深度提升数据库效率

PostgreSQL 性能优化全方位指南:深度提升数据库效率!!在现代互联网应用中,数据库性能优化是系统优化中至关重要的一环,尤其对于数据密集型和高并发的应用而言,PostgreSQL(以下简称PG)凭借其丰富的特性和强大的功能,成为很多企业的首选。然而,随着数据规模的扩展和查询复杂度的提升,PostgreSQL的性能问题逐渐显现。本文将详细介绍PostgreSQL性能优化的各个方面,涵盖硬件调优、数据库配置、索引使用、查询优化等内容,帮助你全方位提升数据库的效率。

2403_83182682的博客 5679

计算机视觉:相机模型与参数标定

一、相机投影模型 1、小孔成像 2、相机模型中的四个平面坐标系 3、像主点偏移 4、畸变现象与内参矩阵 5、外参矩阵 二、相机标定:张正友棋盘格标定法 1、张正友棋盘格标定法描述 2、计算外参和内参 2.1 计算单应性矩阵H 2.2 计算内参数矩阵 2.3 计算外参数矩阵 3、实验:MATLAB实现 3.1 实验数据 3.2实验过程 3.3 实验结果 4、实验:OpenCV实现 4.1 实现代码 4.2 实验结果 5、实验分析与总结

DreamWendy的博客 2514

2万字大厂面经:小米嵌入式面试题及参考答案(持续更新)

BIOS (Basic Input/Output System) 是一种存储在ROM中的固件程序,它为计算机提供了最基础的硬件初始化和服务。BIOS主要存在于传统的x86架构计算机中。嵌入式系统的启动流程取决于具体的硬件平台和所采用的操作系统。复位阶段:当系统通电或复位时,CPU进入复位状态。硬件初始化:CPU和必要的硬件设备(如内存控制器)被初始化。加载引导加载程序:引导加载程序(如U-Boot)被加载到内存中。执行引导加载程序:引导加载程序开始执行,初始化更多的硬件设备,并检测存储介质。

大模型大数据攻城狮的专栏 2530

postgresql大表join优化

postgresql大表join优化 一、背景 1、数据量: 表名 数据量 f_invoice 87346130 f_invoice_item 97535867 2、索引: 表:f_invoice_item CREATE INDEX f_invoice_item_order_item_id_idx ON ins_dw_prd12.f_invoice_item USING btree (order_item_id) CREATE INDEX f_invoice_ite

西湖烟雨梦的专栏 6800

8、内存交织技术详解

本文详细解析了内存交织技术的基本原理、工作机制及实现方式,探讨了其在提升内存访问速度和系统性能中的关键作用。文章还分析了内存交织的优缺点、实际应用场景以及与其他优化技术的结合应用,并提出了未来发展的可能方向,为理解与运用该技术提供了全面参考。

g9h0i1的博客 553

PostgreSQL 配置优化

PostgreSQL配置优化 PostgreSQL配置优化 硬件和系统配置 测试工具 配置文件 主要选项 测试数据 总结 硬件和系统配置 操作系统 Ubuntu13.04 系统位数 64 CPU Intel(R) Core(TM)2 Duo CPU 内存 4G 硬盘 Seagate ST2000DM001-1CH164 测试工具 PostgreSQL-9.1.11 测试工具 工具名称 pg

技术小站 3256

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

Camera2Demo

在Camera2 的基础上进行封装,使得Camera2一堆初始化逻辑与Activity分离,降低耦合度,完成预览拍照并保存的操作

PostgreSQL优化工具配置-pgtune

pgtune是一个用于优化配置的开源工具。它可以自动根据你的硬件和工作负载为你生成合适的PostgreSQL配置文件。详情介绍见官网。

sinat_34953318的博客 778

android 蓝牙通讯demo

android 下蓝牙通讯demo,实现了发现蓝牙,使能蓝牙,通过蓝牙发送消息。

PostgreSQL优化配置

PostgreSQL优化配置

GNAIXGNAHZ的博客 1114

VC中sqlite的使用

在VC中对sqlite的使用,实现数据库大批数据的快速插入,读取数据库中的数据在ListCtrl中显示

PostgreSQL 批量COPY导入优化参数配置

本文针对高频批量导入场景(如日志表、临时数据)提出PostgreSQL性能优化方案。通过调整关键参数实现极致写入性能:禁用同步提交(synchronous_commit=off)、延长WAL写入延迟(wal_writer_delay=200-500ms)、增大检查点间隔(checkpoint_timeout=30min)等,可将吞吐量提升数倍。建议导入时采用事务批处理、禁用约束索引、控制批次大小(如每批10,000行),并在导入后手动执行CHECKPOINT。这套配置特别适合可容忍数据丢失、支持重导入的场景

★【World Of Moshow 郑锴】★ 866
上一篇: 如何使用 Docker 来限制 CPU、内存和 IO等资源? 
下一篇: 使用旁路输出(side output)来拆分和复制流
王树民
博客等级 码龄10年 912粉丝 560原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值