ISUCON10 の振り返りと MySQL の generated columns

バックエンドエンジニアの misu です。最近は塩加減に苦戦しながら、スパイスからカレーを作っています。

この記事について

ISUCON 10 に出場し、予選敗退したのでその振り返りと次回のために MySQL の generated columns について調査したことが書かれています。

ISUCON 10 の振り返り

チーム構成

弊社では、僕が ISUCON 出る人いませんか〜と募ったところ、6 人の戦士が手を上げてくれました。僕以外は全員初出場です。 7 人なので、得意な言語でこんなチーム分けになりました。

• Go チーム 2 人セットが 2 チーム
• Node.js チーム 3 人

振り返り

僕は、Go で普段開発しているので、Go で挑んだのですが、社内 3 チームの中でも一番パフォーマンスが出せませんでした。 610 点と振るわない結果でしたが次回は決勝に行きたいので、よかった点・問題点・やってみることをまとめておこうと思います。 改善ポイントのネタバレは ISUCON の記事で公開されています。

良かったこと

• 改善までの下準備はサクッと終わった(1時間以内)
  • ソースコードの git 管理
  • デプロイ用の Makefile
    • ローカルでビルドして、scp でバイナリ up & サービス再起動のシンプルなもの
  • pprof, NewRelic を仕込んでデプロイ
  • 各サーバに mackerel 仕込む
  • db や nginx など設定ファイルのバックアップ
  • slow query を吐き出すよう設定

問題点

• DB でのフルスキャンの回避方法がわからなかった
  • MySQL 5.7 では desc, asc が混在しているときにインデックスが効かない (8系ではサポートされている)
• アプリケーションと DB を 1台 のサーバに混在させた状態でベンチをしていたのでどちらがボトルネックになっているかわかるようになっていなかった
• モニタリング環境を用意したものの活用できなかった。mackerel のダッシュボードみて CPU, Memory についてアプリケーション、 DB どちらに負荷が寄っているかみて行動するべきだった
  • 自分たちは pprof を見て、負荷の高い検索 API を改修していたが、全体を見るべきだった

次のISUCONまでにやっておくこと/作戦

• MySQL のチューニングポイント、特にインデックスが効かない条件を把握する
• アプリケーション と DB はひとまず分けてベンチを回し、ボトルネックがどちらになっているか常に理解しておく

まとめ

モニタリングの徹底と、DB のチューニングポイントの理解がまだまだ足りないと痛感しました。 モニタリングからのアクションが正しくなかったおかげで、無駄にアプリケーションの高速化を試みて、大した成果を上げられず 時間を溶かしてしまい、苦しい思いをしました。 DB をどうにかしないといけないことに気づいていれば、上位陣がやっているような chair と estate を別 DB サーバに分割するような活路は見いだせたかもしれません。 自分たちは、フルスキャンをアプリケーション側でどうにか解決できないか検討していましたが、そこを早めに諦めて DB 2 台でスループットを上げる手段を検討すべきでした。 この辺は、データの関連、join が必要なのかなどを考慮して適切に分けられるようにならないといけないですね。 今回は DB にボトルネックがありましたが、アプリケーションのある API にボトルネックがある場合 該当の API だけ受けるアプリケーションサーバを用意する手段も思いつきそうです。

参加したメンバーからは、「露骨に修正しやすいところがなくて難易度高いなと思った」「悔しかったので絶対に次は本選に行きたい！」 「社内で ISUCON 対策部立ち上げて来年は本選に行けるようにしたい！」といった声があったのでみんな結構ISUCONにハマったのではないかと思ってます！ 来年はプロダクトも ISUCON も一緒にわいわいできる仲間がより増えているといいですね。

そして、 DB のパフォーマンス up の手札を増やすため、今回初めて知った generated columns について調べておきました。 ISUCON 10 で使われた MySQL 5.7.31 を対象にしています。

generated columns とは

