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°¡ Æ÷ÇÔµÇÀÖ´Ù´Â °ÍÀ» ¾Ë·ÁÁÝ´Ï´Ù.