GA

2016/12/23

MySQLのCOUNTを速くする(?)SQL1本ノック その2

この記事は MySQL Casual Advent Calendar 2016 の23日目の記事です。
そしてどうやら、 日々の覚書 の400本目の公開記事です。そんなに書いてたのか。。

前回 のあらすじ。


mysql57> SHOW CREATE TABLE game_score\G
*************************** 1. row ***************************
       Table: game_score
Create Table: CREATE TABLE `game_score` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `play_end_time` datetime NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `game_id` (`game_id`),
  KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.37 sec)

COUNTが遅い! のでサマリーテーブルを作ってトリガーを仕掛けた! これで勝つる!
(前回とはPRIMARY KEYの順番を変えてあります。1個キーが追加されたのは、FKが勝手に作ったからです)


mysql57> SHOW CREATE TABLE game_score_summary\G
*************************** 1. row ***************************
       Table: game_score_summary
Create Table: CREATE TABLE `game_score_summary` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`play_date`,`game_id`),
  KEY `game_score_summary_ibfk_1` (`game_id`),
  CONSTRAINT `game_score_summary_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary (game_id, play_date, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-19 11:53:52.69
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-19 |       4610 |
|       1 | 2016-12-19 |       4751 |
|       2 | 2016-12-19 |       4532 |
|       3 | 2016-12-19 |       4470 |
|       4 | 2016-12-19 |       4581 |
|       5 | 2016-12-19 |       4534 |
|       6 | 2016-12-19 |       4522 |
|       7 | 2016-12-19 |       4472 |
|       8 | 2016-12-19 |       4583 |
|       9 | 2016-12-19 |       4633 |
+---------+------------+------------+
10 rows in set (0.00 sec)

だが、この設計ではボトルネックを生むのであった_| ̄|○
ちなみにこの段階でボトルネックにならない場合、ボトルネックになるまではここから先に書いてあるようなことは考えなくて良いと思う。

さて、このテーブルのPRIMARY KEYは(play_date, game_id) で、play_date は game_score.play_end_time から派生してくる。game_score.play_end_timeはおそらく NOW() か、それに類するものから派生する。

ということはつまり、このサマリーテーブルはほぼ game_id 単位でしかロックを持たないことになる。
1分に数回しかプレイされないような環境ならまだしも、秒間数回(これはテキトー。 実際にはもっと叩かれていたし フィクションです)となってくるとこれでは詰まる。

パフォーマンスは失われないように、ロックの粒度は大きくなりすぎないように、単位を分割するのがここでのたしなみ。
もちろん、ロックを放棄して走り去るなどといった、はしたないDBAなど存在していようはずもない。

という訳でいくつかの分割案。


1. mysqldでのスレッド単位

コネクションプール環境ならこれが一番確実だと思う。MySQLには mysqld上のprocesslist_idを参照できる CONNECTION_ID関数と pseudo_thread_idセッション変数があるので、これをトリガーに埋め込むことができる。


mysql57> SHOW CREATE TABLE game_score_summary_by_thread\G
*************************** 1. row ***************************
       Table: game_score_summary_by_thread
Create Table: CREATE TABLE `game_score_summary_by_thread` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `processlist_id` bigint(20) unsigned NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`play_date`,`game_id`,`processlist_id`),
  KEY `game_id` (`game_id`),
  CONSTRAINT `game_score_summary_by_thread_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_by_thread_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary_by_thread (game_id, play_date, processlist_id, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), @@pseudo_thread_id, 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-22 12:11:37.18
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql57> SELECT game_id, SUM(count_star) AS count_star FROM game_score_summary_by_thread WHERE play_date = '2016/12/22' GROUP BY game_id;
+---------+------------+
| game_id | count_star |
+---------+------------+
|       0 |       4275 |
|       1 |       4279 |
|       2 |       4240 |
|       3 |       4084 |
|       4 |       4304 |
|       5 |       4231 |
|       6 |       4406 |
|       7 |       4254 |
|       8 |       4166 |
|       9 |       4253 |
+---------+------------+
10 rows in set (0.00 sec)

これで、ロックの粒度をスレッド単位で分割できる。当然ながら1スレッド単位で見れば完全に直列するので、ロックの競合は発生しない。フェッチする行数は game_id * コネクションプールから張られる総数で済むので、GROUP BYでSUMしても大した負荷ではない。

コネクションプール以外の環境(つまり、トランザクションの都度接続して切断するスタイル)の場合、pseudo_thread_idはどんどんインクリメントされるので、つまりgame_scoreからカウントしてるのと変わらなくなってしまってダメ。COUNTじゃなくてSUMな分悪くなると思う。

じゃあ…


2. 接続元ホスト単位

個人的にはイマイチ好きじゃないけど一つの案。
pseudo_thread_idとは違って、同じホストから複数のクエリーが同時に㌧で来るのは十分あり得るので、「スケールアウトよりもスケールアップ」で戦っているケースだとあんまりロックが分割されない。

それでも分割しないよりはマシであろうとは思うけれど、MySQLの中からクライアントの接続元を一発で引ける変数や関数はないので、クライアント側で「トリガーの中で使っているセッション変数に接続元をセットしてやる」か、「トリガーを諦めて同じトランザクションの中で一緒にサマリーの方も更新するか」のどちらかになる。

1ホスト1アカウントになるようにアカウントをIP決め打ちで設定していれば CURRENT_USER関数が使えそう。文字列型になるのでハッシュ化したとしてもちょっと容量かさむけれども。

サマリーテーブルやトリガーやSUMは省略。


3. 時間を使って分割

MySQL 5.6とそれ以降ではNOW関数がマイクロ秒まで出してくれるので、マイクロ秒単位の下2桁で分解する。具体的には RIGHT(NOW(6), 2) で分解してみた。

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_by_time_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary_by_time (game_id, play_date, fragment, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), RIGHT(NOW(6), 2), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-22 19:08:12.68
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)


これが驚くくらい詰まらずにちゃんと分割された。こんなにうまくいくとは思ってなかった。びっくり。


というわけで、

- Connection Poolなら pseudo_thread_id
- それ以外で
  - MySQL 5.6とそれ以降なら RIGHT(NOW(6), 2)
  - MySQL 5.5とそれ以前なら CURRENT_USER() か アプリから文字列渡し

って感じで俺の中では落ち着きました。

明日はクリスマスイブ、カミポゥ さんです!

2016/12/19

MySQLのCOUNTを速くする(?)SQL1本ノック その1

この記事は MySQL Casual Advent Calendar 2016 の19日目の記事です。

「MySQLのCOUNTが遅いなー」って言うと、「トリガーでサマリーテーブル作ったら?」と言われること、多くありませんか? 特に @soudai1025 に言われる率が高い気がしています(個人の感想です)

個人的にはトリガーよりは同じトランザクションの中でカウンターテーブル(サマリーテーブルと違うもののつもりで言ってるけど、世の中でいうサマリーテーブルの中にはこの形式が含まれるかも知れない)をUPDATEする方が好き(だって、トリガーはブラックボックスになるんだもの)なんですが、頭の体操ということでトリガーで組んでみましょう。


サンプルテーブルはこんな感じ。


mysql57> SHOW CREATE TABLE game_score\G
*************************** 1. row ***************************
       Table: game_score
Create Table: CREATE TABLE `game_score` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `play_end_time` datetime NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `game_id` (`game_id`),
  KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4


mysql57> SELECT * FROM game_score LIMIT 3;
+-----+---------+---------+---------------------+--------+
| seq | game_id | user_id | play_end_time       | score  |
+-----+---------+---------+---------------------+--------+
|   1 |       9 |    2445 | 2016-12-19 11:18:32 | 981760 |
|   2 |       9 |    1440 | 2016-12-19 11:18:33 | 108172 |
|   3 |       6 |    7879 | 2016-12-19 11:18:34 | 180756 |
+-----+---------+---------+---------------------+--------+
3 rows in set (0.00 sec)


こんなテーブルの中から、「今日、各ゲームが何回プレイされたか」を抽出するクエリーは

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.37 sec)

おそらくこんなん。
死ぬほど遅いのは、GROUP BY狙いのキー(game_id)を使っちゃってるから。WHERE狙いのキー(play_end_time)に変えさせれば100msくらいにはなる。それでも遅いけど。

これをトリガーに突っ込む。日付の条件は日々変わるだろうから、DATE型までサマライズしたカラムも必要かな。あと、game_scoreはログテーブルなのでINSERT以外は走らないものとする。おそらくバッチでDELETEはするだろうけど、それは取り敢えず置いておく(一緒に消せばいいだけなので)
とするとこんな感じだろうか。


