postgresql优化与维护.docx
《postgresql优化与维护.docx》由会员分享，可在线阅读，更多相关《postgresql优化与维护.docx（10页珍藏版）》请在冰点文库上搜索。
postgresql优化与维护
1.硬件
数据库最重要的就是I/O了。
所以一切从I/O开始。
RAID:
这个基本不用说，数据库放RAID10上面，只读的备份数据库可以放RAID0，反正挂了没关系。
谨记：
数据库是RandomRead
注意的是硬盘外圈要比内圈快，所以跟linux建立swap的原理一样，尽力把数据库的东西放在硬盘的外圈（前端）直接在fdisk的时候就做好。
同时有个省钱方法，因为硬盘的外围速度远远快于内圈的速度，所以选择一个1T的SATA硬盘，然后只用外圈的100G，其他的不用，这样的话，速度要比单买一个100G的SAS还快。
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=4096000=4000kb
bgwrater可以用pg_stat_bgwriter来监测。
如果想要观察bgwrater的运行状况，记得首先清理旧的stat信息。
bgwriter如果设定的太大（做太多事情）那么就会影响到前台的效能（server）但是如果由系统（server）来做bufferflush同样会影响效能。
所以这里的最好设定就是通过观察pg_stat_bgwriter来找到一个最佳的平衡点。
WAL（writeaheadlog）
postgres中的所有写动作都是首先写入WAL，然后才执行的。
这样可以确保数据的准确跟完整。
当中途数据库崩溃的时候，postgres可以通过WAL恢复到崩溃前的状况而不会出现数据错误等等问题。
WAL会在两种情况下被回写硬盘。
1.commit。
当commit数据的时候，WAL会被强制写回硬盘（flush）并且所有这个commit之前的东西如果在WAL中，也会一同被flush。
2.WALwriter进程自己会定时回写。
FSYNCvsASYNC
postgres的default是做fsync，也就是说postgres会等待数据被写入硬盘，才会给query返回成功的信号。
如果设定sync=no关闭fsync的话，postgres不会等待WAL会写硬盘，就直接返回query成功。
通常这个会带来15-25%的性能提升。
但是缺点就是，如果系统崩溃（断电，postgres挂掉）的时候，你将有可能丢失最后那个transcation.不过这个并不会造成你系统的数据结构问题。
（nodatacorrupt）如果说在系统出问题的时候丢失1-2笔数据是可以接受的，那么25%的性能提升是很可观的。
WAL设定：
fsync可以选择on或者off
wal_sync_method：
linux中是使用fdatasync。
其他的。
。
。
不知道，应该是看系统的文件参数了
full_page_writes:
开启的时候，在checkpoint之后的第一次对page的更改，postgres会将每个diskpage写入WAL。
这样可以防止系统当机（断电）的时候，page刚好只有被写一半。
打开这个选项可以保证pageimage的完整性。
关闭的时候会有一定的性能增加。
尤其使用带电池的RAID卡的时候，危险更低。
这个选项属于底风险换取性能的选项，可以关闭
wal_buffers:
WAL的储存大小。
default是64kb。
实验证明， 设定这个值在256kb到1MB之间会提升效能。
wal_writer_delay
WAL检查WAL数据（回写）的间隔时间。
值是毫秒（milliseconds）
Checkpoints
确保数据回写硬盘。
dirtydatapage会被flushed回硬盘。
checkpoint由以下3中条件激发（bgwriter如果设定，会帮忙在后台写入，所以就不会有checkpoint时候的短期高I/O出现）
1.到达设定的WALsegments
2.到达设定的timeout
3.用户下达checkpoint指令
如果checkpoint运行频率高于checkpint_warning值。
postgres会在日志（log）中记录出来，通过观察log，可以来决定checkpoint_segments的设定。
增加cehckpoint_segments或者checkpoint_timeout可以有一定的效能提升。
而唯一的坏处就是如果系统挂了，在重启的时需要多一点时间来回复（系统启动回复期间数据库是不能用的）鉴于postgres很少挂掉，这个其实可以设定的很长（1天都可以）
设定：
checkpoint_segments最多的wallog数量，到达后会激发checkpoint，通常设定在30就好
checkpoint_timeout一般设置15-20分钟，常的可以设定1天也没关系
checkpoint_completion_target这个保持不动就好。
内建是0.5，意思就是每个checkpoint预计在下个checkpoint完成前的一半时间内完成（听起来有点绕嘴，呵呵）
checkpoint_warning如果checkpint速度快于这个时间，在log中记录。
内建是30秒
理论中的完美设定，就是你的backend从来不用回写硬盘。
东西都是由background来写入的。
这个就要靠调整bgwriter,checkpoints跟wal到一个最佳平衡状态。
当然这个是理想中的完美，想真的做到。
。
。
继续想吧。
呵呵
4.维护–保持postgres的笑容
维护数据库是必须的。
基本维护
vacuum
delete数据的时候，数据库只是记录这笔数据是‘不要的‘并不是真的删除数据。
所以这个时候就要vacuum了，vacuum会把标记为‘不要‘的数据清除掉。
这里要注意的是，vacuum不会清理index。
当数据更改超过75%的时候，需要重新建立index。
postgres8.4index可以用cluster重建速度快很多。
在postgres9.x中，vacuum=cluster，没有任何区别了（保留cluster只是为了兼容旧版指令）
FullVacuum
这个会做exclusivelock。
vacuum跟fullvacuum的区别是vacuum会把标志为‘不要‘的空间标志成可以再次使用（回收）而fullvacuum则会把这个空间删除（返还给系统OS）所以vacuum之后你的postgres在硬盘上看到的占用空间不会减少，但是fullvacuum会减小硬盘占用空间。
不建议使用fullvacuum，第一没必要，第二exclusivelock不好玩。
ANALYZE
Analyze会更新统计信息（statistics）所有的query的最佳方案，以及sqlpreparedstatement都是靠这统计信息而决定的。
所以当数据库中的一定量数据变动后（例如超过10%），要作analyze，严格的说，这个是应该常做的东西，属于数据库正常维护的一部分。
另外一个很重要的就是，如果是upload数据（restore那种）做完之后要记得作analyze（restore自动不给你作的）
当建立新的table的时候，或者给table增加index，或者对table作reindex，或者restore数据进数据库，需要手动跑analyze才可以。
analyze直接影响default_statistics_target数据。
Autovacuum
根据postgres的官方资料，autovacuum在8.3之后才变得比较真的实用（8.1推出的）因为在8.3之前，autovacuum一次只能同时做一个数据库中的一个table。
8.3之后的版本，可以作多数据库多table。
设定
log_autovacuum_min_duration:
-1为关闭。
0是log全部。
>0就是说超过这个时间的就log下来。
例如设定为30，那么所有超过30ms的都会被日志记录。
autovacuum_max_workers:
同时启用的autovacuum进程。
通常不要设定太高，3个就可以。
autovacuum_naptime:
检查数据库的时间，default是1分钟，不用改动
autovacuum_vacuum_threshold:
最低n行记录才会引发autovacuum。
也就是数据改变说低于这个值，autovacuum不会运行。
default是50
autovacuum_analyze_threshold:
运行analyze的最低值，跟上面的一样
autovacuum_vacuum_scale_factor:
table中的百分比的计算方式（超过一定百分比作vacuum）内建是20%（0.2）
autovacuum_analyze_scale_factor：
同上，不过是analyze的设定
autovacuum_freeze_max_age:
最大XID出发autovacuum
autovacuum_vacuum_cost_delay:
延迟。
。
如果系统负荷其他东西，可以让vacuum慢点，保证其他东西的运行.这里是通过延迟来限制
autovacuum_vacuum_cost_limit:
同上，也是作限制的，这里是通过cost限制limit
Cluster
Cluster类似于vacuumfull。
建议使用cluster而不是vacuumfull。
cluster跟vacuumfull一样会重写table，移除所有的deadrow。
同样也是要做exclusivelock。
Truncate
Turncat会删除一个table中的所有数据，并且不会造成任何的deadrow（delete则会造成deadrow）同样的，turncate也可以用来重建table
begin;
lockfooinaccessexclusivemode;
createtablebarasselect*fromfoo;
turncatefoo;
insertintofoo（select*frombar）;
commit;
这样就重新清理了foo这个table了。
REINDEX
重新建立index
5.其他
planner:
statistics直接决定planner的结果。
使用planner，那么要记得确保statistics的准确（analyze）
default_statistics_target:
设定analyze分析的值。
这个可以在query中随时设定更改
setdefault_statistics_targetto100;
analyzeverbosemytable;
INFO:
analyzing“aweber_shoggoth.mytable”
INFO:
“mytable”:
scanned30000of1448084pages,containing1355449liverowsand0deadrows;30000rowsinsample,estimatedtotalrows
ANALYZE
setdefault_statistics_targetto300;
analyzeverbosemytable;
INFO:
analyzing“aweber_shoggoth.mytable”
INFO:
“mytable”:
scanned90000of1448084pages,containing4066431liverowsand137deadrows;90000rowsinsample,estimatedtotalrows
ANALYZE
Setstatisticspercolumn给不同的column设定不同的statistics
altertablefooaltercolumnbarsetstatistics120
查找何时需要增加statistics
跑个query作expainanalyze
这个就会看到例如：
->SeqScanonbar（cost=0.00-52.00rows=52width=
升级会员 