MySQL - ストレージエンジン

概要

ストレージエンジンとは、RDBMSにおいてデータやトランザクションの管理を行う機能のことである。
MySQLでは、テーブルごとにストレージエンジンを指定することができる。

• データフォーマットの定義
• データ永続化
• インデックス管理
• トランザクション管理
• ロックおよび排他的制御

主なストレージエンジン

ストレージエンジン	意味
InnoDB	デフォルトのストレージエンジン。 行ロック、トランザクション、クラッシュリカバリ等に対応している。
MyISAM	古いMySQLのデフォルトのストレージエンジン。 テーブルレベルのロックに対応している。
MEMORY	メモリ上にテーブルを配置する。 アクセス頻度の高いデータ向け。
Archive	データを自動的に圧縮する。 データの追加、参照のみ行うことができる。 主に、ログや監査向けで使用する。
NDB	MySQL Cluster構成にて使用される。
CSV	CSV形式でデータを保存および使用する。
Blackhole	/dev/nullのように書き込んだデータを破棄する。

ストレージエンジンの操作

利用可能なストレージエンジンの確認

 SHOW ENGINES;

ストレージエンジンを指定してテーブルの作成

 USE <データベース名>;
 
 CREATE TABLE <テーブル名> (
    <カラム名 1> <型名>
    <カラム名 2> <型名>
    <カラム名 3> <型名>
    -- ...略
 )
 ENGINE = <ストレージエンジン名>;

既存テーブルのストレージエンジン確認

 USE <データベース名>;
 
 SHOW TABLE STATUS
    where name = '<テーブル名>' \G;

既存テーブルのストレージエンジンの変更

 USE <データベース名>;
 
 ALTER TABLE <テーブル名> ENGINE = '<ストレージエンジン名>';
 
 -- 例: sample_tableテーブルのストレージエンジンをMEMORYに変更する
 ALTER TABLE sample_table ENGINE = 'MEMORY';

InnoDB

InnoDBのデータファイル

システム変数innodb_file_per_tableにより、テーブルごとにデータファイルを作成するかどうかを設定することができる。
テーブルごとにファイルを作成することにより、テーブルのDROP文、TRUNCATE文等の実行時において、ディスク使用量を減らすことができる。

 SHOW VARIABLES LIKE 'innodb_file_per_table';

システム変数innodb_file_per_tableがONの場合、データディレクトリ配下にテーブルごとの***.indファイルが作成される。
なお、デフォルトのデータディレクトリは/var/lib/mysql/mysqlディレクトリである。

InnoDBのトランザクションログ

データの更新は、以下に示すような流れで処理される。

1. トランザクションの実行直後に、InnoDBログバッファ (メモリ上の領域) に書き込む。
2. コミット時に、InnoDBログファイル（WAL : Write Ahead Log）、および、バッファプール (メモリ上のキャッシュ) にログバッファの内容を書き込む。
3. 任意のタイミングでバッファプールの内容をデータファイルへ反映する。

バッファプールの内容をデータファイルに反映する前にデータが消えた場合等に、InnoDBログファイルがリカバリに利用される。

I/Oの設定

SHOW VARIABLES LIKE 'innodb_io%';

• innodb_io_capacity

  バッファプールからのページのフラッシュや変更バッファからのデータのマージ等、InnoDBバックグラウンドタスクが実行可能なI/O操作の回数(IOPS)の上限である。

  ワークロードに対して低い値では不十分であることを証明した場合を除き、20000以上のような極端な値は使用しないこと。

  値が大きすぎる場合、早期フラッシュでI/O処理が無駄になることに注意する。

  デフォルト値は、200である。

• innodb_io_capacity_max

  InnoDBは、システム変数innodb_io_capacityで定義されているよりも高い速度のIOPSでフラッシュできる。

  InnoDBバックグラウンドタスクにより実行されるIOPSの最大数を定義する。

  デフォルト値は、2000である。

ログファイルの設定

 SHOW VARIABLES LIKE 'innodb_log%';

• innodb_log_buffer_size

  ログファイルの書き込み時において、InnoDBで使用されるバッファのサイズ。

• innodb_log_checksums

  
  

• innodb_log_compressed_pages

  
  

• innodb_log_file_size

  InnoDBログファイルのサイズ。

  サイズを増加させることにより、ページのフラッシュを遅延させることができ、更新時のスループットが上がる。

  デフォルトは、50[MB]である。

• innodb_log_files_in_group

  ログファイルの数。

• innodb_log_group_home_dir

  ログファイルのディレクトリパス。

• innodb_log_write_ahead_size

  Read on Write問題に関するオプション

ディスクのブロックサイズよりも小さなデータをログファイルの途中に書き込む時、
対象ブロックの読み取り後に書き戻すという非効率な処理を避けるため、ブロックサイズ分のみデータを補完して書き込みのみ行われるようにした。

バッファプールの設定

 SHOW VARIABLES LIKE 'innodb_buffer%';

• innodb_buffer_pool_chunk_size

  
  

• innodb_buffer_pool_dump_at_shutdown

  MySQLのシャットダウン時にバッファプールにキャッシュされるページを記録するかどうか。

• innodb_buffer_pool_dump_now

  
  

• innodb_buffer_pool_dump_pct

  
  

• innodb_buffer_pool_filename

  バッファプールのキャッシュを記録する場合のファイル名。

• innodb_buffer_pool_instances

  
  

• innodb_buffer_pool_load_abort

  
  

• innodb_buffer_pool_load_at_startup

  記録したバッファプールの内容をロードするかどうか。

• innodb_buffer_pool_load_now

  
  

• innodb_buffer_pool_size

  InnoDBバッファプールのサイズ

  使用可能なRAMの50[%]〜70[%]程度を指定する。

  デフォルトは、128[MB]である。