2015年8月22日土曜日

#yapcasia でMySQL 5.7の罠についてLTしてきました

YAPC::Asia Tokyo 2015 お疲れ様でした!

2日目のライトニングトークでしゃべらせていただきました。




ネタ的には 発掘するたび書き溜めてきたブログ記事 から 笑いが取れそうなものを 大事そうなもののみをピックアップして紹介した感じです。

知らないと致命傷、でも知ってれば予防できる(はず)
MySQL 5.7で不幸になる人が1人でも少なくなってくれることを願っています。


さて、今年のYAPC::Asiaはメイントラックもトークを応募していたのですが見事に落選したので、1日目は完全にリラックスして過ごしました。LTの採否、当日になるまでわかんないのか大変だなーとか、他人事だったんですが、

1日目のLT見るじゃないですか。

面白いじゃないですか。

俺もしゃべりたくなるじゃないですか。

なったんですよ!!1


が、翌朝になってLTの応募ページをたどってみると






しかしその後復活していたので




申し込み!




その1時間後にはもう締め切られていた! (あるいは、情けで応募させてくれたのかも。。)
とてもありがたいことに採択してもらえましたが、こういう悪ノリが出来るのもYAPCだからなのかなって思います。
(わたしはChiba.pm出身なので、たぶん結構ノリが近くて)

YAPCで「1年ぶりー」って挨拶した人もいっぱいいて、なんかこう同窓会っぽくて楽しいね、なんて話をしてたりもしました。


ありがとうYAPC::Asia 2015!!

2015年8月12日水曜日

An idea for using MySQL 5.7's generated column like CHECK constraint

This is translation for my early post in Japanese.

As of MySQL 5.7.6, generated column was introduced as 5.7's new feature.
I found that generated column could behave like CHECK constraint.
Let's start :)


mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------------+
| num | val        |
+-----+------------+
|   1 | 2015-08-06 |
+-----+------------+
1 row in set (0.00 sec)

First, there's the bad designed table which has DATE-string in its VARCHAR column.


mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Second, add VIRTUAL generated column `check_val` which calculates DATETIME from `val`'s DATE-string.


mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-09-31'

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
+-----+------------+---------------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
+-----+------------+---------------------+
2 rows in set (0.00 sec)

After that, you can see INSERT statement with incorrect DATE-string fails because of `check_val`'s CAST function raises warning and strict sql_mode can't allow that warning.


mysql57> SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql57> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2015-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
|   3 | 2015-11-31 | NULL                |
+-----+------------+---------------------+
3 rows in set, 1 warning (0.00 sec)

This way depends on sql_mode is strict or not.
Then I tried another way using IF function, which returns NULL when check condition is false, with NOT NULL constraint.


mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (1, 'Tokyo');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (2, 'Osaka');
Query OK, 1 row affected (0.01 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (3, 'Nara');
ERROR 1048 (23000): Column 'check_val' cannot be null

mysql57> SELECT * FROM t2;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
+-----+-------+-----------+
2 rows in set (0.00 sec)

I seem this approach is better.
NOT NULL constraint doesn't depend on sql_mode.

This generated column feature can be introduced into tables which have already been filled data.


mysql57> SELECT * FROM t3; -- Invalid data has already been inserted.
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL; -- Add VIRTUAL generated column.
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
|   3 | Nara  |         0 |
+-----+-------+-----------+
3 rows in set (0.00 sec)

mysql57> INSERT INTO t3 (num, val) VALUES (4, 'Kyoto');
ERROR 1048 (23000): Column 'check_val' cannot be null

With VIRTUAL generated column, the data which is already stored don't be affected by fake CHECK constraint.

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers.
http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

VIRTUAL generated `check_val` doesn't calculate at the time of ALTER TABLE, thus this case doesn't affect stored data but new data are under constraint.


mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null

The other hand, with STORED generated column, all exist data are calculated and checked during ALTER TABLE.
If the table already has invalid (for fake CHECK constraint) data, ALTER TABLE fails.

This is a stage of idea, is not practice in production environment.
But this idea maybe satisfy some case of "OMG, doesn't MySQL have CHECK constraint!?" situation.

2015年8月7日金曜日

MySQL 5.7のgenerated columnでついにCHECK制約っぽいことを実現できる

generated columnそのものの説明はこのへんに。
日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか

というわけでやってみましょう。


mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------------+
| num | val        |
+-----+------------+
|   1 | 2015-08-06 |
+-----+------------+
1 row in set (0.00 sec)

こんなテーブルがあったとするじゃろ?


mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

valの中身をdatetimeにキャストする関数を食わせたgenerated columnを作るじゃろ?


mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-09-31'

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
+-----+------------+---------------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
+-----+------------+---------------------+
2 rows in set (0.00 sec)

なんと、日付として不正な値を入れると、キャストに失敗してエラーが返るんじゃよ!


mysql57> SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql57> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2015-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
|   3 | 2015-11-31 | NULL                |
+-----+------------+---------------------+
3 rows in set, 1 warning (0.00 sec)

もちろん、sql_modeがSTRICT_TRANS_TABLESかSTRICT_ALL_TABLESでないと、キャストの失敗を丸めちゃうので台無しになる。まさかもう非strict_modeとか選ばない…よね?
datetime型の場合はNOT NULL制約をつけても0000-00-00 00:00:00で丸めちゃうからアレだけど、IFをかませてtinyintとかにすると


mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('東京', '大阪'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (1, '東京');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (2, '大阪');
Query OK, 1 row affected (0.01 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (3, '奈良');
ERROR 1048 (23000): Column 'check_val' cannot be null

mysql57> SELECT * FROM t2;
+-----+--------+-----------+
| num | val    | check_val |
+-----+--------+-----------+
|   1 | 東京   |         1 |
|   2 | 大阪   |         1 |
+-----+--------+-----------+
2 rows in set (0.00 sec)

おおおこれ結構良くない? このIF関数で無理矢理NULLを返す方式だと、NOT NULL制約の問題なので非strict_modeでもちゃんとエラーになってくれる。わーい(死)

ちなみに、既に格納されている値に対して後からこの似非CHECK制約を追加する場合、


mysql57> SELECT * FROM t3;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | 東京   |
|   2 | 大阪   |
|   3 | 奈良   |
+-----+--------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+--------+-----------+
| num | val    | check_val |
+-----+--------+-----------+
|   1 | 東京   |         1 |
|   2 | 大阪   |         1 |
|   3 | 奈良   |         0 |
+-----+--------+-----------+
3 rows in set (0.00 sec)

mysql57> INSERT INTO t3 (num, val) VALUES (4, '京都');
ERROR 1048 (23000): Column 'check_val' cannot be null

generated columnの種類を指定しない場合、暗黙のデフォルトとしてVIRTUALになるので、
「VIRTUALなgenerated columnの場合は、今までのものは無視されて、新たに更新するものに対して有効」(VIRTUALなgenerated columnはALTER TABLEの時には何もせず、SELECTされた時に都度関数が走るから)になり、


mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | 東京   |
|   2 | 大阪   |
|   3 | 奈良   |
+-----+--------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null

「STOREDなgenerated columnの場合は、今までのもので似非CHECK制約を通らない行がある場合、そもそもALTER TABLEが通らない」(STOREDなgenerated columnはALTER TABLEの時に関数を通って計算され、それがデータに格納されるから)になる。


おおお面白いんじゃないこれ?


【2015/08/12 10:52】
I translated this post into English, See An idea for using MySQL 5.7's generated column like CHECK constraint

2015年8月4日火曜日

MySQL 5.7.8以降のrpmパッケージではvalidate_passwordプラグインがデフォルトで有効になっている

というよりは、今までが「デフォルトで入っているつもりが入っていなかった」ので、そのバグをFIX、ということらしい。

The validate_password plugin was not installed by RPM packages for platorms using systemd or SysV-style initialization scripts. (Bug #18438833)
MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.8 (2015-08-03)


やったね! 初心者殺しの罠が一つ増えたよ! orz

validate_passwordプラグインについては5.6で導入された機能なので、日本語マニュアルが既にある。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.1.2.6 パスワード検証プラグイン

これが有効になっているということはどういうことかというと、

$ /etc/init.d/mysqld start
Initializing MySQL database:  2015-08-03T15:33:57.341022Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

$ grep password /var/log/mysqld.log
2015-08-03T15:33:57.971473Z 1 [Warning] A temporary password is generated for root@localhost: =w?e&SnQf0aK
2015-08-03T15:34:03.509853Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)

$ mysql -p
Enter password:

mysql> SET PASSWORD= '';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

まあ、こういうことですよね。。


mysql> SHOW GLOBAL VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

デフォルトでは8文字以上、英大文字小文字数字記号の4種類を含む必要がある。
この条件を満たしたパスワードに変更するまで、初期セットアップ状態だとパスワードがEXPIREされた状態なので、SHOWコマンドもSETコマンドも効かない。ははは。

そんな都合の良い文字列パッと思い付かねーよ! って場合は、もとのパスワードをそのまま入れてしまえばいい。なんと通る。
それか、"Do_you_love_MySQL57?"もこの条件を満たすので使ってもいいですよ :)


mysql> SET PASSWORD= '=w?e&SnQf0aK';
Query OK, 0 rows affected (0.01 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.01 sec)

あとはSET GLOBALで強度を弱めるなり、UNINSTALL PLUGINするなりご自由に…という感じ。

初期のランダムパスワードが/var/log/mysqld.logにあることと合わせて、インストールでくじけさせてくれる新機能でした。

MySQL 5.7.8 透過InnoDBページ圧縮のサイズ計り比べ…の前に、取り敢えず有効化するところまで

今までのInnoDB圧縮(ROW_FORMAT= Compress)は InnoDBテーブル圧縮 、新しいやつは InnoDBページ圧縮 としてドキュメントに載っている。

今までのInnoDBテーブル圧縮はzlibのみの対応で、圧縮後のデータの詰め直しなども全部InnoDB側で対応していた。そのデータの詰め直しをファイルシステム側に任せることで、CPUバウンドだった圧縮処理を軽く/圧縮効率を良くしよう、というアレだと認識している。
詳しくはこちら。 InnoDB Transparent PageIO Compression | MySQL Server Blog


CentOS 6.6(2.6.32-504.30.3.el6.x86_64)のext4ではsupport not availableと言われるのが

$ less /var/log/mysqld.log
..
2015-07-22T09:23:24.769459Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.8-rc-log) starting as process 403 ...
2015-07-22T09:23:24.774797Z 0 [Note] InnoDB: PUNCH HOLE support not available
..


CentOS 7.1(3.10.0-229.el7.x86_64)のxfsだとちゃんと有効になっているぽい。

$ less /var/log/mysqld.log
..
2015-07-22T09:21:44.321951Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.8-rc) starting as process 3355 ...
2015-07-22T09:21:44.326762Z 0 [Note] InnoDB: PUNCH HOLE support available
..


