概要
関わる機会があって必要だったのでメモ。
ただしこの操作については公式ドキュメントで、推奨していない操作であるため、本当はするべきではない。
mysql データベース内の MySQL システムテーブルを MyISAM から InnoDB テーブルに変換しないでください。これはサポートされていない操作です。これを行うと、mysql_install_db プログラムを使用してバックアップから古いシステムテーブルをリストアするか、再生成するまで、MySQL は再起動されません。
またこれを行う際にログをNFSマウントを使って共有するのはよくないことらしい。
NFS ボリューム上でデータファイルやログファイルが使用されるように InnoDB を構成することは、適切ではありません。それ以外の場合は、ファイルがほかのプロセスによってロックされ、MySQL で使用できなくなる可能性があります。
MyISAM の主な特徴
- トランザクション非対応
- クラッシュセーフではない
- 更新と参照が入り乱れた場合の同時実行性能がよくない
- テーブルが大きく（数億行とか）なるとINSERTの性能が劣化する
- ロックの粒度がテーブル単位となる
InnoDBの特徴
- トランザクション対応
- クラッシュセーフ
- ロックの粒度がレコード単位でできる
チェック項目
実装的な問題
- INSERT IGNORE INTO ... のクエリを使用していないか
- INSERT INTO ... ON DUPLICATE KEY UPDATE ... のクエリを使用していないか
- LOAD DATA ... IGNORE INTO ... のクエリを使用していないか
- 行を削除するパターンがないか
- AUTO_INCREMENT の順番に依存している実装がないか
- Primary Key がかぶっていた時に、被らないようにナンバリングするAUTO_INCREMENTはないか
- クエリが失敗するしている箇所があれば、移行後デッドロックの危険性がないか
- 移行中、または移行後、 更新処理でMyISAMとInnoDBを混在する形になったりしないか
- サイズが大きなカラム（VARCHARなど）をPRIMARY KEYにしてしまうと、無駄なディスク領域を消費してしまうため、そうなっていないか
- ROW_FORMAT は設定されているか
- old_alter_table システム変数を ON になっているかいないか
インフラ的な問題
- 消費するディスクスペースが移行前の２〜３倍となっても問題ないか
- バックアップの方法は適切か → 「バックアップについて」を参照のこと
- データの移行の際の計画と検証は十分か → 「データ移行作業について」を参照のこと
- カラムのMAXが1000以内に収まっているか
- InnoDb のテーブルには、最大で 1017 個のカラムを含めることができます (MySQL 5.6.9 で、以前の 1000 個の制限から上昇されました)。
- テーブルには、最大で 64 個のセカンダリインデックスを含めることができます。
- InnoDB の内部的な最大キー長は 3500 バイトですが、MySQL 自体では 3072 バイトに制限されています。この制限は、複数カラムインデックス内の結合されたインデックスキーの長さに適用されます。
- 可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除き、行の最大長はデータベースページの半分より少し短くなります。つまり、デフォルトページサイズの 16K バイトでは、行の最大長が約 8000 バイトになります。MySQL インスタンスの作成時に innodb_page_size オプションを指定してページサイズを小さくすると、行の最大長は、8K バイトのページでは 4000 バイト、4K バイトのページでは 2000 バイトになります。LONGBLOB および LONGTEXT カラムは 4G バイト未満である必要があり、BLOB および TEXT カラムを含む行全体の長さは 4G バイト未満である必要があります。
- ALTER TABLE 実行時には、テーブルの容量として、現在のカラムの2倍の容量を一時的に取るため、容量に余裕があるかどうかの確認
- ALTER TABLE 実行時間の最悪コストを見積もり
MyISAM から InnoDB へ乗り換える際の挙動の違い
- Auto Increment の挙動が MyISAM と InnoDB で異なる
- データを削除した時のプライマリキー(AUTO_INCREMENT) の挙動の違い
Auto Incrementの挙動がMyISAMとInnoDBで異なる
Tritonn/MyISAMを利用しているシステムをInnoDB化する際，次の2点について確認が必要
以下に該当するクエリを利用している
INSERT IGNORE INTO ...
INSERT INTO ... ON DUPLICATE KEY UPDATE ...
LOAD DATA ... IGNORE INTO ...
- 行削除するケースがある
具体的にどのような挙動の違いがあるか
次のテーブルを利用して説明。
-- テーブルを作成
CREATE TABLE test (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
UNIQUE INDEX (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
IGNORE INTO文の動作の違い
INSERT IGNORE INTO ...を用いて説明。
なお，INSERT INTO ... ON DUPLICATE KEY UPDATE ...の挙動も同じ
-- 1つめのレコードを入れる
mysql> INSERT IGNORE INTO test (name) VALUES('トマト');
Query OK, 1 row affected (0.00 sec)
-- 1件目なので，1が返る
mysql> SELECT LAST_INSERT_ID()\G
*************************** 1. row ***************************
LAST_INSERT_ID(): 1
1 row in set (0.00 sec)
-- 重複のためスキップされるレコードを入れる
mysql> INSERT IGNORE INTO test (name) VALUES('トマト');
Query OK, 0 rows affected (0.00 sec)
-- 重複のため，1のまま
mysql> SELECT LAST_INSERT_ID()\G
*************************** 1. row ***************************
LAST_INSERT_ID(): 1
1 row in set (0.00 sec)
-- 重複とならないデータを追加
mysql> INSERT IGNORE INTO test (name) VALUES('トマト2');
Query OK, 1 row affected (0.00 sec)
-- MyISAMではidが2となるが，InnoDBでは3
mysql> SELECT LAST_INSERT_ID()\G
*************************** 1. row ***************************
LAST_INSERT_ID(): 3
1 row in set (0.00 sec)
-- InnoDBのテーブル内容は以下の通り
mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 1 | トマト |
| 3 | トマト2 |
+----+------------+
2 rows in set (0.00 sec)
データを削除した時のプライマリキー(AUTO_INCREMENT) の挙動の違い
InnoDB の場合， Auto Increment で取る値は保持されない。
MySQL を一度終了すると，実際に格納されている主キーの最大値+1に自動調整となる挙動。
-- まずは1件目のデータを挿入
mysql> INSERT INTO test (name) VALUES('トマト1');
Query OK, 1 row affected (0.02 sec)
-- 続けて2件目のデータを挿入
mysql> INSERT INTO test (name) VALUES('トマト2');
Query OK, 1 row affected (0.02 sec)
-- 期待通り，idは2となる
mysql> SELECT LAST_INSERT_ID()\G
*************************** 1. row ***************************
LAST_INSERT_ID(): 2
1 row in set (0.00 sec)
-- idが2のデータを削除
mysql> DELETE FROM test WHERE name IN('トマト2');
Query OK, 1 row affected (0.03 sec)
-- 続けて3件目のデータを挿入
mysql> INSERT INTO test (name) VALUES('トマト3');
Query OK, 1 row affected (0.03 sec)
-- 期待通り，idは3となる
mysql> SELECT LAST_INSERT_ID()\G
*************************** 1. row ***************************
LAST_INSERT_ID(): 3
1 row in set (0.00 sec)
-- idが3のデータを削除
mysql> DELETE FROM test WHERE name IN('トマト3');
Query OK, 1 row affected (0.02 sec)
-- ここで，MySQLを再起動し，再度接続後に以下の行を挿入
mysql> INSERT INTO test (name) VALUES('トマト4');
Query OK, 1 row affected (0.03 sec)
-- 再起動により，MyISAMでは4となる一方，InnoDBでは2となる
mysql> SELECT LAST_INSERT_ID()\G
*************************** 1. row ***************************
LAST_INSERT_ID(): 2
1 row in set (0.00 sec)
-- InnoDBのテーブル内容は以下の通り
mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 1 | トマト1 |
| 2 | トマト4 |
+----+------------+
2 rows in set (0.01 sec)
MySQLリファレンスマニュアルのAUTO_INCREMENT カラムが InnoDB 内でどのように機能するか解説されているとのこと。
その他，MyISAMとInnoDBの違いを8つの角度から解説している
漢(オトコ)のコンピュータ道:MyISAMからInnoDBへ切り替えるときの注意点も参考となった。
バックアップについて
速度の変化が激しいので、問題がないか事前にチェックが必要となる。
MyISAM
- mysqlhotcopyコマンドを使って高速にバックアップをとることができる
InnoDB
- MyISAMと同様の方法は取れない
- InnoDB Hot Backupは高速だが有料
- mysqldumpは時間が掛かる
- レプリケーションを用いるとマスターに負荷をかけずにバックアップをとることができるが、サーバーの台数が増える。
データ移行作業について
- アプリケーションのロジックを変更しなければならないので、ストレージエンジンの変更だけでなくアプリケーションの入れ替え作業も同時に実施の場合が多い
- 移行作業はサービス停止が前提となる
- レプリケーションをうまく使えばダウンタイムを最小化することが可能
- 異なるストレージエンジン間でのレプリケーションには制限があるので、事前にしっかりとテストをしておく必要があるだろう
- RBRを利用すれば多少制限はマシになる
- 移行そのものは「ALTER TABLE [table_name] ENGINE=InnoDB」で行うと良い
ROW_FORMAT の設定について
- ROW_FORMAT=DYNAMICまたはCOMPRESSEDを指定することで、インデックスが20バイトに短縮される。
-
- を行うと、すべてTEXT型であっても400カラム以上の設定が可能。
DYNAMIC, COMPRESSEDを利用したテーブルは、innodb_file_format 構成オプションが Barracuda に設定されている場合にのみ作成可能。
それぞれの形式の概要
DYNAMIC：InnoDB PluginのBarracudaフォーマットを用いるデータ圧縮は行わない形式
COMPRESSED：DYNAMIC＋データ圧縮を行う形式でデータ圧縮を用いることでI/0のスループットを改善する。読み取りが多いものに関して威力を発揮する。
old_alter_table の設定について
old_alter_table についてのドキュメントはこちら
older_alter_table は、MySQLのALTER TABLE の元の使用である、ALTER TABLEの対象となるテーブルを一時テーブルとしてコピーし、
そのテーブルに対して、変更をかけたものを適用していくといった形式を使用するかのフラグ。ON/OFFで指定できる。
上記のような仕様があるため、一時的に２倍の容量を必要とする側面がある。
利用できる調査クエリ
MyISAMである既存のテーブルとそのテーブルのカラム数を算出
SELECT
`TABLES`.`TABLE_NAME`,
COUNT(`COLUMNS`.`COLUMN_NAME`)
FROM
TABLES
INNER JOIN COLUMNS ON `TABLES`.`TABLE_NAME` = `COLUMNS`.`TABLE_NAME`
WHERE
`TABLES`.`ENGINE` = 'MyISAM'
AND `TABLES`.`TABLE_SCHEMA` = '[MyISAMか調査したいDB名]'
AND `COLUMNS`.`TABLE_SCHEMA` = '[MyISAMか調査したいDB名]'
GROUP BY `TABLES`.`TABLE_NAME`
テーブルのカラムのサイズの合計が8Kを超えているテーブルを算出するクエリ
SELECT
TABLE_NAME,
SUM((
CASE
WHEN DATA_TYPE = 'tinyint' THEN 1
WHEN DATA_TYPE = 'smallint' THEN 2
WHEN DATA_TYPE = 'mediumint' THEN 3
WHEN DATA_TYPE = 'int' THEN 4
WHEN DATA_TYPE = 'bigint' THEN 8
WHEN DATA_TYPE = 'float' THEN 4
WHEN DATA_TYPE = 'double' THEN 8
WHEN DATA_TYPE = 'decimal' THEN 0
+ FLOOR((NUMERIC_PRECISION - NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_PRECISION - NUMERIC_SCALE) % 9 / 2)
+ FLOOR((NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_SCALE) % 9 / 2)
WHEN DATA_TYPE REGEXP '^(var)?char$' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE REGEXP '^(var)?binary$' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?text$' THEN 768
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?blob$' THEN 768
WHEN DATA_TYPE = 'datetime' THEN 8
WHEN DATA_TYPE = 'date' THEN 3
WHEN DATA_TYPE = 'time' THEN 3
WHEN DATA_TYPE = 'year' THEN 1
WHEN DATA_TYPE = 'timestamp' THEN 4
WHEN DATA_TYPE = 'enum' THEN IF((CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH) < 256, 1, 2)
ELSE NULL
END
)) AS SIZE_unit_Byte
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
-- テーブル名は確認したいテーブルに変更すること。
AND TABLE_NAME IN ([ここに確認したいテーブルの一覧を入れる。SHOW TABLESなどで取得してくると良い])
GROUP BY TABLE_NAME
HAVING SIZE_unit_Byte > 8000;
↑ このクエリについては、出力されたカラムサイズの合計が8Kを超えていないかを確認する。
テーブルのカラムと型などを一覧としてみたい場合のクエリ
SELECT
COLUMN_NAME,
COLUMN_TYPE,
SUM((
CASE
WHEN DATA_TYPE = 'tinyint' THEN 1
WHEN DATA_TYPE = 'smallint' THEN 2
WHEN DATA_TYPE = 'mediumint' THEN 3
WHEN DATA_TYPE = 'int' THEN 4
WHEN DATA_TYPE = 'bigint' THEN 8
WHEN DATA_TYPE = 'float' THEN 4
WHEN DATA_TYPE = 'double' THEN 8
WHEN DATA_TYPE = 'decimal' THEN 0
+ FLOOR((NUMERIC_PRECISION - NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_PRECISION - NUMERIC_SCALE) % 9 / 2)
+ FLOOR((NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_SCALE) % 9 / 2)
WHEN DATA_TYPE REGEXP '^(var)?char$' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE REGEXP '^(var)?binary$' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?text$' THEN 768
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?blob$' THEN 768
WHEN DATA_TYPE = 'datetime' THEN 8
WHEN DATA_TYPE = 'date' THEN 3
WHEN DATA_TYPE = 'time' THEN 3
WHEN DATA_TYPE = 'year' THEN 1
WHEN DATA_TYPE = 'timestamp' THEN 4
WHEN DATA_TYPE = 'enum' THEN IF((CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH) < 256, 1, 2)
ELSE NULL
END
)) AS SIZE_unit_Byte
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
-- テーブル名は確認したいテーブルに変更すること。
AND TABLE_NAME IN ([調査したいテーブル一覧])
AND
COLUMN_NAME IN ([出力したいカラムの一覧])
GROUP BY COLUMN_NAME
HAVING SIZE_unit_Byte;
テーブルエンジンがMyISAMのなかで、カラム一つのバイト数が100を超えているカラム一覧を算出
SELECT
`information_schema`.`COLUMNS`.`TABLE_NAME`,
`information_schema`.`COLUMNS`.`COLUMN_NAME`,
`information_schema`.`COLUMNS`.`COLUMN_TYPE`,
SUM((
CASE
WHEN DATA_TYPE = 'tinyint' THEN 1
WHEN DATA_TYPE = 'smallint' THEN 2
WHEN DATA_TYPE = 'mediumint' THEN 3
WHEN DATA_TYPE = 'int' THEN 4
WHEN DATA_TYPE = 'bigint' THEN 8
WHEN DATA_TYPE = 'float' THEN 4
WHEN DATA_TYPE = 'double' THEN 8
WHEN DATA_TYPE = 'decimal' THEN 0
+ FLOOR((NUMERIC_PRECISION - NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_PRECISION - NUMERIC_SCALE) % 9 / 2)
+ FLOOR((NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_SCALE) % 9 / 2)
WHEN DATA_TYPE REGEXP '^(var)?char$' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE REGEXP '^(var)?binary$' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?text$' THEN 768
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?blob$' THEN 768
WHEN DATA_TYPE = 'datetime' THEN 8
WHEN DATA_TYPE = 'date' THEN 3
WHEN DATA_TYPE = 'time' THEN 3
WHEN DATA_TYPE = 'year' THEN 1
WHEN DATA_TYPE = 'timestamp' THEN 4
WHEN DATA_TYPE = 'enum' THEN IF((CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH) < 256, 1, 2)
ELSE NULL
END
)) AS SIZE_unit_Byte
FROM `information_schema`.`COLUMNS`
INNER JOIN `information_schema`.`TABLES` ON `information_schema`.`TABLES`.`TABLE_NAME` = `information_schema`.`COLUMNS`.`TABLE_NAME`
WHERE `information_schema`.`TABLES`.`TABLE_SCHEMA` = DATABASE()
AND
`information_schema`.`TABLES`.`ENGINE` = 'MyISAM'
-- テーブル名は確認したいテーブルに変更すること。
AND `information_schema`.`TABLES`.`TABLE_NAME` IN ([調査したいテーブル一覧])
GROUP BY `information_schema`.`COLUMNS`.`COLUMN_NAME`
HAVING SIZE_unit_Byte > 100
ORDER BY SIZE_unit_Byte DESC;
カラムで100バイトを超えるようなものがだいたいオーバーする原因となっていることが多いのでは、と思っているので、
参考までに設定しています。そうでなければ、適宜書き換えて利用。
ROW_FORMATの設定をDYNAMICにした後にバイト数がどうなるかの概算を算出するクエリ
-- このクエリで該当するテーブルがある際には、テーブル構成を見直す必要がある。
SELECT
TABLE_NAME,
SUM(
CASE
WHEN DATA_TYPE = 'tinyint' THEN 1
WHEN DATA_TYPE = 'smallint' THEN 2
WHEN DATA_TYPE = 'mediumint' THEN 3
WHEN DATA_TYPE = 'int' THEN 4
WHEN DATA_TYPE = 'bigint' THEN 8
WHEN DATA_TYPE = 'float' THEN 4
WHEN DATA_TYPE = 'double' THEN 8
WHEN DATA_TYPE = 'decimal' THEN 0
+ FLOOR((NUMERIC_PRECISION - NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_PRECISION - NUMERIC_SCALE) % 9 / 2)
+ FLOOR((NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_SCALE) % 9 / 2)
WHEN DATA_TYPE REGEXP '^(var)?char$' THEN (
CASE WHEN CHARACTER_OCTET_LENGTH > 768 THEN 20 ELSE CHARACTER_OCTET_LENGTH END
)
WHEN DATA_TYPE REGEXP '^(var)?binary$' THEN (
CASE WHEN CHARACTER_OCTET_LENGTH > 768 THEN 20 ELSE CHARACTER_OCTET_LENGTH END
)
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?text$' THEN 20
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?blob$' THEN 20
WHEN DATA_TYPE = 'datetime' THEN 8
WHEN DATA_TYPE = 'date' THEN 3
WHEN DATA_TYPE = 'time' THEN 3
WHEN DATA_TYPE = 'year' THEN 1
WHEN DATA_TYPE = 'timestamp' THEN 4
WHEN DATA_TYPE = 'enum' THEN IF((CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH) < 256, 1, 2)
ELSE NULL
END
)
AS SIZE_unit_Byte
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
-- テーブル名は確認したいテーブルに変更すること。
AND TABLE_NAME IN ([調査したテーブル名一覧])
GROUP BY TABLE_NAME
HAVING SIZE_unit_Byte > 8000
ORDER BY SIZE_unit_Byte DESC;
リリース時の注意事項
- ALTER TABLE は一時的にテーブルをコピーするため、容量を2倍取ること → 容量問題ないかコマンドで確認する
- ALTER TABLE の実行中のINSERTは問題なく動作するか。
参考
- 漢(オトコ)のコンピュータ道:MyISAMからInnoDBへ切り替えるときの注意点
- MySQLリファレンスマニュアルのAUTO_INCREMENT カラムが InnoDB 内でどのように機能するか
- 実践ハイパフォーマンスMySQL(第1章)
- GMOメディア エンジニアブログ - MySQLのInnoDBでもPRIMARY KEYの2カラム目以降にAUTO_INCREMENTを使いたい → これがMyISAMではできるが、InnoDBではできない。(というかこれ設計ミスなのでは・・・)
- MySQL InnoDBで8KBの壁に阻まれエラー
- InnoDBの8KBの壁にぶち当たったら。
- MySQLの8KBの壁でエラーが出た時の対処法
- MySQL 用語集 - 公式ドキュメントより
- MySQLのALTERで’The table ‘#sql-xxxx’ is full’と言われたら
- 14.6.4 MyISAM から InnoDB へのテーブルの変換 - MySQL 公式ドキュメントより
- D.10.4 テーブルカラム数と行サイズの制限 - 公式ドキュメント(テーブルごとのバイト数の限界について)
- MySQLでMyISAMからInnoDBに乗り換える際に知らないとハマる、怖い話
- MySQLのInnoDBでもPRIMARY KEYの2カラム目以降にAUTO_INCREMENTを使いたい