mysql57> SHOW CREATE TABLE game_score_summary\G
*************************** 1. row ***************************
       Table: game_score_summary
Create Table: CREATE TABLE `game_score_summary` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`game_id`,`play_date`),
  CONSTRAINT `game_score_summary_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary (game_id, play_date, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-19 11:53:52.69
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)


INSERT INTO .. ON DUPLICATE KEY UPDATEで「無ければ1、あればインクリメント」にしてる。
30万行くらい(別の日付で)追加で突っ込んでみる。

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/25' AND '2016/12/26' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     8817 |
|       1 |     8513 |
|       2 |     8655 |
|       3 |     8782 |
|       4 |     8664 |
|       5 |     8643 |
|       6 |     8525 |
|       7 |     8741 |
|       8 |     8633 |
|       9 |     8428 |
+---------+----------+
10 rows in set (1.23 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/25';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-25 |       8817 |
|       1 | 2016-12-25 |       8513 |
|       2 | 2016-12-25 |       8655 |
|       3 | 2016-12-25 |       8781 |
|       4 | 2016-12-25 |       8664 |
|       5 | 2016-12-25 |       8643 |
|       6 | 2016-12-25 |       8525 |
|       7 | 2016-12-25 |       8741 |
|       8 | 2016-12-25 |       8633 |
|       9 | 2016-12-25 |       8428 |
+---------+------------+------------+
10 rows in set (0.00 sec)

おっと…play_dateにインデックス張るの忘れてたけど、追加してやればシンプルな10行フェッチで済む(PRIMARY (play_date, game_id) にする方が筋がいいかな)

このトリガーを張った瞬間より前の履歴については整合性が保証できないので、一度更新をブロックしてgame_score_summaryにマージしてやるか、諦めて「明日以降のやつはsummaryから取って、今日以前のやつはgame_scoreから取る」というロジックをアプリケーション側に入れ込むか。


mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.08 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
Empty set (0.00 sec)

mysql57> LOCK TABLE game_score READ, game_score_summary WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql57> INSERT INTO game_score_summary SELECT game_id, MAX(DATE(play_end_time)) AS play_date, COUNT(*) AS count_star FROM game_score WHERE play_end_time < '2016/12/20' GROUP BY game_id;
Query OK, 10 rows affected (0.08 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql57> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-19 |       4610 |
|       1 | 2016-12-19 |       4751 |
|       2 | 2016-12-19 |       4532 |
|       3 | 2016-12-19 |       4470 |
|       4 | 2016-12-19 |       4581 |
|       5 | 2016-12-19 |       4534 |
|       6 | 2016-12-19 |       4522 |
|       7 | 2016-12-19 |       4472 |
|       8 | 2016-12-19 |       4583 |
|       9 | 2016-12-19 |       4633 |
+---------+------------+------------+
10 rows in set (0.00 sec)

LOCK TABLEするならこんな感じで。game_score_summaryだけロックしておけばgame_score側は要らないかも知れない(AFTER INSERTトリガーが止まるから)

実はこのテーブル構造、致命的に性能の欠陥があるので 次回 に続く。


明日は ogataka50 さんです!


【2016/12/19 14:39】

ちなみに、サンプルデータを作るのに使ったスクリプトはこちらです。
https://gist.github.com/yoku0825/0608014dcfd3e1cd244a51ef7e590ca0

2016/12/12

試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた

いやあ試された試された。

- (金曜日) 昼の便で夕方には市内に着く予定だったのが出発遅延、チェックインしたのは日付が変わったくらい

- (土曜日) (ちゃんと間に合う時間に)目を覚ましたら、外がなんか人生初体験レベルの積雪だった。東京と同じ感覚で新雪を踏もうとしたらズボっといった

- (土曜日) @kunihirotanakaさん@yoheiaさん が色々似ていることに懇親会で気付く。そしてやっぱり @yoheiaさん のアイコンは貯金箱だと認識されていた

- (日曜日) まさかの止まない雪。続々とやられていく首都圏のPerl Mongerたち。そんな中、かろうじて搭乗手続きが始まった便に乗り込むyoku0825を待っていたものとは…?

- (月曜日) 羽田についたけど、終電がないよ。ぽえーん。


その他、 #yapcjapan で検索すると色々阿鼻叫喚の声や大地の白さを記録したブログなど色々見つかると思います。


北の大地の洗礼をたっぷり受けながらも なんとか日付が変わる前に着陸できそうということは、 日付変わりましたが無事着陸できたお祝いを兼ねて(?) 11日目がんばれって🐬の神様が言ってるんですねそうですね。

というわけで、この記事は MySQL Casual Advent Calendar 2016 の11日目の記事です!



YAPC::Hokkaido 2016 Sapporo で朝イチのセッションでした。同じ時間が @kazuhoさん のキーノートだったので、迷ってくれた方もいらしたようでした。ありがたやありがたや。

@songmuさん に「再演しますか?」って聞いてもらったんですが、過去のもの含め再演は機会(= どこかの勉強会とか、社内勉強会とか、自分で運営せずにお呼ばれできるものなら何でも…) さえあればいくらでも再演しますので、興味のある方はお声がけください!







OSC広島で(というか、 遡れば去年のYAPC::Asia 2015の時から ) @sakaikさん に、「yokuさんを知ってる人は楽しんでくれると思うけど、yokuさんをよく知らない人はあの言葉の通りにMySQLの印象を持っちゃうよ」と言われていくらか悩んだ結果、

今回は「いかにもMySQLが好きで定点観測してる人が、自分の印象を語る」という切り口で行きました。

「5.6から5.7がこうだったから、8.0もこうなるんじゃないか」
「5.7でここがダメだったから、8.0はそこを補ってくれるんじゃないか」
「MySQL Xはどこに行くんだろう」
「罠~罠だよ~」

そんなドキドキとワクワク とピキピキ が伝われば幸いです。

Have fun!!


そして、やっぱり同窓会っぽくとんでもなく楽しいYAPCを届けてくれたJPAのみなさま、コアスタッフのみなさま、当日スタッフのみなさま、本当にありがとうございました!

2016/12/10

MySQLのビルド環境にConoHaを選んでいる理由

この記事は ConoHa Advent Calendar 2016MySQL Casual Advent Calendar 2016 の10個目の窓です。

俺は とある企業のDBA なので、基本的にコードは書きません。Bot書いたりとか運用ツール書いたりとかMySQLにパッチ当てたりだとかそのくらいですね。意外と書いてた。

Botはついでに動かしているだけで、もともとBotのためにConoHaを使っているわけでもなかったし(当時はCPU2コア, メモリー1GB, HDD 100GBが最小プランだったけど、Botに使うにはちょっとオーバースペック)、じゃあ何に使っているのかというと

MySQLのビルド なわけですが。これがConoHaだとかなり良い。とても良い。
というか最近、ソースコードも量が増えて、へたれな仮想サーバーとかPCのVirtualBoxだと まともな時間でビルドが終わらない ようになってきました。特に5.7から先。10時間かかっても終わらないとか挙句OOM Killerに殺されちゃったりとかなってきました。つらい。

その点ConoHaだと8.0.0でも1時間あればお釣りがくるくらいなので助かっています…というか本当に助かったこれは。、

まずMySQLおじさんたるもの、取り敢えず新しいマイナーバージョンが出たらそれをビルドします。pre 5.0は捨てるにしても、5.0.96, 5.1.73, 5.5.53, 5.6.34, 5.7.16, 8.0.0の5つのバージョンのバイナリーとソースコードを保管しないといけない訳です。

あとたまに触りたくなると(触ったあとに消すけど)lab版とかPercona ServerやMariaDB、MySQL Clusterもビルドすることになるので、常時6~8個のmysqldは常に置いておかないといけません。


$ du -sch /usr/mysql/*
104M    /usr/mysql/5.0.96
306M    /usr/mysql/5.1.73
385M    /usr/mysql/5.5.53
676M    /usr/mysql/5.6.34
1.2G    /usr/mysql/5.7.16
1.2G    /usr/mysql/8.0.0
1.6G    /usr/mysql/http
1.2G    /usr/mysql/labs
6.5G    total


あとはもちろんソースコードと、コンパイルしたバイナリーは残しておかないといけません。デバッグとか、動作を理解するためにgdb刺してステップ実行とかは結構します。

$ du -shc mysql-*[0-9]
208M    mysql-5.0.96
503M    mysql-5.1.73
806M    mysql-5.5.53
1.4G    mysql-5.6.34
4.4G    mysql-5.7.16
15M     mysql-connector-java-5.1.40
2.3M    mysql-connector-python-2.1.3
8.9M    mysql-router-2.0.3
7.3G    total


複数台まとめて検証したりするにはDockerが便利ですよね。

$ docker images
REPOSITORY                                TAG                    IMAGE ID            CREATED             SIZE
docker.io/groonga/mroonga                 latest                 a0b64d145f97        5 weeks ago         1.236 GB
docker.io/groonga/mroonga                 mysql5634_mroonga610   6b31bf4e0d48        5 weeks ago         1.236 GB
docker.io/groonga/mroonga                 mysql5716_mroonga610   5c849df969e4        5 weeks ago         1.778 GB
docker.io/groonga/mroonga                 mysql5634_mroonga609   1a7d7e2ba454        6 weeks ago         1.234 GB
docker.io/centos                          centos6.6              d03626170061        3 months ago        202.6 MB
docker.io/centos                          centos6.8              0cd976dc0a98        3 months ago        194.5 MB
docker.io/centos                          centos5                1ae98b2c895d        3 months ago        284.7 MB
docker.io/yoku0825/mysql_router           latest                 4e14f7e09d50        3 months ago        519.6 MB
docker.io/yoku0825/mysql_fabric_command   latest                 6a4304d6b8a2        6 months ago        556.8 MB
docker.io/yoku0825/mysql_fabric_aware     latest                 2fac9fb7cc7e        6 months ago        1.664 GB
docker.io/yoku0825/mysql_fabric_server    latest                 e45808b5e20b        6 months ago        1.673 GB
docker.io/groonga/mroonga                 mysql5623_mroonga410   37f49ca977bc        9 months ago        1.132 GB


とするとこうなりますよね。

$ df -h .
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   48G   44G  3.6G  93% /


CentOS 7.2でDocker最新版使おうと思って旧のHDD 100GBから乗り換えなければよかった。。追加SSD、流石に200GBは要らないんだよなあ。。50GB単位とかになったら嬉しい。


取り敢えず、MySQLのビルドしたりgtagsでタグ付けしてソースコード読んだり、空いたリソースでbotを起動したりする分には、お値段以上に楽しんでいる方だと思います。


明日の ConoHa Advent Calendar 2016 は AtnanasiさんMySQL Casual Advent Calendar 2016 はウッ


【2016/12/13 15:58】

新しいのが出たので計り比べたらこんなんだった。


versionrealusersysinit前のbasedirサイズ
5.5.546:12.0885:35.8050:34.668301M
5.6.3611:14.03410:12.3440:58.376497M
5.7.1732:06.06826:00.0222:30.209873M


https://gist.github.com/yoku0825/f99e1de45d22f60cc932bbb60dd6e3a9

2016/12/08

最近のMroongaさんの構成について

このエントリーは Groonga Advent Calendar 2016MySQL Casual Advent Calendar 2016 の8日目です。

Groonga + MySQLと言えばMroongaです。
GroongaとMroongaを正確に聞き分けてもらうテクニックとして、「じーるんが」と「えむるんが」というと大体通じます。「あーるるんが」もたまに言います。NroongaとDroongaの存在を忘れることにすれば、いい言い分け方じゃないかなーと勝手に思っていますが。

そんなウチのMroongaの構成に少し異変(?)があったのでメモ。


1年半前は ↓ こんな構成をしていた(らしい)Mroongaさん (See also MySQLの全文検索に関するあれやこれや)、何が悲しくて同じ文書を2回InnoDB用とMroonga用にINSERT/UPDATEするんだ…という状態だった(らしい)のですが、



晴れてこうなった(らしい)
全文検索を必要とするテーブルに限った話ではあるけれど、 replicate-do-db/replicate-do-wild-tableあたりを駆使して「マスターはInnoDB、スレーブはInnoDBのものとMroongaのもの」という構成になり、二重書き込みはしなくていいしマスターが倒れてもInnoDBのクラッシュリカバリーに頼れる状態。



ついでに、s3とs4 (Mroongaストレージエンジンのスレーブ)にgroonga-httpd を起動させて、「シンプルな全文検索だけのクエリーはHTTPで、DISTNCTとかしてるクエリーはMroongaでSELECT」とか参照を分けるようにもなりました(DISTINCTっぽいことをgroonga-httpdにやらせてみたけれどMroongaのDISTINCTと速度変わらなかった。。)

「更新処理の伝搬はMySQLのレプリケーション任せ」、「バックアップもMySQLのレプリケーション任せ」、「groonga-httpdでウマウマできるところだけアプリ改修」と実際結構良いことづくめでした。


ウチではGroongaさんは毎回ソースからコンパイルしているので、`make install` した後には `sbin/grroonga-httpd` が出来上がっています( `./configure --enable-groonga-httpd=no` しない限り、一緒にコンパイルされる)

吊るしで立ち上げる時のコンフィグは `etc/groonga/httpd/groonga-httpd.conf` です。
userをmysqlに、groonga_databaseをMroongaのデータファイル($datadir/$schema.mrn) にセットしました。access_logは捨てています(このあたり、rpm版を使うとログローテーションも一緒に入って便利だよって同僚氏は言ってました)


5c5
< user mysql mysql;
---
> user groonga groonga;
18c18
<   groonga_database /data/mroonga_datadir/database_name.mrn;
---
>   groonga_database /usr/local/groonga503/var/lib/groonga/db/db;
27,30c27
<   groonga_database_auto_create off;
<   groonga_log_level NONE;
<   access_log /dev/null;
<
---
>   groonga_database_auto_create on;

あとは `$ sbin/groonga-httpd` と叩くだけで勝手にデーモンになります。
複数台あってロードバランスされているので、死活監視だけして自動再起動とかは特に仕込んでません。


某氏の喜びの声。

一部の全文検索クエリをMroongaからGroongaへ切りかえ

下記のワードは最も効果がある例だが、おおむね0.4秒ほどクエリあたりのレスポンスは改善される。
また、リスト後半に行くにしたがってMySQLのOFFSET,LIMITはパフォーマンスが線形に悪化していくが、Groongaは大きく性能劣化しない。
クローラーがpager=4000 とかリクエストしてきても性能劣化なく応答できる可能性が高い。


某ワードの場合(160万件)
MySQL > カウント = 0.9 sec
MySQL > リスト = 1.2 sec

Groonga > カウント = 0.45 sec
Groonga > リスト = 0.73 sec

オフセット 100万
MySQL > リスト = 6.14 sec
Groonga > 0.75 sec

ちなみにですが…、
現状、見ているページの検索リスト取得と検索結果の総件数取得で2回クエリを発行していますが、groongaは見ているページの検索リスト取得時にデフォルトで総件数も一緒に返ってくるのでクエリ発行回数が1回で済みます。

大成功だったようです。

俺も彼に触発されて groonga-httpd を取り敢えずスレーブに入れてみたクチなんですが、簡単な割に効果が高い(こともある)ので、気になったら試してみることをオススメしております。
(参照だけと割り切れば、効果がなければ放ってMroongaに切り戻せば良いだけだし)

明日のMySQL Casualは @meijik さん、Groongaは…おっと、まだ決まっていないようですね?
参加をお待ちしております :)

2016/12/05

2年越しの #ChugokuDB in 中国地方



これが2年前。




これが1年前。




そしてついに今年。

第18回 中国地方DB勉強会 in 広島 に逝ってきました!

( ´-`).oO(リアルに逝ってしまって本当に申し訳ない。。


スライドはこちらになります。
クエリーチューニングのおともに便利なMySQLer (都内30代・DBA 1.00000人に聞きました) 御用達のツールの紹介です。





恒例(?)の、MySQLおじさんとPostgreSQLおじさんが知らないことを聞きあうセッションもやってきました。 soudai1025_vs_yoku0825/03_hiroshima.md








坂井さん とその夜話したんですが、まあつまり1年半前と同じような感じで


MySQL を好きな人が、MySQLのちょっと変なところを、少しばかり強調しておもしろおかしく伝えている面はあるので、その変な部分だけが一人歩きして多くの人に理解されてしまうことを危惧はしていますが、

10回目の最後のYAPCに2回目の参加をしてきた - sakaikの日々雑感~(T)編


今週末の YAPC::Hokkaido ではちゃんと 最新版の魅力 をお伝えできるようにがんばります。


2016/12/03

mysqlコマンドラインクライアントのコマンド集

この記事は MySQL Casual Advent Calendar 2016 の3つ目の窓です。
昨日は kakuka4430 さんの CentOS6.8にtpcc-mysqlを入れようとして失敗した話 でした。

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.0-labs-opt-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysqlコマンドラインクライアントにはその中に更にコマンドを持っています。
helpを叩くと出てくる、\で始まるやつら(とそのロング形式)


mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

…これは8.0.0でCTEが使える(labsの)コマンドラインクライアントなんですけど、なんか前に比べて増えてない?;


- \c なんかtypoした時とかよく使う。セミコロンが来る前であれば、入力中のSQLをなかったことに出来る。クォート閉じるの忘れた時とかは、閉じクォートを書いて\c(セミコロンがクォートの中に閉じ込められるから、セミコロンは認識されない)

mysql> INSERT INTO t1 VALUES (1, 'one'), (2, 'two), (3, 'three');
    '> '\c
mysql> 


- \G これ実はmysqlコマンドラインクライアントのコマンドなので、他のクライアントではできない(か、互換性のために実装してるものはあるかも)


MySQL Workbenchはそのまま\Gまでサーバーに投げつけちゃって、シンタックスエラーをもらってる(mysqlコマンドラインクライアントではサーバーに送り付ける前にこの\Gをゴニョってから投げてる)

- pagerの使い方は 第6回 mysqlコマンドラインクライアントにページャーを指定する:MySQL道普請便り|gihyo.jp … 技術評論社 に最近書いた

- useコマンドはコマンドで、USEステートメントもある話は前に 日々の覚書: mysqlコマンドラインクライアントでuseの代わりにcdを使う この辺でちょっとだけ

- editは(まだ空きがあるので)そのうちに。


こいつらを上手く使うとmysqlコマンドラインライフが豊かになるので機会があれば是非。

明日は zurazurataicho さんです。

2016/12/02

ペパボの中の人ではありませんがペパボとの2016年を振り返って

このエントリーは  pepabo Advent Calendar 2016 の2日目です。
ちなみに GMOペパボ の人間ではありません。

俺は2016/12/02現在 GMOペパボのものすごく近くの会社 に勤めているので、去年の 論理削除Casua Talks #1 (おや…? #1から1年たつのに#2がないぞ…?) でしゃべらせてもらったのを切っ掛けに、今年は2回、まとまった時間を作ってもらってペパボに(内部の勉強会で)遊びにいきました。






1回目の勉強会の 5.7 + 雑な方は「へぇー、その機能、2年くらい前に言ってたよね。2年くらい前に見たわ」的なマサカリで俺が死ぬんじゃないかとドキドキしていたんですが、

すっげえ楽しそうに話してて、「この人、ほんと MySQL のこと好きなんだなー」と思って。
今回 MySQL 5.7 導入しようとしたときにも顔が浮かんだし。つまり背中を押してもらえたということだ。


この記事を読んだ時にすごくうれしかったです。今もたまに読み返しています。



2回目のGaleraの方は資料無し、ホワイトボードにごりごり思いついたことを書くスタイルでやらせてもらいました。というか参加してくれた方みんなフツーのMySQLのレプリケーション詳しくてすんごい話がしやすかった。Dockerでサクッと上げたPXCに「つまりRBRなのでこういうことすると死にます、というか自殺してフル同期かかります」って言ってサクッと落としてみるとか。


Galera Cluster勉強会@ペパボ – inamuu.com

楽しんでいただけたようで何よりです。



物理的に近く に位置しているものの、「ペパボの人ってすげーなー」「あんちぽさんよくやるなー」「ペパボの常様が常様の中で一番好きだわー」とか思っていただけのパンピーなので、遊びに行かせてもらってすごく楽しかったです。

あんちぽさん と「俺の知ってるMySQLのことは何でも伝えられるので、ウチのWEBサーバー周りの人にバーターで色々教えてください」なんて話をして、来年はもっと行き来ができればいいなと思っています。







以上、GMOペパボの福利厚生からでした。来年もまたよろしくお願いします ;)

2016/12/01

MySQLのNOW関数はどのようにして安全にスレーブでリプレイされるのか

このエントリーは MySQL Casual Advent Calendar 2016 の1日目の記事です!

日々の覚書: 複数のテーブルのON UPDATE current_timestampなカラムの値を揃える方法を考える の派生形なんですが、 "CURRENT_TIMESTAMP および CURRENT_TIMESTAMP() は NOW() のシノニムです。" なので、ちょっとだけ篠田さんの「使い慣れたSQLに潜む実装依存」に対する補足でもあったりします。




NOW関数は デフォルトでは 「そのステートメントの開始時刻」を返します。
この動作の検証としては以下のステートメントが有名でしょう。

mysql57> SELECT NOW(6), SLEEP(1), NOW(6);
+----------------------------+----------+----------------------------+
| NOW(6)                     | SLEEP(1) | NOW(6)                     |
+----------------------------+----------+----------------------------+
| 2016-12-01 10:16:16.596803 |        0 | 2016-12-01 10:16:16.596803 |
+----------------------------+----------+----------------------------+
1 row in set (1.00 sec)

mysql57> SELECT SYSDATE(6), SLEEP(1), SYSDATE(6);
+----------------------------+----------+----------------------------+
| SYSDATE(6)                 | SLEEP(1) | SYSDATE(6)                 |
+----------------------------+----------+----------------------------+
| 2016-12-01 10:16:29.699001 |        0 | 2016-12-01 10:16:30.699117 |
+----------------------------+----------+----------------------------+
1 row in set (1.00 sec)

NOW関数はステートメントの中で2回呼ばれていますが、同じ値を返します。
それに対しSYSDATE関数は「関数が実行された時点の時刻」を返すため、SLEEP(1) をはさんでもう一度実行された時には違う結果を返します。

さてさて、これがレプリケーションをはさむとどうなるか。


master [localhost] {msandbox} (d1) > SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (d1) > INSERT INTO sbr VALUES ('NOW', NOW(6)), ('SYSDATE', SYSDATE(6));
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

master [localhost] {msandbox} (d1) > SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                  |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (d1) > SELECT * FROM sbr;
+---------+----------------------------+
| func    | dt                         |
+---------+----------------------------+
| NOW     | 2016-12-01 11:35:23.865477 |
| SYSDATE | 2016-12-01 11:35:23.871343 |
+---------+----------------------------+
2 rows in set (0.00 sec)

slave1 [localhost] {msandbox} (d1) > SELECT * FROM sbr;
+---------+----------------------------+
| func    | dt                         |
+---------+----------------------------+
| NOW     | 2016-12-01 11:35:23.865477 |
| SYSDATE | 2016-12-01 11:35:23.874431 |
+---------+----------------------------+
2 rows in set (0.00 sec)

SBRの場合、NOW関数はマスターと同じ値が記録されるけれど、SYSDATE関数は同じ値が記録されない。「スレーブでSQLをリプレイした時の現在時刻」になってしまう。


master [localhost] {msandbox} (d1) > SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (d1) > INSERT INTO rbr VALUES ('NOW', NOW(6)), ('SYSDATE', SYSDATE(6));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} (d1) > SELECT * FROM rbr;
+---------+----------------------------+
| func    | dt                         |
+---------+----------------------------+
| NOW     | 2016-12-01 11:36:06.626105 |
| SYSDATE | 2016-12-01 11:36:06.626309 |
+---------+----------------------------+
2 rows in set (0.00 sec)

