innodb_
innodb_
今回は、innodb_について、
innodb_file_per_tableをONでできるようになること
テーブルをtruncateやdropするときにOSのディスク容量を開放できる
従来のibdata1ではdropやtruncateを利用してデータの削除を行っても、
また、
データの保存場所を変更することができる
CREATE TABLE ... DATA DIRECTORYという構文を使ってdatadir外部にテーブルのデータを保持することができます。こちらを利用することで、
例としてmysqlのhomeディレクトリ配下にtest_を作って、
$ pwd /home/mysql $ mkdir test_dir
上記のディレクトリにデータを保持するようにしてみます。
mysql > use d1 Database changed mysql [d1]> CREATE TABLE t1(id int,message varchar(256)) DATA DIRECTORY = '/home/mysql/test_dir'; Query OK, 0 rows affected (0.64 sec) mysql [d1]> show create table t1; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `message` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin DATA DIRECTORY='/home/mysql/test_dir/' | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.06 sec)
作成されたテーブルとして、
$ cd <datadir配下/database名> $ ls | grep t1 t1.frm t1.isl $ cat t1.isl /home/mysql/test_dir/d1/t1.ibd
このislファイルにはidbファイルのパスが記載されています。
ibdファイルがあるか確認してみましょう。
$ ls /home/mysql/test_dir/d1/ t1.ibd
たしかにt1.
OPTIMIZE TABLE
OPTIMIZE TABLE構文を利用することで、
Transportable Tablespaceを利用して他のDBにテーブルをコピーする
トランスポータブルテーブルスペースという機能を使って、
まず、
mysql [d1]> use d1 Database changed mysql [d1]> CREATE TABLE t1(id INT) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql [d1]> INSERT INTO t1 VALUES(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0
続いて別のサーバのデータベースd2にd1と同じテーブル構造のテーブルを作成し、
mysql2 [d2]> use d2 Database changed mysql2 [d2]> CREATE TABLE t1(id INT) ENGINE=InnoDB; Query OK, 0 rows affected (0.29 sec) mysql2 [d2]> ALTER TABLE t1 DISCARD TABLESPACE; Query OK, 0 rows affected (0.06 sec)
再びd1データベースに戻り、
mysql [d1]> FLUSH TABLES t1 FOR EXPORT; Query OK, 0 rows affected (0.00 sec)
エクスポートされたデータを確認し、
$ cd /data/mysql1/d1/
$ ls -alt
合計 224
-rw-r----- 1 mysql mysql 60 9月 25 20:36 2017 db.opt
-rw-r----- 1 mysql mysql 364 9月 25 21:01 2017 t1.cfg
-rw-r----- 1 mysql mysql 8556 9月 25 20:51 2017 t1.frm
-rw-r----- 1 mysql mysql 98304 9月 25 20:57 2017 t1.ibd
$ scp -p t1.{cfg,ibd} <対象サーバー>:/data/mysql2/d2/
続いて、
mysql [d1]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
最後にd2データベースで取得したデータをインポートします。
mysql2 [d2]> ALTER TABLE t1 IMPORT TABLESPACE; Query OK, 0 rows affected (0.15 sec) mysql2 [d2]> SELECT * FROM t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)
トランスポータブルテーブルスペースを利用してデータコピーすることができました。
ただし、
innodb_file_per_tableをONにした時のデメリット
innodb_
- ファイルディスクリプタの使用量が増える
- dropテーブルやtruncateテーブルを実行した場合、
バッファプールのサイズが大きいとロックがかかってしまう
とくに2番目は、
まとめ
今回は、