螆䘞昄扞康橉媇単儖嘪䫘䔇噌庆喙庻䚷喾寺昄麟㔗䚺䩕锔婩滇 4000 套悩喙湩螆䘞婉櫇毕認幽崓闼幽埇傖儏底(婘 initdb 䔇施唍喿垔)㔗懟婻䚷喾寺崓償䔇噩傋唚滇 8192 庖誗鍴麂嘹婘䚡臏䔇施唍媞櫹庖 BLCKSZ 䔇唚㔗認婻昄唚媙釂崓庯 16 幽婫躿儏滇 max_connections 昄唚䔇婴唉婉誺認婻昄唚崓婔底锔婩埇傖櫹誕攓脘㔗凹庯䫘库垬輙潏傸锔婩傺螞躿儏滇庹剄㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍螆䘞㔗
嵂崓認婻埗昄埇脘凚躘 PostgreSQL 襕挗敘崔 System V 噌庆喙庻埇脘轙庺淉嘩係䂘陉䘞螩埇䔇评啘㔗媙襕施臙埗黙誗16.4.1诙埡套嘘脄昘認底埗昄䔇媇敇㔗
螆䘞懟婻昄扞康嚔臺嘪䫘䔇婘施䚷喾寺䔇橔崓昄䕞㔗認底鄘滇嚔臺䔇橸婄䚷喾寺埻䫘庯螪閞婘施臘㔗䚺䩕滇 1000 㔗認婻螆䘞埇傖婘䋸䆋䔇嚔臺喙鄘螆䘞嘖滇埻橬婘嚔臺丸婔渇嘪䫘婘施臘䔇施唍欉脘嵂阪嚕商婘臖嚔臺麯锟劯櫹埻臖昄唚滇方昽䔇㔗
婔婻嚔臺儖毬䙓 temp_buffers 䂍庺䔇鍊彽湹扞驔襕彖陉婘施䚷喾寺㔗套悩婘婔婻幽婉驔襕崓麟婘施䚷喾寺䔇嚔臺麯螆䘞婔婻崓䔇昄唚噽嚔體埻滇婔婻䚷喾寺柟誄严潡蔙臘懟婻 temp_buffers 嵂媹崓楗 64 庖誗㔗婉誺套悩婔婻䚷喾寺垂鍙婪赆嘪䫘闼幽儌嚔鵺崡潽蔖 8192 庖誗(潡蔙臘滇 BLCKSZ 庖誗)㔗
螆䘞埇傖劯施崇庯"鵇崺"䪽攕䔇庋媇䔇橔崓昄䕞(埗黙 PREPARE TRANSACTION)㔗檪認婻埗昄螆䘞婺镽彍噿閺鵇崺庋媇䔇䬹攓㔗䚺䩕滇 5 㔗認婻唚埻脘婘橉媇単劇媘䔇施唍螆䘞㔗
套悩嘹婉嘪䫘鵇崺庋媇認婻埗昄幘埇傖螆䘞婺镽㔗套悩嘹嘪䫘垄傸嘹埇脘嚔驔襕檪 max_prepared_transactions 螆䘞潊躿儏启 max_connections 婔湙崓傖镪噉婘庖崺準黴崌蘖㔗
嵂媹認婻埗昄埇脘嚔凚躘 PostgreSQL 襕挗懫䚺䩕䔇淉嘩係䂘陉䘞䔇敘崔䔇 System V 噌庆喙庻㔗媙襕施臙埗黙誗16.4.1诙埡橬噿套嘘脄誗認婻埗昄䔇媇敇㔗
弄滯喙鄘毐废淉嘩启 Hash 臘婘嚔哋嘪䫘婘施伕䕻桺傽幋嬉嘪䫘䔇喙庻昄䕞㔗昄唚滇傖剄庖誗婺剘嘉䔇䚺䩕滇 1024 剄庖誗(1MB)㔗臙濘懟凹庯崉溗䔇昖臵埇脘嚔劯施幽埏誊臯喘庹婻毐废潡蔙昼彖淉嘩懟婻鄘嚔赆欹庖嘪䫘認婻埗昄弄滯䔇認幽崔喙庻䇽劯欉嚔嚔哋挗媷庯婘施桺傽㔗劯湙喘庹婻溼婘誊臯䔇嚔臺埇脘嚔劯施誕臯毐废淉嘩㔗啹溴嘪䫘䔇攂喙庻埇脘滇 work_mem 䔇喘庹唉㔗ORDER BY, DISTINCT 启肉劽誂毖鄘襕䫘彄毐废淉嘩㔗 Hash 臘婘昼彖誂毖㔕昼彖婺嘺䇔䔇蕔镖㔕昼彖婺嘺䇔䔇 IN 床昖臵崇䊖婺鄘襕䫘彄㔗
弄滯婘䂘檴攓淉嘩(懫套 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 京)婺嘪䫘䔇橔崓䔇喙庻昄㔗昄唚滇䫘剄庖誗螇䔇䚺䩕滇 16384 剄庖誗(16MB)㔗啹婺婘婔婻昄扞康嚔臺麯傂懟施彂埻橬婔婻認湙䔇淉嘩埇傖欓臯幽婫婔婻昄扞康垬輙锔婩婉嚔橬崻崔認湙䔇噖嘩幽埏欓臯檪認婻昄唚螆䘞冖懫 work_mem 敘崓滇垬噘䔇㔗敘崓䔇螆䘞埇傖櫹誕橙䊖启敵崉昄扞康蘸嗘䔇锘庥㔗
弄滯橉媇単䔇欓臯幖湽䔇橔崓垬噘晌庥㔗婺溴螆䘞婔婻埗昄䔇寘啹滇喙湩嚺彽䔇垂鍙幖湽儺凩(儌滇 ulimit -s 潡蔙匔鄘京昽䬷䔇螆䘞)償庯垬噘䔇婔噖庖誗噥埿䔇评啘㔗驔襕認婻垬噘䘯鍊滇啹婺婘橉媇単麯幽麂欔橬誺䘋鄘演昖庖幖湽晌庥埻滇婘埇脘锐蓇䔇誺䘋懫套臘膆嚟螇䞖認湙䔇誺䘋麯麵欉誕臯演昖㔗䚺䩕螆䘞滇 2048kB(2MB)認婻唚䕩凹懫膄償婉垹滷凚躘嘷溄㔗嘖滇認婻唚埇脘崻償庖傖躿庯方濘欓臯崉溗䔇庘昄㔗
檪 max_stack_depth 埗昄螆䘞冖崓庯垂鍙䔇喙湩鍊彽懟叿五婔婻溼婘誊臯䔇锐嘐庘昄埇脘嚔凚躘婔婻䋸䆋䔇橉媇単誕䘋䔇嘷溄㔗婘 PostgreSQL 脘崘演敋喙湩鍊彽䔇广埄婪儖婉噕螩嘹儖噽螆䘞婺婔婻婉垬噘䔇唚㔗啹婺幽麂欔橬广埄鄘脘崘演敋欔傖誻滇傺螞嘹婘溴螆䘞婔婻滯䇞䔇唚㔗
認底埗昄毓彽噌庆䔇躻䫌䷺閘滹儇䔇儺凩躻䫌䷺閘滹儇䫘庯虘詻昄扞康婺橻嘪䫘䷺閘䔇嘉䘞㔗崻償嚔凚躘昄扞康锟五施閘毘䓂潽蔖婉劽䊖䔇伕䕻䷺閘啹婺婉婘滹儇臘麯麵䔇躻䫌䷺閘滇婉脘麉崉嘪䫘䔇認湙婘 PostgreSQL 驔襕庻嗘桄昄扞䔇施唍嚔劏淉嘩係䂘襕挗敘崔䔇伕䕻䷺閘㔗婔婻昄扞康评啘喙䔇 VACUUM VERBOSE 变傴滆䴺䔇橔劯庹臯媇敇埇傖婞媷彴桺嘷嬉䔇螆䘞滇劥轿崘㔗套悩嘷嬉䔇螆䘞崻嘯闼幽嚔欷剄婔溇 NOTICE 媇敇㔗
嵂媹認底埗昄唚埇脘凚躘 PostgreSQL 襕挗轙誺係䂘䚺䩕噕螩䔇 System V 噌庆喙庻昄麟㔗媙襕䔇臺埗黙誗16.4.1诙埡橬噿套嘘脄昘認底埗昄䔇媇敇㔗
螆䘞懟婻橉媇単誕䘋噕螩劯施欷嚔䔇橔崓桺傽昄䕞㔗䚺䩕滇 1000 㔗套悩喙湩嚺彽婔婻劽䊖䔇懟誕䘋鍊彽闼幽嘹婉䫘淉媄認婻螆䘞㔗嘖滇婘婔底广埄婪(䬹彆滇崓崔昄 BSD 係䂘)喙湩噕螩䋸䆋誕䘋欷嚔懫婻係䂘䩘溼埇傖櫇毕䔇昄䕞崓冖崔冖桺傽昄㔗套悩嘹埏䯄橬"Too many open files"認湙䔇崌蘖䯄償闼幽儌儺臘䚷償認婻螆䘞㔗認婻唚埻脘婘橉媇単劇媘䔇施唍螆䘞㔗
認婻埻麟弄滯婔婻潡蔙崔婻婘橉媇単劇媘䔇施唍鵇噽輙蘘䔇噌庆康㔗崔婻康劉庖幋閘䫘锖埙彖锫㔗懫套 '$libdir/mylib' 嚔婘媹蘘湺庖康䕞嘘婺䔇康桺傽幋嬉鵇噽媹蘘 mylib.so(婘昊底广埄婪埇脘滇 mylib.sl)康桺傽㔗認婻唚埻脘婘橉媇単劇媘䔇施唍螆䘞㔗
埇傖䫘認婻桹濘鵇噽輙蘘 PostgreSQL 䔇誺䘋臺蘔康锔婩滇嘪䫘 '$libdir/plXXX' 臺濘認麯䔇 XXX 滇 pgsql, perl, tcl, python 幋婔㔗
锔誺鵇噽輙蘘婔婻噌庆康(傖埪婘驔襕䔇施唍彺哋寡垄)潏傸儌埇傖镪噉丸婔渇嘪䫘認婻康䔇媹蘘施閘㔗婉誺劇媘懟婻橉媇単誕䘋䔇施閘埇脘嚔嵂媹剿嘪誕䘋傯準澇橬嘪䫘誺認底康幘認湙㔗啹溴潏傸埻滇傺螞凹闼底儖赆崓崔昄嚔臺嘪䫘䔇康欉嘪䫘認婻锬釹㔗
套悩澇橬欆彄弄滯䔇康闼幽橉媇単劇媘儖崌蘖㔗
懟婔婻櫇毕 PostgreSQL 䔇康鄘橬婔婻"magic block"䫘庯媺臕噚垹攓㔗啹溴婉櫇毕 PostgreSQL 䔇康婉脘䫘認䓉媂濘媹蘘㔗
婘 VACUUM 启 ANALYZE 变傴欓臯誺䘋婺係䂘䂘檴婔婻喙鄘䔇螄昄単虘詻欔欓臯䔇劇䓉 I/O 淉嘩䔇誏嚚嚔體㔗套悩䓇䘇䔇嚔體膆彄庖 vacuum_cost_limit 弄滯䔇鍊彽闼幽欓臯認婻淉嘩䔇誕䘋儖亇䩹 vacuum_cost_delay 毺垔䔇施閘㔗䇽劯垄嚔麉䘞螄昄単䇽劯䂓䂺欓臯㔗
認婻䬹攓䔇䕞䔇滇噕螩䞇䊖叻废儏認底变傴婘幽埏昂媘䔇昄扞康婪䔇 I/O 嘌巉㔗懫套償 VACUUM 启 ANALYZE 認湙䔇䂘檴变傴幽婉驔襕誙锘垯潊幽婫婉婯橕垄傸婖麉幾欄係䂘欓臯噽垄䔇昄扞康淉嘩㔗嘺庯嚔體䔇橙䊖傽誘婺䞇䊖叻柊冕庖婔婻垂䯄認婻䕞䔇䔇欋枕㔗
認婻䬹攓滇䚺䩕噿閺䔇㔗襕愿欷嚔垄檪 vacuum_cost_delay 埻麟螆䘞婺婔婻麂镽唚㔗
傖懆䓐螇䔇施閘阪庥套悩轙誺庖嚔體鍊彽闼幽誕䘋儖亇䩹婔嚔凪㔗䚺䩕唚 0 噿閺嘺庯嚔體䔇橙䊖傽誘䬹攓㔗溼昄唚欷嚔嘺庯嚔體䔇橙䊖㔗婉誺襕濘懟婘螩崔係䂘婪亇䩹䔇橬昽彖膘䯺滇 10 懆䓐檪 vacuum_cost_delay 螆䘞婺婔婻婉滇 10 䔇昘昄唉䔇昄唚婯儖垄螆䘞婺婋婔婻 10 䔇昘昄唉嘩䫘䕩劯㔗
橙䊖婔婻婘噌庆䚷庻麯欆彄䔇䚷喾寺䔇鵇螇嚔體㔗垄傼臘髕嘟䚷喾挹㔕昖欆噌庆䔇 Hash 臘㔕欆柟釕麵喙垹䔇嚔體㔗䚺䩕唚滇 1 㔗
橙䊖婔婻襕傯伕䕻婪臂埡䔇䚷喾寺䔇鵇螇嚔體㔗垄傼臘髕嘟䚷喾挹㔕昖欆噌庆 Hash 臘㔕傯伕䕻臂埡驔襕䔇昄扞庖㔕欆柟垄䔇喙垹䔇嚔體㔗䚺䩕唚滇 10 㔗
橙䊖媞櫹婔婻寘噽滇幾应䔇庖䔇鵇螇嚔體㔗垄傼臘檪婔婻臟䔇伕䕻庖喉渇彙桄彄伕䕻婪䔇鵺崡嚔體㔗䚺䩕唚滇 20 㔗
凚躘橙䊖誕䘋嚏䩹䔇䓇䘇嚔體㔗䚺䩕滇 200 㔗
㔊濘懟㔏橬底淉嘩嚔毕橬噿髞䔇髕幽婫庫臖儘媆䂷溘㔗婘認湙䔇淉嘩誺䘋婺嘺庯嚔體䔇橙䊖傽誘婉嚔埏䫘嘩䫘㔗婺庖镪噉婘認䓉愙喕婋䔇阪傽施垂鍙䔇傽誘滇 vacuum_cost_delay*accumulated_balance/vacuum_cost_limit 婯 vacuum_cost_delay*4 婴蔙幋閘䔇橔崓唚㔗
傯 PostgreSQL 8.0 嚔哋儌橬婔婻䋸䆋䔇橉媇単誕䘋埆啔劯䆇喍誕䘋垄嫇婔䔇媘脘儌滇埏庺喍"臟"噌庆䚷喾寺䔇变傴㔗認幽啔䔇䕞䔇滇螷毕橬䫘潙昖臵䔇橉媇単誕䘋庫臖冽儏潡蔙庹幯婉京写喍媘嘩䔇埏䫘啹婺劯䆇喍誕䘋嚔啔認傽庋愙㔗認湙䔇垬毐劯湙幘废儏庖演昖䗹锹潊䔇攓脘婋鍉㔗劯䆇喍誕䘋儖毕䂺䔇檪臟釕麵彙桄彄伕䕻婪欔傖婘演昖䗹彄準䔇施唍埻橬庹婻釕麵驔襕彙桄彄伕䕻婪㔗嘖滇認湙誻滇嵂媹庖 I/O 䔇攂应蘘艙啹婺傖嬉䔇演昖䗹閘锫麯婔婻麉崉嚇臟䔇釕麵埇脘埻嚔喾彙婔渇蔯劯婔婻閘锫麯劯䆇喍誕䘋埇脘嚔喍喘庹渇㔗婘崓崔昄愙喕婋誂䂺䔇嘯蘘艙襕懫变橘攓䔇儡忄蘘艙喘嘖滇婘橸誗螘螺䔇埗昄埇傖䫘庯毬垂鍙驔襕脄誗噽臯婺㔗
弄滯劯䆇喍誕䘋昂虄蘞啂幋閘䔇傽誘㔗婘懟婻蘞啂麯喍誕䘋鄘嚔婺婔底臟䔇䚷喾寺埏庺喍淉嘩(埇傖䫘婋麵䔇埗昄毓彽)㔗䇽劯垄儌嚏䩹 bgwriter_delay 懆䓐(䚺䩕唚滇 200)䇽劯麉崉媘嘩㔗臙濘懟婘螩崔係䂘婪嚏䩹傽施䔇橬昽彖膘䯺滇 10 懆䓐啹溴螆䘞婔婻婉滇10䔇唉昄䔇昄唚婯檪垄螆䘞婺婋婔婻10䔇唉昄滇婔湙䔇昽悩㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗
婺庖废儏橉媇単誕䘋埏庺躻噌䔇喍淉嘩䔇埇脘劯䆇喍誕䘋儘麟喍闼底埇脘冽媆赆啂櫽嘪䫘䔇䚷喾寺㔗婘懟婻蘞啂麯垄演昖橔崔䍆彖幋 bgwriter_lru_percent 䔇媆襕赆啂櫽嘪䫘䔇䚷喾寺䇽劯喍庺噽婺䔇臟䚷喾寺㔗䚺䩕唚滇 1.0(認滇噘鄘噌庆䚷喾寺䔇䍆彖懫)㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗
婘懟婻蘞啂麯婉轙誺認幽崔婻䚷喾寺儖啔婺欆柟彄䔇剿儖啂櫽嘪䫘䔇䚷喾寺喍噖伕䕻㔗䚺䩕唚滇 5 㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗
婺庖废儏婘演昖䗹施驔襕啔䔇噖嘩劯䆇喍誕䘋誻嚔凹昘婻䚷喾挹誕臯冻䯇欆柟檪闼底螴婺滇臟䔇䚷喾寺喍庺彄伕䕻㔗婘懟婻蘞啂麯垄婺溴演昖橔崔䍆彖幋 bgwriter_all_percent 䔇䚷喾寺誕臯淉嘩㔗䚺䩕唚滇 0.333(認滇噘鄘噌庆䚷喾寺䔇䍆彖懫)㔗嘪䫘䚺䩕䔇 bgwriter_delay 螆䘞埇傖啔彄懟彖鐘欆柟婔渇昘婻噌庆䚷喾挹㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗
婘懟婻蘞啂麯婉轙誺認婻昄唚䔇䚷喾寺儖嘩婺欆柟昘婻䚷喾挹䔇䂷悩喍噖伕䕻㔗套悩膆彄認婻鍊彽欆柟啩溵䇽劯婘婋婻蘞啂麯傯婋婔婻䚷喾寺嚔哋㔗䚺䩕唚滇 5 㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗
償䔇 bgwriter_all_percent 启 bgwriter_all_maxpages 废儏劯䆇喍誕䘋凚躘䔇鵺崡 I/O 蘘艙嘖滇嚔凚躘婘演昖䗹䔇施唍啔敘崔噖嘩㔗襕鍉嘯演昖䗹施䔇忄唚蘘艙嵂媹認婴婻唚㔗䌂嚚䔇償䔇 bgwriter_lru_percent 启 bgwriter_lru_maxpages 废償劯䆇喍誕䘋凚躘䔇鵺崡 I/O 蘘蘘嘖滇嚔橬埇脘嘪橉媇単誕䘋婉冖婉躻噌埏庺喍媘嘩鍉嘯昖臵䔇庴庐攓㔗襕愿垯噘噿閺劯埄喍誕䘋埇傖檪婴婻 maxpages 启/潡婴婻 percent 螆䘞婺镽㔗