slave1 [localhost] {msandbox} (d1) > SELECT * FROM rbr;
+---------+----------------------------+
| func    | dt                         |
+---------+----------------------------+
| NOW     | 2016-12-01 11:36:06.626105 |
| SYSDATE | 2016-12-01 11:36:06.626309 |
+---------+----------------------------+
2 rows in set (0.00 sec)

それに対してRBRは関数をリプレイしないのでどちらでも問題なく動く(そもそも「関数の結果」をバイナリーログに書き込むため、スレーブではリプレイされない)
MIXEDの場合、SYSDATE関数がレプリケーションアンセーフな関数(非決定性関数)のためRBRにフォールバックする。


NOW関数の仕掛けはこうだ。

- クエリー開始時にTIMESTAMPセッション変数の中に値が入っているかどうかを探す
  - ちなみにTIMESTAMP変数は32ビット符号つきかつ負値をバリデーションではじいている、つまりunixtime
  - TIMESTAMP変数が0ならば、thd->query_start() を読んでTIMESTAMP変数に入れる
- TIMESTAMP変数をunixtimeからDATETIMEに変換して返す
  - クエリー終了まで、一度セットされたTIMESTAMP変数を使い続ける
- クエリー終了後、もとのTIMESTAMP変数が0だった場合は0に戻す


