日々の覚書: 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() かアプリから文字列渡し

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

明日はクリスマスイブ、ｶﾐﾎﾟｩさんです！

日々の覚書

GA

2016/12/23

MySQLのCOUNTを速くする（？）SQL1本ノックその2

0 件のコメント :

コメントを投稿

GA

2016/12/23

MySQLのCOUNTを速くする（？）SQL1本ノック その2

0 件のコメント :

コメントを投稿

MySQLのCOUNTを速くする（？）SQL1本ノックその2