参考
環境
- MySQL5.6
- datadir=/var/lib/mysql
やってみる
データベース作成
mysql> create database testdb;
このタイミングでdatadir配下にデータベース名のディレクトリ(今回はtestdbディレクトリ)及びdb.optファイルというデータベースの設定ファイルが作成されました。
内容は以下の通り。
$cat testdb/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
テーブル作成
14.5.3 File-Per-Table モードの有効化および無効化
mysql>create table t1 ( id INT, dep INT, name CHAR(30));
このタイミングでt1.frm及びt1.idbというテーブル名を利用した2つのファイルが作成されます。
$ll testdb/
合計 112
-rw-rw---- 1 mysql mysql 61 4月 20 01:31 db.opt
-rw-rw---- 1 mysql mysql 8614 4月 20 01:39 t1.frm
-rw-rw---- 1 mysql mysql 98304 4月 20 01:39 t1.ibd
中身はcatで確認するとバイナリのようでした。
上記はそれぞれ何のかという説明の前に前段の情報を幾つか記載します。
ストレージエンジンについて
MySQLではテーブルごとにどのストレージエンジンを使うか設定することができます。
MySQL5.6ではデフォルトのストレージエンジンがInnoDBとなっています。
# サポートストレージエンジン及びデフォルトストレージエンジンの確認
mysql>show engines;
+--------------------+---------+------------------------------------------------
| Engine | Support | Comment
+--------------------+---------+------------------------------------------------
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| CSV | YES | CSV storage engine
| MyISAM | YES | MyISAM storage engine
| BLACKHOLE | YES | /dev/null storage engine (anything you write to
| MEMORY | YES | Hash based, stored in memory, useful for tempor
| PERFORMANCE_SCHEMA | YES | Performance Schema
| ARCHIVE | YES | Archive storage engine
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
| FEDERATED | NO | Federated MySQL storage engine
+--------------------+---------+-----------------------------------------------
# 作成されたテーブルのストレージエンジンを確認
mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`dep` int(11) DEFAULT NULL,
`name` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
ということで今回のテーブル作成時に作成されたファイル群はInnoDBを選択した時に作成されるファイルとなります。
他のストレージエンジンを指定した場合、作成されるファイルは違うので注意が必要です。
InnoDBのファイルの保存方法について
InnoDBのファイル保存方法は2通りがあります。
- 通常の保存方法
- innodb_file_per_table
一つ目の「通常の方法」では全てのInnoDB型テーブルのデータとインデックスが「テーブルスペース」に保存されます。
テーブルスペースはdatadir/ibdata1という形で作成され、UNDOログやInnoDB内部で使用されるシステム情報が書かれます。こちらの保存方法ではこのテーブルスペースに各テーブルとインデックスの情報が併せて記載されます。
テーブルスペース用のファイルは複数指定作成することもでき、テーブル情報が複数のテーブルスペース用ファイルにまたがる事もあるようです。
一方「innodb_file_per_table」では名前の通り、テーブルごとに「hoge.ibd」というファイル名でテーブルごとにデータとインデックス情報が保存されるようです。なお、「テーブルスペース」自体はこちらの保存方法でも必要です（UNDOログやInnoDB内部でのシステム情報を書くため)
また、REDOログというトランザクションに関する記憶を行うファイルとしてdatadir配下にib_logfile0、ib_logfile1というような形で追記されます。
InnoDBファイルの保存方法の確認
今回はidbファイルが存在するのでinnodb_file_per_table設定だと思うのですが、確認してみます。
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
確かに有効化されていました。
まとめ
色々書いてしまったのですが、まとめると状況としては以下です。
- デフォルトのストレージエンジンはInnoDBであり、テーブル作成時にはInnoDBで必要なファイル群が作成された
- InnoDBのファイルの保存方法は2種類あるが、今回は「innodb_file_pertable」となっている
- こちらの保存方法ではテーブルごとにデータ、インデックス情報を格納した「.ibd」ファイルを作成する
作成されたファイルは以下を意味します。
- .frmファイル->テーブル定義が追記されているファイル
- .ibdファイル->データおよびインデックスを保存するファイル(MyISAMストレージエンジンのデータを示すMYDファイル及びインデックスを示すMYIファイルが合わさったもの)
InnoDBの基本設定(テーブルスペース,REDOログ,メモリ割当)
先ほど出てきた、テーブルスペースとREDOログ及びメモリ割り当てについて記載。
指定はMySQLの設定ファイルmy.cnfにてできるようです。
テーブルスペース
デフォルトでは以下の設定となっているようです。
$mysqladmin -u root variables -p |grep innodb_data_file_path
Enter password:
| innodb_data_file_path | ibdata1:12M:autoextend
書式としては以下。
- ファイル名:サイズ[autoextend:max:最大サイズ]
上記よりデフォルトだと「ibdata1というファイル名で12Mバイトの自動拡張のファイルを用意」という意味となります。
実際に確認すると確かに12Mとなっていました。
$ll --block-size=M /var/lib/mysql/ |grep ibdata
-rw-rw---- 1 mysql mysql 12M 4月 20 01:39 ibdata1
自動拡張を示す「autoextend」が指定されるとテーブルスペースが足りなくなると一度に8Mバイトずつ増分されるようです。:maxを指定すると自動拡張時の最大サイズが決めれるようです。
また、;区切りで複数のテーブルスペースのファイルを指定することもできるのでディスク容量が少なくなった時にディクスを追加でマウントし、新しくパスを指定することもできるようです。
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/HDD1/a/ibdata1:1G;/HDD2/a/ibdata2:1G
REDOログファイル
こちらもまずはデフォルトを確認します。
$mysqladmin -u root variables -p |grep -e 'innodb_log_group_home_dir' -e 'innodb_log_files_in_group' -e 'innodb_log_file_size'
Enter password:
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./
それぞれの値は以下を示すようです。
- innodb_log_file_size->ログファイルのサイズ(byte)
- innodb_log_files_in_group->ログファイルの数
- innodb_log_group_home_dir->ログファイルを置くディレクトリ
実際のREDOログファイルはdatadir配下に配置されていますが、変更したい場合にはinnodb_log_group_home_dirを指定すれば良さそうです。
ログファイル数は2でログファイルサイズは50331648byte(48MByte)となっていました。
size = ARGV[0].to_i
puts size.to_s + 'B'
puts (size / 1024).to_s + 'KB'
puts (size / 1024 / 1024).to_s + 'MB'
puts (size / 1024 / 1024 / 1024).to_s + 'GB'
$ruby change_size.rb 50331648
50331648B
49152KB
48MB
0GB
実際のディレクトリも確認します。
$ll --block-size=M /var/lib/mysql/ |grep logfile
-rw-rw---- 1 mysql mysql 48M 4月 20 01:39 ib_logfile0
-rw-rw---- 1 mysql mysql 48M 4月 19 04:45 ib_logfile1
ファイル数が2で、ファイルサイズもそれぞれ48Mbyteとなっていることが確認できました。
メモリ割り当て
innodb_buffer_pool_sizeのチューニング：どれくらい割り当てる？
InnoDBで利用するデータとインデックスをメモリにキャッシュ可能なサイズを指定します。
例えばselct文をした時に結果がメモリに確保されていれば、ディスクIOが発生せず、高速です。メモリに確保されていなければディスクIOが発生し、メモリからの取得時と比較して遅くなってしまいます。
上記のような場合にどのぐらいの要領のメモリを割り当てるかを指定します。
デフォルト値をこちらも確認してみます。
$mysqladmin -u root -p variable |grep innodb_buffer_pool_size
Enter password:
| innodb_buffer_pool_size | 134217728
$ruby change_size.rb 134217728
134217728B
131072KB
128MB
0GB
現在は128MByteが割り当てられていますが、じゃあ現在の利用状況はどうなっているのかというのは別途コマンドで確認できます。
mysql>show engine innodb status\G
・・・
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 52164
Buffer pool size 8191
Free buffers 8010
Database pages 181
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 160, created 21, written 76
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: 181, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
・・・
テスト用に作ったDBで全く使っていないので、Free buffersも余っています。この値が少なくなっている場合には割り当てサイズを増やした方が良さそうです。