mysqlbinlogを出すと、しょっちゅう SET TIMESTAMP= .. で指定されているのを見ることができる。これは、そういう(NOW関数をスレーブでも決定性にするための)意味だったのだ。

# at 6439
#161201 11:35:23 server id 1  end_log_pos 6526 CRC32 0x2e37a667         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1480559723.865477/*!*/;
BEGIN
/*!*/;
# at 6526
#161201 11:35:23 server id 1  end_log_pos 6671 CRC32 0xab51a6f0         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1480559723.865477/*!*/;
INSERT INTO sbr VALUES ('NOW', NOW(6)), ('SYSDATE', SYSDATE(6))
/*!*/;
# at 6671
#161201 11:35:23 server id 1  end_log_pos 6702 CRC32 0xa975b625         Xid = 73
COMMIT/*!*/;


豆知識でした。


明日 12/2 は @kakuka4430 さんです!

2016/11/22

MySQL 5.7.16, 8.0.0現在、slave_skip_errorsはエラーコードの3000番台をスキップできない(MySQL 5.7.19, 8.0.2で修正)

日々の覚書: MySQL 5.7.6でエラーコードが変わった件 の時からMySQLのエラーコードに3000番台が加わった。

それまで1000番台はサーバーサイド、2000番台はクライアントサイドだけだったものが、3000番台もサーバーサイドのエラーコードとして設定されている(エラー番号はMySQL 5.7.16現在)



