Ä¿ÇÇÇâÀÌ ³ª´Â *NIX
Ä¿ÇǴнº
½Ã½ºÅÛ/³×Æ®¿÷/º¸¾ÈÀ» ´Ù·ç´Â °÷
*
HanIRCÀÇ #coffeenix ¹æ
[
Àåºñ ¹× ȸ¼± ÈÄ¿ø
]
> Forum <
IT ÀÏÁ¤
N
e
w
!
ÀÚµ¿È ÇÁ·ÎÁ§Æ®
HOME
>
µ¥ÀÌÅͺ£À̽º(database)
>
MySQL
µµ¿ò¸»
°Ë»ö :
»çÀÌÆ®
WHOIS
À¥¼¹ö Á¾·ù
MySQL Äõ¸® ½Ç½Ã°£ ¸ð´ÏÅ͸µ ¹× ÀúÀåÇϱâ
ÀÛ¼ºÀÏ : 2008/11/25 19:49
±Û¾´ÀÌ : ÁÁÀºÁøÈ£ (
http://coffeenix.net/
)
Á¶È¸¼ö : 24313
[
ÀÌÀüȸé
/
¼öÁ¤
] ºñ¹Ð¹øÈ£ :
Á¦ ¸ñ : 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
Ä¿ÇǴнº Ä«Æä ÃÖ±Ù ±Û
[04/22]
Re: OpenSSL Ãë¾àÁ¡ Á¤¸®, Logjam(·Î±×Àë)¿¡¼ Heartbleed±îÁö
[04/21]
LET¡¯S START WITH ON
[04/21]
º¸Çè
[04/20]
Á¦ÁÖ
[04/20]
±¹³»
[04/19]
Á¦ÁÖ
[04/18]
??? ?????
[04/17]
???? onion ?????? -
[04/11]
±¹°¡
[04/10]
Stride Into Dream:
[03/20]
Re: ¿Â¶óÀΰÔÀÓÀÇ Á¾ÁÖ±¹ ´ëÇѹα¹
[10/20]
Cross Compiler ±ò
[07/14]
SSL ¬¡¬°
[04/26]
Re: µµ½ºÈ¸é ¿ø°ÝÁ¶Á¾ ¿©ºÎ
[04/25]
µµ½ºÈ¸é ¿ø°ÝÁ¶Á¾ ¿©ºÎ
N
e
w
! ÃÖ±Ù¿¡ µî·ÏÇÑ ÆäÀÌÁö
KiCad EDA Suite project (Free/Libre/Open-Source EDA Suite) (CAD)
¿ÀÇÂij½ºÄÉÀ̵å ijµå (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ÀÏ~