PostgreSQL配置优化

硬件和系统配置

操作系统

Ubuntu13.04

系统位数

64

CPU

Intel(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_connections

100

允许客户端连接的最大数目

因为在测试的过程中,100个连接已经足够

fsync

on

强制把数据同步更新到磁盘

因为系统的IO压力很大,为了更好的测试其他配置的影响,把改参数改为off

shared_buffers

24MB

决定有多少内存可以被PostgreSQL用于缓存数据(推荐内存的1/4)

在IO压力很大的情况下,提高该值可以减少IO

work_mem

1MB

使内部排序和一些复杂的查询都在这个buffer中完成

有助提高排序等操作的速度,并且减低IO

effective_cache_size

128MB

优化器假设一个查询可以用的最大内存,和shared_buffers无关(推荐内存的1/2)

设置稍大,优化器更倾向使用索引扫描而不是顺序扫描

maintenance_work_mem

16MB

这里定义的内存只是被VACUUM等耗费资源较多的命令调用时使用

把该值调大,能加快命令的执行

wal_buffer

768kB

日志缓存区的大小

可以降低IO,如果遇上比较多的并发短事务,应该和commit_delay一起用

checkpoint_segments

3

设置wal log的最大数量数(一个log的大小为16M)

默认的48M的缓存是一个严重的瓶颈,基本上都要设置为10以上

checkpoint_completion_target

0.5

表示checkpoint的完成时间要在两个checkpoint间隔时间的N%内完成

能降低平均写入的开销

commit_delay

0

事务提交后,日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling

能够一次写入多个事务,减少IO,提高性能

commit_siblings

5

设置触发commit_delay的并发事务数,根据并发事务多少来配置

减少IO,提高性能

测试数据

  • 测试的数据是运行3次,取平均值。
  • 关闭fsync是为了更好的体现出其他参数对PostgreSQL的影响。

参数

修改值

事务总数

tps(包括建立连接)

tps(不包括建立连接)

默认设置

 

8464

140.999792

141.016182

fsync

off

92571

1479.969755

1480.163355

shared_buffers

1GB

100055

1635.759275

1635.977823

work_mem

10MB

101209

1665.804812

1666.04082

effective_cache_size

2GB

98209

1636.733152

1636.970271

maintenance_work_mem

512MB

92930

1548.029233

1548.223108

checkpoint_segments

32

195982

3265.995

3266.471064

checkpoint_completion_target

0.9

194390

3239.406493

3239.842596

wal_buffer

8MB

198639

3310.241458

3310.724067

恢复fsync

off

11157

185.883542

185.909849

commit_delay && commit_siblings

10 && 4

11229

187.103538

187.131747

总结

 

事务总数

tps(包括建立连接)

tps(不包括建立连接)

优化前

8464

140.999792

141.016182

优化后(fsync=on)

11229

187.103538

187.131747

优化后(fsync=off)

198639

3310.241458

3310.724067

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

原文链接:http://blog.csdn.net/kyle__shaw/article/details/17576259

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
上一篇:Mybatis-plus实现多表查询


下一篇:如何解决switct(ns) dns解析失败