エラー番号 マクロ 備考
1000~1884 ER_*, WARN_* サーバーサイドエラー
2000~2062 CR_* クライアントサイドエラー(libmysqlclientの場合)
3000~3193 ER_* サーバーサイドエラー(5.7.6から)
例えばレプリケーションのI/OスレッドはMySQLサーバーの中にいるけれど実際はクライアントなので2000番台のエラーもハンドルする必要があったりして、ちょこちょこと"2000より小さければサーバーサイドエラー", "2000以上ならクライアントサイドエラー" みたいな判定があったりする。


もうお気付きだろう。3000番台のエラー番号は2000より大きい。

MAX_SLAVE_ERROR マクロは2000で、

https://github.com/mysql/mysql-server/blob/mysql-5.7.16/sql/rpl_slave.h#L66

ここ とか ここ とか ここ とかで err_code < MAX_SLAVE_ERROR で判定されている。


というわけでバグレポートしたのでしたん。

MySQL Bugs: #83184: Can't set slave_skip_errors > 3000


【2017/07/27 13:15】
MySQL 5.7.19, 8.0.2で修正されました!

https://bugs.mysql.com/bug.php?id=83184

2016/11/18

WindowsでもRabbitとRabbiterを使いたい

初めて成功したので忘れないうちにメモ。

0. CygwinのRubyにパスが通ってると、rabbitを起動した時にCygwinのRubyを掴もうとすることがあってダメぽになることがある。

1. RubyInstallerでRubyをインストール。取り敢えず2.3.1で上手く動いた。 Downloads

2. 対応したDevKitをダウンロードして適当な場所に展開(取り敢えず C:\Ruby23-x64\DevKit に展開して上手くいってる) Development Kit · oneclick/rubyinstaller Wiki によると "Download it, run it to extract it somewhere (permanent)." らしい。消しちゃダメなのか。

> ruby dk.rb init
> ruby dk.rb install


2-1. `ruby dk.rb install` で "Invalid configuration or no Rubies listed. Please fix 'config.yml'" と言われる。ruby.exeの場所が見付けられていないので、config.ymlを修正してもう一度。

3. これでやっと `gem install rabbit rabbiter` できるかと思いきや、SSLのエラーを食らう。

ERROR:  Could not find a valid gem 'rabbit' (>= 0), here is why:
          Unable to download data from https://rubygems.org/ - SSL_connect returned=1 errno=0 state=SSLv3 read server certificate B: certificate verify failed (https://api.rubygems.org/specs.4.8.gz)
ERROR:  Could not find a valid gem 'rabbiter' (>= 0), here is why:
          Unable to download data from https://rubygems.org/ - SSL_connect returned=1 errno=0 state=SSLv3 read server certificate B: certificate verify failed (https://api.rubygems.org/specs.4.8.gz)

bundle install がこけるようになった の "rubygems-updateをインストールする" で解決した。 bundle install がこけるようになった(改訂版) には "rubygems 2.4にはバグがあってこの方法はとってはいけない" みたいなことも書いてあるけれど、2016年10月現在の rubygems-update の最新版は2.6.7だったのでもういいかなと思ってやってみた。
取りあえず問題は出ていない。

> gem install --local rubygems-update-2.6.8.gem
> update_rubygems

4. ようやくRabbitとRabbiterが入った。後は楽しむだけ!

> gem install rabbit
> gem install rabbiter


