MySQL - ストレージエンジン

提供：MochiuWiki - SUSE, Electronic Circuit, PCB
ナビゲーションに移動 検索に移動

概要

ストレージエンジンとは、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で設定できるシステム変数を知りたい場合は、MySQLの公式ドキュメントを参照すること。

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で使用されるバッファのサイズ。
    大規模なログバッファにおいては、トランザクションおよびコミットの前にログをディスクに書き込むことなく大規模なトランザクションが実行できる。
    したがって、多数のレコードの更新、挿入、削除するトランザクションの場合、ログバッファを大きくするとディスクI/Oを節約できる。
    デフォルト値は、16[MB]である。

  • innodb_log_checksums
    redoログページのチェックサムを有効または無効を指定する。
    redoログページのCRC-32Cチェックサムアルゴリズムを有効にする。
    このシステム変数が無効な時は、redoログページのチェックサムフィールドの内容は無視される。

    redoログヘッダページおよびredoログチェックポイントページのチェックサムは無効化されない。
  • innodb_log_compressed_pages
    再圧縮ページのイメージをredoログに書き込むかどうかを指定する。
    圧縮されたデータが変更されると、再圧縮が発生する場合がある。
    このシステム変数は、リカバリ時に異なるバージョンのzlib圧縮アルゴリズムが使用された場合に発生する可能性がある破損を防ぐため、デフォルトで有効になっている。
    zlibのバージョンが変更されないことが確実な場合は、このシステム変数を無効にすることにより、圧縮データを変更するワークロードのredoログ生成を減らすことができる。

    このシステム変数の有効化または無効化の影響を測定するには、同じワークロードで両方の設定のredoログ生成を比較する。
    redoログ生成の測定オプションには、SHOW ENGINE INNODB STATUS文の結果において、
    LOGカラムでのLog sequence number (LSN) の監視、または、redoログファイルに書き込まれたバイト数のInnodb_os_log_writtenステータスの監視が含まれる。

  • innodb_log_file_size
    InnoDBログファイルのサイズ。
    サイズを増加させることにより、ページのフラッシュを遅延させることができ、更新時のスループットが上がる。
    デフォルトは、50[MB]である。

  • innodb_log_files_in_group
    ログファイルの数。
    ログファイルを結合したサイズ (innodb_log_file_size * innodb_log_files_in_group) は、最大で512[GB]にすることができる。
    デフォルト値(推奨値)は、2である。

  • 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]である。



「https://mochiuwiki.one/index.php?title=MySQL_-_ストレージエンジン&oldid=12022」から取得