MySQL鏁版嵁搴撶殑鎬ц兘浼樺寲閰嶇疆 浜_mysql

(涓€)鍑忓皯鏁版嵁搴撹闂?/p>

瀵逛簬鍙互闈欐€佸寲鐨勯〉闈紝灏藉彲鑳介潤鎬佸寲

瀵逛竴涓姩鎬侀〉闈腑鍙互闈欐€佺殑灞€閮紝閲囩敤闈欐€佸寲

閮ㄥ垎鏁版嵁鍙互鐢熸垚XML锛屾垨鑰呮枃鏈枃浠跺舰寮忎繚瀛?/p>

浣跨敤鏁版嵁缂撳瓨鎶€鏈紝渚嬪锛歁emCached
聽

(浜?浼樺寲鐨勬娴嬫柟娉?/p>

1.鐢ㄦ埛浣撻獙妫€娴?/p>

2.Mysql鐘舵€佹娴?/p>

鍦∕ysql鍛戒护琛岄噷闈娇鐢╯how status鍛戒护锛屽緱鍒板綋鍓峬ysql鐘舵€併€?/p>

涓昏鍏虫敞涓嬪垪灞炴€э細

key_read_requests (绱㈠紩璇荤殑璇锋眰鏁帮級(key_buffer_size璁剧疆褰卞搷)

key_reads锛堢储寮曡鍝嶅簲鏁?

Key_blocks_used

Qcache_*

Open_tables(閫氳繃table_cache鐨勮缃奖鍝?

Opened_table

table_locks

3. 绗笁鏂瑰伐鍏锋娴?/p>

mysqlreporthttp://hackmysql.com/mysqlreport

mytophttp://jeremy.zawodny.com/mysql/mytop/

绯荤粺鍙奙ysql鐨凩og

绯荤粺鍛戒护锛?top, sar

Mysql鐨凩og: slow_query.log
聽

(涓?纭欢鏂归潰鐨勪紭鍖?/p>

纭欢鏂归潰锛屾渶瀹规槗鎴愪负Mysql鐡堕鐨勯儴鍒嗘槸纾佺洏锛屽叾娆℃槸CPU鍜屽唴瀛?/p>

纾佺洏鏂归潰锛?/p>

浣跨敤鏇村揩鐨勭鐩橈紝浼氬Mysql鏈夊緢濂界殑甯姪
浣跨敤鏇村鐨勭‖鐩橈紝閫氳繃Raid锛屽彲浠ユ彁楂樺崟鍧楃鐩橀€熷害鐨勯棶棰?br> 瀵逛簬Raid鏂瑰紡锛屽缓璁噰鐢≧aid 0+1 鎴栬€?Raid 1+0

CPU锛?/p>

姣棤鐤戦棶锛屾洿楂樹富棰戠殑CPU鍜屾洿澶氱殑CPU鏁伴噺鍙互缁橫ysql鏇撮珮鐨勬€ц兘

鍐呭瓨锛?/p>

鏇撮珮鐨勫唴瀛橈紝寰€寰€鍙互璁㎝ysql涓殑鏇村鐨勬暟鎹紦瀛樺湪鍐呭瓨涓紝
浣嗘槸锛屼竴涓噸瑕佺殑鍥犵礌鏄紝闇€瑕佹湁姝ｇ‘鐨凪ysql鐨勯厤缃?/p>

缃戝崱锛?/p>

浣跨敤鍗冨厗缃戝崱鍙婂崈鍏嗙綉缁?br> 聽

(鍥?鎿嶄綔绯荤粺鏂归潰鐨勪紭鍖?/p>

1.涓嶄娇鐢ㄤ氦鎹㈠尯銆傚鏋滃唴瀛樹笉瓒筹紝澧炲姞鏇村鐨勫唴瀛樻垨閰嶇疆浣犵殑绯荤粺浣跨敤杈冨皯鍐呭瓨

2.涓嶈浣跨敤NFS纾佺洏

3.澧炲姞绯荤粺鍜孧ySQL鏈嶅姟鍣ㄧ殑鎵撳紑鏂囦欢鏁伴噺
浣跨敤ulimit 鈥搉 65535

4.澧炲姞绯荤粺鐨勮繘绋嬪拰绾跨▼鏁伴噺銆?/p>

5.鍏抽棴涓嶅繀瑕佺殑搴旂敤锛屼紭鍖栫‖鐩樺弬鏁帮紝浣跨敤hdparm娴嬭瘯
聽

(浜?搴旂敤绾х殑浼樺寲

1.浣跨敤澶氭湇鍔″櫒璐熻浇鍧囪　(澶氬彴璇诲拰鍐?鐢ㄥ鍒舵妧鏈繘琛屾暟鎹悓姝?

2.琛ㄧ殑鍒嗗尯 (鑷畾涔夊垎鍖?mysql5.1寮€濮嬫敮鎸佽嚜甯﹀垎鍖哄姛鑳?

3.浣跨敤鏁版嵁缂撳瓨鎶€鏈痬emcached
聽

(鍏?Mysql閰嶇疆鐨勪紭鍖?/p>

1.key_buffer(=512):绱㈠紩缂撳啿浣跨敤鐨勫唴瀛樻暟閲?/p>

杩欏MyISAM琛ㄦ潵璇撮潪甯搁噸瑕?璁惧畾鍦ㄥ彲鐢ㄥ唴瀛樼殑25%-30%杈冨ソ,閫氳繃妫€鏌ョ姸鎬佸€?Key_read_requests鍜?Key_reads,
鍙互鐭ラ亾key_buffer璁剧疆鏄惁鍚堢悊銆傛瘮渚媖ey_reads / key_read_requests搴旇灏藉彲鑳界殑浣庯紝鑷冲皯鏄?:100锛?:1000鏇村ソ ,鍚﹀垯璇存槑 key_buffer 璁剧疆鏈夌偣鍋忓皬銆?/p>

2.innodb_buffer_pool_size(= 512): 绱㈠紩缂撳啿浣跨敤鐨勫唴瀛樻暟閲?/p>

3.table_cache (=1024):鏁版嵁琛ㄧ紦瀛樺尯鐨勫昂瀵?/p>

姣忓綋MySQL璁块棶涓€涓〃鏃讹紝濡傛灉鍦ㄨ〃缂撳啿鍖轰腑杩樻湁绌洪棿锛岃琛ㄥ氨琚墦寮€骞舵斁鍏ュ叾涓紝杩欐牱鍙互鏇村揩鍦拌闂〃鍐呭銆?br> 閫氳繃妫€鏌ヨ繍琛屽嘲鍊兼椂闂寸殑 Open_tables 鍜?Opened_tables 鐘舵€佸€硷紝鍙互鍐冲畾鏄惁闇€瑕佽皟鏁?table_cache 鐨勫€笺€?br> 濡傛灉浣犲彂鐜?open_tables 鐨勫€肩瓑浜?table_cache锛屽苟涓斿彂鐜?opened_tables 鐘舵€佸€煎湪涓嶆柇澧為暱锛岄偅涔堜綘灏遍渶瑕佸鍔?table_cache 鍙傛暟鍊间簡,涔熶笉鑳界洸鐩湴鎶?table_cache 鍙傛暟璁剧疆鎴愬緢澶х殑鍊硷紝濡傛灉璁剧疆寰楀お楂橈紝鍙兘浼氶€犳垚鏂囦欢鎻忚堪绗︿笉瓒筹紝浠庤€岄€犳垚鎬ц兘涓嶇ǔ瀹氭垨鑰呰繛鎺ュけ璐ャ€?/p>

4.sort_buffer_size (=256):鎸囧畾鎺掑簭鐢ㄧ紦鍐插尯鐨勯暱搴?/p>

璇ュ弬鏁板搴旂殑鍒嗛厤鍐呭瓨鏄瘡杩炴帴鐙崰锛佸鏋滄湁100涓繛鎺ワ紝閭ｄ箞瀹為檯鍒嗛厤鐨勬€诲叡鎺掑簭缂撳啿鍖哄ぇ灏忎负100 脳 6 锛?600MB銆?br> 鎵€浠ワ紝瀵逛簬鍐呭瓨鍦?GB宸﹀彸鐨勬湇鍔″櫒鎺ㄨ崘璁剧疆涓?-8M

5.join_buffer_size :鍏宠仈鏌ヨ鐢ㄧ紦鍐插尯鐨勯暱搴?/p>

4G鍐呭瓨浠ヤ笂锛屽缓璁ぇ浜?2M,璇ュ弬鏁板搴旂殑鍒嗛厤鍐呭瓨涔熸槸姣忚繛鎺ョ嫭浜紒

6.max_connections (=1024):鍙互澶嶇敤鐨勭嚎绋嬫暟閲?/p>

鍏佽鍚屾椂杩炴帴MySQL鏈嶅姟鍣ㄧ殑瀹㈡埛鏁伴噺 ,鍙互瑙傚療鍜屼及璁＄郴缁熷湪宄板€兼渶澶х殑骞跺彂杩炴帴鏁版潵璁剧疆

7.thread_cache(=*):鍙互澶嶇敤鐨勭嚎绋嬫暟閲?/p>

涓€鑸缃负CPU鏁懊?

8.innodb_buffer_pool_size(= 512):innodb琛ㄧ紦瀛樻睜澶у皬

杩欏Innodb琛ㄦ潵璇撮潪甯搁噸瑕併€侷nnodb鐩告瘮MyISAM琛ㄥ缂撳啿鏇翠负鏁忔劅銆侻yISAM鍙互鍦ㄩ粯璁ょ殑 key_buffer_size 璁剧疆涓嬭繍琛岀殑鍙互锛岀劧鑰孖nnodb鍦ㄩ粯璁ょ殑innodb_buffer_pool_size 璁剧疆涓嬪嵈璺熻湕鐗涗技鐨勩€?br> 鐢变簬Innodb鎶婃暟鎹拰绱㈠紩閮界紦瀛樿捣鏉ワ紝鏃犻渶鐣欑粰鎿嶄綔绯荤粺澶鐨勫唴瀛橈紝鍥犳濡傛灉鍙渶瑕佺敤Innodb鐨勮瘽鍒欏彲浠ヨ缃畠楂樿揪 70-80% 鐨勫彲鐢ㄥ唴瀛樸€?br> 涓€浜涘簲鐢ㄤ簬 key_buffer 鐨勮鍒欐湁 — 濡傛灉浣犵殑鏁版嵁閲忎笉澶э紝骞朵笖涓嶄細鏆村锛岄偅涔堟棤闇€鎶奿nnodb_buffer_pool_size 璁剧疆鐨勫お澶т簡.

9.innodb_flush_logs_at_trx_commit(=1) :浜嬪姟鎻愪氦鍚庣殑鏃ュ織鍒锋柊妯″紡

鏄惁涓篒nnodb姣擬yISAM鎱?000鍊嶈€屽ご澶?鐪嬫潵涔熻浣犲繕浜嗕慨鏀硅繖涓弬鏁颁簡銆傞粯璁ゅ€兼槸 1锛岃繖鎰忓懗鐫€姣忔鎻愪氦鐨勬洿鏂颁簨鍔?鎴栬€呮瘡涓簨鍔′箣澶栫殑璇彞)閮戒細鍒锋柊鍒扮鐩樹腑锛岃€岃繖鐩稿綋鑰楄垂璧勬簮锛屽挨鍏舵槸娌℃湁鐢垫睜澶囩敤缂撳瓨鏃躲€傚緢澶氬簲鐢ㄧ▼搴忥紝灏ゅ叾鏄粠 MyISAM杞彉杩囨潵鐨勯偅浜涳紝鎶婂畠鐨勫€艰缃负 2 灏卞彲浠ヤ簡锛屼篃灏辨槸涓嶆妸鏃ュ織鍒锋柊鍒扮鐩樹笂锛岃€屽彧鍒锋柊鍒版搷浣滅郴缁熺殑缂撳瓨涓娿€傛棩蹇椾粛鐒朵細姣忕鍒锋柊鍒扮鐩樹腑鍘伙紝鍥犳閫氬父涓嶄細涓㈠け姣忕1-2娆℃洿鏂扮殑娑堣€椼€傚鏋滆缃?涓?灏卞揩寰堝浜嗭紝涓嶈繃涔熺浉瀵逛笉瀹夊叏浜?MySQL鏈嶅姟鍣ㄥ穿婧冩椂灏变細涓㈠け涓€浜涗簨鍔°€傝缃负2鎸囨尌涓㈠け鍒锋柊鍒版搷浣滅郴缁熺紦瀛樼殑閭ｉ儴鍒嗕簨鍔°€?/p>

聽

杞浇锛歨ttp://www.4u4v.net/mysql-database-performance-optimization.html