( ´-`).oO(ところで、rabbitのsample.rdとかsample.mdとかを動かそうとするとrabbitがハングするの俺だけ…?

2016/10/19

MySQL 5.7, MySQL 8.0 でちょっとだけmysqladmin shutdownが変わる

日々の覚書: MySQL 5.7.9でSHUTDOWN *ステートメント* が実装されたよ! (我ながらなんて雑なエントリーなんだ。。)のタイミングで、mysqladmin shutdownの内部動作にも変更が入っていて、

A new SHUTDOWN SQL statement is available. This provides an SQL-level interface to the same functionality previously available using the mysqladmin shutdown command or the mysql_shutdown() C API function. See SHUTDOWN Syntax.
The mysql_shutdown() function and corresponding COM_SHUTDOWN client/server protocol command are deprecated and will be removed in a future version of MySQL. Instead, use mysql_query() to execute a SHUTDOWN statement.

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.9 (2015-10-21, General Availability)


サーバーがMySQL 5.7.9よりも前のバージョンの場合は mysql_shutdown C API を、5.7.9とそれより後のバージョンの場合は SHUTDOWNステートメント を発行するようになっている。

https://github.com/mysql/mysql-server/blob/mysql-5.7.9/client/mysqladmin.cc#L713-L718


MySQL 5.7.8とそれより前のmysqladmin shutdownはこのmysql_shutdown C APIをずっと使っていたんだけれど、MySQL 8.0.0ではこのmysql_shutdown C APIが削除された。


The deprecated mysql_shutdown() C API function and corresponding COM_SHUTDOWN client/server protocol command have been removed. Instead, use mysql_query() to execute a SHUTDOWN statement.

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.0 (2016-09-12, Development Milestone)


これによって、MySQL 5.7.8とそれ以前の mysqladmin shutdown はMySQL 8.0.0とそれ以降をシャットダウンできなくなっている。そして、MySQL 8.0.0の mysqladmin shutdown もMySQL 5.7.8とそれより前のバージョンのサーバーをシャットダウンできない。

$ /usr/mysql/5.6.34/bin/mysqladmin -S /usr/mysql/8.0.0/data/mysql.sock shutdown
/usr/mysql/5.6.34/bin/mysqladmin: shutdown failed; error: 'Unknown command'

5.6 mysqld 5.7 mysqld 8.0 mysqld
5.6 mysqladmin o o x
5.7 mysqladmin o o o
8.0 mysqladmin x o o
こんな感じ。好き好んで違うバージョンのmysqladminでmysqldをシャットダウンするとは思わないけれど、mysqld_multi使ってたらたまたま気が付きました。

2016/10/18

.mylogin.cnfはmysqldの設定に影響を及ぼすのか

なんかこう、如何にも `.my.login.cnf` が当たり前のように読み取られる前提でドキュメントに書いてあって、実際問題 `mysqld` も `~/.mylogin.cnf` をstatsしているので、MySQLサーバーも `.mylogin.cnf` を読むんじゃないかと思った次第。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.2.6 オプションファイルの使用


$ strace -f -e stat,open bin/mysqld_safe |& grep "cnf"
[pid 10841] stat("/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=4317, ...}) = 0
[pid 10841] open("/etc/my.cnf", O_RDONLY) = 3
[pid 10841] stat("/etc/mysql/my.cnf", 0x7ffd4860c300) = -1 ENOENT (No such file or directory)
[pid 10841] stat("/usr/local/mysql/etc/my.cnf", 0x7ffd4860c300) = -1 ENOENT (No such file or directory)
[pid 10841] stat("/usr/mysql/5.7.16/my.cnf", 0x7ffd4860c300) = -1 ENOENT (No such file or directory)
[pid 10841] stat("/home/yoku0825/.my.cnf", 0x7ffd4860c300) = -1 ENOENT (No such file or directory)
[pid 10841] stat("/home/yoku0825/.mylogin.cnf", {st_mode=S_IFREG|0600, st_size=24, ...}) = 0
[pid 10841] open("/home/yoku0825/.mylogin.cnf", O_RDONLY) = 3
[pid 11202] stat("/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=4317, ...}) = 0
[pid 11202] open("/etc/my.cnf", O_RDONLY) = 3
[pid 11202] stat("/etc/mysql/my.cnf", 0x7fffcf68d980) = -1 ENOENT (No such file or directory)
[pid 11202] stat("/usr/local/mysql/etc/my.cnf", 0x7fffcf68d980) = -1 ENOENT (No such file or directory)
[pid 11202] stat("/usr/mysql/5.7.16/my.cnf", 0x7fffcf68d980) = -1 ENOENT (No such file or directory)
[pid 11202] stat("/home/yoku0825/.my.cnf", 0x7fffcf68d980) = -1 ENOENT (No such file or directory)
[pid 11202] stat("/home/yoku0825/.mylogin.cnf", {st_mode=S_IFREG|0600, st_size=24, ...}) = 0
[pid 11202] open("/home/yoku0825/.mylogin.cnf", O_RDONLY) = 3

しっかり開いてしっかり読んでる。
とはいえ、 `.mylogin.cnf` を作る `mysql_config_editor` は変な書式をしていて、


$ mysql_config_editor --help
mysql_config_editor Ver 1.0 Distrib 5.7.16, for Linux on x86_64
Copyright (c) 2012, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

MySQL Configuration Utility.
Usage: mysql_config_editor [program options] [command [command options]]
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  -?, --help          Display this help and exit.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE

Where command can be any one of the following :
       set [command options]     Sets user name/password/host name/socket/port
                                 for a given login path (section).
       remove [command options]  Remove a login path from the login file.
       print [command options]   Print all the options for a specified
                                 login path.
       reset [command options]   Deletes the contents of the login file.
       help                      Display this usage/help information.


`mysql_config_editor set --help` とサブコマンドで `--help` しないと全然わからない。。


$ mysql_config_editor set --help
mysql_config_editor Ver 1.0 Distrib 5.7.16, for Linux on x86_64
Copyright (c) 2012, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

MySQL Configuration Utility.

Description: Write a login path to the login file.
Usage: mysql_config_editor [program options] [set [command options]]
  -?, --help          Display this help and exit.
  -h, --host=name     Host name to be entered into the login file.
  -G, --login-path=name
                      Name of the login path to use in the login file. (Default
                      : client)
  -p, --password      Prompt for password to be entered into the login file.
  -u, --user=name     User name to be entered into the login file.
  -S, --socket=name   Socket path to be entered into login file.
  -P, --port=name     Port number to be entered into login file.
  -w, --warn          Warn and ask for confirmation if set command attempts to
                      overwrite an existing login path (enabled by default).
                      (Defaults to on; use --skip-warn to disable.)

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
host                              (No default value)
login-path                        client
user                              (No default value)
socket                            (No default value)
port                              (No default value)
warn                              TRUE

`.mylogin.cnf` に書き込む内容を指定するには何故かオプション形式で指定しないといけない(思うに、難読化しちゃって部分修正が面倒なので、入力の段階でバリデーションをかける意図なのかしらん)ので、ソースをゴニョらない限りは `host`, `user`, `socket`, `port` 以外のオプションは埋め込めない。

が、セクションの名前を決める `--login-path` は好きに埋め込めそうだし、 `user`, `socket`, `port` は `[mysqld]` セクションにも同じ名前のオプションがあるので案外埋め込めるんじゃないかと思って埋め込んでみた。


$ mysql_config_editor set --login-path=mysqld --socket=/home/yoku0825/percona-xtrabackup-2.4.4/mysql.sock --port=11111

$ mysql_config_editor print --all
[mysqld]
socket = /home/yoku0825/percona-xtrabackup-2.4.4/mysql.sock
port = 11111

$ bin/mysqld_safe &
[1] 13416
2016-10-18T01:52:25.335653Z mysqld_safe Logging to '/usr/mysql/5.7.16/data/error.log'.
2016-10-18T01:52:25.374749Z mysqld_safe Starting mysqld daemon with databases from /usr/mysql/5.7.16/data

$ bin/mysql -S /home/yoku0825/percona-xtrabackup-2.4.4/mysql.sock -uroot
mysql> SELECT @@socket;
+----------------------------------------------------+
| @@socket                                           |
+----------------------------------------------------+
| /home/yoku0825/percona-xtrabackup-2.4.4/mysql.sock |
+----------------------------------------------------+
1 row in set (0.00 sec)

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

うん、しっかりがっつり `.mylogin.cnf` 読んでやがる。

ところで読んでるってことは…


$ mysql_config_editor reset
$ mysql_config_editor set --login-path=mysqld --socket="$(echo '/home/yoku0825/percona-xtrabackup-2.4.4/mysql.sock' ; echo 'skip-grant-tables')"
$ mysql_config_editor print --all
[mysqld]
socket = /home/yoku0825/percona-xtrabackup-2.4.4/mysql.sock
skip-grant-tables

( ゚д゚) あっ


mysql> SHOW GRANTS;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

( ゚д゚) あっ、あっ。


嫌がらせくらいにしか使えなさそうだけれども。


【2016/10/18 19:14】
Verifiedいただきました。

MySQL Bugs: #83420: mysql_config_editor should validate parameters

2016/09/13

MySQL 8.0.0でついにヒストグラムがサポートされるらしい(が、自動で統計してくれるわけではない様子)

MySQL :: WL#8706: Persistent storage of Histogram data

ドキュメントが出てきてないので正直使い方とか全くわからないんだけれども。


mysql80> DESC mysql.column_stats;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| database_name | varchar(64) | NO   | PRI | NULL    |       |
| table_name    | varchar(64) | NO   | PRI | NULL    |       |
| column_name   | varchar(64) | NO   | PRI | NULL    |       |
| histogram     | json        | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

取り敢えずJSONで手で書くのか。。


と思ったら 8.0.0現在、こんなw

Note
Currently, the optimizer does not yet consult the column_stats table in the course of query execution plan construction.

MySQL :: MySQL 8.0 Reference Manual :: 9.9.6 Optimizer Statistics


【2017/08/24 11:21】

一体いつ変わったのかリリースノートにも全然書いてないんだけど、 mysql.column_statistics に名前が変わってシステムテーブルとして直接参照が不可能に、中身を覗くには information_schema.column_statistics に名前が変わっていた。
mysql80 8> SELECT * FROM i_s.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: d1
 TABLE_NAME: t1
COLUMN_NAME: val
  HISTOGRAM: {"buckets": [["base64:type254:b25l", 0.5], ["base64:type254:dHdv", 1]], "data-type": "string", "charset-id": 303, "null-values": 0, "last-updated": "2017-08-24 01:17:07.000000", "sampling-rate": 1, "histogram-type": "singleton", "number-of-buckets-specified": 10}
1 row in set (0.00 sec)
ANALYZE TABLE .. UPDATE HISTOGRAM .. なる構文も増えていて、ちょっと今後が気になる(RCになったらちゃんと調べようかな)

MySQL 8.0.0 INVISIBLE KEYとやらはどうやって指定すればいいのか

INVISIBLEなるキーワードがインデックスに指定できるようになったので、取り敢えずADD KEYしてみたけれど…。


mysql80> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql80> ALTER TABLE t1 ADD KEY (val) INVISIBLE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`) /*!50800 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

mysql80> explain SELECT * FROM t1 WHERE val = 'two';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql80> explain SELECT * FROM t1 FORCE INDEX(val) WHERE val = 'two';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

おお、ホントだ FORCE INDEXでも使われない!!
けど、これってどうやって使わせるの…? たぶん SELECT /*+ .. */ スタイルの構文で食わせそうな気がするんだけど、まだ情報が見当たらない…(´・ω・`)

テーブルの1/3をフェッチするからコスト的に選ばれてないアレじゃないよね? と思ってフツーのINDEXも作ったけど、こっちはちゃんと選ばれる。ということはちゃんとインビジボーなのだね。

mysql80> ALTER TABLE t1 ADD KEY (val);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql80> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`) /*!50800 INVISIBLE */,
  KEY `val_2` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql80> explain SELECT * FROM t1 WHERE val = 'two';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | val_2         | val_2 | 131     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

