ÔÚLinux VPSϵͳÉÏÓÐʱºò»á·¢ÏÖMySQLÕ¼ÓÃCPU¸ß£¬µ¼ÖÂϵͳµÄ¸ºÔرȽϸߡ£ÕâÖÖÇé¿öºÜ¿ÉÄÜÊÇij¸öSQLÓï¾äÖ´ÐеÄʱ¼äÌ«³¤µ¼Öµġ£ÓÅ»¯Ò»ÏÂÕâ¸öSQLÓï¾ä»òÕßÓÅ»¯Ò»ÏÂÕâ¸öSQLÒýÓõÄij¸ö±íµÄË÷ÒýÒ»°ãÄܽâ¾öÎÊÌâ¡£ µ«ÊÇÔõôÕÒµ½...
     

     ÔÚLinux VPSϵͳÉÏÓÐʱºò»á·¢ÏÖMySQLÕ¼ÓÃCPU¸ß£¬µ¼ÖÂϵͳµÄ¸ºÔرȽϸߡ£ÕâÖÖÇé¿öºÜ¿ÉÄÜÊÇij¸öSQLÓï¾äÖ´ÐеÄʱ¼äÌ«³¤µ¼Öµġ£ÓÅ»¯Ò»ÏÂÕâ¸öSQLÓï¾ä»òÕßÓÅ»¯Ò»ÏÂÕâ¸öSQLÒýÓõÄij¸ö±íµÄË÷ÒýÒ»°ãÄܽâ¾öÎÊÌâ¡£

µ«ÊÇÔõôÕÒµ½ÊÇÄĸöSQLÓï¾äµÄÖ´ÐÐʱ¼ä¹ý³¤ÄØ£¿¿ÉÒÔͨ¹ýMySQL Slow LogÀ´ÕÒ£¬Ïê½âÈçÏ¡£

Ê×ÏÈÕÒµ½MySQLµÄÅäÖÃÎļþmy.cnf£¬¸ù¾Ý²»Í¬°æ±¾µÄmysql¿ªÆôÂý²éѯµÄÅäÖÃÒ²²»Ò»Ñù

mysql 5.0

[mysqld]
long_query_time = 1
log-slow-queries = /var/log/mysql/slow.log

mysql 5.1

[mysqld]
long_query_time = 1
slow_query_log=1
slow_query_log_file = /var/log/mysql/slow.log

long_query_time ÊÇÖ¸Ö´Ðг¬¹ý¶à¾ÃµÄsql»á±»logÏÂÀ´£¬ÕâÀïÊÇ1Ãë¡£
log-slow-queriesºÍslow_query_log_file ÉèÖðÑÈÕ־дÔÚÄÄÀï

°ÑÉÏÊö²ÎÊý´ò¿ª£¬ÔËÐÐÒ»¶Îʱ¼ä£¬¾Í¿ÉÒԹصôÁË£¬Ê¡µÃÓ°ÏìÉú²ú»·¾³

½ÓÏÂÀ´¾ÍÊÇ·ÖÎöÁË£¬ÎÒÕâÀïµÄÎļþÃû×ֽР/var/log/mysql/slow.log¡£
ÏÈmysqldumpslow ¨ChelpÏ£¬Ö÷ÒªÓõÄÊÇ

-s ORDER what to sort by (t, at, l, al, r, ar etc), ¡®at¡¯ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string

-s£¬ÊÇorderµÄ˳Ðò£¬ËµÃ÷дµÄ²»¹»Ïêϸ£¬Ö÷ÒªÓÐ
c,t,l,rºÍac,at,al,ar£¬·Ö±ðÊÇ°´ÕÕquery´ÎÊý£¬Ê±¼ä£¬lockµÄʱ¼äºÍ·µ»ØµÄ¼Ç¼ÊýÀ´ÅÅÐò£¬Ç°Ãæ¼ÓÁËaµÄʱµ¹Ðò
-t£¬ÊÇtop nµÄÒâ˼£¬¼´Îª·µ»ØÇ°Ãæ¶àÉÙÌõµÄÊý¾Ý
-g£¬ºó±ß¿ÉÒÔдһ¸öÕýÔòÆ¥Åäģʽ£¬´óСд²»Ãô¸ÐµÄ

mysqldumpslow -s c -t 20 /var/log/mysql/slow.log
mysqldumpslow -s r -t 20 /var/log/mysql/slow.log

ÉÏÊöÃüÁî¿ÉÒÔ¿´³ö·ÃÎÊ´ÎÊý×î¶àµÄ20¸ösqlÓï¾äºÍ·µ»Ø¼Ç¼¼¯×î¶àµÄ20¸ösql¡£

mysqldumpslow -t 10 -s t -g ¡°left join¡± /var/log/mysql/slow.log
Õâ¸öÊÇ°´ÕÕʱ¼ä·µ»ØÇ°10ÌõÀïÃ溬ÓÐ×óÁ¬½ÓµÄsqlÓï¾ä¡£

ÓÃÁËÕâ¸ö¹¤¾ß¾Í¿ÉÒÔ²éѯ³öÀ´ÄÇЩsqlÓï¾äÊÇÐÔÄܵÄÆ¿¾±£¬½øÐÐÓÅ»¯£¬±ÈÈç¼ÓË÷Òý£¬¸ÃÓ¦ÓõÄʵÏÖ·½Ê½µÈ¡£