http://www.mysqlkorea.co.kr
Çѱ۸Ŵº¾ó 5.0 , Çѱ۸Ŵº¾ó 5.1 , MySQL 5.1 HA , »ç¿ëÀڸŴº¾ó
Advanced Knowle...  
¿£Áö´Ï¾î ³ëÆ®  
ºí·Î±×Á¸  
±Û·Î¹ú MySQL  
MySQL 5.5 GA  
MySQL 5.6 Developer  
ÃֽűÛ
foreign key ¼³Á¤¡¦
ÀÎÅØ½º Ãß°¡ µî¿¡¡¦
mysql master - s¡¦
´Ù´ë´Ù °ü°è¿¡¼­ ¡¦
mysql myÆÄÀϰú ¡¦
 
MySQL5.5.x Performance Schema
±Û¾´ÀÌ : royster   ³¯Â¥ : 11-10-13 09:18   Á¶È¸¼ö : 11401

MySQL5.5.x Performance Schema

MySQL Performance Schema´Â Low Level¿¡¼­ MySQL ServerÀÇ Performance¸¦ ¸ð´ÏÅ͸µ ÇϱâÀ§ÇÑ 5.5¹öÁ¯ÀÇ »õ·Î¿î ±â´ÉÀÔ´Ï´Ù.
º» chapter¿¡¼­´Â Performance SchemaÀ» »ç¿ëÇÏ´Â ¹æ¹ý°ú ¿¹Á¦¸¦ ÅëÇÏ¿© Performance Schema¸¦ ¼Ò°³ÇÕ´Ï´Ù.
MySQL Server¿¡ Performance Schema¸¦ »ç¿ëÇÒ ¼ö ÀÖµµ·Ï ±¸¼ºÇÏ¿©¾ß ÇÕ´Ï´Ù.
¿©·¯ºÐÀº command line¿¡ ¾Æ·¡¿Í °°ÀÌ Performance SchemaÀÇ Á¤º¸¸¦ Á¶È¸ÇØ º¼¼ö ÀÖ½À´Ï´Ù.
Ãâ·Â³»¿ëÀº performance_schema·Î ½ÃÀÛÇÏ´Â À̸§ÀÇ variablesµéÀÌ Ãâ·Â µË´Ï´Ù.
shell> mysqld --verbose --help
...
  --performance_schema
                      Enable the performance schema.
  --performance_schema_events_waits_history_long_size=#
                      Number of rows in events_waits_history_long.
...
¸¸¾à¿¡ performance_schema·Î ½ÃÀ۵Ǵ variables°¡ Ãâ·ÂµÇÁö ¾Ê´Â °æ¿ì¿¡´Â ÇØ´ç MySQL¹öÁ¯ÀÌ performance schema¸¦ Áö¿øÇÏÁö ¾Ê°Å³ª
MySQL Server°¡ performance schema¸¦ Áö¿øÇϵµ·Ï ¼³Á¤ µÇÁö ¾ÊÀº°ÍÀÔ´Ï´Ù.
performance schema¸¦ »ç¿ëÇÒ ¼ö ÀÖ´Ù°í °¡Á¤ÇÒ¶§ default·Î ºñȰ¼ºÈ­ »óÅÂÀÔ´Ï´Ù.
performace_schema variables¸¦ »ç¿ëÇÏ¿© MySQL Server¸¦ Àç½ÃÀÛ Çϼ¼¿ä.
my.cn f ÆÄÀÏ¿¡ ¾Æ·¡¿Í °°ÀÌ ¼³Á¤Çϼ¼¿ä.
[mysqld]
performance_schema
MySQL Server°¡ Àç½ÃÀ۵Ǹé performance_schema¸¦ ÃʱâÈ­ÇÕ´Ï´Ù.
¾Æ·¡ performance_schema variableÀÇ °ªÀÌ ON ÀÎÁö È®ÀÎÇϼ¼¿ä.
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
performance_schema variableÀÇ °ªÀÌ ON À̶ó¸é Á¤»óÀûÀ¸·Î MySQL Server°¡ start-up µÈ°ÍÀ̸ç
¸¸¾à¿¡ OFF ¶ó¸é ¿À·ù°¡ ¹ß»ýµÈ °ÍÀÔ´Ï´Ù. hostname.errÆÄÀÏÀ» Âü°íÇϼ¼¿ä.
performance_schema´Â Storage EngineÀ¸·Î ÀνÄÇÕ´Ï´Ù.
performance_schema EngineÀ» »ç¿ëÇÒ ¼öÀÖ´Â °æ¿ì ¿©·¯ºÐÀº INFORMATION_SCHEMA.ENGINES TABLE À̳ª
show engines ¸í·É¹®À¸·Î Á¶È¸ÇÒ¼ö ÀÖ½À´Ï´Ù.(¾Æ·¡¸¦ Âü°íÇϼ¼¿ä)
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES
    -> WHERE ENGINE='PERFORMANCE_SCHEMA'\G