で、どうやって選ばせればいいの…。ドキュメントはよ…。

MySQL 8.0.0現在で追加されているperformance_schema

MySQL 8.0.0時代のmy.cnfの探り方 で出てきたvariables_info の他にも events_errors_summary_* というテーブルが増えてる。


$ diff <(mysql80 -sse "SHOW TABLES FROM p_s") <(mysql57 -sse "SHOW TABLES FROM p_s")
3,7d2
< events_errors_summary_by_account_by_error
< events_errors_summary_by_host_by_error
< events_errors_summary_by_thread_by_error
< events_errors_summary_by_user_by_error
< events_errors_summary_global_by_error
93d87
< variables_info

中身はこんな感じ。


mysql80> SELECT * FROM events_errors_summary_global_by_error LIMIT 10;
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
| ERROR_NUMBER | ERROR_NAME              | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
|         NULL | NULL                    | NULL      |                0 |                 0 | NULL       | NULL      |
|         1002 | ER_NO                   | HY000     |                0 |                 0 | NULL       | NULL      |
|         1003 | ER_YES                  | HY000     |                0 |                 0 | NULL       | NULL      |
|         1004 | ER_CANT_CREATE_FILE     | HY000     |                0 |                 0 | NULL       | NULL      |
|         1005 | ER_CANT_CREATE_TABLE    | HY000     |                0 |                 0 | NULL       | NULL      |
|         1006 | ER_CANT_CREATE_DB       | HY000     |                0 |                 0 | NULL       | NULL      |
|         1007 | ER_DB_CREATE_EXISTS     | HY000     |                0 |                 0 | NULL       | NULL      |
|         1008 | ER_DB_DROP_EXISTS       | HY000     |                0 |                 0 | NULL       | NULL      |
|         1010 | ER_DB_DROP_RMDIR        | HY000     |                0 |                 0 | NULL       | NULL      |
|         1012 | ER_CANT_FIND_SYSTEM_REC | HY000     |                0 |                 0 | NULL       | NULL      |
+--------------+-------------------------+-----------+------------------+-------------------+------------+-----------+
10 rows in set (0.00 sec)

エラーコード単位で、何回起こったかとfirst_seen, last_seenを記録してくれるっぽい。
取り敢えず何かエラーにしてみようか。


mysql80> DROP DATABASE non_exist_database_name;
ERROR 1008 (HY000): Can't drop database 'non_exist_database_name'; database doesn't exist

mysql80> SELECT * FROM events_errors_summary_global_by_error LIMIT 10;
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME              | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         NULL | NULL                    | NULL      |                0 |                 0 | NULL                | NULL                |
|         1002 | ER_NO                   | HY000     |                0 |                 0 | NULL                | NULL                |
|         1003 | ER_YES                  | HY000     |                0 |                 0 | NULL                | NULL                |
|         1004 | ER_CANT_CREATE_FILE     | HY000     |                0 |                 0 | NULL                | NULL                |
|         1005 | ER_CANT_CREATE_TABLE    | HY000     |                0 |                 0 | NULL                | NULL                |
|         1006 | ER_CANT_CREATE_DB       | HY000     |                0 |                 0 | NULL                | NULL                |
|         1007 | ER_DB_CREATE_EXISTS     | HY000     |                0 |                 0 | NULL                | NULL                |
|         1008 | ER_DB_DROP_EXISTS       | HY000     |                1 |                 0 | 2016-09-09 12:28:01 | 2016-09-09 12:28:01 |
|         1010 | ER_DB_DROP_RMDIR        | HY000     |                0 |                 0 | NULL                | NULL                |
|         1012 | ER_CANT_FIND_SYSTEM_REC | HY000     |                0 |                 0 | NULL                | NULL                |
+--------------+-------------------------+-----------+------------------+-------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

記録された。


mysql80> SELECT * FROM events_errors_summary_by_account_by_error WHERE SUM_ERROR_RAISED <> 0;
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| USER | HOST      | ERROR_NUMBER | ERROR_NAME                | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| root | localhost |         1008 | ER_DB_DROP_EXISTS         | HY000     |                1 |                 0 | 2016-09-09 12:28:01 | 2016-09-09 12:28:01 |
| root | localhost |         1049 | ER_BAD_DB_ERROR           | 42000     |                1 |                 0 | 2016-09-09 12:21:11 | 2016-09-09 12:21:11 |
| root | localhost |         1329 | ER_SP_FETCH_NO_DATA       | 02000     |                2 |                 2 | 2016-09-09 12:21:27 | 2016-09-09 12:21:34 |
| root | localhost |         3554 | ER_NO_SYSTEM_TABLE_ACCESS | HY000     |              142 |                 0 | 2016-09-09 12:18:41 | 2016-09-09 12:29:11 |
+------+-----------+--------------+---------------------------+-----------+------------------+-------------------+---------------------+---------------------+
4 rows in set (0.01 sec)

ここまでやるなら、 events_errors_summary_by_digest_by_error テーブル(エラーになったステートメントと紐付く)もあったら嬉しかったりするんだけどな。。
レプリケーション関連のテーブル (replication_*)は今のところ動きがなさげ(つまりまだ Seconds_behind_masterとかは取れない)

MySQL 8.0.0のdatadirにあるなんか変なSDIファイル

取り敢えず mysqld --initialize したdatadirを覗いてみると、見慣れないSDIファイルがあることと見慣れた.frmファイルが **ない** ことに気付いた。


