MySQL8.0からのパーティションについての確認
MySQL5.7からは、InnoDB Native Partitioningがサポートされるようになり、多くパーティションを利用している環境で、メモリーの利用率を大幅に下げる事が出来るようになっている。
MySQL8.0からは、Partition Storage Engineがディフォルトでロードされなくなるので注意を促す為に、MySQL5.7.17から”–disable-partition-engine-check”を設定して下さいというWarningがエラーログに出るようになっています。

yoku0825さんが速攻で確認して、ブログを書かれているので確認してみて下さい。

【InnoDB Native Partitioningに関して】
http://mysqlserverteam.com/innodb-native-partitioning-early-access/

【抜粋】

WL#6035: Add native partitioning support to InnoDB
https://dev.mysql.com/worklog/task/?id=6035

WL#8971: Deprecate and remove partitioning storage engine
https://dev.mysql.com/worklog/task/?id=8971

MySQL5.7からは、InnoDB Native Paritioningがサポートされている。
そして、MySQL5.7までは、partition Storage EngineもまだDefaultで含まれている。

MySQL5.7.17で確認すると以下のような感じ。


mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.17-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    -> PLUGIN_NAME as Name,
    -> PLUGIN_VERSION as Version,
    -> PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| InnoDB             | 5.7     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| BLACKHOLE          | 1.0     | DISABLED |
| partition          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| ARCHIVE            | 3.0     | DISABLED |
+--------------------+---------+----------+
11 rows in set (0.00 sec)

mysql> CREATE TABLE T_PARTITION (c1 int)
    -> PARTITION BY RANGE COLUMNS(c1) 
    -> (
    -> PARTITION p1 VALUES LESS THAN (5),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
    -> );

Query OK, 0 rows affected (0.45 sec)

mysql> select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION from information_schema.partitions where table_name = 'T_PARTITION';
+-------------+----------------+------------------+-----------------------+
| TABLE_NAME  | PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+-------------+----------------+------------------+-----------------------+
| T_PARTITION | p1             | RANGE COLUMNS    | 5                     |
| T_PARTITION | p2             | RANGE COLUMNS    | 10                    |
| T_PARTITION | p3             | RANGE COLUMNS    | MAXVALUE              |
+-------------+----------------+------------------+-----------------------+
3 rows in set (0.00 sec)

mysql> 

MySQL5.7までは、まだメタデータのFRMファイルも作成されている。

[oracle@shinya01-mysql-1 mydatabase]$ ls -l T_PART*
-rw-r----- 1 oracle oracle  8556 Apr 20 04:01 T_PARTITION.frm
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p1.ibd
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p2.ibd
-rw-r----- 1 oracle oracle 98304 Apr 20 04:01 T_PARTITION#P#p3.ibd
[oracle@shinya01-mysql-1 mydatabase]$

MySQL8.0.1の段階で、既にpartition storage engineは含まれていない。

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT
    -> PLUGIN_NAME as Name,
    -> PLUGIN_VERSION as Version,
    -> PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| InnoDB             | 8.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| ARCHIVE            | 3.0     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
+--------------------+---------+----------+
10 rows in set (0.00 sec)

