제  목 : 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