$ ll data/mysql/*.frm
ls: cannot access data/mysql/*.frm: No such file or directory

$ ll data/*.SDI
-rw-r----- 1 yoku0825 yoku0825 225 Sep  5 10:17 data/performance_sche_3.SDI
-rw-r----- 1 yoku0825 yoku0825 210 Sep  5 10:17 data/sys_4.SDI

SDIファイルは他にもいっぱいあって、 `find -name "*.SDI"` とかやるとごろっと出てくる。
Seralized Dictionary Informationの略らしい。New Data Dictionaryの一環で、.frmファイルからSDIファイルに変わったらしい。

中身はJSONで、いかにもメタデータっぽいのが詰まってる。
スキーマ用のSDIとテーブル用のSDIで(それぞれ持ってるメタデータの量が全然違うからだけど)中身は全然違う。

$ cat data/sys_4.SDI  ### Schema's SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Schema",
    "dd_object": {
        "name": "sys",
        "default_collation_id": 33,
        "created": 0,
        "last_altered": 0
    }
}

$ cat ./data/mysql/slow_log_103.SDI ### Table's SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "slow_log",
        "mysql_version_id": 80000,
        "created": 20160905101714,
        "last_altered": 20160905101714,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "start_time",
                "type": 18,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": false,
                "ordinal_position": 1,
                "char_length": 26,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "datetime_precision": 6,
                "has_no_default": false,
                "default_value_null": false,
                "default_value": "AAAAAAAAAA==",
                "default_option": "CURRENT_TIMESTAMP(6)",
                "update_option": "CURRENT_TIMESTAMP(6)",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "",
                "column_key": 1,
                "column_type_utf8": "timestamp(6)",
                "elements": [],
                "collation_id": 8
            },
            .. snip ..
        ],
        "schema_ref": "mysql",
        "hidden": false,
        "se_private_id": 18446744073709551615,
        "engine": "CSV",
        "comment": "Slow log",
        "se_private_data": "",
        "row_format": 2,
        "partition_type": 0,
        "partition_expression": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "default_subpartitioning": 0,
        "indexes": [],
        "foreign_keys": [],
        "partitions": [],
        "collation_id": 33
    }
}

ちなみにこれ、何故かInnoDBのテーブルには存在しない。


mysql80> CREATE DATABASE d1;
Query OK, 1 row affected (0.01 sec)

mysql80> CREATE TABLE d1.t1 (num serial, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql80> CREATE TABLE d1.t2 (num serial, val varchar(32)) Engine= MyISAM;
Query OK, 0 rows affected (0.00 sec)


$ ll data/d1*
-rw-r----- 1 yoku0825 yoku0825  209 Sep  5 11:05 data/d1_6.SDI

data/d1:
total 136
-rw-r----- 1 yoku0825 yoku0825 131072 Sep  5 11:05 t1.ibd
-rw-r----- 1 yoku0825 yoku0825   3787 Sep  5 11:05 t2_325.SDI
-rw-r----- 1 yoku0825 yoku0825      0 Sep  5 11:05 t2.MYD
-rw-r----- 1 yoku0825 yoku0825   1024 Sep  5 11:05 t2.MYI

作ったスキーマに対する d1_*.SDI (数字部分はスキーマを通した連番?)と、MyISAMのテーブルに対する t2_*.SDI (こっちはテーブル単位で連番?)はあるけど、InnoDBテーブルのt1.ibdに対するSDIファイルはない。

停止してinnochecksumを叩いてみると、


$ bin/innochecksum -S data/d1/t1.ibd

File::data/d1/t1.ibd
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
       1        Index page
       2        SDI Index page
       0        Undo log page
       1        Inode page
       0        Insert buffer free list page
       2        Freshly allocated page
       1        Insert buffer bitmap
       0        System page
       0        Transaction system page
       1        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Subsequent Compressed BLOB page
       0        SDI BLOB page
       0        Compressed SDI BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 0 insert, 0 update, 0 other
Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other

$ bin/innochecksum -D >(cat -) data/d1/t1.ibd


Filename::data/d1/t1.ibd
==============================================================================
        PAGE_NO         |               PAGE_TYPE                       |       EXTRA INFO
==============================================================================
#::       0             |               File Space Header               |       -
#::       1             |               Insert Buffer Bitmap            |       -
#::       2             |               Inode page                      |       -
#::       3             |               SDI Index page                  |       index id=18446744073709551615 (copy_num=0), page level=0, No. of records=0, garbage=0, -
#::       4             |               SDI Index page                  |       index id=18446744073709551614 (copy_num=1), page level=0, No. of records=0, garbage=0, -
#::       5             |               Index page                      |       index id=143, page level=0, No. of records=0, garbage=0, -
#::       6             |               Freshly allocated page          |       -
#::       7             |               Freshly allocated page          |       -

"SDI Index page" なる新しいページタイプが2ページ記録されているので、ここに入っているぽい。InnoDBだけは、ibdファイル(Not ibdata1)にSDIを記録することで、SDIファイルを必要とせず、しかもクラッシュセーフにできる…ってことなのかな。
ちなみにちょっと期待していた DROP TABLE のロールバックは


mysql80> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80> DROP TABLE t1;
Query OK, 0 rows affected (0.02 sec)

mysql80> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t2           |
+--------------+
1 row in set (0.01 sec)

mysql80> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql80> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t2           |
+--------------+
1 row in set (0.00 sec)

まだ、なのか、「そんなことする必要ないでしょ」なのか、できなかった。
DDLのトランザクション化というよりは、DDLのクラッシュセーフ化って感じな気がする。

MySQL 8.0.0現在の文字コードについて

Planning the defaults for MySQL 5.8 | MySQL Server Blog の時点で

In addition to utf8mb4, we are also considering switching the default collation to be utf8mb4_unicode_520_ci.

と地雷宣言が為されていた文字コード問題。
(utf8mb4_unicode_520_ci は🍣と🍺を区別するけど、ハハとパパを区別してくれないヤーツ)

|                    | utf8mb4_bin | utf8mb4_general_ci | utf8mb4_unicode_ci | utf8mb4_unicode_520_ci|
|--------------------|-------------|--------------------|--------------------|-----------------------|
| Hiragana-Katakana  | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Youon              | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Dakuten-Handakuten | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Wide-Narrow        | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Sushi-Beer         | cs          | ci                 | ci                 | cs                    |

MySQL Bugs: #79977: utf8mb4_unicode_520_ci don't make sense for Japanese FTS


かみぽさんが「せっかくActiveRecordで直したのに今度はMySQLでデフォルトになるん…」みたいなことを言っていたやつ。

取り敢えず手元の8.0.0現在では、

$ bin/mysqld --no-defaults --help --verbose | egrep '(character-set|collation)-server'
mysqld: Can't change dir to '/usr/local/mysql/data/' (Errcode: 2 - No such file or directory)
2016-09-05T04:05:23.602489Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-09-05T04:05:23.602584Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2016-09-05T04:05:23.602946Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
  -C, --character-set-server=name
  --collation-server=name
character-set-server                                         latin1
collation-server                                             latin1_swedish_ci

文字コード/照合順序は5.7と同じくlatin1/latin1_swedish_ci。
その一方で、


mysql80> SHOW COLLATION LIKE 'utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen |
+----------------------------+---------+-----+---------+----------+---------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 |         | Yes      |       8 |
| utf8mb4_bin                | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_croatian_ci        | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_cs_0900_ai_ci      | utf8mb4 | 266 |         | Yes      |       8 |
| utf8mb4_czech_ci           | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci          | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_da_0900_ai_ci      | utf8mb4 | 267 |         | Yes      |       8 |
| utf8mb4_de_pb_0900_ai_ci   | utf8mb4 | 256 |         | Yes      |       8 |
| utf8mb4_eo_0900_ai_ci      | utf8mb4 | 273 |         | Yes      |       8 |
| utf8mb4_esperanto_ci       | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_estonian_ci        | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_es_0900_ai_ci      | utf8mb4 | 263 |         | Yes      |       8 |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 |         | Yes      |       8 |
| utf8mb4_et_0900_ai_ci      | utf8mb4 | 262 |         | Yes      |       8 |
| utf8mb4_general_ci         | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_german2_ci         | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_hr_0900_ai_ci      | utf8mb4 | 275 |         | Yes      |       8 |
| utf8mb4_hungarian_ci       | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_hu_0900_ai_ci      | utf8mb4 | 274 |         | Yes      |       8 |
| utf8mb4_icelandic_ci       | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_is_0900_ai_ci      | utf8mb4 | 257 |         | Yes      |       8 |
| utf8mb4_latvian_ci         | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_la_0900_ai_ci      | utf8mb4 | 271 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci      | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_lt_0900_ai_ci      | utf8mb4 | 268 |         | Yes      |       8 |
| utf8mb4_lv_0900_ai_ci      | utf8mb4 | 258 |         | Yes      |       8 |
| utf8mb4_persian_ci         | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_pl_0900_ai_ci      | utf8mb4 | 261 |         | Yes      |       8 |
| utf8mb4_polish_ci          | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_romanian_ci        | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_roman_ci           | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_ro_0900_ai_ci      | utf8mb4 | 259 |         | Yes      |       8 |
| utf8mb4_sinhala_ci         | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_sk_0900_ai_ci      | utf8mb4 | 269 |         | Yes      |       8 |
| utf8mb4_slovak_ci          | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_slovenian_ci       | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_sl_0900_ai_ci      | utf8mb4 | 260 |         | Yes      |       8 |
| utf8mb4_spanish2_ci        | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_spanish_ci         | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_sv_0900_ai_ci      | utf8mb4 | 264 |         | Yes      |       8 |
| utf8mb4_swedish_ci         | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_tr_0900_ai_ci      | utf8mb4 | 265 |         | Yes      |       8 |
| utf8mb4_turkish_ci         | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci     | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_unicode_ci         | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci      | utf8mb4 | 247 |         | Yes      |       8 |
| utf8mb4_vi_0900_ai_ci      | utf8mb4 | 277 |         | Yes      |       8 |
+----------------------------+---------+-----+---------+----------+---------+
47 rows in set (0.00 sec)

utf8mb4の照合順序は結構増えてる。0900_ai_ciはUnicode 9.0.0ベースのAccentInsensitiveだろうか。どうも各言語ごとに0900_ai_ciが生えるような形をしてる。

MySQL :: WL#9108: Add language specific case insensitive collations of utf8mb4


「ai_ciが追加されるからunicode_ciはAccentSensitiveでいいよね」って流れになってもらわないと、このままutf8mb4_unicode_ciがデフォルトになる ( Planning the defaults for MySQL 5.8 | MySQL Server Blog ) のはつらい。WL#9108 見ても、japanese_ciを作る予定はなさそうだし(なさそうだしそもそも、罠いのが **暗黙のデフォルトになる** のが嫌なのだ俺は。。)

mysql80> SELECT 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_ci;
+------------------------------------------------+
| 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_ci     |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql80> SELECT 'ハハ' = 'パパ' COLLATE utf8mb4_general_ci;
+------------------------------------------------+
| 'ハハ' = 'パパ' COLLATE utf8mb4_general_ci     |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql80> SELECT 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_520_ci;
+----------------------------------------------------+
| 'ハハ' = 'パパ' COLLATE utf8mb4_unicode_520_ci     |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)



今後どうなるのか(特に 暗黙のデフォルトが 何になるのか)注目しておいた方がよさげ。