$ mysql -sse "show tables" tpcc | while read table ; do
> mysql -e "ALTER TABLE $table Compression= 'zlib'"
> done

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: customer
         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: 0
 Auto_increment: NULL
    Create_time: 2015-07-22 18:45:09
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: COMPRESS="zlib"
        Comment:
..

Create_options: COMPRESS="zlib"になった。



20WH突っ込んでみたところ。


無圧縮。

$ ll -h
total 1.8G
-rw-r----- 1 mysql mysql 9.2K Jul 22 19:34 customer.frm
-rw-r----- 1 mysql mysql 392M Jul 22 19:33 customer.ibd
-rw-r----- 1 mysql mysql   65 Jul 22 19:24 db.opt
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:34 district.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:32 district.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:34 history.frm
-rw-r----- 1 mysql mysql  72M Jul 22 19:34 history.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 19:24 item.frm
-rw-r----- 1 mysql mysql  17M Jul 22 19:24 item.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 19:34 new_orders.frm
-rw-r----- 1 mysql mysql  13M Jul 22 19:33 new_orders.ibd
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:34 order_line.frm
-rw-r----- 1 mysql mysql 572M Jul 22 19:34 order_line.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:34 orders.frm
-rw-r----- 1 mysql mysql  48M Jul 22 19:33 orders.ibd
-rw-r----- 1 mysql mysql 9.0K Jul 22 19:34 stock.frm
-rw-r----- 1 mysql mysql 692M Jul 22 19:33 stock.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:24 warehouse.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:32 warehouse.ibd


