postgresql – 如何从更新(软件和硬件)优化重I / O数据库

情况
我有一个 postgresql 9.2数据库,它一直在大量更新.因此系统受I / O限制,我正在考虑进行另一次升级,我只需要一些指示,从哪里开始改进.

以下是过去3个月情况的情况：

如您所见,更新操作占据了大部分磁盘利用率.这是另一张情况如何在更详细的3小时窗口中看到的情况：

如您所见,峰值写入速率约为20MB / s

软件
服务器正在运行ubuntu 12.04和postgresql 9.2.
通常在ID标识的各行上更新更新类型.例如. UPDATE汽车SET price = some_price,updated_at = some_time_stamp WHERE id = some_id.
我已经尽可能多地删除和优化了索引,并且服务器配置(linux内核和postgres conf)也得到了很好的优化.

硬件
硬件是一个专用服务器,具有32GB ECC RAM,4x 600GB 15.000 rpm SAS磁盘,采用RAID 10阵列,由带BBU的LSI raid控制器和Intel Xeon E3-1245 Quadcore处理器控制.

问题

>图表所见的性能是否适用于此系统
口径(读/写)？
>因此,我是否应该专注于进行硬件升级或深入研究软件(内核调整,confs,查询等)？
>如果进行硬件升级,磁盘数量是性能的关键吗？

—————————— UPDATE ——————- —————-

我现在用四个intel 520 SSD而不是旧的15k SAS磁盘升级了我的数据库服务器.我正在使用相同的raid控制器.事情已经有了很大的改进,从下面你可以看到,峰值I / O性能提高了大约6-10倍 – 这太棒了！

然而,根据新SSD的答案和I / O功能,我期待更多的改进20-50倍.所以这里有另一个问题.

新问题
我当前的配置中是否有某些内容限制了我的系统的I / O性能(瓶颈在哪里)？

我的配置：

/etc/postgresql/9.2/main/postgresql.conf

data_directory = '/var/lib/postgresql/9.2/main'
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.2-main.pid'
listen_addresses = '192.168.0.4,localhost'
port = 5432
unix_socket_directory = '/var/run/postgresql'
wal_level = hot_standby
synchronous_commit = on
checkpoint_timeout = 10min
archive_mode = on
archive_command = 'rsync -a %p postgres@192.168.0.2:/var/lib/postgresql/9.2/wals/%f </dev/null'
max_wal_senders = 1
wal_keep_segments = 32
hot_standby = on
log_line_prefix = '%t '
datestyle = 'iso,mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
default_statistics_target = 100
maintenance_work_mem = 1920MB
checkpoint_completion_target = 0.7
effective_cache_size = 22GB
work_mem = 160MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 7680MB
max_connections = 400

/etc/sysctl.conf中

# sysctl config
#net.ipv4.ip_forward=1
net.ipv4.conf.all.rp_filter=1
net.ipv4.icmp_echo_ignore_broadcasts=1
# ipv6 settings (no autoconfiguration)
net.ipv6.conf.default.autoconf=0
net.ipv6.conf.default.accept_dad=0
net.ipv6.conf.default.accept_ra=0
net.ipv6.conf.default.accept_ra_defrtr=0
net.ipv6.conf.default.accept_ra_rtr_pref=0
net.ipv6.conf.default.accept_ra_pinfo=0
net.ipv6.conf.default.accept_source_route=0
net.ipv6.conf.default.accept_redirects=0
net.ipv6.conf.default.forwarding=0
net.ipv6.conf.all.autoconf=0
net.ipv6.conf.all.accept_dad=0
net.ipv6.conf.all.accept_ra=0
net.ipv6.conf.all.accept_ra_defrtr=0
net.ipv6.conf.all.accept_ra_rtr_pref=0
net.ipv6.conf.all.accept_ra_pinfo=0
net.ipv6.conf.all.accept_source_route=0
net.ipv6.conf.all.accept_redirects=0
net.ipv6.conf.all.forwarding=0
# Updated according to postgresql tuning
vm.dirty_ratio = 10
vm.dirty_background_ratio = 1
vm.swappiness = 0
vm.overcommit_memory = 2
kernel.sched_autogroup_enabled = 0
kernel.sched_migration_cost = 50000000

/etc/sysctl.d/30-postgresql-shm.conf

# Shared memory settings for PostgreSQL
# Note that if another program uses shared memory as well,you will have to
# coordinate the size settings between the two.
# Maximum size of shared memory segment in bytes
#kernel.shmmax = 33554432
# Maximum total size of shared memory in pages (normally 4096 bytes)
#kernel.shmall = 2097152
kernel.shmmax = 8589934592
kernel.shmall = 17179869184
# Updated according to postgresql tuning

MegaCli64 -LDInfo -LAll -aAll的输出

Adapter 0 -- Virtual Drive Information:
Virtual Drive: 0 (Target Id: 0)
Name                :
RAID Level          : Primary-1,Secondary-0,RAID Level Qualifier-0
Size                : 446.125 GB
Sector Size         : 512
Is VD emulated      : No
Mirror Data         : 446.125 GB
State               : Optimal
Strip Size          : 64 KB
Number Of Drives per span:2
Span Depth          : 2
Default Cache Policy: WriteBack,ReadAhead,Direct,Write Cache OK if Bad BBU
Current Cache Policy: WriteBack,Write Cache OK if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy   : Disk's Default
Encryption Type     : None
Is VD Cached: No