MySQL Äõ¸® ½Ç½Ã°£ ¸ð´ÏÅ͸µ ¹× ÀúÀåÇϱâ | ÀÛ¼ºÀÏ : 2008/11/25 19:49 |
Á¶È¸¼ö : 24268 |
Á¦ ¸ñ : MySQL Äõ¸® ½Ç½Ã°£ ¸ð´ÏÅ͸µ ¹× ÀúÀåÇϱâ ÀÛ¼ºÀÚ : ÁÁÀºÁøÈ£(truefeel, http://coffeenix.net/ ) ÀÛ¼ºÀÏ : 2008.11.24(¿ù) MySQL¼¹ö´Â log ¿É¼Ç(my.cnf¿¡¼ log=ÆÄÀϸí. slow query´Â log-slow-queries=ÆÄÀϸí)À» ÅëÇؼ ½ÇÇàµÈ DB Äõ¸®¸¦ ¸ðµÎ ÆÄÀÏ·Î ÀúÀåÇÒ ¼ö°¡ ÀÖ´Ù. ±×·¯³ª DB ¼¹ö¿¡¼ ÀÛÀº »çÀÌÆ®°¡ ¾Æ´Ï°í¼± ¾öû³ ¾çÀÇ ½Ç½Ã°£ Äõ¸®¸¦ ¸ðµÎ ½×´Â´Ù´Â °ÍÀº Èûµé´Ù. DB ¿î¿µµµÁß¿¡ Àá½Ãµ¿¾È Äõ¸® ·Î±×¸¦ ½×°Ú´Ù°í MySQL Àç½ÇÇàÇÒ ¼öµµ ¾ø´Â ³ë¸©ÀÌ´Ù. MySQL 5.1¿¡¼´Â Àç½ÇÇà¾øÀÌ ·Î±×¸¦ ½×À» °ÍÀÎÁö ¾ÈÇÒ °ÍÀÎÁö¸¦ °áÁ¤ÇÒ ¼ö ÀÖÁö¸¸, À̺κÐÀº µÚ¿¡¼ ¼³¸íÇϱâ·Î ÇÏ°í, DB Äõ¸®¸¦ ½Ç½Ã°£À¸·Î »ìÆ캸´Â ¸î°¡Áö ¹æ¹ýÀ» ¼Ò°³ÇÑ´Ù. 1. Äõ¸®¸¦ ½Ç½Ã°£À¸·Î ½º´ÏÇÎÇϱâ Poor man¡¯s query logging (Posted by Maciej Dobrzanski) ( http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ )¿¡¼ tcpdump¿Í perlÀÇ Á¤±ÔÇ¥ÇöÀ¸·Î ½Ç½Ã°£ Äõ¸®¸¦ »ìÆ캸´Â ±¦ÂúÀº ¹æ¹ýÀ» ¼Ò°³ÇÏ°í ÀÖ´Ù. * À§ ºí·Î±×¿¡ ¿Ã¶ó¿Â °£´ÜÇÑ ¸í·É
À§ ¸í·ÉÀ» È°¿ëÇÏ¿© SELECT/UPDATE/DELETE/INSERT µîÀ» highlight Çϵµ·Ï ÀÛ¼ºÇß´Ù. highlight ¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ±ÛÀº '·Î±× ¸ð´ÏÅ͸µ½Ã ƯÁ¤ ¹®ÀÚ¸¦ highlightÇϱâ (2008.1, ±Û ÁÁÀºÁøÈ£)' ( http://coffeenix.net/board_view.php?bd_code=1562 )¸¦ »ìÆ캸±â ¹Ù¶õ´Ù. * query_sniff.sh ³»·Á¹Þ±â
* query_view.pl ³»·Á¹Þ±â
query_sniff.sh ´Â MySQL Äõ¸®¸¦ ½º´ÏÇÎÇÏ°í, query_view.pl´Â Äõ¸®¸¦ ³Ñ°Ü¹Þ¾Æ highlight ¸¦ ÇÑ´Ù. ½ÇÇàÀº °£´ÜÇÏ´Ù.
[ MySQL Äõ¸®¸¦ ½º´ÏÇÎÇÑ ÈÄ highlight. 'count'¹®ÀÚµµ ÇÔ²² highlight ] 2. php¿¡¼ ÇÊ¿ä½Ã ½ÇÇà Äõ¸® ÀúÀåÇϱâ À¥ÆäÀÌÁö¿¡¼ È£ÃâµÈ Äõ¸®¸¸ »ìÆ캻´Ù¸é, °¡Àå º¸ÆíÀûÀ¸·Î ÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀÌ´Ù.
À§Ã³·³ php¿¡¼ °øÅëÀûÀ¸·Î »ç¿ëÇÏ´Â ¶óÀ̺귯¸®¿¡ ³Ö¾îµÎ°í, MySQL Äõ¸®¸¦ ó¸®ÇØÁÖ´Â php Ŭ·¡½º ºÎºÐ¿¡ ´ÙÀ½°ú °°ÀÌ ³Ö¾îÀÖ´Ù¸é ÇÊ¿ä½Ã¸¶´Ù(DEBUG_QUERY¸¦ Á¤ÀÇ¿©ºÎ¿¡ µû¶ó) Äõ¸®¸¦ /LOG/db/YYYYMMDD ·Î±× ÆÄÀÏ·Î ÀúÀåÇÒ ¼ö ÀÖ´Ù.
3. MySQL 5.1¿¡¼ Äõ¸® »ìÆ캸±â, ±× ÀÌ¿Ü MySQL 5.1(ÇöÀç 5.1.x ¹öÀüÀº ÃÖÁ¾ RC¹öÀüÀÌ ¹ßÇ¥µÈ »óÅÂÀ̸ç, °ð Á¤½Ä ¹öÀüÀÌ ³ª¿Â´Ù.)¿¡¼´Â Äõ¸®¸¦ ·Î±ëÇÒÁö ¾ÈÇÒÁö ¿©ºÎ¸¦ Àç½ÇÇà¾øÀÌ º¯°æÇÒ ¼ö ÀÖ´Â ±â´ÉÀÌ ÀÖ´Ù. ´ÙÀ½°ú °°ÀÌ ·Î±× ÀúÀåÀ» ONÀ¸·Î ¼³Á¤ÇÒ ¼ö ÀÖ°í, ÇÊ¿ä¾øÀ» ¶§ ´Ù½Ã OFF¸¦ ÇÏ¸é µÈ´Ù.
ÆÄÀÏÀÌ ¾Æ´Ñ Å×À̺í·Î ·Î±×¸¦ ÀúÀåÇÏ·Á¸é ´ÙÀ½°ú °°ÀÌ ¼³Á¤ÇÑ´Ù.
log_output= ¼³Á¤ °ªÀ¸·Î´Â FILE, TABLE, NONE (log_output = 'FILE', log_output = 'TABLE', ..)À» ÁöÁ¤ÇÒ ¼ö ÀÖ´Ù. DB Å×À̺í·Î Äõ¸®¸¦ ÀúÀåÇÏ°Ô µÇ¸é Äõ¸® Àüü´Â general_log Å×ÀÌºí¿¡, slow Äõ¸®´Â slow_log Å×ÀÌºí¿¡ °¢°¢ ÀúÀåµÈ´Ù. general_log Å×À̺íÀÇ ½ºÅ°¸¶´Â ´ÙÀ½°ú °°À¸¸ç, slow_log Å×À̺íÀº query_time, lock_time µî ´Ù¸¥ Çü½ÄÀÇ µ¥ÀÌÅ͵éÀÌ ÀúÀåµÈ´Ù.
ÀÌ ¹æ¹ý ¿Ü¿¡, À§ '1. Äõ¸®¸¦ ½Ç½Ã°£À¸·Î ½º´ÏÇÎÇϱâ'¿¡¼ ¼Ò°³ÇÑ ºí·Î±×ÀÇ ´ñ±ÛÀ» º¸¸é, perl·Î ¸¸µé¾îÁø MySQL Query Sniffer( http://iank.org/querysniffer/ , Net::Pcap ¸ðµâ ÇÊ¿ä)·Î Äõ¸®¸¦ ½º´ÏÇÎÇÒ ¼ö ÀÖ´Ù. ÀÌ ±ÛÀº 'Poor man¡¯s query logging'ºí·Î±× ±ÛÀÌ ¾Æ´Ï¾ú´Ù¸é, »ý°¢ÇÏÁö ¸øÇßÀ» °ÍÀÌ´Ù. Maciej Dobrzanski´Ô¿¡°Ô °¨»ç. ^^ 4. Âü°íÀÚ·á * Poor man¡¯s query logging (2008.11, Maciej Dobrzanski) http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ * ·Î±× ¸ð´ÏÅ͸µ½Ã ƯÁ¤ ¹®ÀÚ¸¦ highlightÇϱâ (2008.1, ±Û ÁÁÀºÁøÈ£) http://coffeenix.net/board_view.php?bd_code=1562 * MySQL Protocol (MySQL 4.1.x ±âÁØÀ¸·Î, MySQL ÇÁ·ÎÅäÄÝ ¼³¸í) http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html * MySQL 5.1 Reference Manual :: Server Command Options (general_log ¼³Á¤, slow_query_log ¼³Á¤, log_output ¼³Á¤) http://dev.mysql.com/doc/refman/5.1/en/server-options.html * MySQL 5.1 Reference Manual :: Selecting General Query and Slow Query Log Output Destinations http://dev.mysql.com/doc/refman/5.1/en/log-tables.html |
Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ / URL : http://coffeenix.net/board_view.php?bd_code=1660 |