posgre存范围值_PosgreSQL快速参数调优和sysbench压测

关于PostgreSQL的性能调优可以参考《PostgreSQL 9.0 High Performance》，以及朱贤文在2014 PostgreSQL中国用户大会上分享的《高性能Postgres 最佳实践》。当然，首先还是应该看看PostgreSQL手册的相关章节。我们在调优时不必每个细节都做到最优，抓住主要矛盾即可。因为有些东西不在你的控制之下，或者那样优化之后维护起来麻烦。下面尝试在虚机下进行快速的PostgreSQL参数调优。

1. 虚机环境

CPU: 4 core

Mem: 8G

OS: CentOS 6.3(64 Bit)

PostgreSQL:9.4.5

sysbench:0.4.12

文件系统:ext4

2.参数设置

基本的性能参数设置可以利用下面这个在线小工具评估。

填入系统信息，并固定最大连接数为300后，选择不同DB Type，这个工具会给出不同的参数。

Web applications

max_connections = 300

shared_buffers = 2GB

effective_cache_size = 6GB

work_mem = 6990kB

maintenance_work_mem = 512MB

checkpoint_segments = 32

checkpoint_completion_target = 0.7

wal_buffers = 16MB

default_statistics_target = 100

Online transaction processing systems

max_connections = 300

shared_buffers = 2GB

effective_cache_size = 6GB

work_mem = 6990kB

maintenance_work_mem = 512MB

checkpoint_segments = 64

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 100

Data warehouses

max_connections = 300

shared_buffers = 2GB

effective_cache_size = 6GB

work_mem = 3495kB

maintenance_work_mem = 1GB

checkpoint_segments = 128

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 500

上面3种DB Type，越往后写越重，checkpoint的频率也调得越低。由于后面要做OLTP的性能评估，所以选用Online transaction processing systems的设置。

综合考虑log等需求，初步在postgresql.conf中设置参数如下

listen_addresses = '*'

port = 5432

max_connections = 300

shared_buffers = 2GB

effective_cache_size = 6GB

work_mem = 6990kB

maintenance_work_mem = 512MB

checkpoint_segments = 64

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistic