커피닉스, 시스템 엔지니어의 쉼터 커피향이 나는 *NIX
커피닉스
시스템/네트웍/보안을 다루는 곳
* HanIRC의 #coffeenix 방
[ 장비 및 회선 후원 ]
HOME > 데이터베이스(database) > MySQL 도움말
검색 : 사이트 WHOIS 웹서버 종류


  MySQL 쿼리 실시간 모니터링 및 저장하기 작성일 : 2008/11/25 19:49
 
  • 글쓴이 : 좋은진호 ( http://coffeenix.net/ )
  • 조회수 : 21427
          [ 이전화면 / 수정 ]   비밀번호 :     인쇄용 화면
      제  목 : 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
      커피닉스 카페 최근 글
    [10/20] Cross Compiler 깔
    [07/14] SSL АО
    [04/26] Re: 도스화면 원격조종 여부
    [04/25] 도스화면 원격조종 여부
    [10/30] Cshell에서 난수 설정
    [10/23] 공항철도주식회사 SE 구인 件
    [01/26] Re: wget으로 다른서버에있는 디렉토리를 가져오려고합니다.
    [01/25] wget으로 다른서버에있는 디렉토리를 가져오려고합니다.
    [01/11] 특정 안드로이드 WebView 버전에서 SSL 문제 (WebView 버그)
    [08/01] DNS forwarder (전달자) 서버를 통해서 쿼리하면 역방향을 받아오질 못합니다.
    [05/16] (주)후이즈 시스템엔지니어 (경력자) 모집
    [02/15] [AWS] Cloudfront edge 확인하기
    [01/20] Mobile Service/eCommerce 기업에서 Server / Java / PHP 개발자 구인
    [01/11] 탄탄한 퍼블리싱 모바일기업에서 Mobile 개발자를 모십니다.
    [01/11] 탄탄한 퍼블리싱 모바일기업에서 Web Front 개발자를 모십니다.
      New!   최근에 등록한 페이지
      KiCad EDA Suite project (Free/Libre/Open-Source EDA Suite) (CAD)
      오픈캐스케이드 캐드 (OpenCASCADE CAD)
      QCad for Windows --- GNU GPL (Free Software)
      The Hello World Collection
      IPMI를 활용한 리눅스 서버관리
      DNS 설정 검사
      nagiosgraph 설치 방법
      Slony-I 설치 방법 (postgresql replication tool)
      Qmail기반의 Anti spam 시스템 구축하기
      clusterssh

    [ 함께하는 사이트 ]




    운영진 : 좋은진호(truefeel), 야수(yasu), 범냉이, sCag
    2003년 8월 4일~