mysql> CREATE TABLE T_PARTITION (c1 int)
    -> PARTITION BY RANGE COLUMNS(c1) 
    -> (
    -> PARTITION p1 VALUES LESS THAN (5),
    -> PARTITION p2 VALUES LESS THAN (10),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> select TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION from information_schema.partitions where table_name = 'T_PARTITION';
+-------------+----------------+------------------+-----------------------+
| TABLE_NAME  | PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+-------------+----------------+------------------+-----------------------+
| T_PARTITION | p1             | RANGE COLUMNS    | 5                     |
| T_PARTITION | p2             | RANGE COLUMNS    | 10                    |
| T_PARTITION | p3             | RANGE COLUMNS    | MAXVALUE              |
+-------------+----------------+------------------+-----------------------+
3 rows in set (0.00 sec)

mysql> 

また、MySQL8.0からはfrmがテーブルに格納されるのでファイルは無い。

[root@DockerHost sakila]# ls -l T_PART*
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p1.ibd
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p2.ibd
-rw-r----- 1 27 27 131072 Apr 20 04:02 T_PARTITION#P#p3.ibd
[root@DockerHost sakila]# 

MySQL8.0でNDB,InnoDB以外でパーティションを作成しようとすると以下のようにエラーになる。

mysql> CREATE TABLE `T_PARTITION_MyISAM` (
    ->   `c1` int(11) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
    -> /*!50500 PARTITION BY RANGE  COLUMNS(c1)
    -> (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM,
    ->  PARTITION p2 VALUES LESS THAN (10) ENGINE = MyISAM,
    ->  PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */;
ERROR 1178 (42000): The storage engine for the table doesn't support native partitioning
mysql>

データ量が昔と比較して増えてきたからか、最近はパーティションの話を良く聞くので、
簡易的に再確認してみました。いざ確認してみると、色々と忘れている事もあり頭がリフレッシュ出来ました。
パーティショニン種類に関しては、以下のサイトに詳しく出ているので参照してみて下さい。
また、MySQL5.6のマニュアルも日本語化されたので参考にしてみて下さい。

MySQL5.6日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/

19.2. パーティショニングタイプ
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-types.html

——————————————————————————————–
※奥野さんのサイトにも書かれていますが、必ずしもパーティションを利用して、
 パフォーマンスが良くなる事ばかりでは無いです。効率良くIndexが使えていたりデータによっては、
 付けない方が良い事もあるので、先ずは検証環境で確認してみて下さい。

 パーティショニングの使用例 – カーディナリティが低いカラムを使って検索する場合
 http://nippondanji.blogspot.jp/2009/04/1.html
——————————————————————————————–

第19章 パーティション化
https://dev.mysql.com/doc/refman/5.6/ja/partitioning.html

19.6. パーティショニングの制約と制限
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html#partitioning-limitations-subpartitions

確認:バージョン

root@localhost [test]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.24-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> 

プラグインで有効になっているか確認

root@localhost [test]> SELECT 
    -> PLUGIN_NAME as Name, 
    -> PLUGIN_VERSION as Version, 
    -> PLUGIN_STATUS as Status 
    -> FROM INFORMATION_SCHEMA.PLUGINS 
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| ARCHIVE            | 3.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| InnoDB             | 5.6     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| partition          | 1.0     | ACTIVE   | < --- こちらがACTIVEであれば利用可能
+--------------------+---------+----------+
11 rows in set (0.01 sec)

root@localhost [test]> 

■ 標準的なRANGEパーティショニング

root@localhost [test]> show create table tbl_partition\G
*************************** 1. row ***************************
       Table: tbl_partition
Create Table: CREATE TABLE `tbl_partition` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

root@localhost [test]> explain partitions select * from tbl_partition where year = 2015;
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table         | partitions | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_partition | p2016      | ref  | idx_tbl_partition | idx_tbl_partition | 2       | const |    2 | NULL  |
+----+-------------+---------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 

■ サブパーティション
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-subpartitions.html

サブパーティションに関する問題
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html#partitioning-limitations-subpartitions

サブパーティションに関する問題 サブパーティションは HASH または KEY パーティショニングを使用する必要があります。サブパーティション化できるのは RANGE および LIST パーティションのみです。HASH および KEY パーティションはサブパーティション化できません。 現在のところ、SUBPARTITION BY KEY にはサブパーティショニングカラムを明示的に指定する必要がありますが、PARTITION BY KEY の場合は省略できます (その場合、テーブルの主キーカラムがデフォルトで使用されます)


root@localhost [test]> show create table tbl_sub_partition01\G
*************************** 1. row ***************************
       Table: tbl_sub_partition01
Create Table: CREATE TABLE `tbl_sub_partition01` (
  `member_id` int(11) DEFAULT NULL,
  `platform` varchar(10) NOT NULL,
  `purchased` date DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
SUBPARTITIONS 12
(PARTITION sub_y2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION sub_y2011 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION sub_y2012 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION sub_y2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION sub_y2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION sub_y2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION sub_y2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION sub_y2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION sub_y2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION sub_y2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION sub_y2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION sub_y_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

timestamp型などを利用すると、
”ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”というエラーになります。

root@localhost [test]> select * from tbl_sub_partition01;
+-----------+----------+------------+---------------------+
| member_id | platform | purchased  | updated_at          |
+-----------+----------+------------+---------------------+
|         1 | iphone   | 2015-06-28 | 2015-06-30 11:53:32 |
|         2 | iphone   | 2015-06-29 | 2015-06-30 11:53:39 |
|         3 | iphone   | 2015-06-30 | 2015-06-30 11:53:47 |
|         4 | iphone   | 2015-07-01 | 2015-06-30 12:08:13 |
|         5 | iphone6  | 2015-07-02 | 2015-06-30 12:08:26 |
|         6 | iphone6  | 2015-07-03 | 2015-06-30 12:10:11 |
|         7 | iphone3s | 2015-07-04 | 2015-06-30 12:10:27 |
|         8 | iphone4s | 2015-07-05 | 2015-06-30 12:10:37 |
|         9 | iphone4s | 2015-07-06 | 2015-06-30 12:10:46 |
|        10 | iphone6s | 2015-07-07 | 2015-06-30 12:10:59 |
+-----------+----------+------------+---------------------+
10 rows in set (0.00 sec)

root@localhost [test]> explain partitions select * from tbl_sub_partition01 where purchased = '2015-07-01';
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | partitions             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_sub_partition01 | sub_y2016_sub_y2016sp5 | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------------------+------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@localhost [test]> 

パーティションにDATA DIRECTORYなどを指定する場合。
MyISAMで且つ、テーブル全体では無くSubpartition毎に指定しています。

root@localhost [test]> show create table tbl_sub_partition_dir\G
*************************** 1. row ***************************
       Table: tbl_sub_partition_dir
Create Table: CREATE TABLE `tbl_sub_partition_dir` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
(PARTITION p0_dir VALUES LESS THAN (1990)
 (SUBPARTITION s0 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s1 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM),
 PARTITION p1_dir VALUES LESS THAN (2000)
 (SUBPARTITION s2 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s3 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM),
 PARTITION p2_dir VALUES LESS THAN MAXVALUE
 (SUBPARTITION s4 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM,
  SUBPARTITION s5 DATA DIRECTORY = '/home/mysql/data' INDEX DIRECTORY = '/home/mysql/index' ENGINE = MyISAM)) */
1 row in set (0.01 sec)

root@localhost [test]> system ls /home/mysql/data/
tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD  tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD  tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD  test
tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD  tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD  tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD
root@localhost [test]> system ls /home/mysql/index/
tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI  tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI  tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI
tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI  tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI  tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI
root@localhost [test]> 

sub_myisam

DATAディレクトリーにシンボリックリンクが作成されています。

[root@GA01 test]# pwd
/usr/local/mysql/data/test
[root@GA01 test]# ls -l tbl_sub_partition_dir*
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p0_dir#SP#s0.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p0_dir#SP#s0.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p0_dir#SP#s1.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p0_dir#SP#s1.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p1_dir#SP#s2.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p1_dir#SP#s2.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p1_dir#SP#s3.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p1_dir#SP#s3.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p2_dir#SP#s4.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p2_dir#SP#s4.MYI
lrwxrwxrwx. 1 mysql mysql   57  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD -> /home/mysql/data/tbl_sub_partition_dir#P#p2_dir#SP#s5.MYD
lrwxrwxrwx. 1 mysql mysql   58  6月 30 13:31 tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI -> /home/mysql/index/tbl_sub_partition_dir#P#p2_dir#SP#s5.MYI
-rw-rw----. 1 mysql mysql 8596  6月 30 13:31 tbl_sub_partition_dir.frm
-rw-rw----. 1 mysql mysql  104  6月 30 13:31 tbl_sub_partition_dir.par
[root@GA01 test]# 

data foler

19.6.4. パーティショニングとロック
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations-locking.html

MySQL 5.6.6 はパーティションロックプルーニングを実装し、これによって多くの場合に不必要なロックが排除されます。
MySQL 5.6.6 以降では、パーティション化された MyISAM テーブルに対して読み取りまたは更新を行うほとんどのステートメントで、影響を受けるパーティションのみがロックされます。たとえば、MySQL 5.6.6 より前は、パーティション化 MyISAM テーブルからのSELECT でテーブル全体がロックされました。MySQL 5.6.6 以降は、SELECT ステートメントの WHERE 条件を満たす行を実際に含むパーティションのみがロックされます。これには、パーティション化された MyISAM テーブルに対する同時操作の速度および効率を向上させる効果があります。この改善は、多く (32 以上) のパーティションを持つ MyISAM テーブルを操作するときに特に顕著になります。

DATA DIRECTORY、INDEX DIRECTORY
http://dev.mysql.com/doc/refman/5.6/ja/create-table.html

InnoDB では、DATA DIRECTORY=’directory’ オプションを使用すると、MySQL データディレクトリ以外の場所に新しいInnoDB file-per-table テーブルスペースを作成できます。MySQL は、指定されたディレクトリ内にデータベース名に対応するサブディレクトリを作成し、さらにその中に新しいテーブルの .ibd ファイルを作成します。InnoDB テーブルで DATA DIRECTORY オプションを使用するには、innodb_file_per_table 構成オプションを有効にする必要があります。このディレクトリは、ディレクトリへの (相対パスではなく) フルパス名である必要があります。
詳細は、「テーブルスペースの位置の指定」を参照してください。
MyISAM テーブルを作成する場合は、DATA DIRECTORY=’directory’ 句、INDEX DIRECTORY=’directory’ 句、またはその両方を使用できます。これらは、それぞれ MyISAM テーブルのデータファイルとインデックスファイルを配置する場所を指定します。
InnoDB テーブルとは異なり、DATA DIRECTORY または INDEX DIRECTORY オプションで MyISAM テーブルを作成する場合、MySQL はデータベース名に対応するサブディレクトリを作成しません。各ファイルは、指定されたディレクトリ内に作成されます。

重要
https://bugs.mysql.com/bug.php?id=32091
テーブルレベルの DATA DIRECTORY および INDEX DIRECTORY オプションは、パーティション化されたテーブルでは無視されます。(Bug #32091)

■InnoDBで、SUBPARTITIONを指定せずに実行した場合


root@localhost [test]> CREATE TABLE `tbl_partition_innodb` (
    ->   `member_id` varchar(40) NOT NULL,
    ->   `platform` varchar(10) NOT NULL,
    ->   `year` smallint(5) unsigned NOT NULL,
    ->   `month` tinyint(2) unsigned NOT NULL,
    ->   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`member_id`,`year`,`month`),
    ->   KEY `idx_tbl_partition` (`year`),
    ->   KEY `idx_tbl_partition_id` (`member_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY = '/home/mysql/data';
Query OK, 0 rows affected (0.02 sec)

root@localhost [test]> show create table tbl_partition_innodb\G
*************************** 1. row ***************************
       Table: tbl_partition_innodb
Create Table: CREATE TABLE `tbl_partition_innodb` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY='/home/mysql/data/'
1 row in set (0.00 sec)

ALTERテーブルでパーティション化すると、DATA DIRECTORYは無効になりファイルは削除されます。

root@localhost [test]> Alter table tbl_partition_innodb PARTITION BY RANGE (`year`)
    -> (PARTITION p2013_innodb VALUES LESS THAN (2013) ENGINE = InnoDB,
    ->  PARTITION p2014_innodb VALUES LESS THAN (2014) ENGINE = InnoDB,
    ->  PARTITION p2015_innodb VALUES LESS THAN (2015) ENGINE = InnoDB,
    ->  PARTITION p2016_innodb VALUES LESS THAN (2016) ENGINE = InnoDB,
    ->  PARTITION p2017_innodb VALUES LESS THAN (2017) ENGINE = InnoDB,
    ->  PARTITION p2018_innodb VALUES LESS THAN (2018) ENGINE = InnoDB,
    ->  PARTITION p2019_innodb VALUES LESS THAN (2019) ENGINE = InnoDB,
    ->  PARTITION p2020_innodb VALUES LESS THAN (2020) ENGINE = InnoDB,
    ->  PARTITION pmax_innodb VALUES LESS THAN MAXVALUE ENGINE = InnoDB) ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table tbl_partition_innodb\G
*************************** 1. row ***************************
       Table: tbl_partition_innodb
Create Table: CREATE TABLE `tbl_partition_innodb` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013_innodb VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014_innodb VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015_innodb VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016_innodb VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017_innodb VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018_innodb VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019_innodb VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020_innodb VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax_innodb VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

※ Alter Tableでパーティショニングすると、
  DATA DIRECTORY=’/home/mysql/data/’に存在していたibdファイルは削除されます。

file_innodb_alter_update

■圧縮テーブルとパーティショニング
パーティション毎にストレージエンジンンが指定出来ますが、圧縮はパーティション毎に指定出来ません。
圧縮する場合はテーブル毎に指定してください。

root@localhost [test]> show create table tbl_partition_sample_zip\G
*************************** 1. row ***************************
       Table: tbl_partition_sample_zip
Create Table: CREATE TABLE `tbl_partition_sample_zip` (
  `member_id` varchar(40) NOT NULL,
  `platform` varchar(10) NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(2) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`,`year`,`month`),
  KEY `idx_tbl_partition` (`year`),
  KEY `idx_tbl_partition_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
/*!50100 PARTITION BY RANGE (`year`)
(PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> 

補足メモ:

パーティションでは無いですが、パスを変更してデータを配置する方法。
MYSQL DATA DIRECTORY と INDEX DIRECTORYの指定

5.7からは複数テーブルを配置出来る、GENERAL TABLESPACEが利用可能になります。
InnoDB General Tablespace


root@localhost [test]> CREATE TABLE `tbl_DATA_DIRECTORY` (
    ->   `member_id` varchar(40) NOT NULL,
    ->   `platform` varchar(10) NOT NULL,
    ->   `year` smallint(5) unsigned NOT NULL,
    ->   `month` tinyint(2) unsigned NOT NULL,
    ->   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`member_id`,`year`,`month`),
    ->   KEY `idx_tbl_DATA_DIRECTORY` (`year`),
    ->   KEY `idx_tbl_DATA_DIRECTORY_id` (`member_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY = '/home/mysql/data';
Query OK, 0 rows affected (0.03 sec)

root@localhost [test]> 

■テーブル定義はDefault Dataディレクトリーで,データ用テーブルスペースが指定したディレクトリーに作成されます。


[root@GA01 test]# ls -l /usr/local/mysql/data/test/tbl_DATA*
-rw-rw----. 1 mysql mysql 8712  6月 30 15:05 /usr/local/mysql/data/test/tbl_DATA_DIRECTORY.frm
-rw-rw----. 1 mysql mysql   44  6月 30 15:05 /usr/local/mysql/data/test/tbl_DATA_DIRECTORY.isl
[root@GA01 test]# ls -l /home/mysql/data/test/
合計 192
-rw-rw----. 1 mysql mysql 131072  6月 30 15:05 tbl_DATA_DIRECTORY.ibd
[root@GA01 test]# 

基本ファイル構成


MySQL5.6ではinnodb_file_per_tableがDefaultでテーブル毎にテーブルスペース(ファイル)が作成されますが、MySQL5.7.6 DMRからは、CREATE TABLESPACEステートメントによって、
複数テーブルで共有出来るテーブルスペースが作成出来るようになりました。また、Defaultデータディレクトリーとは別のパスにテーブルスペースを作成出来るので、
負荷が高いテーブルなどをSSDなどに配置するなど柔軟に対応することが可能になります。Oracle(テーブルスペース)やMS SQL(ファイルグループ)に関しては、
以前から同様に指定出来ますが、オープンソースデータベースのMySQLは5.7になり、更にそれらの商用データベースと同様の機能も利用出来る汎用性を備えたデータベースになってきました。
MySQL5.7はまだ5.7.7RCですが、更に新たな機能が追加されているので適宜可能な範囲で、こちらにてご紹介したいとお思います。

参照: 13.1.15 CREATE TABLESPACE Syntax
CREATE TABLESPACE is supported with InnoDB as of MySQL 5.7.6.
A general tablespace is a shared tablespace, similar to the system tablespace.
It can hold multiple tables, and supports all table row formats.
General tablespaces can also be created in a location relative to or independent of the MySQL data directory.

Tablespaceの作成


root@localhost [USER01]> CREATE TABLESPACE U_TABLESPACE01 ADD DATAFILE '/home/mysql/user_tablespace01.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

root@localhost [USER01]> CREATE TABLESPACE U_TABLESPACE02_8K ADD DATAFILE '/home/mysql/user_tablespace02_8k.ibd' FILE_BLOCK_S8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

テーブルスペースを指定してテーブルを作成

root@localhost [USER01]> CREATE TABLE `T_USER01` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    ->  PRIMARY KEY (`id`)
    ->  ) TABLESPACE = U_TABLESPACE01 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [USER01]> CREATE TABLE `T_USER02_8K` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) TABLESPACE = U_TABLESPACE02_8K ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOC=8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES where NAME like 'U_%';
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
| SPACE | NAME              | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE |
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
|   165 | U_TABLESPACE01    | 2048 | Any         | Any        |     16384 |             0 | General    |
|   166 | U_TABLESPACE02_8K | 2089 | Barracuda   | Compressed |     16384 |          8192 | General    |
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
2 rows in set (0.00 sec)

tablespace2

Defaultデータディレクトリーとは別にテーブルスペースが作成されている事を確認


root@localhost [USER01]> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

root@localhost [USER01]> system ls /home/mysql/
user_tablespace01.ibd  user_tablespace02_8k.ibd
root@localhost [USER01]> 

先程、T_USER01を作成してテーブルスペースに追加でテーブルを作成


root@localhost [USER01]> CREATE TABLE `T_USER02` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) TABLESPACE = U_TABLESPACE01 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> show tables;
+------------------+
| Tables_in_USER01 |
+------------------+
| T_USER01         |
| T_USER02         |
| T_USER02_8K      |
+------------------+
3 rows in set (0.00 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
3 rows in set (0.00 sec)

root@localhost [USER01]> 

tablespace_innodb

既存のテーブルを共通テーブルスペースへ移動する場合はAlter Tableコマンドにて対応

root@localhost [USER01]> CREATE TABLE `T_USER03` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
|      174 | USER01/T_USER03    |    1 |      5 |   167 | Antelope    | Compact    |             0 | Single     |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
4 rows in set (0.00 sec)

root@localhost [USER01]> ALTER TABLE T_USER03 TABLESPACE U_TABLESPACE01;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
|      175 | USER01/T_USER03    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
4 rows in set (0.00 sec)

root@localhost [USER01]> 

tablespace_alter

メモ:
5.7でサポートされる以下のページサイズも対応していますが、圧縮機能はサポートされていません。
64K 64K (65536) Compression is not supported
32K 32K (32768) Compression is not supported

Tablespace Row Formatのサポートについて
General tablespaces support all table row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED)
with the caveat that compressed and uncompressed tables cannot exist in the same general tablespace due to different physical page sizes.


Transportable Tablespace on MySQLの動作確認
MyISAMではなく、InnoDBがこれからのMySQLのコアなストレージエンジンなので、
MyISAMの時のようにファイルコピーして、データベースを他のサーバーでも
利用する機会があるかと思い、一応InnoDBで実装されたTransportable Tablespaceを確認。

Sakilaデータベースのlanguage tableを他のDBインスタンスに移動してみる。

root@localhost [(none)]>select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS01   |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| audit_test         |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

root@localhost [(none)]>

root@localhost [(none)]>use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [sakila]>show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| rental2                    |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
24 rows in set (0.00 sec)


root@localhost [sakila]>show create database sakila;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| sakila   | CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [sakila]>


root@localhost [sakila]>show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@localhost [sakila]>

root@localhost [sakila]>FLUSH TABLES language FOR EXPORT;
Query OK, 0 rows affected (0.01 sec)

root@localhost [sakila]>

FOR EXPORTで対象にしたテーブル用にcfgファイルが出来ているので、
ibdとcfgファイルをコピー先にコピーする。

[root@CentOS01 sakila]# pwd
/usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/sakila
[root@CentOS01 sakila]# ls -l language.*
-rw-rw----. 1 mysql mysql   477 11月  3 15:03 language.cfg
-rw-rw----. 1 mysql mysql  8648 10月 30 12:08 language.frm
-rw-rw----. 1 mysql mysql 98304 10月 30 12:08 language.ibd
[root@CentOS01 sakila]# 

[root@CentOS01 sakila]# scp language.{ibd,cfg} 192.168.56.102:/usr/local/mysql/data/sakila
root@192.168.56.102's password: 
language.ibd                                                                                                                                100%   96KB  96.0KB/s   00:00    
language.cfg                                                                                                                                100%  477     0.5KB/s   00:00    
[root@CentOS01 sakila]# 

コピーが終わったので、テーブルをUNLOCKして利用可能にする。

root@localhost [sakila]>UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)

root@localhost [sakila]>

root@localhost [sakila]>select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

root@localhost [sakila]>

UNLOCKするまで、対象テーブルはSELECT出来ますが、
INSERT,UPDATE,DELETEなど変更を伴うDMLは待たされます。

lock

コピー先のインスタンスに予め空のDBとテーブルを作成しておく。
※Scriptはコピー元でSHOW CREATEコマンドで確認したSCRIPTを実行してあります。

root@localhost [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS02   |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

root@localhost [(none)]> 


root@localhost [(none)]> CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use sakila;
Database changed
root@localhost [sakila]> CREATE TABLE `language` (
    ->   `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` char(20) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`language_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

root@localhost [sakila]> alter table language DISCARD TABLESPACE;                                                                                                             Query OK, 0 rows affected (0.08 sec)

root@localhost [sakila]> 

上記で空テーブルを無効にしてあるので、コピー元のインスタンスからファイルコピーし終わったら、
以下のコマンドでテーブルスペースをIMPORTする。

root@localhost [sakila]> alter table language Import TABLESPACE;
Query OK, 0 rows affected (0.13 sec)

root@localhost [sakila]> 

root@localhost [sakila]> show tables;
+------------------+
| Tables_in_sakila |
+------------------+
| language         |
+------------------+
1 row in set (0.00 sec)

root@localhost [sakila]> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

root@localhost [sakila]> 

これで、同じ内容のテーブルが2台のサーバーで利用可能になりました。
コピーしたcfgファイルはバイナリ―ファイルになります。

参考
http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html


MYSQL5.6も近い将来リリースされるので再度基本レビューしておく。

確認したMYSQLバージョン

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.5.29-log |
+------------+
1 row in set (0.00 sec)

MYSQLファイルパスについて
basedir MYSQLインストールディレクトリー
datadir データディレクトリー(mysql_install_dbスクリプト実行時に–datadir=/dataなどで指定可能)
/etc/my.cnfにて変更可能

	[root@HOME001 mysql]# cat /etc/my.cnf | grep innodb
	#innodb_data_home_dir = /usr/local/mysql/data
	#innodb_data_file_path = ibdata1:10M:autoextend
	#innodb_log_group_home_dir = /usr/local/mysql/data
	innodb_buffer_pool_size = 32M
	innodb_additional_mem_pool_size = 2M
	innodb_log_file_size =   8M
	innodb_log_buffer_size = 8M
	innodb_flush_log_at_trx_commit = 1
	innodb_lock_wait_timeout = 50
	[root@HOME001 mysql]#
mysql> show variables like '%dir%';
+-----------------------------------------+-------------------------------------------------------+
| Variable_name                           | Value                                                 |
+-----------------------------------------+-------------------------------------------------------+
| basedir                                 | /usr/local/mysql                                      |
| binlog_direct_non_transactional_updates | OFF                                                   |
| character_sets_dir                      | /usr/local/mysql-5.5.29-linux2.6-i686/share/charsets/ |
| datadir                                 | /usr/local/mysql/data/                                |
| innodb_data_home_dir                    |                                                       |
| innodb_log_group_home_dir               | ./                                                    |
| innodb_max_dirty_pages_pct              | 75                                                    |
| lc_messages_dir                         | /usr/local/mysql-5.5.29-linux2.6-i686/share/          |
| plugin_dir                              | /usr/local/mysql/lib/plugin/                          |
| slave_load_tmpdir                       | /tmp                                                  |
| tmpdir                                  | /tmp                                                  |
+-----------------------------------------+-------------------------------------------------------+
11 rows in set (0.00 sec)

mysql>

TCP/IP PORT・Socket接続について。
複数インスタンスを立ち上げる場合はPort、Socket、Datadirが被らないようにする必要あり。

mysql> show variables like '%socket%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| socket        | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> show variables like '%port%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_support_xa   | ON    |
| large_files_support | ON    |
| port                | 3306  |
| report_host         |       |
| report_password     |       |
| report_port         | 3306  |
| report_user         |       |
+---------------------+-------+
7 rows in set (0.01 sec)

mysql>

コネクションに関しては、Default値がバージョンによっても異なるので、
確認して不足している場合は適宜値を変更する必要があります。
Java EE, RubyなどのようにコネクションPoolする場合とPHPなどのように
都度接続する場合など調整方法も変更する必要があると思います。
MSSQLなどを利用している場合も同様に.Netやサーバー側のPool設定
またはLoad Balancerなどの調整なども環境によっては必要になると思います。

mysql> show variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| max_connections          | 151             |
| max_user_connections     | 0               |
+--------------------------+-----------------+
4 rows in set (0.00 sec)

mysql>

既定のストレージエンジンについて
CREATE TABLE文実行時にENGINE=MyISAMなどで指定する事も可能。

mysql> show variables like '%storage%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

mysql>

ログファイルについて。
log_binに関しては、リカバリーやレプリケーションで必須なので設定。

mysql> show variables like '%log%';
+-----------------------------------------+-----------------------------------------------+
| Variable_name                           | Value                                         |
+-----------------------------------------+-----------------------------------------------+
| back_log                                | 50                                            |
| binlog_cache_size                       | 32768                                         |
| binlog_direct_non_transactional_updates | OFF                                           |
| binlog_format                           | MIXED                                         |
| binlog_stmt_cache_size                  | 32768                                         |
| expire_logs_days                        | 7                                             |
| general_log                             | OFF                                           |
| general_log_file                        | /usr/local/mysql/data/HOME001.log             |
| innodb_flush_log_at_trx_commit          | 1                                             |
| innodb_locks_unsafe_for_binlog          | OFF                                           |
| innodb_log_buffer_size                  | 8388608                                       |
| innodb_log_file_size                    | 8388608                                       |
| innodb_log_files_in_group               | 2                                             |
| innodb_log_group_home_dir               | ./                                            |
| innodb_mirrored_log_groups              | 1                                             |
| log                                     | OFF                                           |
| log_bin                                 | ON                                            |
| log_bin_trust_function_creators         | OFF                                           |
| log_error                               | /usr/local/mysql/data/HOME001.localdomain.err |
| log_output                              | FILE                                          |
| log_queries_not_using_indexes           | OFF                                           |
| log_slave_updates                       | OFF                                           |
| log_slow_queries                        | OFF                                           |
| log_warnings                            | 1                                             |
| max_binlog_cache_size                   | 18446744073709547520                          |
| max_binlog_size                         | 1073741824                                    |
| max_binlog_stmt_cache_size              | 18446744073709547520                          |
| max_relay_log_size                      | 0                                             |
| relay_log                               |                                               |
| relay_log_index                         |                                               |
| relay_log_info_file                     | relay-log.info                                |
| relay_log_purge                         | ON                                            |
| relay_log_recovery                      | OFF                                           |
| relay_log_space_limit                   | 0                                             |
| slow_query_log                          | OFF                                           |
| slow_query_log_file                     | /usr/local/mysql/data/HOME001-slow.log        |
| sql_log_bin                             | ON                                            |
| sql_log_off                             | OFF                                           |
| sync_binlog                             | 0                                             |
| sync_relay_log                          | 0                                             |
| sync_relay_log_info                     | 0                                             |
+-----------------------------------------+-----------------------------------------------+
41 rows in set (0.00 sec)

mysql>

mysql> show variables like '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | MIXED                |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

mysql>

遅いQueryを早期発見してDBを安定稼動させ続ける為に必要です。
設定を有効にしておきましょう。
オンラインでも設定可能ですが再起動に備えて/etc/my.cnfに設定入れておくと良い。

mysql> show variables like '%slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| log_slow_queries    | OFF                                    |
| slow_launch_time    | 2                                      |
| slow_query_log      | OFF                                    |
| slow_query_log_file | /usr/local/mysql/data/HOME001-slow.log |
+---------------------+----------------------------------------+
4 rows in set (0.00 sec)

mysql> SET GLOBAL log_slow_queries = 1;
Query OK, 0 rows affected, 1 warning (0.39 sec)

mysql> show variables like '%slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| log_slow_queries    | ON                                     |
| slow_launch_time    | 2                                      |
| slow_query_log      | ON                                     |
| slow_query_log_file | /usr/local/mysql/data/HOME001-slow.log |
+---------------------+----------------------------------------+
4 rows in set (0.00 sec)

mysql>

設定は反映されるかWarningに以下のメッセージが出るので、
以降は”SET GLOBAL slow_query_log = 1;”コマンドで有効に設定する。

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

SLOWログの閾値を設定する。
Default10秒なので1秒に変更するが再起動が必要。
規定値として1秒に設定しておいて、I/O負荷状況や必要に応じて
”log_slow_queries”でON・OFFを切り替えるのが良いかと思う。

mysql> show variables like '%long%';
+---------------------------------------------------+-----------+
| Variable_name                                     | Value     |
+---------------------------------------------------+-----------+
| long_query_time                                   | 10.000000 |
| max_long_data_size                                | 1048576   |
| performance_schema_events_waits_history_long_size | 10000     |
+---------------------------------------------------+-----------+
3 rows in set (0.00 sec)

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long%';
+---------------------------------------------------+-----------+
| Variable_name                                     | Value     |
+---------------------------------------------------+-----------+
| long_query_time                                   | 10.000000 |
| max_long_data_size                                | 1048576   |
| performance_schema_events_waits_history_long_size | 10000     |
+---------------------------------------------------+-----------+
3 rows in set (0.00 sec)

mysql>

SLOWログの設定を設定ファイルに入れて再起動。
long_query_timeはマイクロ秒単位で設定可能(例:long_query_time=0.5)

[root@HOME001 mysql]# vi /etc/my.cnf
[root@HOME001 mysql]# cat /etc/my.cnf | egrep -i "slow|long"
# In this file, you can use all long options that a program supports.
slow_query_log=ON
slow_query_log_file=home001-mysql-slow.log
long_query_time=1
[root@HOME001 mysql]#


mysql> show variables like '%slow%';
+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| log_slow_queries    | ON                     |
| slow_launch_time    | 2                      |
| slow_query_log      | ON                     |
| slow_query_log_file | home001-mysql-slow.log |
+---------------------+------------------------+
4 rows in set (0.00 sec)

mysql>

mysql> show variables like '%long%';
+---------------------------------------------------+----------+
| Variable_name                                     | Value    |
+---------------------------------------------------+----------+
| long_query_time                                   | 1.000000 |
| max_long_data_size                                | 1048576  |
| performance_schema_events_waits_history_long_size | 10000    |
+---------------------------------------------------+----------+
3 rows in set (0.00 sec)

mysql> 

Fusion-IOなどの高性能のディスクも出てきましたが、
メモリーの方が高速である事は変わりないのでパフォーマンスに影響ある
innodb_buffer_pool_sizeを適切に設定しInnoDB内のデータ領域を
メモリー内に保持する必要があります。
サーバーを1つのMYSQLインスタンス専用として利用している場合は、
物理メモリーサイズの60%~80%程度を割り当てて様子を見て調整すれば良いようです。
Linuxの古いカーネルなどを利用している場合は、InnoDBログサイズなどが大きく
ファイルシステムのキャッシュから圧迫される事でSwapが頻発してしまう可能性があるそうです。
残りのメモリーは、OSやアプリケーション接続スレッドなどに利用される。
※ 参考:unmap_mysql_logs https://github.com/yoshinorim/unmap_mysql_logs
※ Swapの確認に関しては下に追記しておきます。

mysql> show variables like '%buffer_pool%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| innodb_buffer_pool_instances | 1        |
| innodb_buffer_pool_size      | 33554432 |
+------------------------------+----------+
2 rows in set (0.01 sec)

mysql>

MySQL innodb_flush_method = O_DIRECTに関してネットで確認してみると
検証されている方も沢山いて非常に参考になります。
全ての環境でパフォーマンスが上がる訳では無さそうなので検証してしてみる必要あり。

MySQL innodb_flush_method = O_DIRECTを設定するとダイレクトI/OというOSの機能が有効になります。
ダイレクトI/Oを利用するとInnoDBのメモリー領域とディスク間のデータのやりとりに(InnoDB Buffer<->Disk)、
OSのファイルシステムキャッシュを利用しないI/O方式になるとの事。
※ オーバーヘット増:Disk -> OS Page Cache -> InnoDB Buffer
※ オーバーヘット減:Disk -> InnoDB Buffer
※ メモリーが多い環境で有効との事。

mysql> show variables like '%flush%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| flush                          | OFF   |
| flush_time                     | 0     |
| innodb_adaptive_flushing       | ON    |
| innodb_flush_log_at_trx_commit | 1     |
| innodb_flush_method            |       |
+--------------------------------+-------+
5 rows in set (0.00 sec)

mysql>

innodb_data_file_pathパラメーターに関しては、
データファイルの初期サイズやディレクトリーを指定する為に利用。
データファイルの拡張が頻繁に発生する環境では予めファイルサイズを大きめに取って、
ユーザーがサイトを利用している間にファイル拡張が発生して
パフォーマンスが落ちないようにしておく方が良いかと思います。

mysql> show variables like '%innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

innodb_autoextend_incrementがautoextendで拡張する拡張単位を指定します。
Defaultでは8MB単位になっているので必要に応じて変更。

mysql> show variables like '%innodb_autoextend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 8     |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_autoextend_increment = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%innodb_autoextend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 10    |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql>

再起動で変更が消えてしまうので、my.cnfに設定を追記しておくこと。

[root@HOME001 mysql]# vi /etc/my.cnf
[root@HOME001 mysql]# cat /etc/my.cnf | grep innodb_autoextend_increment
innodb_autoextend_increment = 10
[root@HOME001 mysql]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@HOME001 mysql]#

innodb_log_file_sizeはInnoDBのログファイルサイズを指定出来ます。
OracleのREDOログやMS SQLのT-Logと同じなのでクラッシュリカバリーでも利用されます。
大きいとログスイッチの頻度やチェックポイント処理を下げる事が出来ますが、
多くのトランザクションログが一つのファイルに含まれて障害発生時にログの破損やリカバリー処理に
時間がかかり困る場合もあるので、パフォーマンスとリカバリーの観点から適切なサイズを選択する必要があります。

innodb_log_file_size
小さい: リカバリー高速だが更新処理が遅くなる。
大きい: 高速だがリカバリーが遅い。ログファイルの破損の注意。

mysql> show variables like '%innodb_log%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_buffer_size    | 8388608 |
| innodb_log_file_size      | 8388608 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+---------------------------+---------+
4 rows in set (0.00 sec)

mysql>


安定性からMYSQLコンパイル済みのバイナリーを利用してますが、
コンパイル済みMYSQLのConfigオプションを確認したい場合は以下のファイルを確認。


[root@HOME001 docs]# tail -n 100 /usr/local/mysql/docs/INFO_BIN
===== Information about the build process: =====
Build was run at 2012-12-10 07:35:23 on host 'loki02'

Build was done on  Linux-2.6.9-89.ELsmp using i686
Build was done using cmake 2.8.5

===== Compiler flags used (from the 'sql/' subdirectory): =====
# compile C with /usr/local/gcc-4.3.4/bin/gcc
# compile CXX with /usr/local/gcc-4.3.4/bin/gcc
C_FLAGS =  -fPIC -Wall -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/sql -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/regex -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/zlib -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/sql    -DHAVE_YASSL -DYASSL_PURE_C -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED
C_DEFINES = -DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER
CXX_FLAGS = -fno-exceptions  -fPIC -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/sql -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/regex -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/zlib -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/sql    -DHAVE_YASSL -DYASSL_PURE_C -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED
CXX_DEFINES = -DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER

Pointer size: 4

===== Feature flags used: =====
-- Cache values
CMAKE_BUILD_TYPE:STRING=RelWithDebInfo
CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql
COMMUNITY_BUILD:BOOL=ON
ENABLED_PROFILING:BOOL=ON
ENABLE_DEBUG_SYNC:BOOL=ON
ENABLE_GCOV:BOOL=OFF
FEATURE_SET:STRING=community
INSTALL_LAYOUT:STRING=STANDALONE
MYSQL_DATADIR:PATH=/usr/local/mysql/data
MYSQL_MAINTAINER_MODE:BOOL=OFF
WITH_ARCHIVE_STORAGE_ENGINE:BOOL=ON
WITH_BLACKHOLE_STORAGE_ENGINE:BOOL=ON
WITH_DEBUG:BOOL=OFF
WITH_EMBEDDED_SERVER:BOOL=ON
WITH_EXAMPLE_STORAGE_ENGINE:BOOL=OFF
WITH_EXTRA_CHARSETS:STRING=all
WITH_FEDERATED_STORAGE_ENGINE:BOOL=ON
WITH_INNOBASE_STORAGE_ENGINE:BOOL=ON
WITH_LIBEDIT:BOOL=OFF
WITH_LIBWRAP:BOOL=OFF
WITH_PARTITION_STORAGE_ENGINE:BOOL=ON
WITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON
WITH_PIC:BOOL=ON
WITH_READLINE:BOOL=ON
WITH_SSL:STRING=bundled
WITH_UNIT_TESTS:BOOL=ON
WITH_VALGRIND:BOOL=OFF
WITH_ZLIB:STRING=bundled

===== EOF =====
[root@HOME001 docs]#


参考:
MySQL 5.1のスロークエリログ
MySQL 5.1のmysqldumpslowで快速チューニング
MySQL innodb_flush_method = O_DIRECTの検討
非同期I/Oの謎
初期化パラメータ filesystemio_options の値によってI/Oに関するシステムコールがどのように変化するか
Oracleの同期IOと非同期IOについて

その他パフォーマンス関連調整項目:
MySQL InnoDBストレージエンジンのチューニング(前編)
MySQL InnoDBストレージエンジンのチューニング(後編)

mysql> show variables like '%innodb_io_capacity%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_io_capacity | 200   |
+--------------------+-------+
1 row in set (0.00 sec)

mysql>


mysql> show variables like '%innodb_doublewrite';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%_io_threads';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql>

Swapに関しての確認メモ
以下のようなコマンドで確認する事が出来る。

[root@HOME001 ~]#  /sbin/swapon -s
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       4161528 0       -1
[root@HOME001 ~]#

[root@HOME001 ~]# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       4161528 0       -1
[root@HOME001 ~]#


[root@HOME001 ~]#  cat /proc/meminfo
MemTotal:        1938948 kB
MemFree:         1679616 kB
Buffers:           10584 kB
Cached:           149116 kB
SwapCached:            0 kB
Active:            55040 kB
Inactive:         140456 kB
Active(anon):      36056 kB
Inactive(anon):        4 kB
Active(file):      18984 kB
Inactive(file):   140452 kB
Unevictable:           0 kB
Mlocked:               0 kB
HighTotal:       1189716 kB
HighFree:         995844 kB
LowTotal:         749232 kB
LowFree:          683772 kB
SwapTotal:       4161528 kB
SwapFree:        4161528 kB
Dirty:                 8 kB
Writeback:             0 kB
AnonPages:         35816 kB
Mapped:            13712 kB
Shmem:               264 kB
Slab:              37544 kB
SReclaimable:       7596 kB
SUnreclaim:        29948 kB
KernelStack:        1056 kB
PageTables:         1688 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     5131000 kB
Committed_AS:     341716 kB
VmallocTotal:     122880 kB
VmallocUsed:       13716 kB
VmallocChunk:      90828 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:       10232 kB
DirectMap2M:      897024 kB
[root@HOME001 ~]#




[root@HOME001 ~]# free -kt
             total       used       free     shared    buffers     cached
Mem:       1938948     259580    1679368          0      10836     149104
-/+ buffers/cache:      99640    1839308
Swap:      4161528          0    4161528
Total:     6100476     259580    5840896
[root@HOME001 ~]#


[root@HOME001 ~]# free -mt
             total       used       free     shared    buffers     cached
Mem:          1893        253       1640          0         10        145
-/+ buffers/cache:         97       1796
Swap:         4063          0       4063
Total:        5957        253       5704
[root@HOME001 ~]#


[root@HOME001 ~]# df -h /dev/mapper/vg_home001-lv_swap
Filesystem            Size  Used Avail Use% マウント位置
-                     940M  260K  940M   1% /dev
[root@HOME001 ~]# 

[root@HOME001 ~]# vmstat 10 -S M
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0   1639     10    145    0    0    21    10   27   64  0  0 98  1  0
 0  0      0   1639     10    145    0    0     0     0   10   41  0  0 100  0  0




InnoDB_Pluginについて

13.6. The InnoDB Storage Engineからの抜粋
At the 2008 MySQL User Conference, Innobase announced availability of an InnoDB Plugin for MySQL.
This plugin for MySQL exploits the “pluggable storage engine” architecture of MySQL.
The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB.
The version of the InnoDB Plugin is 1.0.6 as of MySQL 5.5.1 and is considered of Release Candidate (RC) quality.

The InnoDB Plugin offers new features, improved performance and scalability, enhanced reliability
and new capabilities for flexibility and ease of use.
Among the features of the InnoDB Plugin are ….. 以下プラグイン概要….

————————————————————————-
① Fast index creation
② Table and index compression
③ File format management
④ New INFORMATION_SCHEMA tables
⑤ Capacity tuning
⑥ Multiple background I/O threads
⑦ Group commit.
————————————————————————-

詳細は以下のinnodb.comを参照
InnoDB Plugin 1.0 for MySQL 5.1 (Early Adopter Release) User’s Guide

先日、MYSQL5.5をインストールしたので、Innodb_pluginはディフォルトで有効になっている。
以下のコマンドでバージョンを確認する事が出来ます。


select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
from information_schema.plugins;

SELECT @@innodb_version;

innodb_plugin_version

メモ: INFORMATION_SCHEMA.PLUGINSテーブルにはマイナーバージョン番号は表示されないようです。
Note that the PLUGIN_VERSION column in the table INFORMATION_SCHEMA.PLUGINS does not
display the third component of the version number, only the first and second components, as in 1.0.

その他留意事項
———————————————————————————————————-
1.5. Operational Restrictions
———————————————————————————————————-
Because the InnoDB Plugin introduces a new file format, with new on-disk data structures within
both the database and log files, there are important restrictions on the use of the plugin in typical user
environments. Specifically, you should pay special attention to the information presented here about
file format compatibility with respect to the following scenarios:

1) Downgrading from the InnoDB Plugin to the built-in InnoDB, or otherwise using different
versions of InnoDB with database files created by the InnoDB Plugin
2) Using mysqldump
3) Using MySQL replication
4) Using InnoDB Hot Backup

WARNING: Once you use the InnoDB Plugin on a set of database files, care must be taken to avoid
crashes and corruptions when using those files with an earlier version of InnoDB, as might happen by
opening the database with MySQL when the plugin is not installed. It is strongly recommended that
you use a “slow shutdown” (SET GLOBAL innodb_fast_shutdown=0) when stopping the MySQL server
when the InnoDB Plugin is enabled. This will ensure log files and other system information written by the
plugin will not cause problems when using a prior version of InnoDB.
11.3, “How to Downgrade”.

WARNING:If you dump a database containing compressed tables with mysqldump,
the dump file may contain CREATE TABLE commands that attempt to create compressed tables,
or those using ROW_FORMAT=DYNAMIC in the new database. Therefore, you should be sure
the new database is running the InnoDB Plugin, with the proper settings for innodb_file_format and innodb_file_per_table, if you want to have the tables re-created as they exist in the original database.
Typically, however, when the mysqldump file is loaded, MySQL and InnoDB will ignore
CREATE TABLE options they do not recognize, and the table(s) will be created in a format
used by the running server.

WARNING: If you use MySQL replication, you should be careful to ensure all slaves are configured
with the InnoDB Plugin, with the same settings for innodb_file_format and innodb_file_per_table.
If you do not do so, and you create tables that require the new “Barracuda” file format, replication
errors may occur. If a slave MySQL server is running the built-in InnoDB, it will ignore the
CREATE TABLE options to create a compressed table or one with ROW_FORMAT=DYNAMIC,
and create the table uncompressed, with ROW_FORMAT=COMPACT.

WARNING: The current version of InnoDB Hot Backup does not support the new “Barracuda” file
format. Using InnoDB Hot Backup Version 3 to backup databases in this format will cause
unpredictable behavior. A future version of InnoDB Hot Backup will support databases used
with the InnoDB Plugin. As an alternative, you may back up such databases with mysqldump.

———————————————————————————————————-

Innovative Technologiesfor Performance andData Protection

mysql> select Variable_name from
-> information_schema.GLOBAL_VARIABLES
-> where Variable_name like ‘innodb_%’;
+———————————+
| Variable_name |
+———————————+
| INNODB_VERSION |
| INNODB_LOCKS_UNSAFE_FOR_BINLOG |
| INNODB_BUFFER_POOL_SIZE |
| INNODB_CONCURRENCY_TICKETS |
| INNODB_OLD_BLOCKS_PCT |
| INNODB_LOG_BUFFER_SIZE |
| INNODB_MAX_PURGE_LAG |
| INNODB_DOUBLEWRITE |
| INNODB_IO_CAPACITY |
| INNODB_TABLE_LOCKS |
| INNODB_AUTOEXTEND_INCREMENT |
| INNODB_THREAD_SLEEP_DELAY |
| INNODB_REPLICATION_DELAY |
| INNODB_STATS_ON_METADATA |
| INNODB_ROLLBACK_ON_TIMEOUT |
| INNODB_CHANGE_BUFFERING |
| INNODB_FILE_FORMAT |
| INNODB_DATA_FILE_PATH |
| INNODB_STRICT_MODE |
| INNODB_MAX_DIRTY_PAGES_PCT |
| INNODB_AUTOINC_LOCK_MODE |
| INNODB_COMMIT_CONCURRENCY |
| INNODB_MIRRORED_LOG_GROUPS |
| INNODB_SUPPORT_XA |
| INNODB_SYNC_SPIN_LOOPS |
| INNODB_ADAPTIVE_FLUSHING |
| INNODB_ADAPTIVE_HASH_INDEX |
| INNODB_DATA_HOME_DIR |
| INNODB_READ_IO_THREADS |
| INNODB_WRITE_IO_THREADS |
| INNODB_FORCE_RECOVERY |
| INNODB_LOG_FILES_IN_GROUP |
| INNODB_OPEN_FILES |
| INNODB_FILE_FORMAT_CHECK |
| INNODB_READ_AHEAD_THRESHOLD |
| INNODB_LOG_GROUP_HOME_DIR |
| INNODB_FAST_SHUTDOWN |
| INNODB_THREAD_CONCURRENCY |
| INNODB_STATS_SAMPLE_PAGES |
| INNODB_FLUSH_LOG_AT_TRX_COMMIT |
| INNODB_FLUSH_METHOD |
| INNODB_CHECKSUMS |
| INNODB_LOG_FILE_SIZE |
| INNODB_SPIN_WAIT_DELAY |
| INNODB_FILE_PER_TABLE |
| INNODB_LOCK_WAIT_TIMEOUT |
| INNODB_OLD_BLOCKS_TIME |
| INNODB_ADDITIONAL_MEM_POOL_SIZE |
| INNODB_USE_SYS_MALLOC |
+———————————+
49 rows in set (0.00 sec)

mysql>

    新機能として

[innodb_file_format」
InnoDBのファイルフォーマットを指定可能
Antelope  従来のファイルフォーマット
Barracuda 圧縮機能サポートファイルフォーマット

innodb_file_format

検証
CREATE DATABASE `T_INNODB` /*!40100 DEFAULT CHARACTER SET utf8 */


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

ROW_FORMAT

* REDUNDANT
* COMPACT
* DYNAMIC (Barracuda format & No Compress)
* COMPRESSED: (Barracuda format & Compress)

innodb_plugin_test

よく見てみると、上記QueryはWarrningが出ていてテーブルが圧縮されていない事が分かった。

オプションファイルに以下の設定を入れて再度、MYSQLを再起動して設定を反映させた。
innodb_file_format= Barracuda

innodb_file_format_bara

mysql> show variables like 'innodb_file%';

barracuda

ファイルフォーマットがBarracudaに変わったので再度テーブルを作成してみる。


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 DEFAULT CHARSET=utf8
/* BLOCKサイズを4に変更した */;


select TABLE_SCHEMA,TABLE_NAME,ENGINE,ROW_FORMAT
from information_schema.tables
where TABLE_SCHEMA = 'T_INNODB';

innodb_file_formatを変更した事でwarrningが消えた事を確認
barracuda_confirm

実際のファイルを確認してみるとファイルサイズが違う。
※ ブロックサイズを8で作成してもファイルサイズはBarracudaの方が小さい。

compare

information_schema.INNODB_CMPの変化の確認。
圧縮を行った回数(compress_ops), 圧縮が成功した回数(compress_ops_ok)
などを見て圧縮への対応を検討していく事が可能です。


CREATE TABLE `T_Antelope_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

select * from information_schema.INNODB_CMP;

innodb_cmp

【メモ:パフォーマンス検証の際は、以下の値を確認】
innodb_buffer_pool_size
innodb_flush_methodをO_DIRECT

参考サイト
——————————————
INNOBASE
InnoDB Plugin Change History
13.6. The InnoDB Storage Engine
InnoDB Plugin 1.0.4 – InnoDB史上極めて重要なリリース



SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.tables
where TABLE_SCHEMA = 'information_schema';

mysql55_information_schema

■CHARACTER_SETS | MEMORY | 利用できる文字セットに関する情報
■COLLATIONS | MEMORY | 各文字セットの照合に関する情報
■COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY | どの文字セットがどの照合に適用できるかを示します
■COLUMNS | MyISAM | テーブルのカラムに関する情報
■COLUMN_PRIVILEGES | MEMORY | カラムの権限に関する情報
■ENGINES | MEMORY | ストレージ エンジンに関する情報
■EVENTS | MyISAM | 計画したイベントに関する情報
■FILES | MEMORY | NDBディスクデータテーブルが保存されるファイルに関する情報
■GLOBAL_STATUS | MEMORY | サーバーのステータス変数に関する情報(GLOBAL)
■GLOBAL_VARIABLES | MEMORY | サーバーのステータス変数に関する情報(GLOBAL)
■KEY_COLUMN_USAGE | MEMORY | どのキーカラムに制約があるかを説明
■PARTITIONS | MyISAM | テーブルの分割に関する情報 (パーティショニング)
■PLUGINS | MyISAM | サーバーのプラグインに関する情報
■PROCESSLIST | MyISAM | 動作しているスレッドに関する情報
■PROFILING | MEMORY | プロファイリング情報(SHOW PROFILES/SHOW PROFILE)
■REFERENTIAL_CONSTRAINTS | MEMORY | 外部キーに関する情報
■ROUTINES | MyISAM | 保存されたルーチンに関する情報
■SCHEMATA | MEMORY | データベースに関する情報
■SCHEMA_PRIVILEGES | MEMORY | スキーマ(データベース)権限に関する情報
■SESSION_STATUS | MEMORY | サーバーのステータス変数に関する情報(SESSION)
■SESSION_VARIABLES | MEMORY | サーバーのステータス変数に関する情報(SESSION)
■STATISTICS | MEMORY | テーブル インデックスの情報
■TABLES | MEMORY | データベースのテーブルに関する情報
■TABLE_CONSTRAINTS | MEMORY | テーブルに制約があるか情報
■TABLE_PRIVILEGES | MEMORY | テーブル権限に関する情報
■TRIGGERS | MyISAM | トリガに関する情報
■USER_PRIVILEGES | MEMORY | グルーバル権限に関する情報
■VIEWS | MyISAM | データベースの表示に関する情報
■INNODB_CMP_RESET | MEMORY | InnoDB Plugin関連(圧縮テーブルに関する情報/統計)
■INNODB_TRX | MEMORY | InnoDB Plugin関連(実行中のトランザクション)
■INNODB_CMPMEM_RESET | MEMORY | InnoDB Plugin関連(Buffer pool内の圧縮されたPageに関する情報)
■INNODB_LOCK_WAITS | MEMORY | InnoDB Plugin関連(Blocking/Requestingトランザクション情報)
■INNODB_CMPMEM | MEMORY | InnoDB Plugin関連(Buffer pool内の圧縮されたPageに関する情報)
■INNODB_CMP | MEMORY | InnoDB Plugin関連(圧縮テーブルに関する情報/統計)
■INNODB_LOCKS | MEMORY | InnoDB Plugin関連(ロック競合が発生しているトランザクション)

INFORMATION_SCHEMA ON MYSQL5.5 (基本)
information_schema_55_basic

INFORMATION_SCHEMA ON MYSQL5.5 (INNODB_PLUGIN関連)
information_schema_55_innodb_p

参考サイト
————————————————————————
Chapter 6. InnoDB INFORMATION_SCHEMA tables


以下のテーブルはi1 char(10)の列に `i1` (`i1`(3))と文字数を3文字に制限したインデックスを
作成してある。最初の3文字でデータが比較出来るようなSELECT文を実行した場合
参照するIndexのデータ量も少なくパフォーマンスが向上する。
但し、対外は色々なアプリケーションから一つのテーブルを参照する事が殆どだと思いますので
あまり利用する頻度は高くありません。列にNOT NULLを常に設定するなどと心がけて対応
する方が実際の運用では全体的なパフォーマンスを向上してくれるかと思います。


CREATE TABLE `fastindex` (
`i1` char(10) NOT NULL default '',
`i2` char(10) NOT NULL default '',
KEY `i1` (`i1`(3)),
KEY `i2` (`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

show_index_char

SELECT i1 FROM fastindex WHERE i1 LIKE 'abc%';
SELECT i2 FROM fastindex WHERE i2 LIKE 'abc%';

以下のEXPLAINではデータが殆ど入っていないので、PLANは変わりませんが
i1のインデックスは最初の3文字のみを利用して作成されているので、上記のSELECT
を実行した場合「WHERE i1 LIKE ‘abc%’;」の方が実行が早い。
index_fast

—————————————————————————————————————-
Indexing cannot only help speed up SELECT queries, but it can also improve
UPDATE and DELETE statements. This is because indexing can help the
server

    find the rows more quickly that should be updated or deleted

.
On the other hand, indexes will slow down UPDATE and DELETE statements because
not only the original data have to be updated but also the indexes.
—————————————————————————————————————-

WHERE, ORDER BY, GROUP BYを利用しない場合やデータの種類が少ない場合は
INDEXを付けてもパフォーマンスが向上する事は殆どありません。INSERT、DELETE、
UPDATEなどの処理でINDEXの更新も入るので遅くなる場合が考えられます。
インデックスが実際に利用されるかどうか常にイメージしながらINDEXを作成するように
心がけると良いかも知れません。

    以下のテーブルはName列に5文字を利用したIndexを付与してあります。


CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `idx_City_Name` (`Name`(5))
) ENGINE=MyISAM AUTO_INCREMENT=4083 DEFAULT CHARSET=utf8;

index_on_city

後からインデックスを追加する場合は、以下のようなコマンドを実行すれば良い。
——————————
mysql> CREATE INDEX Name ON City(Name(5));
mysql> ALTER TABLE City ADD INDEX(Name(5));

データのINSERT処理を早くするには?
————————————————————————————————————————-
You could use multiple-row inserts, rather than single-row inserts.
LOAD DATA INFILE will run even faster than any INSERT statement that inserts the
same amount of rows. For InnoDB tables, you could group inserts within a transaction
so that InnoDB will flush changes only when the transaction ends,
rather than after every single INSERT statement. If you’re planning to replace rows using
DELETE and INSERT, you could as well use the MySQL extension REPLACE that runs faster.

For a multiple-row INSERT statementでエラーが起きた場合………….
■ MyISAM では, エラーが起きるまでデータがINSERTされます。
■ InnoDBでは, Rolls backが起きてデータが一見もINSERTされずテーブルはEmptyのままになります。


テーブルを作成する時に、明示的にストレージエンジンを毎回指定
して作成しているか、既定のストレージエンジンを意図したものに設定して
いれば問題は無いが、他のDBと違いMYSQLには色々なストレージエンジン
があるのでオブジェクト作成する時には少しだけ考慮する事が必要。

+—————-+——–+
| Variable_name | Value |
+—————-+——–+
| storage_engine | MyISAM |
+—————-+——–+


mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> CREATE TABLE default_engine (id INT);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'default_engine'\G
*************************** 1. row ***************************
Name: default_engine
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-22 14:18:46
Update_time: 2009-07-22 14:18:46
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> SET storage_engine = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE set_default_engine (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLE STATUS LIKE 'set_default_engine'\G
*************************** 1. row ***************************
Name: set_default_engine
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 11534336
Auto_increment: NULL
Create_time: 2009-07-22 14:19:14
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql>

set_engine


mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> CREATE TABLE set_default_engine_manu (id INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'set_default_engine_manu'\G
*************************** 1. row ***************************
Name: set_default_engine_manu
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-22 14:24:17
Update_time: 2009-07-22 14:24:17
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

mysql>

set_engine_man

※ 稼動中に以下のコマンドでディフォルトストレージエンジンを変更可能。


mysql> set global storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> set @@global.storage_engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-07-23 00:14:31 |
+---------------------+
1 row in set (0.00 sec)

設定はDBに接続しなおすか、新規接続から有効になります。
しかしオプションファイルに記入しておかないと、
再起動と同時にディフォルトエンジンは元に戻ります。
コマンド:      mysqld –default-storage-engine=InnoDB
オプションファイル:  default-storage-engine=InnoDB
特定セッション: SET SESSION storage_engine=InnoDB;

※新規セッションから有効
set_global1
※再起動後の確認

[root@colinux ~]# /etc/init.d/mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@colinux ~]#
[root@colinux ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.01 sec)

mysql>


複数行をINSERTした時に列の長さを超えたデータと正常なデータをINSERTした
場合の結果は、SQL_MODEによって違う。

    以下の条件によっても結果は違う事に注意

MyISAM
InnoDB
STRICT_ALL_TABLES
STRICT_TRANS_TABLES


mysql> SELECT @@session.sql_mode;
+---------------------+
| @@session.sql_mode |
+---------------------+
| NO_AUTO_CREATE_USER |
+---------------------+
1 row in set (0.01 sec)


mysql> CREATE TABLE string_test (
-> comment varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc string_test;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'comment' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

string_test


mysql> SET @@session.sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES |
+--------------------+
1 row in set (0.00 sec)

mysql>


mysql> insert into string_test values('THIS IS LONG VALUE FOR VARCHAR(10)'), ('THIS IS OK');
ERROR 1406 (22001): Data too long for column 'comment' at row 1
mysql> select * from string_test;
+------------+
| comment |
+------------+
| THIS IS LO |
| THIS IS OK |
+------------+
2 rows in set (0.00 sec)

mysql>

strict