*************************** 1. row ***************************
      ENGINE: PERFORMANCE_SCHEMA
     SUPPORT: YES
     COMMENT: Performance Schema
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
mysql> SHOW ENGINES\G
...
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
...
performance_schema Storage EngineÀº performance_schema Database ³»ÀÇ °¢°¢ÀÇ Tableµé¿¡¼­ ¿î¿µµË´Ï´Ù.
use database-name ¹®À¸·Î accessÇÒ¼ö ÀÖ½À´Ï´Ù.
mysql> USE performance_schema;
performance_schema TableµéÀº performance_schema Database¿¡ ÀúÀåµË´Ï´Ù.
INFORMATION_SCHEMA.TABLES ¿¡¼­ SELECT Çϰųª, SHOW statements ¸¦ »ç¿ëÇÏ¸é µË´Ï´Ù.(¾Æ·¡¸¦ Âü°íÇϼ¼¿ä)
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema';
+----------------------------------------------+
| TABLE_NAME                                   |
+----------------------------------------------+
| cond_instances                               |
| events_waits_current                         |
| events_waits_history                         |
| events_waits_history_long                    |
| events_waits_summary_by_instance             |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name    |
| file_instances                               |
| file_summary_by_event_name                   |
| file_summary_by_instance                     |
| mutex_instances                              |
| performance_timers                           |
| rwlock_instances                             |
| setup_consumers                              |
| setup_instruments                            |
| setup_timers                                 |
| threads                                      |
+----------------------------------------------+
mysql> SHOW TABLES FROM performance_schema;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| cond_instances                               |
| events_waits_current                         |
| events_waits_history                         |
...
performance_schema DatabaseÀÇ À̸§Àº ¼Ò¹®ÀÚÀ̸ç, ¶ÇÇÑ Query ÀÛ¼º½Ã¿¡µµ ¼Ò¹®ÀÚ·Î ÁöÁ¤ÇÏ¿©¾ß ÇÕ´Ï´Ù.
note) MySQL 5.5.8 ÀÌÀü version¿¡¼­´Â System VariableÀÎ lower_case_table_namesÀÇ Æ¯Á¤ °ªÀÌ ÀϺΠSystem¿¡¼­ ¹®Á¦Á¡ÀÌ ÀÖ¾ú½À´Ï´Ù.
ÀÚ ±×·³ ÀÌÁ¦ performance_schema DB ³»ÀÇ TableÀÇ ±¸Á¶À» Á¶È¸ÇÏ·Á¸é show create table table-name\G¹®À¸·Î Á¶È¸ÇØ º¸¼¼¿ä.
mysql> SHOW CREATE TABLE setup_timers\G
*************************** 1. row ***************************
       Table: setup_timers
Create Table: CREATE TABLE `setup_timers` (
  `NAME` varchar(64) NOT NULL,
  `TIMER_NAME` enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK')
   NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
ÇöÀç event , event history ¹× ¿ä¾à ,object instances ±×¸®°í ¼³Á¤Á¤º¸ performance_schema µ¥ÀÌŸº£À̽ºÀÇ TablesÀº
Á¤º¸ÀÇ Á¾·ù¿¡ µû¶ó ±×·ìÈ­ ÇÒ¼ö ÀÖ½À´Ï´Ù.
¾Æ·¡ ¿¹Á¦´Â ÀÌ·¯ÇÑ Tableµé¿¡ ´ëÇÑ »ç¿ë¹ýÀ» ¼³¸íÇÕ´Ï´Ù.
MySQL Server°¡ ÇöÀç ¹«¾ùÀ» ÇÏ´ÂÁö¸¦ Á¶È¸ÇÏ·Á¸é events_waits_current TableÀ» Á¶È¸Çϼ¼¿ä.
°¢°¢ÀÇ ThreadÀÇ ÃÖ±Ù ¸ð´ÏÅ͸µ À̺¥Æ®¸¦ Ãâ·ÂÇÕ´Ï´Ù.
mysql> SELECT * FROM events_waits_current\G
*************************** 1. row ***************************
            THREAD_ID: 0
             EVENT_ID: 5523
           EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex
               SOURCE: thr_lock.c:525
          TIMER_START: 201660494489586
            TIMER_END: 201660494576112
           TIMER_WAIT: 86526
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 142270668
     NESTING_EVENT_ID: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: 0
...
thread_ID 0 Àº THR_LOCK::mutex¿¡ lockÀ» ¾ò±â À§ÇØ 86,525 picoseconds(1Á¶ºÐÀÇ1ÃÊ)¸¦ ´ë±âÇÔÀ» ³ªÅ¸³À´Ï´Ù.
• IDÄ®·³Àº eventÀÇ threadÇ¥½Ã ¹× event¹øÈ£ ÀÔ´Ï´Ù.
• EVENT_NAMEÀº °èÃø¹üÀ§¸¦ ³ªÅ¸³»¸ç SOURCE´Â source file¿¡ instrumented code°¡ Æ÷ÇÔµÇÀÖ´Ù´Â °ÍÀ» ¾Ë·ÁÁÝ´Ï´Ù.
ÀÌÀü±Û MySQL5.5.x Storage Engines 
´ÙÀ½±Û MySQL 5.5 Semi-Synchronous Replication 
MySQL Korea »çÀÌÆ®ÀÇ ÄÁÅÙÃ÷ ¼ÒÀ¯±ÇÀº (ÁÖ)»ó»óÀ̺ñÁî¿¡ ÀÖÀ¸¹Ç·Î ¹«´ÜÀüÀ縦 ±ÝÇÕ´Ï´Ù.
Copyright ¨Ï ssebiz All Rights Reserved.