InnoDBページ圧縮(zlib)。

$ ll -h
total 1.3G
-rw-r----- 1 mysql mysql 9.2K Jul 22 19:15 customer.frm
-rw-r----- 1 mysql mysql 392M Jul 22 19:14 customer.ibd
-rw-r----- 1 mysql mysql   65 Jul 22 18:42 db.opt
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:15 district.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:05 district.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:15 history.frm
-rw-r----- 1 mysql mysql  72M Jul 22 19:15 history.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 18:45 item.frm
-rw-r----- 1 mysql mysql  17M Jul 22 18:56 item.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 19:15 new_orders.frm
-rw-r----- 1 mysql mysql  13M Jul 22 19:05 new_orders.ibd
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:15 order_line.frm
-rw-r----- 1 mysql mysql 564M Jul 22 19:15 order_line.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:15 orders.frm
-rw-r----- 1 mysql mysql  48M Jul 22 19:14 orders.ibd
-rw-r----- 1 mysql mysql 9.0K Jul 22 19:15 stock.frm
-rw-r----- 1 mysql mysql 692M Jul 22 19:14 stock.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 18:45 warehouse.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:04 warehouse.ibd


すいません性能とか圧縮率とかまだ真面目に調べられてますん><

MySQL 5.7.8のリリースノートななめ読み(変更になったパラメーターとか)

概要だけでベンチマークとかしてないです。あとななめ読みなので俺の興味のないところ(空間インデックスとか)はかっ飛ばしてます。

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.8 (Not yet released)


* innodb_adaptive_hash_index_parts
  * Adaptive Hashのラッチを分割できるらしい。暗黙のデフォルトは8 (以前は1固定)
    * Adaptive Hashが有効だと性能が劣化する書き込みバウンドのケースで効くか?

innodb_log_checksum_algorithm
  * innodb_checksum_algorithm(や、旧innodb_checksums)がInnoDBテーブルスペースファイルのチェックサムだったのに対して、こっちはInnoDBログファイルに対するチェックサム。
    * 今までログのチェックサムは取られていなかったのだとしたら、チェックサムを取るってだけで性能が落ちそう。
  * 暗黙のデフォルトはinnodb(= ソフトウェア計算チェックサム)なので、書き込みバウンドだと性能が劣化するかも。
    * innodb_checksum_algorithmと同じく、crc32がいいと思われる。

* innodb_flush_sync
  * 今まではチェックポイント中もinnodb_io_capacityの値に従ってI/O量を制御していたけれど、チェックポイント中ってことはトランザクション止まってるのでinnodb_io_capacityの値を無視して全力でI/O動かせばいいじゃーん、っていうパラメーターっぽい。
  * 暗黙のデフォルトはON、チェックポイント中は全力でI/Oする。

* innodb_purge_threads, innodb_page_cleaners
  * 1から4に増えてる。

* /etc/init.d/mysqld stopのタイムアウトが600秒に
  * 今までは60秒

