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 さんです!