MySQLÕ¼ÓÃCPU¹ý¸ßÈçºÎÓÅ»¯ Z-}A"n
p/LV^TQ
Ò»´ÎÉú²úDB·þÎñÆ÷µÄ ³¬¸ººÉÔËÐÐÎÊÌâ½â¾ö£º uTO%O}D N
1.²é¿´Éú²úDB·þÎñÆ÷topÁÐ±í£¬ )iC@n8f7o
Ö´ÐÐ top ÃüÁî Z"Q9^;0%
²é¿´Cpu(s) ²ÎÊýÒ»Ö±´¦ÓÚ 98% ״̬ £¬load average´ïµ½ÁË 5 (4ºË·þÎñÆ÷) M.qv'zV`xG
%)d7iT~M
¿É¼ûDBÒѾ³¬¸ººÉÔËÐÐÁË b,jo94.G
KT(v'KE 1
2.ʹÓÃrootÓû§µÇ¼mysql 'F/uD1;
Ö´ÐÐ show full processlist ²é¿´Âý²éѯ£¬·´¸´Ö´ÐУ¬·¢ÏÖÒ»Ö±ÓÐ3¸öselect ²éѯÓï¾ä´æÔÚ£¬ÎªÁË»º½âDB·þÎñÆ÷ѹÁ¦£¬Ö±½ÓʹÓÃkillÃüÁîɱµô LV$Ko_9eA
kill Âý²éѯµÄid Ks^wX
·þÎñÆ÷ѹÁ¦»º½â£¬»Ö¸´Õý³£¡£ )L":I
ʹÓÃÕâÌõSQLÓï¾äÈ¥ÏîÄ¿ÖÐÕÒµ½¶ÔÓ¦¹¦ÄÜ£¬¾²éÊÇûÓõŦÄÜ£¬Ö±½ÓÒþ²ØÕâ¸ö¹¦ÄÜ£¬ÎÊÌâ½â¾ö¡£ i&)([C0z$
o1{3[=G
8090+ (U
"8a ?KQ
MysqlÕ¼ÓÃCPU¹ý¸ßÈçºÎÓÅ»¯£¿ -i91nMi]
y
GmFi
MySQL´¦Ôڸ߸ºÔØ»·¾³Ï£¬´ÅÅÌIO¶Áд¹ý¶à£¬¿Ï¶¨»áÕ¼Óúܶà×ÊÔ´£¬±ØÈ»CP»áUÕ¼Óùý¸ß¡£ :7Uv)@iUk
Õ¼ÓÃCPU¹ý¸ß£¬¿ÉÒÔ×öÈçÏ¿¼ÂÇ£º vwa*'C
1.´ò¿ªÂý²éѯÈÕÖ¾£¬²éѯÊÇ·ñÊÇij¸öSQLÓï¾äÕ¼Óùý¶à×ÊÔ´£¬Èç¹ûÊǵĻ°£¬¿ÉÒÔ¶ÔSQLÓï¾ä½øÐÐÓÅ»¯£¬±ÈÈçÓÅ»¯ insert Óï¾ä¡¢ÓÅ»¯ group by Óï¾ä¡¢ÓÅ»¯ order by Óï¾ä¡¢ÓÅ»¯ join Óï¾äµÈµÈ£» >xF&>SDC
2.¿¼ÂÇË÷ÒýÎÊÌ⣻ t-}IKrbv
3.¶¨ÆÚ·ÖÎö±í£¬Ê¹ÓÃoptimize table£» OP=-fX|*Q
4.ÓÅ»¯Êý¾Ý¿â¶ÔÏó£» =E(ed,gH8
5.¿¼ÂÇÊÇ·ñÊÇËøÎÊÌ⣻ E1w8d4P,G
6.µ÷ÕûһЩMySQL Server²ÎÊý£¬±ÈÈçkey_buffer_size¡¢table_cache¡¢innodb_buffer_pool_size¡¢innodb_log_file_sizeµÈµÈ£» ~oK0k_{~
7.Èç¹ûÊý¾ÝÁ¿¹ý´ó£¬¿ÉÒÔ¿¼ÂÇʹÓÃMySQL¼¯Èº»òÕߴ¸ß¿ÉÓû·¾³¡£ ^;[_CF_
P)Oe?z;G?
mysql show processlist + show status + kill IdÃüÁîÏê½â X1[zkb
O69TU[Vn
1. SHOW PROCESSLIST %uiCC>cC
ÏÔʾÄÄЩÏß³ÌÕýÔÚÔËÐС£ÄúÒ²¿ÉÒÔʹÓÃmysqladmin processlistÓï¾äµÃµ½´ËÐÅÏ¢¡£Èç¹ûÄúÓÐSUPERȨÏÞ£¬Äú¿ÉÒÔ¿´µ½ËùÓÐÏ̡߳£·ñÔò£¬ÄúÖ»ÄÜ¿´µ½Äú×Ô¼ºµÄỊ̈߳¨Ò²¾ÍÊÇ£¬ÓëÄúÕýÔÚʹÓõÄMySQL ÕË»§Ïà¹ØµÄỊ̈߳©¡£Çë²Î¼û13.5.5.3½Ú£¬¡°KILLÓï·¨¡±¡£Èç¹ûÄú²»Ê¹ÓÃFULL¹Ø¼ü´Ê£¬ÔòÖ»ÏÔʾÿ¸ö²éѯµÄÇ°100¸ö×Ö·û¡£ +^AAik<yl
zYWVz3l
±¾Óï¾ä±¨¸æTCP/IPÁ¬½ÓµÄÖ÷»úÃû³Æ£¨²ÉÓÃhost_name:client_port¸ñʽ£©£¬ÒÔ·½±ãµØÅж¨Äĸö¿Í»§¶ËÕýÔÚ×öʲô¡£ -H|!KnR
(Gapv9R
Èç¹ûÄúµÃµ½¡°too many connections¡±´íÎóÐÅÏ¢£¬²¢ÇÒÏëÒªÁ˽âÕýÔÚ·¢ÉúµÄÇé¿ö£¬±¾Óï¾äÊǷdz£ÓÐÓõġ£MySQL±£ÁôÒ»¸ö¶îÍâµÄÁ¬½Ó£¬ÈÃÓµÓÐSUPERȨÏÞµÄ ÕË»§Ê¹Óã¬ÒÔÈ·±£¹ÜÀíÔ±Äܹ»ËæʱÁ¬½ÓºÍ¼ì²éϵͳ£¨¼ÙÉèÄúûÓаѴËȨÏÞ¸øÓèËùÓеÄÓû§£©¡£ XgeUS;qtta
&t