MySQL InnoDBã«ãããããã¯ç«¶åã®è§£ææé
ãã¼ã¿ãã¼ã¹ã®éç¨ã§é¿ããããªãã®ããããã¯ç«¶åã«ãã£ã¦èµ·ããã·ã¹ãã ãã©ãã«ã¸ã®å¯¾å¿ã§ããã2æã¾ã§ã«çµããã¯ãã®ãããå¦çãæã«ãªã£ã¦ãçµãã£ã¦ããªããã¨ããè² è·ãé«ãããã§ããªãã®ã«ã·ã¹ãã ãç¡å¿çã«ãªã£ã¦ãããã¨ãã£ããã©ãã«ãçºçããã¨ããDBã¨ã³ã¸ãã¢ã¯ãããããã¯ç«¶åã«ãããã®ãªã®ãã©ãããåãåãã¦ãé©åã«å¯¾å¦ããªããã°ãªãã¾ããã
ããã¾ã§InnoDBã¯ããã¯ç«¶åã«å¯¾ãã¦ã»ã¨ãã©æã¤æããªãã£ãã®ã§ãããæè¿ãããã対å¦æ¹æ³ãã§ã¦ãã¾ãããä»æ¥ã¯ãã®æé ã確èªãã¦ããããã¨æãã¾ãã
åæ
ä»åãç´¹ä»ããæé ã¯ãMySQLã®ä»¥ä¸ã®ãã¼ã¸ã§ã³ã対象ã«ãã¦ãã¾ãã
- MySQL 5.1ï¼InnoDB Plugin 1.0
- MySQL 5.4
ãããªããã¼ãã«ãä¸ãã¦ãã¾ã£ã¦ç³ã訳ããã¾ãããããã¼ã¸ã§ã³5.0以ä¸ãç´ ã®5.1ã§ã¯ä½¿ãã¾ããã®ã§ã注æãã ããã以éã®å®è¡ä¾ã¯ãã¹ã¦MySQL Community Server 5.1.35ï¼InnoDB Plugin 1.0.3ã§è¡ã£ããã®ã§ãã
information_schemaã®æ°ãããã¼ãã«
InnoDBã®ææ°ãã¼ã¸ã§ã³ã§ã¯ãinformation_schemaãã¼ã¿ãã¼ã¹ã«æ°ãããã¼ãã«ã7ã¤è¿½å ããã¦ãã¾ãã
mysql> show tables like 'INNO%'; +--------------------------------------+ | Tables_in_information_schema (INNO%) | +--------------------------------------+ | INNODB_CMP_RESET | | INNODB_TRX | | INNODB_CMPMEM_RESET | | INNODB_LOCK_WAITS | | INNODB_CMPMEM | | INNODB_CMP | | INNODB_LOCKS | +--------------------------------------+ 7 rows in set (0.01 sec)
ãããåãããã¨æãã¾ããããããã®ãã¼ãã«ããããã¯ç¶æ ãåå¾ãããã¨ãã§ããã¨ããããã§ããä»åã¯ãã®ãã¡3ã¤ã使ãã¾ããã¾ãã¯INNODB_TRXãã確èªãã¦ããã¾ãããã
mysql> desc INNODB_TRX; +-----------------------+---------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------------------+-------+ | trx_id | varchar(18) | NO | | | | | trx_state | varchar(13) | NO | | | | | trx_started | datetime | NO | | 0000-00-00 00:00:00 | | | trx_requested_lock_id | varchar(81) | YES | | NULL | | | trx_wait_started | datetime | YES | | NULL | | | trx_weight | bigint(21) unsigned | NO | | 0 | | | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | | trx_query | varchar(1024) | YES | | NULL | | +-----------------------+---------------------+------+-----+---------------------+-------+ 8 rows in set (0.00 sec) mysql> select * from INNODB_TRX order by trx_id\G *************************** 1. row *************************** trx_id: F09 trx_state: RUNNING trx_started: 2009-06-17 23:03:18 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 8 trx_query: NULL *************************** 2. row *************************** trx_id: F0A trx_state: LOCK WAIT trx_started: 2009-06-17 23:03:21 trx_requested_lock_id: F0A:3:3:9 trx_wait_started: 2009-06-17 23:03:21 trx_weight: 2 trx_mysql_thread_id: 9 trx_query: update emp set sal = sal + 200 where empno = 7788 2 rows in set (0.00 sec)
INNODB_TRXã¯ç¾å¨å®è¡ä¸ã®ãã©ã³ã¶ã¯ã·ã§ã³ã表示ãããã¼ãã«ã§ããInnoDBãå
é¨ã§æã£ã¦ãããã©ã³ã¶ã¯ã·ã§ã³ID(trx_id)ããã©ã³ã¶ã¯ã·ã§ã³ã®éå§æå»(trx_started)ãå®è¡ä¸ã®SQLãããã°ãã®SQLæ(trx_query)ãåºåããã¾ããInnoDBã®ãã©ã³ã¶ã¯ã·ã§ã³IDã¨show processlistã§è¡¨ç¤ºãããã¹ã¬ããID(trx_mysql_thread_id)ã¨ã®å¯¾å¿ã¥ããã§ããã¨ããããã¤ã³ãã§ãã
次ã¯INNODB_LOCKSã§ãã
mysql> desc INNODB_LOCKS; +-------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+-------+ | lock_id | varchar(81) | NO | | | | | lock_trx_id | varchar(18) | NO | | | | | lock_mode | varchar(32) | NO | | | | | lock_type | varchar(32) | NO | | | | | lock_table | varchar(1024) | NO | | | | | lock_index | varchar(1024) | YES | | NULL | | | lock_space | bigint(21) unsigned | YES | | NULL | | | lock_page | bigint(21) unsigned | YES | | NULL | | | lock_rec | bigint(21) unsigned | YES | | NULL | | | lock_data | varchar(8192) | YES | | NULL | | +-------------+---------------------+------+-----+---------+-------+ 10 rows in set (0.01 sec) mysql> select * from INNODB_LOCKS order by lock_id\G *************************** 1. row *************************** lock_id: F09:3:3:9 lock_trx_id: F09 lock_mode: X lock_type: RECORD lock_table: `scott`.`emp` lock_index: `PRIMARY` lock_space: 3 lock_page: 3 lock_rec: 9 lock_data: 0x9E6C *************************** 2. row *************************** lock_id: F0A:3:3:9 lock_trx_id: F0A lock_mode: X lock_type: RECORD lock_table: `scott`.`emp` lock_index: `PRIMARY` lock_space: 3 lock_page: 3 lock_rec: 9 lock_data: 0x9E6C 2 rows in set (0.00 sec)
INNODB_LOCKSã¯ããã¯ç«¶åãèµ·ããã¦ãããã©ã³ã¶ã¯ã·ã§ã³ã®æ
å ±ã表示ãããã¼ãã«ã§ããå¾
ããã¦ããæ¹ã¨å¾
ãããã¦ããæ¹ã®ä¸¡æ¹ãåºåããã¾ããä¸æ¹ãããã¯ãåå¾ãã¦ãã¦ãä»ã®ã»ãã·ã§ã³ã¨ç«¶åãã¦ããªããã©ã³ã¶ã¯ã·ã§ã³ã¯åºåããã¾ãããããã§ã¯ãã©ã®ãã¼ãã«(lock_table)ã®ã©ã®ã¤ã³ããã¯ã¹(lock_index)ãã¤ããã§ããããåããããã«ãªã£ã¦ããã¨ããããã¤ã³ãã§ãã
3ã¤ç®ã¯ãINNODB_LOCK_WAITSã§ãã
mysql> desc INNODB_LOCK_WAITS; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | requesting_trx_id | varchar(18) | NO | | | | | requested_lock_id | varchar(81) | NO | | | | | blocking_trx_id | varchar(18) | NO | | | | | blocking_lock_id | varchar(81) | NO | | | | +-------------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select * from INNODB_LOCK_WAITS\G *************************** 1. row *************************** requesting_trx_id: F0A requested_lock_id: F0A:3:3:9 blocking_trx_id: F09 blocking_lock_id: F09:3:3:9 1 row in set (0.01 sec)
INNODB_LOCK_WAITSã¯ã©ã®ãã©ã³ã¶ã¯ã·ã§ã³ãã©ã®ãã©ã³ã¶ã¯ã·ã§ã³ãå¾ ããã¦ããã®ããåºåãããã¼ãã«ã§ããblockingãå¾ ããã¦ããæ¹ãrequestingãå¾ ãããã¦ããæ¹ã«ãªãã¾ãã
ããã¯ç«¶åã表示ããSQL
ãã®ããã«ãInnoDBã®ææ°ãã¼ã¸ã§ã³ã§ã¯ããã¯ç«¶åãåºåããããã®é¨åãååã«æã£ã¦ãã¾ãããã¨ã¯è§£æã«å¿ è¦ãªæ å ±ãã¤ãªãã¦åºãã¦ãããã°ããã¨ããããã§ããä¾ãã°ä»¥ä¸ã®ãããªSQLã«ãªãã§ãããã
select t_b.trx_mysql_thread_id blocking_id, t_w.trx_mysql_thread_id requesting_id, p_b.HOST blocking_host, p_w.HOST requesting_host, l.lock_table lock_table, l.lock_index lock_index, l.lock_mode lock_mode, p_w.TIME seconds, p_b.INFO blocking_info, p_w.INFO requesting_info from information_schema.INNODB_LOCK_WAITS w, information_schema.INNODB_LOCKS l, information_schema.INNODB_TRX t_b, information_schema.INNODB_TRX t_w, information_schema.PROCESSLIST p_b, information_schema.PROCESSLIST p_w where w.blocking_lock_id = l.lock_id and w.blocking_trx_id = t_b.trx_id and w.requesting_trx_id = t_w.trx_id and t_b.trx_mysql_thread_id = p_b.ID and t_w.trx_mysql_thread_id = p_w.ID order by requesting_id, blocking_id \G
å®è¡çµæ(横)ã§ãã
+-------------+---------------+-----------------+-----------------+---------------+------------+-----------+---------+---------------+---------------------------------------------------+ | blocking_id | requesting_id | blocking_host | requesting_host | lock_table | lock_index | lock_mode | seconds | blocking_info | requesting_info | +-------------+---------------+-----------------+-----------------+---------------+------------+-----------+---------+---------------+---------------------------------------------------+ | 8 | 9 | localhost:37001 | localhost:37002 | `scott`.`emp` | `PRIMARY` | X | 72 | NULL | update emp set sal = sal + 200 where empno = 7788 | +-------------+---------------+-----------------+-----------------+---------------+------------+-----------+---------+---------------+---------------------------------------------------+ 1 row in set (0.01 sec)
å®è¡çµæ(縦)ã§ãã
*************************** 1. row *************************** blocking_id: 8 requesting_id: 9 blocking_host: localhost:37001 requesting_host: localhost:37002 lock_table: `scott`.`emp` lock_index: `PRIMARY` lock_mode: X seconds: 86 blocking_info: NULL requesting_info: update emp set sal = sal + 200 where empno = 7788 1 row in set (0.00 sec)
ããããã
- 8çªã®ã¹ã¬ããã9çªã®ã¹ã¬ããã86ç§éå¾ ããã¦ãã
- 8çªã®ã¹ã¬ããã¯empãã¼ãã«ã®ä¸»ãã¼ãæä»ããã¯(X)ãã¦ãã
- 8çªã®ã¹ã¬ããã¯ç¾å¨ã¢ã¤ãã«ç¶æ ã§ãã
- 9çªã®ã¹ã¬ããã¯empno = 7788ã®ã¬ã³ã¼ããæ´æ°ãããã¨ãã¦ãã
ã¨ãã£ãæ
å ±ãèªã¿åããã¨ãã§ãã¾ããããã¯Oracle Databaseã¨éè²ã®ãªãå
容ã«ãªã£ã¦ãã(åèï¼ããã¯ãã¤ã¶ãï¼ æåã«çãã¹ãåå ï¼1/3ï¼ â ï¼ IT)ãã»ã¼å®ç§ã¨ãã£ã¦ããã¬ãã«ã§ãã
ãããããä»åã®ä¾ã®ããã«ãã¼ã¿ãã¼ã¹ã«TCP/IPçµç±ã§æ¥ç¶ãã¦ããå ´åã¯ãTCPã®ãã¼ãçªå·ããã¯ã©ã¤ã¢ã³ãããã»ã¹ãç¹å®ãããã¨ãã§ãã¾ãã
# netstat -np | grep 37001 tcp 0 0 127.0.0.1:37001 127.0.0.1:3306 ESTABLISHED 6105/mysql tcp 0 0 127.0.0.1:3306 127.0.0.1:37001 ESTABLISHED 6000/mysqld
ãã®ä¾ã§ã¯8çªã®ã¯ã©ã¤ã¢ã³ãã¯mysqlã³ãã³ãã©ã¤ã³ãã¼ã«ã§ãããããã»ã¹IDã¯6105çªã§ãããã¨ãåããã¾ããããã¾ã§åããã°ãã¨ã¯è©²å½ã®ããã»ã¹ãkillããã¨ãã£ãæ«å®å¯¾å¦ããããã°ã©ã ãä¿®æ£ããã¨ãã£ãæ¬æ ¼å¯¾å¦ãã¨ããã¨ãã§ãã¾ãã
ææ
InnoDBã¯ãã¯ãYet Another Oracle Databaseã¨ãã¦ã¨ããã人ãå¤ãããã®ã¨ãæ©è½é¢ã§Oracle Databaseã«ãã£ã¦InnoDBã«ãªããã®ã¯ä½ãã¨èããã¨ãä»åã®ãããªããã¯ã®å¯è¦åã¨ãããããã¯ã®ãã®ã³ã°ã常ã«ä¸ä½ã«æ¥ã¾ãã
2008å¹´ã«MySQL Conference & Expoã§InnoDBã®ä¼è°ã«åºå¸ããã®ã§ãããå¨ãã®åå è
ã¯æ¡ã®å®Oracleã¨ã³ã¸ãã¢ãã»ã¨ãã©ã§ããããã¿ãªå£ã
ã«ããã¯ãè¦ããããã«ããã¨è¦æãåºãã¦ãã¾ããããããããããMySQL 5.4ã§å®è£
ãããéã³ã¨ãªããããããMySQLãä¼æ¥åãã®å©ç¨ãé²ããã¨ã«ãªãã®ããããã¾ãããã©ãªã¼ã»ã¨ãªã½ã³ããã½ãæ²ããªããã°(^^;
2009/06/27追è¨
MySQL 5.4ã«ã¤ãã¦ã§ããã5.4.1ã®æç¹ã§ã¯ã¾ã ãããã®information schemaã®ãã¼ãã«ç¾¤ã¯å®è£ ããã¦ãã¾ããã®ã§ã注æãã ãããæ°æ©è½ã®ã¢ãã¦ã³ã¹ã«ã¤ãã¦ã¯MySQL Conference & Expo 2009ã®è³æInnoDB: Innovative Technologies for Performance and Data Protection (PDF)ããåç §ãã ããã