PostgreSQL的磁盘空间占用问题
http://tech.ddvip.com 2007年10月26日 社区交流
本文详细介绍PostgreSQL的磁盘空间占用问题
2.3EffectiveCacheSize
ThisparameterallowsPostgreSQLtomakebestpossibleuseofRAMavailableonyourserver.IttellsPostgreSQLthesizeofOSdatacache.SothatPostgreSQLcandrawdifferentexecutionplanbasedonthatdata.
Saythereis1.5GBRAMinyourmachine,sharedbuffersaresetto32MBandeffectivecachesizeissetto800MB.Soifaqueryneeds700MBofdataset,PostgreSQLwouldestimatethatallthedatarequiredshouldbeavailableinmemoryandwouldoptformoreaggressiveplanintermsofoptimization,involvingheavierindexusageandmergejoins.Butifeffectivecacheissettoonly200MB,thequeryplannerisliabletooptforthemoreI/Oefficientsequentialscan.
Whilesettingthisparametersize,leaveroomforotherapplicationsrunningontheservermachine.TheobjectiveistosetthisvalueatthehighestamountofRAMwhichwillbeavailabletoPostgreSQLallthetime.
2.4FsyncandtheWALfiles
Thisparameterssetswhetherornotwritedatatodiskassoonasitiscommitted,whichisdonethroughWriteAheadLogging(WAL).Ifyoutrustyourhardware,yourpowercompany,andyourbatterypowersupplyenough,yousetthistoNoforanimmediateboosttodatawritespeed.Butbeveryawarethatanyunexpecteddatabaseshutdownwillforceyoutorestorethedatabasefromyourlastbackup.
Ifthat'snotanoptionforyou,youcanstillhavetheprotectionofWALandbetterperformance.SimplymoveyourWALfiles,usingeitheramountorasymlinktothepg_xlogdirectory,toaseparatediskorarrayfromyourmaindatabasefiles.Inhigh-write-activitydatabases,WALshouldhaveitsowndiskorarraytoensurecontinuoushigh-speedaccess.VerylargeRAIDarraysandSAN/NASdevicesfrequentlyhandlethisforyouthroughtheirinternalmanagementsystems.
责编：豆豆技术应用