Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ
  MySQL ÆÛÆ÷¸Õ½º¸¦ top¸í·É¾îó·³ ¸ð´ÏÅ͸µ mytop ÀÛ¼ºÀÏ : 2003/09/08 01:45
 
  • ±Û¾´ÀÌ : ÁÁÀºÁøÈ£ ( http://coffeenix.net/ )
  • Á¶È¸¼ö : 13082
     
    ±Û¾´ÀÌ : ÁÁÀºÁøÈ£(truefeel, http://coffeenix.net/ )
    ÀÛ¼ºÀÏ : 2003.9.2(È­)
    ¼öÁ¤ÀÏ : 2003.9.7(ÀÏ)

    mytopÀº MySQL DB ÆÛÆ÷¸Õ½º¸¦ top¸í·É¾îó·³ ½Ç½Ã°£À¸·Î ¸ð´ÏÅ͸µÇÏ´Â ÅøÀÌ´Ù.
    ȨÆäÀÌÁö : http://jeremy.zawodny.com/mysql/mytop/

    MySQLÀÇ show full processlist¿Í show status Äõ¸® °á°ú°ªÀ» Åä´ë·Î µð½ºÇ÷¹ÀÌÇϴµ¥,
    È­¸é ¼³¸íÀº µÚ¿¡¼­ ÇÒ °ÍÀÌ´Ù.

    ¿î¿µ ȯ°æÀº ´ÙÀ½°ú °°´Ù. (mytopÀº perl·Î °³¹ßµÇ¾ú´Ù.)

    * Perl
    * ÆÞ ¸ðµâ - DB°ü·Ã - DBI, DBD::mysql (Çʼö)
    * ÆÞ ¸ðµâ - Term::ReadKey   (Çʼö)
    * ÆÞ ¸ðµâ - Ä÷¯·Î º¸±âÀ§Çؼ­´Â Term::ANSIColor (¼±ÅÃ)
    * ÆÞ ¸ðµâ - º¸´Ù ³ªÀº ½Ç½Ã°£ Á¤º¸¸¦ À§Çؼ­´Â Time::HiRes (¼±ÅÃ)

    1) ¼³Ä¡

    ¼³Ä¡´Â °úÁ¤Àº ´ÙÀ½ ¼ø¼­´ë·Î.

    ÆÞ ¸ðµâ DBI, DBD::mysql -> Term::ReadKey -> ¼±ÅûçÇ×°ú °ü·ÃµÈ ¸ðµâ -> mytop
    DBI¿Í DBD::mysql ¸ðµâÀº º¸Åë ¼³Ä¡µÈ °æ¿ì(Redhat 8ÀÌ»óÀº ±âº», 7.x´Â DBI ±âº»)°¡
    ¸¹À¸¸ç ¼³Ä¡ ¾ÈµÆ´õ¶ó°í CPAN¿¡¼­ ¹Þ¾Æ¼­ ¼³Ä¡ÇÏ¸é µÈ´Ù.

    http://search.cpan.org/dist/DBI/
    http://search.cpan.org/dist/DBD-mysql/
    http://search.cpan.org/dist/TermReadKey/
    http://search.cpan.org/dist/ANSIColor/

    ¸ðµâÀÇ ¼³Ä¡°úÁ¤Àº ¸ðµÎ ´ÙÀ½ °úÁ¤À» µû¸£¸é µÈ´Ù. mytopµµ ¸¶Âù°¡Áö ¹æ¹ýÀ¸·Î ¼³Ä¡ÇÑ´Ù.
    -------------------------------------------
    # tar xvfz ?????.tar.gz
    # cd ?????
    # perl Makefile.PL
    # make
    # make test
    # make install
    -------------------------------------------

    2) ½ÇÇàÈ­¸é

    # mytop -u root -p ?????? (???´Â mysql DB root PW)



    ºÎºÐº° Àǹ̸¦ ¾Ë¾Æº¸ÀÚ.

    =============================  ==========================  =======================
    Ç¥    ½Ã                       ÀÇ    ¹Ì                    °ü·Ã show status º¯¼ö¸í
    ==============================  ==========================  =======================
    MySQL on localhost (3.23.49a)   È£½ºÆ®¸í°ú MySQL ¹öÀü
    ------------------------------  --------------------------  -----------------------
    up 11+23:04:23 [17:17:46]       MySQL uptime°ú ÇöÀç ½Ã°£    Uptime
    ==============================  ==========================  =======================
    Queries: 947.4k                 uptimeÁßÀÇ ÃÑ Äõ¸® °³¼ö     Questions
    qps:    1 Slow:     0.0         ÃÊ´ç Äõ¸® ¼ö Æò±Õ°ú         Questions
                                    slow Äõ¸® ¼ö                Slow_queries

    ¡Ø Slow_queriesÀ̶õ MySQL ¼­¹ö ¼³Á¤°ªÀÎ long_query_time(default´Â 10ÃÊ)º¸´Ù ´õ ±ä
       ½Ã°£µ¿¾È ÁúÀÇµÈ Äõ¸® ¼ö¸¦ ÀǹÌÇÑ´Ù.
    ------------------------------  --------------------------  -----------------------
    Se/In/Up/De(%):    70/21/04/00  ÃÑ Äõ¸® Áß °¢°¢ Select,     Com_select
                                    Insert+Replace,             Com_insert, Com_replace
                                    Update,                     Com_update
                                    Delete ºñÀ²                 Com_delete
    ==============================  ==========================  =======================
    qps now:    2 Slow qps: 0.0     ÇöÀç Äõ¸® ¼ö¿Í              Questions
                                    ÇöÀç slow Äõ¸® ¼ö           Slow_queries
    ¡Ø Slow qps°¡ ÀÚÁÖ ³ª¿À¸é SQL¹® ÃÖÀûÈ­°¡ ÇÊ¿ä
    ------------------------------  --------------------------  -----------------------
    Threads:   11 (   3/   0)       11Àº ¾²·¹µå ÃÑ °³¼ö         Threads_connected
                                    3Àº µ¿ÀÛÁßÀÎ ¾²·¹µå         Threads_running
                                    0Àº ij½ÃµÈ ¾²·¹µå           Threads_cached
    ------------------------------  --------------------------  -----------------------
    62/12/00/00                     °¢°¢ ÇöÀçÀÇ Select,.. ºñÀ²  Com_select, ...
    ==============================  ==========================  =======================
    Key Efficiency: 100.0%          Key ij½Ã È÷Æ®À²             Key_reads, Key_read_requests

    ¡Ø Key Efficiency ³ôÀ» ¼ö·Ï Å° ¹öÆÛ¿¡¼­ ÀоîµéÀÎ °ÍÀÓ.
       ¼öÄ¡°¡ 99%º¸´Ù ³·´Ù¸é key_buffer_size Á¶Á¤ ÇÊ¿ä
    ------------------------------  --------------------------  -----------------------
    Bps in/out:  77.0/537.7         ÃÊ´ç ÁÖ°í¹ÞÀº Æò±Õ bytes    Bytes_received
                                                                Bytes_send
    ------------------------------  --------------------------  -----------------------
    Now in/out:   8.4/569.6         ÇöÀç ÁÖ°í¹ÞÀº bytes         Bytes_received
                                                                Bytes_send
    ==============================  ==========================  =======================


    ´Ù¾çÇÑ ½ÇÇà ¿É¼ÊÀº manÆäÀÌÁö¸¦ ÅëÇØ È®ÀÎÇϱ⠹ٶõ´Ù.
    ¶ÇÇÑ ½ÇÇàÁß »ç¿ëÇÏ´Â Å°´Â ? ¸¦ ´©¸£¸é µµ¿ò¸»À» º¼ ¼ö ÀÖ´Ù.
    c(commandº° »ç¿ë ºñÀ²°ú °Ç¼ö), p(ÀϽà Á¤Áö), s(È­¸é ¾÷µ¥ÀÌÆ®°£°Ý º¯°æ),
    q(Á¾·á), i(sleeping »óÅÂÀÇ ¾²·¹µå Ç¥½Ã ¿©ºÎ º¯°æ) µî ´Ù¾çÇÏ´Ù.

    c¸¦ ´­·¯ commandº°(select, insert, update µî) »ç¿ë ºñÀ²°ú °Ç¼ö¸¦ º¸ÀÚ.

      

    3) ~/.mytop ȯ°æ ¼³Á¤

    mytop -u ??? -p ??? ó·³ ½ÇÇàÇßÀ» ¶© Æнº¿öµå À¯Ãâ °¡´É¼ºµµ ÀÖ°í ¸Å¹ø ½ÇÇàÇϱ⵵
    ºÒÆíÇÒ ¼ö ÀÖ´Ù. ÀÌ·² ¶© Ȩµð·ºÅ丮ÀÇ ~/.mytop¿¡ ¼³Á¤ÇØµÎ¸é µÈ´Ù.

    -------------------------------------------
    user=root
    pass=???????
    host=localhost
    db=test
    port=3306
    socket=
    -------------------------------------------

    Âü°í·Î MySQLÆ©´×¿¡ ´ëÇÑ ±ÛÀº ´ÙÀ½À» Àо±â ¹Ù¶õ´Ù.

    ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ÀÇ ÃÖÀûÈ­  DB¿Í Mysql (±Û ¹®ÅÂÁØ)
    http://coffeenix.net/doc/mysql/mysql_tuning.html

    mysql ÃÖÀûÈ­ (¹ø¿ª ÃÖ¿µºÀ)
    http://database.sarang.net/database/mysql/tuning/optimize_mysql.html


    Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ / URL : http://coffeenix.net/board_view.php?bd_code=57