postgresql优化与维护.docx
- 文档编号:7574709
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:10
- 大小:23.33KB
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就需要4个硬盘
节俭选择是:
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=
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- postgresql 优化 维护