|
Ä¿ÇÇÇâÀÌ ³ª´Â *NIX
Ä¿ÇǴнº
½Ã½ºÅÛ/³×Æ®¿÷/º¸¾ÈÀ» ´Ù·ç´Â °÷
|
|
|
|
ÀÌÀü ÁÖÁ¦ º¸±â :: ´ÙÀ½ ÁÖÁ¦ º¸±â |
±Û¾´ÀÌ |
¸Þ½ÃÁö |
jjun
°¡ÀÔ: 2009³â 12¿ù 10ÀÏ ¿Ã¸° ±Û: 8
|
¿Ã·ÁÁü: 2010.4.26 ¿ù, 11:55 am ÁÖÁ¦: tcpdump·Î MySQL Äõ¸® È®ÀÎ |
|
|
Æ®·¡ÇÈÀÌ ÆøÁÖÇϴ³¯ DB¿¡¼ ¾î¶² Äõ¸®°¡ ½ÇÇàµÇ°í ÀÖ´ÂÁö,
MySQL replication ȯ°æ¿¡¼ Äõ¸®°¡ ±¸ºÐµÇ¾î ½ÇÇàµÇ´ÂÁö ±Ã±ÝÇؼ ã¾Æº» ÀÚ·áÀÔ´Ï´Ù.
slow query ·Î±×´Â ÁöÁ¤µÈ ½Ã°£º¸´Ù ´À¸®°Ô µÇ´Â °Í¸¸ º¸ÀÔ´Ï´Ù.
Àοë: | general logµµ ¼³Á¤µµ ÀÖ´Â °Í °°Àºµ¥, DB¼¹ö Àç½ÃÀÛÀÌ ÇÊ¿äÇϹǷÎ.. |
´Ù¸¥ ¹æ¹ýÀ¸·Î´Â MySQL Proxy¶ó´Â °ÍÀ¸·Î º¼ ¼ö µµ ÀÖÁö¸¸,
tcpdump·Î ³×Æ®¿öÅ© Æ®·¡ÇÈ¿¡¼µµ ã¾Æº¼ ¼ö ÀÖ´Ù°í ÇÕ´Ï´Ù.
Ãâó : http://jetpackweb.com/blog/2009/09/16/unobstrusive-viewing-of-mysql-queries-with-tcpdump/
¾Æ·¡¿¡¼ -i lo ¸¸ ½ÇÁ¦ Åë½Å¿¡ »ç¿ëµÇ´Â ÀåÄ¡¸íÀ¸·Î º¯°æÇÏ¸é µË´Ï´Ù.
º¸Åë -i eth0 ÀÔ´Ï´Ù.
ÄÚµå: |
tcpdump -i lo -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.=" $_";
}
}'
|
> tcpdump.out °ú °°ÀÌ ¸®´ÙÀÌ·º¼ÇÀ» Ãß°¡ÇÏ¿© ÆÄÀÏ·Î ÀúÀåÇؼ ºÐ¼®ÇÒ ¼ö µµ ÀÖ½À´Ï´Ù.
´Ù¸¸ ½Ã°£ÀÌ ±â·ÏÀÌ ¾ÈµÇ´Â °ÍÀÌ ¾Æ½±½À´Ï´Ù.
tcpdump ¿É¼ÇÀ» Ãß°¡ÇÒ¸é µÉ°Íµµ °°´Âµ¥ È®ÀÎÇغÁ¾ß°Ú½À´Ï´Ù.
¶ÇÇÑ »ç¿ëµÇ´Â perl ±¸¹®¿¡ ´ëÇؼµµ ºÐ¼®ÀÌ ÇÊ¿äÇÕ´Ï´Ù.
* ½ÇÇàµÈ select/update/insert/delete Äõ¸® °³¼ö È®Àοë
Àοë: |
cat tcpdump.out | grep -vi "set" | awk '{ print $1 }' | sort | uniq -c | sort -r | more
------
72556 select
2475 SELECT
39 insert
26 delete
7 INSERT
|
* Äõ¸®°¡ ½ÇÇàµÈ tables°ú Äõ¸® °¹¼ö È®Àοë
Àοë: |
cat tcpdump.out | grep -vi "set" | awk -Ffrom '{ print $2 }' | awk '{ print $1}' | sort | uniq -c | sort -r | more
------
205915 Å×À̺í¸í1
60062 Å×À̺í¸í2
43146 Å×À̺í¸í3 |
³ªÁß¿¡ º°µµ·Î Åë°è¿ëÀ¸·Î ¾²±â À§Çؼ´Â ´ÙÀ½°ú °°ÀÌ »ç¿ëÇصµ µÈ´Ù°í ÇÕ´Ï´Ù.
ÄÚµå: |
tcpdump -i lo port 3306 -s 65535 -x -n -q -tttt> tcpdump.out
|
ÀÌ·¸°Ô ÀúÀåµÈ tcpdump.outÀº Maatkit À̶ó´Â MySQL toolkit¿¡ Æ÷ÇÔµÈ mk-query-digest ¸í·ÉÀÇ -type=tcpdump ¿É¼ÇÀ» ÀÌ¿ëÇؼ È®ÀÎÀÌ °¡´ÉÇÏ´Ù°í ÇÕ´Ï´Ù.
Âü°í : http://www.mysqlperformanceblog.com/2009/07/01/gathering-queries-from-a-server-with-maatkit-and-tcpdump/
jjun °¡ 2010.4.27 È, 12:54 pm¿¡ ¼öÁ¤ÇÔ, ÃÑ 1 ¹ø ¼öÁ¤µÊ |
|
À§·Î |
|
|
truefeel Ä«Æä °ü¸®ÀÚ
°¡ÀÔ: 2003³â 7¿ù 24ÀÏ ¿Ã¸° ±Û: 1277 À§Ä¡: ´ëÇѹα¹
|
¿Ã·ÁÁü: 2010.4.27 È, 8:47 am ÁÖÁ¦: tcpdump·Î MySQL Äõ¸® È®ÀÎ (Ãß°¡ ÀÚ·á) |
|
|
2008³â¿¡ ½áµÐ 'MySQL Äõ¸® ½Ç½Ã°£ ¸ð´ÏÅ͸µ ¹× ÀúÀåÇϱâ'µµ Âü°íÇϽñ⠹ٶø´Ï´Ù.
´ÙÀ½°ú °°Àº ³»¿ëÀÌ ÀÖ½À´Ï´Ù.
1. tcpdump·Î MySQLÄõ¸®¸¦ ½º´ÏÇÎÇÒ ¶§, highlightÇÏ´Â ¹æ¹ý (ÀÌ ºÎºÐÀº jjun´Ô ¾²½Å ±Û°ú ÇÙ½ÉÀº °°À½)
2. ½º´ÏÇÎÇÒ ¶§, ƯÁ¤ Äõ¸®¹®¸¸ ã´Â ¹æ¹ý
3. php¿¡¼ MySQL ½ÇÇàÄõ¸®¸¦ ÅؽºÆ®(error logÆÄÀÏ)·Î ÀúÀåÇÏ´Â ¹ý
4. MySQL 5.1¿¡¼ ½ÇÇà Äõ¸® ·Î±×¸¦ ÀúÀåÇÏ´Â ¹æ¹ý |
|
À§·Î |
|
|
|
|
»õ·Î¿î ÁÖÁ¦¸¦ ¿Ã¸± ¼ö ÀÖ½À´Ï´Ù ´ä±ÛÀ» ¿Ã¸± ¼ö ÀÖ½À´Ï´Ù ÁÖÁ¦¸¦ ¼öÁ¤ÇÒ ¼ö ¾ø½À´Ï´Ù ¿Ã¸° ±ÛÀ» »èÁ¦ÇÒ ¼ö ¾ø½À´Ï´Ù ÅõÇ¥¸¦ ÇÒ ¼ö ¾ø½À´Ï´Ù
|
Powered by phpBB © 2001, 2005 phpBB Group
|