ストレージエンジン
どのようにデータを格納するか、トランザクションを処理するかなどはMySQL独自の「ストレージエンジン」で実装している
mysql> show engines\G # 利用可能なストレージエンジンする +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | SPHINX | YES | Sphinx storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | CSV | YES | CSV storage engine | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
主な機能
- InnoDBのデータサイズの上限は64TB
- トランザクション対応
- クラッシュリカバリ
- データとインデックスを表領域に格納
- 外部キーのサポート
アーキテクチャ
InnoDBはMVCC (Multi Version Concurrency Control)でトランザクションを実装
トランザクション開始時にディスク上のデータをメモリ上の「バッファプール」にコピーし、以降の操作をメモリ上で行う
コミットが実行されるとディスク上のログファイルに書き出される
このログファイルをトランザクションログ(InnoDBログ、WAL(Write Ahead Log)とも)と呼び、このログでクラッシュリカバリを実現する
トランザクションログへの書き込みはシーケンシャルであり、シーク時間がなくとても速い
コミット時に発生するディスクへの書き込みはログのみで、データそのものはチェックポイントと呼ばれるタイミングで別途テーブルスペースに書き出される
トランザクションログとテーブルスペースを合わせて、すべてのデータとなることに注意
mysql> show variables like 'innodb_max_dirty_pages_pct'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_max_dirty_pages_pct | 75 | # ダーティページが75%を越えると、強制的にチェックポイント +----------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_doublewrite'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_doublewrite | ON | # テーブルスペース反映直前にこちらに書き出してから、反映する。ファイルシステムでいうジャーナル +--------------------+-------+ 1 row in set (0.00 sec)
ファイル構成
mysql> create table novel(id int) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> create table magazine(id int) engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)
root@akat:/var/lib/mysql/books# ls /var/lib/mysql/
books
ib_logfile0
ib_logfile1
ibdata1
root@akat:/var/lib/mysql/books# ls
db.opt magazine.frm novel.MYD novel.MYI novel.frm
# table_name.frm テーブル定義の保存
# ※テーブル構造のデータは、テーブル名.frmファイル内だけでなく、テーブルスペース内にも格納される
# table_name.MYD テーブルデータの保存
# table_name.MYI インデックスの保存
# ibdata1 テーブルデータやインデックスの保存(テーブルスペースと呼ばれる)
# ib_logfile0,ib_logfile1 トランザクションログファイル
# データを保存するファイルはデフォルトの設定では1つとなっており、10MBのサイズデータが格納されて容量が足りなくなると自動で拡張されるように設定されている
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_log_file_%';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
+---------------------------+---------+
2 rows in set (0.00 sec)
mysql> show table status from books like 'magazine'\G
*************************** 1. row ***************************
Name: magazine
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 11534336
Auto_increment: NULL
Create_time: 2013-08-07 22:01:53
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
# デフォルト確認方法
mysql> select @@storage_engine;
+------------------+
| @@storage_engine |
+------------------+
| InnoDB |
+------------------+
1 row in set (0.00 sec)
トランザクションログ(InnoDBログとも)について、デフォルトの設定では1つのファイルサイズが10MB、ファイル数は2つでローテーション
つまり、複数のテーブルのデータとインデックスを1つのこの2つのファイルに格納っする
このファイルは、あらかじめ容量を設定しておくことや、容量を自動拡張するautoextendオプションを設定可能
複数ファイルを指定した場合は、autoextendオプションが使えるのはリストの最後に書かれたファイルのみ
また、innodb-file-per-tableオプションを使うと、テーブルごとにデータとインデックスを格納する表領域ファイルを作成することができる
※InnoDB はサーバを再起動する時にファイル(トランザクションログ)を作成し、初期化している
既存のトランザクションログが設定よりも大きかったりすれば、InnoDBにアクセスするとエラーとなる
クラスタードインデックス
InnoDBテーブルを作成すると、クラスタードインデックスを作成する
全ての InnoDB テーブルは、行のデータが格納されている clustered index と呼ばれる特別なインデックスを持っている
通常主キーに設定されるが、主キーがない場合は、InnoDBが自動的に6バイトのローIDと呼ぶフィールドをレコードに追加し、このローIDを用いてクラスタードインデックスを構成する
セカンダリインデックス(通常のインデックス)は、インデックス検索結果から実テーブルにアクセスする
インデックス用のページがあり、そこにキーとロケータが格納されている。ロケータを頼りに実テーブルにアクセスする
このインデックス用のページ(リーフと呼ばれる)と実テーブルアクセスは、アクセス頻度からキャッシュされにくくメモリではなくディスクアクセスとなり低速となる
しかしクラスタードインデックスはインデックス検索結果(リーフ)がテーブルのレコードとなっているため
実テーブルにアクセスする必要がなくなり、ランダムリードが少なくなるため、高速に動作する
またディスクにアクセスするときは、1エクステント(16KB(1ブロックあたりの容量) * 64ブロック)を先読みしなるべくディスクアクセスを減らす仕組みになっている
(MyISAMはread_buffer_sizeで指定した文を先読みする)
デメリット
- インデックスのサイズが大きくなる
- データ追加時のオーバヘッドが大きい
- セカンダリインデックスを用いた検索が遅くなる
(クラスタードインデックスを用いる場合、データはすべて主キーに格納された特殊な構造となるため、セカンダリインデックスも特殊な構造となる)
mysql> create table personal(id int, name varchar(20));
Query OK, 0 rows affected (0.14 sec)
mysql> desc personal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE personal ADD PRIMARY KEY(id);
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show index from personal\G
*************************** 1. row ***************************
Table: personal
Non_unique: 0
Key_name: PRIMARY # MySQLのインデックスの種類はPRIMARY・INDEX・UNIQUE・FULLTEXTだけであり、PRIMARYがあればそれをクラスタードインデックスとして利用し、なければUNIQUE(ただしNOT NULLであること)を利用し、それもなければ内部に保持する
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
クラッシュリカバリについて
MySQLは再起動時にib_logfileからコミットされたトランザクションを読み出し、それが実データ（デフォルトだと ibdata）に反映されていなければ反映させるという処理が走る
innodb_flush_log_at_trx_commitの値により、1秒毎、またはコミット毎にトランザクションをログファイルにフラッシュする
0の場合、コミットされたトランザクションは1秒毎にログファイルにフラッシュされ、コミット毎にはフラッシュされない
1の場合はコミットされたクエリは都度ログファイルにフラッシュされるので、失われるデータは理論上0となる
SHOW ENGINE INNODB STATUS
mysql> SHOW ENGINE INNODB STATUS\G # MySQL5.5では"SHOW INNODB STATUS"が廃止されている
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
140803 3:54:56 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 26526 1_second, 26526 sleeps, 2349 10_second, 3059 background, 3059 flush
srv_master_thread log flush and writes: 171015
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2961, signal count 2958
Mutex spin waits 1188, rounds 35640, OS waits 1164
RW-shared spins 1778, rounds 53313, OS waits 1772
RW-excl spins 6, rounds 750, OS waits 25
Spin rounds per wait: 30.00 mutex, 29.98 RW-shared, 125.00 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
4969 OS file reads, 77138 OS file writes, 35149 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 553229, node heap has 17 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 244707017 # ログバッファへの更新が行われたトータルのバイト数
Log flushed up to 244707017 # トランザクションログへの書き込みが行われたバイト数
Last checkpoint at 244707017 # 最後にチェックポイントが行われたバイト数
# トランザクションログにはチェックポイントが完了していない全てのデータを記録する必要があるため、[Log sequence number] - [Last checkpoint] の値が、[innodb_log_file_size] * [innodb_log_files_in_group]の値を超えている場合、innodb_log_file_sizeを調整したほうがよい
Max checkpoint age 7782360
Checkpoint age target 7539162
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
27132 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 275513344; in additional pool allocated 0
Total memory allocated by read views 104
Internal hash tables (constant factor + variable factor)
Adaptive hash index 4708576 (4425832 + 282744)
Page hash 277432 (buffer pool 0 only)
Dictionary cache 1243539 (1107952 + 135587)
File system 88720 (82672 + 6048)
Lock system 665312 (664936 + 376)
Recovery system 0 (0 + 0)
Dictionary memory allocated 135587
Buffer pool size 16383
Buffer pool size, bytes 268419072
Free buffers 10469
Database pages 5897
Old database pages 2156
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4946, created 951, written 48133
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5897, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
0 transactions active inside InnoDB
0 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 4D48A
Read view up limit trx id 4D48A
Read view low limit trx id 4D48A
Read view individually stored trx ids:
-----------------
Main thread process no. 19340, id 140199879227136, state: flushing log
Number of rows inserted 7915, updated 3271, deleted 17141, read 2925543
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 4D48A
Purge done for trx's n:o < 4D483 undo n:o < 0
History list length 1391
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 8631, OS thread handle 0x7f82fea98700, query id 169975 localhost root
SHOW ENGINE INNODB STATUS
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
参考URL
http://www.dbonline.jp/mysql/storage/index3.html
http://akio0911.net/archives/2601
http://enterprisezine.jp/dbonline/detail/3711
http://nippondanji.blogspot.jp/2009/01/innodb.html
http://nippondanji.blogspot.jp/2010/10/innodb.html
http://blog.livedoor.jp/sasata299/archives/51336006.html
http://qiita.com/sion_cojp/items/04a2aa76a1021fe77079
shutdownの際、innodb buffer pool内のログをflushするようGLOBAL変数を変更
mysql> set global innodb_fast_shutdown = 0;
# innodbの場合、テーブルスペースに反映されてない部分をshutdown時に書き込むことにより
# shutdown後のデータの整合性を高くする事が出来きます。