PostgreSQL性能优化.docx

PostgreSQL性能优化.docx

《PostgreSQL性能优化.docx》由会员分享，可在线阅读，更多相关《PostgreSQL性能优化.docx（32页珍藏版）》请在冰豆网上搜索。

PostgreSQL性能优化

PostgreSQL 性能优化

1.硬件

数据库最重要的就是I/O了。

所以一切从I/O开始。

RAID:

这个基本不用说，数据库放RAID10上面，只读的备份数据库可以放RAID0，反正挂了没关系。

谨记：

数据库是RandomRead

RAID卡的选择：

RAID卡一定要带电池的才可以（BBU）有电源的才能做到东西写进CACHE，RAID就返回硬盘写成功（不用等）

1.Areca

2.LSI（真正的LSI，re-brand不要）

3.HPP400以上系列

硬盘选择：

首选是SAS:

15KRPM每个SAS大约能提供25MB/s的RandomWrite。

也就是说在RAID10的设定下，如果需要50MB/s的RandomWrite就需要４个硬盘

节俭选择是：

SATA可以多用几个硬盘（SAS一倍数量）达到在RAID10中接近SAS的速度。

就算SATA买SAS一倍的数量，价格仍然比SAS便宜。

也可以买产品：

例如Compaq的MSA70（P800BatterybackedRAIDcontrol）

CPU：

64位

Cache：

越大越好（现在个人电脑都3M的cache了）

CORE：

越多越好（postgresql毕竟是跑cpu的）建议最少4个core

RAM：

最少4G。

通常根据具体需求，用16-64G的RAM

2.OS（系统）

可用系统：

1.DebianStable

2.CentOS

3.UbuntuLTS

4.RedHat

5.SUSEEnterprise

如果准备付费（服务），那么就是Canonical,Novell跟Redhat这三家选择而已

如果准备不买任何服务，可以用Debian,CentOS,UbuntuLTS

这里还是觉得系统用RedHat（不付费就CentOS）毕竟人家是企业级的老大哥，错不了。

*现在CentOS也可以买到服务了。

不可用系统：

例如fedora（redhatQA）ubuntu（non-LTS）

Scheduler:

Grub增加：

elevator=deadline

redhat的图标可以看出，deadline是数据库的最佳选择

文件系统（Filesystem）

这里的选择是：

ext2，ext3跟ext4。

为什么只考虑这几个呢？

因为数据库还是稳定第一，内核开发人员所做的文件系统，理论上说出问题的情况会少点。

WAL：

放ext2因为WAL本身自己有Journal了，不需要用ext3（ext2快很多）

data：

ext3

BlockSize：

postgres自己是8k的blocksize。

所以文件系统也用8k的blocksize。

这样才能最佳的提高系统的效能。

ext4：

出来时间还不够长，不考虑。

分区（Partitioning）

Postgres跟系统OS应该在不同分区

系统（OS）：

系统应该放独立的RAID1

数据库（PostgresData）：

数据库应该放独立的RAID10上。

如果RAID是带电池的，mount的时候给data=writeback的选项

独立的数据库分区，就不许要记录文件时间了（都是放数据的）所以mount的时候要给noatime的选项，这样可以节约更新时间（timestamp）的I/O了。

WAL日志（xlogs）：

独立的RAID1上（EXT2系统）日志是Sequentialwrite，所以普通的硬盘（SATA）速度就足够了，没有必要浪费SAS在log上

Postgresql日志（logs）：

直接丢给syslog就可以。

最好在syslog.conf中设定单独的文件名.这里例如用local2来做postgresql

local2.*-/var/log/postgres/postgres.log

记得log要给Async，这样才不会等卡在log的I/O上，同时记得设定logrotate以及创建路径（path）

ext2VSext3性能测试：

HPDL585

4DualCore8222processors

64GBRAM

（2）MSA70directattachedstoragearrays.

25spindlesineacharray（RAID10）

HPP800Controller

6DiskinRAID10onembeddedcontroller

xlogwithext3:

avg=87418.44KB/sec

xlogwithext2:

avg=115375.34KB/sec

3.Postgres内存（MemoryUsage）

SharedBufferCache

WorkingMemory

MaintenanceMemory

SharedBuffers

Postgres启动时要到的固定内存。

每个allocation是8k。