テーブルに定義されているカラムを指定した計算結果をカラムとして持てるようにできる機能です。 仮想カラムのため、ディスク I/O やテーブル変更時に全体の再構築コストはありません。 テーブルアクセス時に予め設定された計算を行って値を返します。そのため、手軽に削除、追加を行えます。

ISUCON でのハックでいうと、MySQL 5.7 では昇順降順を指定したクエリにインデックスが効かないという罠を回避する手段で使えたようです。 (降順指定のカラムをマイナス値で計算させることで昇順でクエリできるような手段がありました。)

利用例

mysql> CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sei VARCHAR(50) NOT NULL,
    mei VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (CONCAT(sei,' ',mei))
);
Query OK, 0 rows affected (0.02 sec)

mysql> show columns from user;
+----------+--------------+------+-----+---------+-------------------+
| Field    | Type         | Null | Key | Default | Extra             |
+----------+--------------+------+-----+---------+-------------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment    |
| sei      | varchar(50)  | NO   |     | NULL    |                   |
| mei      | varchar(50)  | NO   |     | NULL    |                   |
| fullname | varchar(101) | YES  |     | NULL    | VIRTUAL GENERATED |
+----------+--------------+------+-----+---------+-------------------+

mysql> select * from user;
+----+------+------+-----------+
| id | sei  | mei  | fullname  |
+----+------+------+-----------+
|  1 | hoge | fuga | hoge fuga |
+----+------+------+-----------+
1 row in set (0.00 sec)

仮想カラムのメタデータ情報は、 InnoDB SYS_COLUMNS システムテーブルに格納されています。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name in ('sei', 'mei', 'fullname');
+----------+----------+-------+-------+---------+-----+
| TABLE_ID | NAME     | POS   | MTYPE | PRTYPE  | LEN |
+----------+----------+-------+-------+---------+-----+
|      348 | sei      |     1 |    12 | 2949391 | 200 |
|      348 | mei      |     2 |    12 | 2949391 | 200 |
|      348 | fullname | 65539 |    12 | 2961423 | 404 |
+----------+----------+-------+-------+---------+-----+

fullname の計算方法を記録するために SYS_VIRTUAL というシステムテーブルも存在します。 これを見ると POS 65539 の c が POS 1, 2 の sei, mei から計算されていることがわかります。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL;
+----------+-------+----------+
| TABLE_ID | POS   | BASE_POS |
+----------+-------+----------+
|      348 | 65539 |        1 |
|      348 | 65539 |        2 |
+----------+-------+----------+

その他できること

• セカンダリインデックスとして指定することも可能です。ただし、この場合は仮想カラムののデータは実質的にマテリアライズされ、セカンダリインデックスレコードに格納されます。つまり、仮想カラムがクエリーされる際に仮想カラムの値を計算する必要はありません。

mysql> CREATE INDEX fullname_idx ON user(fullname);

• 仮想カラムと非仮想カラムで複合インデックスを作成すること
• 仮想カラムと非仮想カラムから仮想カラムを作ること(インデックスも設定可)

仮想インデックスの制約

• 主キーに仮想列を含めることはできません
• 仮想インデックスは外部キーとして使用できません
• ストアド関数およびユーザー定義関数は許可されていません
• ストアドプロシージャと関数のパラメーターは許可されていません
• サブクエリは許可されていません

などなど

まとめ

機能自体は、2015 年からあるものの実際にプロダクトで使われていないと中々知り得ないなと思いました。 この機能を知っていれば、DB 設計時に余計なカラムを設けてテーブルサイズを大きくしたり、アプリケーション側で不要な処理を行うことが防げそうですね。 今回の反省を生かして、バックエンドアプリケーションの改善の手札を増やしつつ、スケーラブルなプロダクト作りに活かしたいと思います。 来年はさらに精進して決勝に行きたいです！

参考

• Virtual Columns and Effective Functional Indexes in InnoDB
• JSON Labs Release: Effective Functional Indexes in InnoDB
• 13.1.18.7 CREATE TABLE and Generated Columns