Mysql 鏌ョ湅杩炴帴鏁?鐘舵€_mysql

mysql 鏌ョ湅杩炴帴鏁?鐘舵€?
http://blog.csdn.net/starnight_cbj/article/details/4492555
鍛戒护锛?show processlist;
濡傛灉鏄痳oot甯愬彿锛屼綘鑳界湅鍒版墍鏈夌敤鎴风殑褰撳墠杩炴帴銆傚鏋滄槸鍏跺畠鏅€氬笎鍙凤紝鍙兘鐪嬪埌鑷繁鍗犵敤鐨勮繛鎺ャ€?
show processlist;鍙垪鍑哄墠100鏉★紝濡傛灉鎯冲叏鍒楀嚭璇蜂娇鐢╯how full processlist;
mysql> show processlist;

鍛戒护锛?show status;

鍛戒护锛歴how status like ‘%涓嬮潰鍙橀噺%’;

Aborted_clients 鐢变簬瀹㈡埛娌℃湁姝ｇ‘鍏抽棴杩炴帴宸茬粡姝绘帀锛屽凡缁忔斁寮冪殑杩炴帴鏁伴噺銆?
Aborted_connects 灏濊瘯宸茬粡澶辫触鐨凪ySQL鏈嶅姟鍣ㄧ殑杩炴帴鐨勬鏁般€?
Connections 璇曞浘杩炴帴MySQL鏈嶅姟鍣ㄧ殑娆℃暟銆?
Created_tmp_tables 褰撴墽琛岃鍙ユ椂锛屽凡缁忚鍒涢€犱簡鐨勯殣鍚复鏃惰〃鐨勬暟閲忋€?
Delayed_insert_threads 姝ｅ湪浣跨敤鐨勫欢杩熸彃鍏ュ鐞嗗櫒绾跨▼鐨勬暟閲忋€?
Delayed_writes 鐢↖NSERT DELAYED鍐欏叆鐨勮鏁般€?
Delayed_errors 鐢↖NSERT DELAYED鍐欏叆鐨勫彂鐢熸煇浜涢敊璇?鍙兘閲嶅閿€?鐨勮鏁般€?
Flush_commands 鎵цFLUSH鍛戒护鐨勬鏁般€?
Handler_delete 璇锋眰浠庝竴寮犺〃涓垹闄よ鐨勬鏁般€?
Handler_read_first 璇锋眰璇诲叆琛ㄤ腑绗竴琛岀殑娆℃暟銆?
Handler_read_key 璇锋眰鏁板瓧鍩轰簬閿琛屻€?
Handler_read_next 璇锋眰璇诲叆鍩轰簬涓€涓敭鐨勪竴琛岀殑娆℃暟銆?
Handler_read_rnd 璇锋眰璇诲叆鍩轰簬涓€涓浐瀹氫綅缃殑涓€琛岀殑娆℃暟銆?
Handler_update 璇锋眰鏇存柊琛ㄤ腑涓€琛岀殑娆℃暟銆?
Handler_write 璇锋眰鍚戣〃涓彃鍏ヤ竴琛岀殑娆℃暟銆?
Key_blocks_used 鐢ㄤ簬鍏抽敭瀛楃紦瀛樼殑鍧楃殑鏁伴噺銆?
Key_read_requests 璇锋眰浠庣紦瀛樿鍏ヤ竴涓敭鍊肩殑娆℃暟銆?
Key_reads 浠庣鐩樼墿鐞嗚鍏ヤ竴涓敭鍊肩殑娆℃暟銆?
Key_write_requests 璇锋眰灏嗕竴涓叧閿瓧鍧楀啓鍏ョ紦瀛樻鏁般€?
Key_writes 灏嗕竴涓敭鍊煎潡鐗╃悊鍐欏叆纾佺洏鐨勬鏁般€?
Max_used_connections 鍚屾椂浣跨敤鐨勮繛鎺ョ殑鏈€澶ф暟鐩€?
Not_flushed_key_blocks 鍦ㄩ敭缂撳瓨涓凡缁忔敼鍙樹絾鏄繕娌¤娓呯┖鍒扮鐩樹笂鐨勯敭鍧椼€?
Not_flushed_delayed_rows 鍦↖NSERT DELAY闃熷垪涓瓑寰呭啓鍏ョ殑琛岀殑鏁伴噺銆?
Open_tables 鎵撳紑琛ㄧ殑鏁伴噺銆?
Open_files 鎵撳紑鏂囦欢鐨勬暟閲忋€?
Open_streams 鎵撳紑娴佺殑鏁伴噺(涓昏鐢ㄤ簬鏃ュ織璁拌浇锛?
Opened_tables 宸茬粡鎵撳紑鐨勮〃鐨勬暟閲忋€?
Questions 鍙戝線鏈嶅姟鍣ㄧ殑鏌ヨ鐨勬暟閲忋€?
Slow_queries 瑕佽姳瓒呰繃long_query_time鏃堕棿鐨勬煡璇㈡暟閲忋€?
Threads_connected 褰撳墠鎵撳紑鐨勮繛鎺ョ殑鏁伴噺銆?
Threads_running 涓嶅湪鐫＄湢鐨勭嚎绋嬫暟閲忋€?
Uptime 鏈嶅姟鍣ㄥ伐浣滀簡澶氬皯绉掋€?

My.ini閰嶇疆 铏氭嫙鍐呭瓨

innodb_buffer_pool_size=576M   ->128M InnoDB寮曟搸缂撳啿鍖?

query_cache_size=100M             ->32 鏌ヨ缂撳瓨
tmp_table_size=102M                  ->32M 涓存椂琛ㄥぇ灏?
key_buffer_size=16m                  ->8M

璁剧疆max_connections

鍛戒护锛歴how variables like ‘%max_connections%’
锛堣繖涓姙娉曞湪debian锛媘ysql  Ver 12.22 Distrib 4.0.22, for pc-linux (i386)
閲屽疄楠屼簡锛?
璁剧疆鍔炴硶鏄湪my.cnf鏂囦欢涓紝娣诲姞涓嬮潰鐨勬渶鍚庣孩鑹茬殑涓€琛岋細

——————————————————————————–

[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
set-variable = max_connections=32000
锛堝湪闄㈤噷鐨凞ELL鏈哄櫒mysql4.0閲岀殑璇硶涓嶅悓
max_connecionts=2000
鐩存帴杩欎箞鍐欏氨濂戒簡

锛?

——————————————————————————–

淇敼瀹屾瘯鍚庯紝閲嶅惎MySQL鍗冲彲銆傚綋鐒讹紝涓轰簡纭繚璁剧疆姝ｇ‘锛屽簲璇ユ煡鐪嬩竴涓媘ax_connections銆?

娉ㄦ剰锛?
1銆佽櫧鐒惰繖閲屽啓鐨?2000銆備絾瀹為檯MySQL鏈嶅姟鍣ㄥ厑璁哥殑鏈€澶ц繛鎺ユ暟16384锛?
2銆侀櫎max_connections澶栵紝涓婅堪鍏朵粬閰嶇疆搴旇鏍规嵁浣犱滑绯荤粺鑷韩闇€瑕佽繘琛岄厤缃紝涓嶅繀鎷樻偿锛?
3銆佹坊鍔犱簡鏈€澶у厑璁歌繛鎺ユ暟锛屽绯荤粺娑堣€楀鍔犱笉澶с€?
4銆佸鏋滀綘鐨刴ysql鐢ㄧ殑鏄痬y.ini浣滈厤缃枃浠讹紝璁剧疆绫讳技锛屼絾璁剧疆鐨勬牸寮忚绋嶄綔鍙橀€氥€?

鐢╩ysqld –help 鍙互鏌ョ湅鍒癿ax_connections 鍙橀噺銆傘€€
鎴栬€?mysql -uuser -p
鍚巑ysql>show variables;
涔熶細鐪嬪埌max_connections 銆?nbsp;  

涓嬮潰鏄慨鏀瑰紶鑰佸笀 鐨剅edhat9鐨勬柟娉曪細

鍏堟槸mysql -uw01f -p
mysql>show variables;
鐪嬪埌max_connections 涓?00
mysql>exit;
vi /etc/my.cnf
    [mysqld]
set-variable=max_connections=250  #鍔犲叆杩欎簺鍐呭
:wq

/etc/init.d/mysqld restart
濂戒簡锛岃浜嗐€?

涓嬮潰鐨勬槸鎶勭殑锛屾垜鐢ㄤ笉浜?
mysql鐨勬渶澶ц繛鎺ユ暟榛樿鏄?00, 杩欎釜鏁板€煎浜庡苟鍙戣繛鎺ュ緢澶氱殑鏁版嵁搴撳簲鐢ㄦ槸杩滆繙涓嶅鐨勶紝褰撹繛鎺ヨ姹傚ぇ浜庨粯璁よ繛鎺ユ暟鍚庯紝灏变細鍑虹幇鏃犳硶杩炴帴鏁版嵁搴撶殑閿欒锛屽洜姝ゆ垜浠渶瑕佹妸瀹冮€傚綋璋冨ぇ涓€浜涳紝 鏈変袱绉嶅姙娉曞彲浠ヤ慨鏀规渶澶ц繛鎺ユ暟锛屼竴绉嶆槸淇敼safe_mysqld锛屽彟涓€绉嶆槸鐩存帴淇敼鍘熶唬鐮佸苟閲嶆柊缂栬瘧銆備笅闈㈡垜浠氨鍒嗗埆浠嬬粛杩欎袱绉嶆柟娉曪細

1.淇敼safe_mysqld
鎵惧埌safe_mysqld缂栬緫瀹冿紝鎵惧埌mysqld鍚姩鐨勯偅涓よ锛屽湪鍚庨潰鍔犱笂鍙傛暟 锛?

-O max_connections=1000

渚嬪 锛?鍏朵腑鍓嶉潰鏈?–鐨勬槸鍘熸潵鐨勫唴瀹癸紝鑰?++鏄慨鏀硅繃浠ュ悗鐨勶級
— safe_mysqld.orig Mon Sep 25 09:34:01 2000
+++ safe_mysqld Sun Sep 24 16:56:46 2000
@@ -109,10 +109,10 @@
if test “$#” -eq 0
then
nohup $ledir/mysqld –basedir=$MY_BASEDIR_VERSION –datadir=$DATADIR /
– –skip-locking >> $err_log 2>&1
+ –skip-locking -O max_connections=1000 >> $err_log 2>&1
else
nohup $ledir/mysqld –basedir=$MY_BASEDIR_VERSION –datadir=$DATADIR /
– –skip-locking “$@” >> $err_log 2>&1
+ –skip-locking “$@” -O max_connections=1000 >> $err_log 2>&1
fi
if test ! -f $pid_file # This is removed if normal shutdown
then
鐒跺悗鍏抽棴mysql閲嶅惎瀹冿紝鐢?
/mysqladmin鎵€鍦ㄨ矾寰?mysqladmin -uroot -p variables
杈撳叆root鏁版嵁搴撹处鍙风殑瀵嗙爜鍚庡彲鐪嬪埌
| max_connections | 1000 |
鍗虫柊鏀瑰姩宸茬粡鐢熸晥銆?

2.淇敼鍘熶唬鐮?

瑙ｅ紑MySQL鐨勫師浠ｇ爜锛岃繘鍏ラ噷闈㈢殑sql鐩綍淇敼mysqld.cc鎵惧埌涓嬮潰涓€琛岋細

{ “max_connections”, (long*) &max_connections,1000,1,16384,0,1},

鎶婂畠鏀逛负锛?

{ “max_connections”, (long*) &max_connections,1000,1,16384,0,1},

瀛樼洏閫€鍑猴紝鐒跺悗./configure ;make;make install鍙互鑾峰緱鍚屾牱鐨勬晥鏋溿€?

欢迎大家阅读《Mysql 鏌ョ湅杩炴帴鏁?鐘舵€_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)