* super_read_only
  * Super_privを持ってるユーザーでもread_onlyを強制できるオプション。
  * 日々の覚書: Percona Server 5.6.21にsuper_read_onlyが来ました

* disabled_storage_engines
  * そう! MyISAM利用禁止とかできるんですよ奥さん!
    * ちなみに「今既にあるMyISAMテーブル」には何の影響も及ぼさない(= 読み込みも書き込みも可能。TRUNCATEもできた)

mysql> SELECT @@disabled_storage_engines;
+----------------------------+
| @@disabled_storage_engines |
+----------------------------+
| MyISAM                     |
+----------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE d1.t1 (num int) Engine= MyISAM;
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).

* innodb_undo_tablespaces
  * 上限が126 => 95へ
  * とはいえそんなに大きくしなくても、innodb_undo_log_truncate に必要な2で特に困ってない。
    * 日々の覚書: MySQL 5.7.5のInnoDB undo log truncationを試してみた


なんかもうちょっとあったような。。

MySQL 5.7.8をrpmで入れると、初回のservice mysqld startのタイミングでSSL証明書も作ってくれる

日々の覚書: MySQL 5.7.6からSSL周りのセットアップが扱いやすくなった のmysql_ssl_rsa_setup がそのまま/etc/init.d/mysqld の中で呼ばれるようになった…というだけの話なんですが、理屈を知らないとエラーログが矛盾してそうに思えることになっていたので解説がてら。


# rpm -i mysql-community-server-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-common-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-client-5.7.8-0.3.rc.el5.x86_64.rpm mysql-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-libs-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-libs-compat-5.7.8-0.3.rc.el5.x86_64.rpm
..

# less /var/log/mysqld.log
2015-07-15T07:44:47.363530Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-07-15T07:44:47.504281Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-07-15T07:44:47.582258Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5dd4d949-2ac5-11e5-8a55-0242ac11000a.
2015-07-15T07:44:47.586384Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2015-07-15T07:44:47.586799Z 0 [Warning] Failed to setup SSL
2015-07-15T07:44:47.586825Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-07-15T07:44:47.587546Z 1 [Warning] A temporary password is generated for root@localhost: 0J:ra.dZgrmF
150715 16:44:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-07-15T07:44:52.939037Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-07-15T07:44:52.940030Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.8-rc) starting as process 413 ...
..
2015-07-15T07:44:53.156280Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2015-07-15T07:44:53.156620Z 0 [Warning] CA certificate ca.pem is self signed.
2015-07-15T07:44:53.156875Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2015-07-15T07:44:53.156942Z 0 [Note] IPv6 is available.
2015-07-15T07:44:53.156952Z 0 [Note]   - '::' resolves to '::';
2015-07-15T07:44:53.156957Z 0 [Note] Server socket created on IP: '::'.
2015-07-15T07:44:53.158530Z 0 [Note] InnoDB: Buffer pool(s) load completed at 150715 16:44:53
2015-07-15T07:44:53.173689Z 0 [Note] Event Scheduler: Loaded 0 events
2015-07-15T07:44:53.174035Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.8-rc'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

* "[Warning] Failed to setup SSL"と言いながら
* "[Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them." と言って
* "[Warning] CA certificate ca.pem is self signed." とまとめる。


というのは、1つ目の"Failed to setup SSL"はmysqld --initializeのタイミングで呼ばれているので、この時点ではSSL証明書がなくてmysqld起動時のSSL有効化に失敗する。
/etc/init.d/mysqldの中では、 mysqld --initialize => mysql_ssl_rsa_setup => mysqld_safe の順番で呼んでいるので、
2つ目の"Found ca.pem"はmysqld_safeから起動されたmysqldが、その前に走っていたmysql_ssl_rsa_setupで作成されたSSL証明書を拾ってSSLを有効化にして起動している。
3つ目は読んで字のごとく。

5.7.5以降 mysqldの--ssl がデフォルトで有効になっているので、クライアント側でca.pemを使ってSSL接続するようにしさえすればそれだけでOK。