postgesql维护_VACUUM.docVIP

postgresql维护_VACUUM postgresql缁存姢_VACUUM 鎽樿锛氫负浜嗕繚鎸佹墍瀹夎鐨?PostgreSQL 鏈嶅姟鍣ㄥ钩绋宠繍琛? 鎴戜滑蹇呴』鍋氫竴浜涙棩甯告€х殑缁存姢宸ヤ綔銆傛垜浠湪杩欓噷璁ㄨ鐨勮繖浜涘伐浣滈兘鏄粡甯搁噸澶嶇殑浜嬫儏锛?鍙互寰堝鏄撳湴浣跨敤鏍囧噯鐨?Unix 宸ュ叿锛屾瘮濡俢ron 鑴氭湰鏉ュ疄鐜? 鐩綍 1. 缁艰堪锛?2. 鏃ュ父娓呯悊锛? 2.1 VACUUM; 2.1.1 璇硶缁撴瀯锛?2.1.2 鎻忚堪锛?2.1.3 鍙傛暟锛?2.1.4 涓轰粈涔堣鐢╒ACUUM锛? 2.2 鎭㈠纾佺洏绌洪棿; 2.2.1 姒傝堪; 2.2.2 VACUUM FULL; 2.3 鏇存柊瑙勫垝鍣ㄧ粺璁? 2.4 閬垮厤浜嬪姟 ID 閲嶅彔閫犳垚鐨勯棶棰? 2.5 auto-vacuum 瀹堟姢杩涚▼; 3. 缁忓父閲嶅缓绱㈠紩; 4. 鏃ュ織鏂囦欢缁存姢; 5. 鍏充簬鏈枃; 6. 鏇存柊鏃ュ織; 7. 鍙傝€冩枃妗? 8. 鐩稿叧鏂囨。; +++++++++++++++++++++++++++++++++++++++++++ 姝ｆ枃 +++++++++++++++++++++++++++++++++++++++++++ 1. 缁艰堪; 涓轰簡淇濇寔鎵€瀹夎鐨?PostgreSQL 鏈嶅姟鍣ㄥ钩绋宠繍琛? 鎴戜滑蹇呴』鍋氫竴浜涙棩甯告€х殑缁存姢宸ヤ綔銆傛垜浠湪杩欓噷璁ㄨ鐨勮繖浜涘伐浣滈兘鏄粡甯搁噸澶嶇殑浜嬫儏锛?鍙互寰堝鏄撳湴浣跨敤鏍囧噯鐨?Unix 宸ュ叿锛屾瘮濡俢ron 鑴氭湰鏉ュ疄鐜般€?涓嶈繃锛岃缃悎閫傜殑鑴氭湰浠ュ強妫€鏌ュ畠浠槸鍚︽垚鍔熸墽琛屽垯鏄暟鎹簱绠＄悊鍛樼殑璐ｄ换锛?涓€浠跺緢鏄庢樉鐨勭淮鎶ゅ伐浣滃氨鏄粡甯告€у湴鍒涘缓鏁版嵁鐨勫浠芥嫹璐濄€?濡傛灉娌℃湁鏈€杩戠殑澶囦唤锛岄偅涔堟偍灏辨病鏈変粠鐏鹃毦涓仮澶嶇殑鏈轰細锛堟瘮濡傜鐩樺潖浜嗭紝澶辩伀锛岃鍒犱簡琛ㄧ瓑绛夛級銆? 鍏跺畠涓昏鐨勭淮鎶よ寖鐣寸殑宸ヤ綔鍖呮嫭鍛ㄦ湡鎬х殑 vacuuming 锛堟竻鐞嗭級鏁版嵁搴撱€? 鍏跺畠闇€瑕佸懆鏈熸€ф敞鎰忕殑涓滆タ鏄棩蹇楁枃浠剁殑绠＄悊銆? PostgreSQL 鍜屽叾瀹冩暟鎹簱浜у搧姣旇緝璧锋潵鏄綆缁存姢閲忕殑銆?浣嗘槸锛岄€傚綋鍦ㄨ繖浜涗换鍔′笂鏀句竴浜涙敞鎰忓皢鏇村姞鑳藉纭繚鎴戜滑鐨勬剦蹇伐浣滃拰鑾峰彇瀵硅繖涓郴缁熷瘜鏈夋垚鏁堢殑缁忛獙銆?鎿嶄綔鐜锛歅ostgreSQL8.2+Ubuntu 7.04 2. 鏃ュ父娓呯悊锛? 2.1 VACUUM; 2.1.1 璇硶缁撴瀯锛? VACUUM [ FULL | FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] 2.1.2 鎻忚堪锛? VACUUM 鍥炴敹宸插垹闄ゅ厓缁勫崰鎹殑瀛樺偍绌洪棿銆?鍦ㄤ竴鑸殑 PostgreSQL 鎿嶄綔閲岋紝 閭ｄ簺宸茬粡 DELETE 鐨勫厓缁勬垨鑰呰 UPDATE 杩囧悗杩囨椂鐨勫厓缁勬槸娌℃湁浠庡畠浠墍灞炵殑琛ㄤ腑鐗╃悊鍒犻櫎鐨勶紱 鍦ㄥ畬鎴?VACUUM 涔嬪墠瀹冧滑浠嶇劧瀛樺湪銆?鍥犳鎴戜滑鏈夊繀椤诲懆鏈熷湴杩愯 VACUUM锛?鐗瑰埆鏄湪甯告洿鏂扮殑琛ㄤ笂锛屽鏋滄病鏈夊弬鏁帮紝VACUUM 澶勭悊褰撳墠鏁版嵁搴撻噷姣忎釜琛紝 濡傛灉鏈夊弬鏁帮紝VACUUM 鍙鐞嗛偅涓〃锛岀畝鍗曠殑 VACUUM 锛堟病鏈塅ULL锛?鍙槸绠€鍗曞湴鍥炴敹绌洪棿骞朵笖浠ゅ叾鍙互鍐嶆浣跨敤锛? 2.1.3 鍙傛暟锛? FULL 閫夋嫨瀹屽叏娓呯悊锛岃繖鏍峰彲浠ユ仮澶嶆洿澶氱殑绌洪棿锛?浣嗘槸鑺辩殑鏃堕棿鏇村骞朵笖鍦ㄨ〃涓婃柦鍔犱簡鎺掑畠閿併€? FREEZE 閫夋嫨婵€杩涚殑鍏冪粍鍐荤粨銆? VERBOSE 涓烘瘡涓〃鎵撳嵃涓€浠借缁嗙殑娓呯悊宸ヤ綔鎶ュ憡銆? ANALYZE 鏇存柊鐢ㄤ簬浼樺寲鍣ㄧ殑缁熻淇℃伅锛屼互鍐冲畾鎵ц鏌ヨ鐨勬渶鏈夋晥鏂规硶銆? table 瑕佹竻鐞嗙殑琛ㄧ殑鍚嶇О锛堝彲浠ユ湁妯″紡淇グ锛夈€傜己鐪佹椂鏄綋鍓嶆暟鎹簱涓殑鎵€鏈夎〃銆? column 瑕佸垎鏋愮殑鍏蜂綋鐨勫垪/瀛楁鍚嶇О銆傜己鐪佹槸鎵€鏈夊垪/瀛楁銆? 2.1.4 涓轰粈涔堣鐢╒ACUUM锛? VACUUM鍛戒护鐨勫惈涔変负锛氬瀮鍦炬敹闆嗕互鍙婂彲閫夊湴鍒嗘瀽涓