Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ
  MySQL Äõ¸® ½Ç½Ã°£ ¸ð´ÏÅ͸µ ¹× ÀúÀåÇϱâ ÀÛ¼ºÀÏ : 2008/11/25 19:49
 
  • ±Û¾´ÀÌ : ÁÁÀºÁøÈ£ ( http://coffeenix.net/ )
  • Á¶È¸¼ö : 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ÀÇ Á¤±ÔÇ¥ÇöÀ¸·Î ½Ç½Ã°£ Äõ¸®¸¦ »ìÆ캸´Â ±¦ÂúÀº ¹æ¹ýÀ» ¼Ò°³ÇÏ°í ÀÖ´Ù.

    * À§ ºí·Î±×¿¡ ¿Ã¶ó¿Â °£´ÜÇÑ ¸í·É
     # tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
    while(<>) { chomp; next if /^[^ ]+[ ]*$/;
    ¡¡¡¡¡¡if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    ¡¡¡¡¡¡¡¡¡¡¡¡if (defined $q) { print ¡°$q\n¡±; }
    ¡¡¡¡¡¡¡¡¡¡¡¡$q=$_;
    ¡¡¡¡¡¡} else {
    ¡¡¡¡¡¡¡¡¡¡¡¡$_ =~ s/^[ \t]+//; $q.=¡± $_¡±;
    ¡¡¡¡¡¡}
    }¡¯
     


    À§ ¸í·ÉÀ» È°¿ëÇÏ¿© SELECT/UPDATE/DELETE/INSERT µîÀ» highlight Çϵµ·Ï ÀÛ¼ºÇß´Ù. highlight ¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ±ÛÀº '·Î±× ¸ð´ÏÅ͸µ½Ã ƯÁ¤ ¹®ÀÚ¸¦ highlightÇϱâ (2008.1, ±Û ÁÁÀºÁøÈ£)' ( http://coffeenix.net/board_view.php?bd_code=1562 )¸¦ »ìÆ캸±â ¹Ù¶õ´Ù.

    * query_sniff.sh ³»·Á¹Þ±â
     
    #!/bin/sh
    #
    # MySQL ÆÐŶ »ìÆ캸±â
    #
    # by ÁÁÀºÁøÈ£(truefeel, http://coffeenix.net/ )
    # 2008.11.24

    INTERFACE="eth1"
    PORT="3306"

    if [ "$1" != "" ]; then
            arg=$1
    fi

    tcpdump -p -i $INTERFACE -s 0 -l -w - dst port $PORT | strings -6 | ./query_view.pl $arg
     


    * query_view.pl ³»·Á¹Þ±â
     
    #!/usr/bin/perl
    #
    # MySQL query highlightÇϱâ
    #
    # by ÁÁÀºÁøÈ£(truefeel, http://coffeenix.net/ )
    # 2008.11.24

    # color
    $szColBk ="^[[;30m";    $szColBk1 ="^[[1;30m";  # black
    $szColRe ="^[[;31m";    $szColRe1 ="^[[1;31m";  # red
    $szColGr ="^[[;32m";    $szColGr1 ="^[[1;32m";  # green
    $szColYe ="^[[;33m";    $szColYe1 ="^[[1;33m";  # yellow
    $szColBl ="^[[;34m";    $szColBl1 ="^[[1;34m";  # blue
    $szColPu ="^[[;35m";    $szColPu1 ="^[[1;35m";  # magenta(purple)
    $szColCy ="^[[;36m";    $szColCy1 ="^[[1;36m";  # cyan
    $szColGy ="^[[;37m";    $szColWh  ="^[[1;37m";  # white
    $szNormal="^[[;m";

    #
    $szPattSel="SELECT";
    $szPattChg="UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|SHOW";
    $szPattMisc=" FROM | INTO| SET | VALUES";
    $szPattWhere=" WHERE ";
    $szPatt="$szPattSel|$szPattChg";

    #
    if ($#ARGV == 0) {
            $szPattWant="$ARGV[0]";
    }

    #
    while(<STDIN>) {
            chomp; next if /^[^ ]+[ ]*$/;
            if(/^($szPatt)/i) {
                    if (defined $q) {
                            $q=~ s/($szPattSel)/$szColGr$1$szNormal/gi;
                            $q=~ s/($szPattChg)/$szColYe1$1$szNormal/gi;
                            $q=~ s/($szPattMisc)/$szColYe$1$szNormal/gi;
                            $q=~ s/($szPattWhere)/$szColCy1$1$szNormal/gi;
                            $q=~ s/($szPattWant)/$szColRe1$1$szNormal/gi if ( $szPattWant );
                            print "$q\n";
                    }
                    $q=$_;
            } else {
                    $_ =~ s/^[ \t]+//;
                    $q.=" $_";
            }
    }
     


    query_sniff.sh ´Â MySQL Äõ¸®¸¦ ½º´ÏÇÎÇÏ°í, query_view.pl´Â Äõ¸®¸¦ ³Ñ°Ü¹Þ¾Æ highlight ¸¦ ÇÑ´Ù. ½ÇÇàÀº °£´ÜÇÏ´Ù.

     # ./query_sniff.sh ¶Ç´Â
    # ./query_sniff.sh "°Ë»ö¾î"
     




    [ MySQL Äõ¸®¸¦ ½º´ÏÇÎÇÑ ÈÄ highlight. 'count'¹®ÀÚµµ ÇÔ²² highlight ]

    2. php¿¡¼­ ÇÊ¿ä½Ã ½ÇÇà Äõ¸® ÀúÀåÇϱâ

    À¥ÆäÀÌÁö¿¡¼­ È£ÃâµÈ Äõ¸®¸¸ »ìÆ캻´Ù¸é, °¡Àå º¸ÆíÀûÀ¸·Î ÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀÌ´Ù.

     $szLog=date("Ymd");
    ini_set("error_log", "/LOG/db/$szLog");
     


    À§Ã³·³ php¿¡¼­ °øÅëÀûÀ¸·Î »ç¿ëÇÏ´Â ¶óÀ̺귯¸®¿¡ ³Ö¾îµÎ°í, MySQL Äõ¸®¸¦ ó¸®ÇØÁÖ´Â php Ŭ·¡½º ºÎºÐ¿¡ ´ÙÀ½°ú °°ÀÌ ³Ö¾îÀÖ´Ù¸é ÇÊ¿ä½Ã¸¶´Ù(DEBUG_QUERY¸¦ Á¤ÀÇ¿©ºÎ¿¡ µû¶ó) Äõ¸®¸¦ /LOG/db/YYYYMMDD ·Î±× ÆÄÀÏ·Î ÀúÀåÇÒ ¼ö ÀÖ´Ù.

     define("DEBUG_QUERY", true);

    if ( defined("DEBUG_QUERY") ) {
    ¡¡¡¡¡¡error_log("$szQuery");
    fi
     


    3. MySQL 5.1¿¡¼­ Äõ¸® »ìÆ캸±â, ±× ÀÌ¿Ü

    MySQL 5.1(ÇöÀç 5.1.x ¹öÀüÀº ÃÖÁ¾ RC¹öÀüÀÌ ¹ßÇ¥µÈ »óÅÂÀ̸ç, °ð Á¤½Ä ¹öÀüÀÌ ³ª¿Â´Ù.)¿¡¼­´Â Äõ¸®¸¦ ·Î±ëÇÒÁö ¾ÈÇÒÁö ¿©ºÎ¸¦ Àç½ÇÇà¾øÀÌ º¯°æÇÒ ¼ö ÀÖ´Â ±â´ÉÀÌ ÀÖ´Ù. ´ÙÀ½°ú °°ÀÌ ·Î±× ÀúÀåÀ» ONÀ¸·Î ¼³Á¤ÇÒ ¼ö ÀÖ°í, ÇÊ¿ä¾øÀ» ¶§ ´Ù½Ã OFF¸¦ ÇÏ¸é µÈ´Ù.

     SET GLOBAL general_log = 'ON';
    SET GLOBAL slow_query_log = 'ON';
    ... »ý·« ...
    SET GLOBAL general_log = 'OFF';
    SET GLOBAL slow_query_log = 'OFF';
     


    ÆÄÀÏÀÌ ¾Æ´Ñ Å×À̺í·Î ·Î±×¸¦ ÀúÀåÇÏ·Á¸é ´ÙÀ½°ú °°ÀÌ ¼³Á¤ÇÑ´Ù.
     SET GLOBAL log_output = 'TABLE';
     


    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 µî ´Ù¸¥ Çü½ÄÀÇ µ¥ÀÌÅ͵éÀÌ ÀúÀåµÈ´Ù.

     mysql> desc mysql.general_log;
    +--------------+-------------+------+-----+-------------------+-----------------------------+
    | Field        | Type        | Null | Key | Default           | Extra                       |
    +--------------+-------------+------+-----+-------------------+-----------------------------+
    | event_time   | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    | user_host    | mediumtext  | NO   |     | NULL              |                             |
    | thread_id    | int(11)     | NO   |     | NULL              |                             |
    | server_id    | int(11)     | NO   |     | NULL              |                             |
    | command_type | varchar(64) | NO   |     | NULL              |                             |
    | argument     | mediumtext  | NO   |     | NULL              |                             |
    +--------------+-------------+------+-----+-------------------+-----------------------------+
    6 rows in set (0.00 sec)
     


    ÀÌ ¹æ¹ý ¿Ü¿¡, À§ '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