MySQL ºÎÇÏüũ - Slow Query(½½·Î¿ì Äõ¸®)
Slow Query(½½·Î¿ìÄõ¸®)
Slow Query(½½·Î¿ìÄõ¸®)´Â ÁöÁ¤ÇÑ ½Ã°£º¸µµ ½ÇÇàÇϴµ¥ ¿À·¡°É¸®´Â Äõ¸®¿¡ ´ëÇÑ log¸¦ °ÍÀÔ´Ï´Ù
ƯÁ¤ ÀÛ¾÷ÀÌ ¿À·¡ °É¸°´Ù¸é ·Î±×¸¦ ÅëÇØ ¿øÀÎÀ» ÆľÇÇÒ ¼ö ÀÖ°Ú½À´Ï´Ù.
¶ÇÇÑ ´ë·®ÀÌ µ¥ÀÌÅ͸¦ Á¶È¸Çϰųª À߸øµÈ À妽º »ç¿ëÀ¸·Î ÀÎÇÑ Àå½Ã°£ MySQL ÇÁ·Î¼¼½º°¡ ¹°¸®´Â Çö»óÀÌ ¹ß»ý ÇÒ °æ¿ì ¾î¶² Äõ¸®¹®¿¡ ÀÌ·± Çö»óÀ» ¹ß»ý ÇÏ¿´´À³Ä¿¡ ´ëÇؼ ºÐ¼®½Ã Slow Query¸¦ »ç¿ëÇÏ¸é ºÎÇÏ°¡ Áõ°¡µÇ´Â Äõ¸®¸¦ ãÀ» ¼ö ÀÖÀ»²¨¶ó°í »ý°¢µË´Ï´Ù.
| MySQL Slow Query ¼³Á¤¹æ¹ý |
MySQL ¼³Á¤ÆÄÀÏ Slow Query ¼³Á¤
MySQL my.cnf ¼³Á¤
1 2 3 4 | [root@web ~]# vi my.cnf [mysqld] log_slow_queries = /var/ log /mysql/mysql-slow. log long_query_time = 3 |
½½·Î¿ì Äõ¸®°¡ 3ÃÊ°¡ ÃÊ°úÇÏ´Â Äõ¸®¿¡ ´ëÇÑ ·Î±×¸¦ ³²±â´Â ¼³Á¤ÀÔ´Ï´Ù.
½½·Î¿ì Äõ¸®°¡ 3ÃÊ°¡ ÃÊ°úÇÏ´Â Äõ¸®¿¡ ´ëÇÑ ·Î±×¸¦ ³²±â´Â ¼³Á¤ÀÔ´Ï´Ù.
log_slow_querise : ÀúÀåµÉ ·Î±×°æ·Î
long_query_time : Äõ¸®Å¸ÀÓÀÌ ÁöÁ¤ÇÑ ½Ã°£À» ÃÊ°úÇÏ´Â ºÎºÐ¸¸ ÀúÀå
MySQL Àç½ÃÀÛ
1 | [root@web ~]# /etc/init.d/mysqld restart |
| MySQL Slow Query ºÐ¼® |
Slow Query(½½·Î¿ì Äõ¸®) ·Î±×ÆÄÀÏ
1 2 3 4 | # User@Host: DB_À¯Àú[DB_À¯Àú] @ server_hostname [¼¹öIP] # Query_time: 0.000599 Lock_time: 0.000163 Rows_sent: 3 Rows_examined: 86 SET timestamp=1489118717; select count(*) |
À§¿Í °°Àº Çü½ÄÀ¸·Î ·Î±×ÆÄÀÏ¿¡ Slow Äõ¸®¸¦ ³²±â°Ô µË´Ï´Ù.
Slow Query(½½·Î¿ì Äõ¸®)·Î ³²Àº Äõ¸®µéÀº explainÀ» »ç¿ëÇÏ¿© Á¤»óÀûÀÎ index¸¦ Ÿ°í ÀÖ´ÂÁö üũÇÏ´Â ¹æ¹ýÀ¸·Î ¹®Á¦¸¦ ãÀ¸½Ã¸é µÉ°ÍÀ¸·Î »ý°¢µË´Ï´Ù.
Query_time : Äõ¸® 󸮽ð£
Lock_time : lockÀÌ °É¸° Ƚ¼ö
Row_sent : Á¶È¸ °á°ú Row ¼ö
Rows_examined : Á¶È¸ ´ë»ó ROW ¼ö
| MySQL Äõ¸® ÀÀ´ä½Ã°£ üũ |
mysqladmin ¸í·É¾î·Î »óŸ¦ Ãâ·Â
1 2 3 | [root@web ~]# mysqladmin -i5 status -u root -p Enter password: Uptime: 10578 Threads: 1 Questions: 4809 Slow queries: 589 Opens: 1321 Flush tables: 1 Open tables: 348 Queries per second avg: 0.454 |
Uptime : MySQL server ½Ã»èµÈ ÈÄ ÇöÀç ½Ã°£ (ÃÊ ´ÜÀ§)
Threads : ÇöÁ¦ DB ¼¹ö¿¡ ¿¬°áµÈ À¯Àú¼ö
Questions : ¼¹ö ½ÃÀÛÈÄ Áö±Ý±îÁö ¿äûµÈ Äõ¸®¼ö
Slow queries : mysql ¼³Á¤ÆÄÀÏ¿¡ ½½·Î¿ìÄõ¸®ÀÇ Äõ¸®½Ã°£ ÀÌ»óÀ» °¡Áø ¿äû¼ö
Opens : ¼¹ö°¡ ½ÃÀÛµÈ ÈÄ ÇöÀç±îÁö ¿·È´ø Å×À̺í¼ö
Open tables : ÇöÀç ¿·Á ÀÕ´Â Å×ÀÌºí ¼ö
Queries per second avg : Æò±Õ ÃÊ´Ü Äõ¸®¼ö