Postgres不直接做硬盘读写，而是把硬盘中的东西放入SharedBuffers，然后更改SharedBuffers，在flush到硬盘去。

通常SharedBuffers设定为内存（availablememory）的25%-40%左右。

在系统（OS）中，记得设置kernel.shmmax的值（/etc/sysctl.conf）

kernel.shmmax决定了进程可调用的最大共享内存数量。

简单的计算方法是

kernel.shmmax=postgresshared_buffers+32MB

要保留足够的空间（不然会outofmemory）postgresql除了sharedbuffer还会用到一些其他的内存，例如max_connections,max_locks_pre_transaction

WorkingMemory

这个是postgres运行作业中（task）需要的内存，例如内存内的hashed（aggregates，hashjoins）sort（orderby,distinct等等）合理的设定，可以保证postgres在做这些东西的时候可以完全在内存内完成，而不需要把数据吐回到硬盘上去作swap。

但是设定太大的话，会造成postgres使用的内存大于实际机器的内存，这个时候就会去硬盘swap了。

（效能下降）

workingmemory是perconnectionandpersort的设定。

所以设定一定要非常小心。

举例来说，如果设定workingmemory为32MB，那么以下例子：

select*fromlines,lineitems

wherelines.lineid=lineitems.lineid

andlineid=6

orderbybaz;

这里就可能用到64MB的内存。

hashjoinbetweenlinesandlineitems（32MB）

orderbybaz（32MB）

要注意自己有多少query是用到了orderby或者join

如果同时有100个链接，那么就是100connectionX64MB=6400MB（6G）内存

通常来说，workingmem不要给太大，2-4MB足够

在postgres8.3之后的版本，workingmem可以在query中设定

Query:

begin;

setwork_memto‘128MB’;

select*fromfooorderbybar;

insertintofoovalues（‘bar’）;

resetwork_mem;

commit;

Function:

createfunctionreturn_foo（）returnssetoftextas

$select*fromfooorderbybar;$

SETwork_memto‘128MB’

LANGUAGE’sql’

postgres官方不建议（但是支持）在postgresql.conf文件中更改work_mem然后HUP（数据库应该没有任何中断）

利用explainanalyze可以检查是否有足够的work_mem

sort（cost=0.02..0.03rows=1width=0）（actualtime=2270.744..22588.341rows=1000000loops=1）

SortKey:

（generate_series（1,1000000））

SortMethod:

externalmergeDisk:

13696kb

->Result（cost=0.00..0.01rows=1width=0）（actualtime=0.006..144.720rows=1000000loops=1）

Totalruntime:

3009.218ms

（5rows）

以上的query分析显示，这里需要从硬盘走13MB的东西。

所以这个query应给setwork_mem到16MB才能确保性能。

MaintenanceMemory（维护内存）

maintenance_work_mem决定系统作维护时可以调用的内存大小。

这个也是同样可以在query中随时设定。

这个内存只有在VACUUM,CREATEINDEX以及REINDEX等等系统维护指令的时候才会用到。

系统维护是，调用硬盘swap会大大降低系统效能。

通常maintenance_work_mem超过1G的时候并没有什么实际的效能增加（如果内存够，设定在1G足以）

BackgroundWriter（bgwriter）

功能：

负责定时写sharedbuffercache中的dirtysharedbuffers

好处：

a.减少系统flushsharedbuffers到硬盘（已经被bgwriter做了）

b.在checkpoint中，不会看到I/O的突然性暴增，因为dirtybuffers在背景中已经被flush进硬盘

坏处：

因为一直定时在背后flushdisk，会看到平均硬盘I/O怎加（好过checkpoint时I/O暴增）

设定：

bgwriter_delay：

sleepbetweenrounds。

default200（根据机器，数据而调整）

bgwriter_lru_maxpages:

决定每次bgwriter写多少数据。

如果实际数据大于这里的设定，那么剩余数据将会被postgres的进程（serverprocess）来完成。

serverporcess自己写的数据会造成一定的性能下降。

如果想确定所有的数据都由bgwriter来写，可以设定这里的值为-1

bgwriter_lru_multiplier:

采用计算的方式来决定多少数据应该被bgwriter来写。

这里保持内置的2.0就可以。

计算bgwriter的I/O:

1000/bgwriter_delay*bgwriter_lru_maxpages*8192=实际I/O

（8192是postgres的8kblock）

例如：

1000/200*100*8192=409