MySQL 쿼리 실시간 모니터링 및 저장하기 > Mysql 정보

본문 바로가기

검색취소

MySQL 쿼리 실시간 모니터링 및 저장하기

검색 이전 다음 목록

본문

제  목 : 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_sniff_sample.png

[ 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

등록된 댓글이 없습니다.

검색취소

Mysql 정보

검색
  • no image
  • [MySQL] ARCHIVE 엔진 및 INNODB 테이블 압축

    MySQL (혹은 MariaDB도 동일)에는 기본적으로 다양한 테이블 엔진이 있습니다.최근에 필요에 따라 ARCHIVE 엔진을 사용해 볼 기회가 있었습니다.이 엔진은 로그 저장과 같이 계속해서 Append 되기만 하…

  • no image
  • [펌] MySQL 운영하면서 겪은 문제점과 해법들

     MySQL 운영하면서 겪은 문제점과 해법들지난 2년간 번개장터 서비스를 운영하면서 MySQL 을 사용하였는데 여러가지 한계에 부딛히면서 전문 업체에게 컨설팅을 받아서 시행착오를 줄이는게 낫겠다는 생각을 했습니다. …

  • LIKE vs INSTR() vs BINARY LIKE  속도 비교
  • LIKE vs INSTR() vs BINARY LIKE 속도 비교

    [MYSQL] LIKE vs INSTR()0. 배경1. 영문 검색어 테스트  1-1. 앞 부분 검색  1-2. 중간 부분 검색  1-3. 끝 부분 검색2. 한글 검색어 테스트  2-1. 앞 부분 검색  2-2. 중간…

  • no image
  • [DBMS] MySQL 의 InnoDB 엔진 의 특성

    다들 한번씩은 들어봄직한 InnoDB 엔진에는 개발시 매우 중요한 부분을 차지할 수도 있을 만한 특성이 있습니다. 이 특성 3가지를 언급해 보고자 합니다. 행여 InnoDB 를 사용하여 개발하시는 분들은 참고하시길 …

  • no image
  • 로그활용4 - slow query log

    로그활용4 - slow query logslow-query-log(--slow-query-log)처리에 시간이 걸린 쿼리를 기록하기 위한 옵션이다.쿼리 실행에 long_query_time에 세팅된 초수(표준 10초)…

  • no image
  • Mysql 기록하기 sql log record log 기록

    5.11.3. 일반 쿼리 로그일반 쿼리 로그는 mysqld의 일반적인 실행 결과를 기록한 것이다. 서버는 클라이언트가 접속을하거나 또는 접속을 끊을 때 정보를 이 로그에 기록하고, 클라이언트에서 받는 각 SQL 명령…

?>