ISUCONの失敗から学ぶパフォーマンスチューニングの勘所 / isucon10

ISUCONの失敗から学ぶパフォーマンスチューニングの勘所～ ISUCON10の失敗から学ぶデータベースチューニング～中国地方DB勉強会＆OSC広島

ISUCON?      What is it?

What is it?

いいかんじにスピードアップコンテスト      What is it?

What is it?

現実は厳しい…      What is it?

だからこそ、失敗から学ぶ      What is it?

1. 自己紹介  2. isuumoの悪夢  3. INDEXを制する者は検索を制する  4. ボトルネックを分散するには  5. まとめ 
あじぇんだ

自己紹介  曽根壮大（35歳）  Have Fun Tech LLC 代表社員    そ　
ね　　たけとも  • 日本PostgreSQLユーザ会勉強会分科会担当  • 3人の子供がいます（長女、次女、長男）  • 技術的にはWeb/LL言語/RDBMSが好きです  • コミュニティが好き

本書きました 

1. 自己紹介  2. isuumoの悪夢  3. INDEXを制する者は検索を制する  4. ボトルネックを分散するには  5. まとめ 
あじぇんだ

ある日、突然Webサービスを    いい感じにスピードアップする  isuumoの悪夢

紹介動画    https://www.youtube.com/watch?v=TC4mLW-pQ0U  isuumoの悪夢

18時に公開されるWebサービス    それ以上でも、それ以下でもない  isuumoの悪夢

どんなWebサービスか？  どんなミドルウェアを使っているか？  どんなボトルネックがあるか？  isuumoの悪夢

自分たちで見つけて、改善するのが    ISUCON  isuumoの悪夢

ISUCON10    満を持して現れた今年の問題  isuumoの悪夢

ISUUMOの悪夢

めちゃめちゃ遅い検索を    チューニングしていきます  isuumoの悪夢

1. 自己紹介  2. ISUUMOの悪夢  3. INDEXを制する者は検索を制する  4. ボトルネックを分散するには  5. まとめ 
あじぇんだ

早速テーブルを見ていきましょう      INDEXを制する者は検索を制する

設定されたDDL CREATE TABLE isuumo.estate ( id INTEGER NOT NULL PRIMARY
KEY, name VARCHAR(64) NOT NULL, description VARCHAR(4096) NOT NULL, thumbnail VARCHAR(128) NOT NULL, address VARCHAR(128) NOT NULL, latitude DOUBLE PRECISION NOT NULL, longitude DOUBLE PRECISION NOT NULL, rent INTEGER NOT NULL, door_height INTEGER NOT NULL, door_width INTEGER NOT NULL, features VARCHAR(64) NOT NULL, popularity INTEGER NOT NULL ); CREATE TABLE isuumo.chair ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(64) NOT NULL, description VARCHAR(4096) NOT NULL, thumbnail VARCHAR(128) NOT NULL, price INTEGER NOT NULL, height INTEGER NOT NULL, width INTEGER NOT NULL, depth INTEGER NOT NULL, color VARCHAR(64) NOT NULL, features VARCHAR(64) NOT NULL, kind VARCHAR(64) NOT NULL, popularity INTEGER NOT NULL, stock INTEGER NOT NULL ); • INDEXが無い • シンプルなテーブルが2つ • 椅子と物件を検索するサイトなのでその元データ • 初期データ数は3万弱

意外とデータが少ない？      INDEXを制する者は検索を制する

意外とデータが少ない？  ↓  実行環境のスペックが低い！！  INDEXを制する者は検索を制する

$ grep physical.id /proc/cpuinfo | sort -u | wc -l
1   INDEXを制する者は検索を制する 1コアしかない！2020年だぞ！？ちなみにメモリは2GBでした

限られた環境で高速化するために    まずはINDEXを活用しよう  INDEXを制する者は検索を制する

INDEXを制する者は検索を制する

しかし検索条件が不定    そのため、単一のINDEXで倒せない  INDEXを制する者は検索を制する

INDEXが活用しにくい      INDEXを制する者は検索を制する

INDEXが活用しにくい  ↓  INDEXを使える形に変える  INDEXを制する者は検索を制する

計算結果を用意する (省略) ︙ door_height INTEGER NOT NULL, door_width INTEGER NOT
NULL, + rent_t INTEGER, + door_height_t INTEGER, + door_width_t INTEGER, features VARCHAR(64) NOT NULL, popularity INTEGER NOT NULL ); (省略) ︙ height INTEGER NOT NULL, width INTEGER NOT NULL, depth INTEGER NOT NULL, + price_t INTEGER, + height_t INTEGER, + width_t INTEGER, + depth_t INTEGER, • 検索条件は80cm以下等の特定の範囲にマッチするかどうかなのでそれに合わせて計算してtype を分類する • 実際にカラムを作る以外にも MySQLなどは仮想列の機能を使って対象のカラムを作る事もできる • width >= 80 AND width <= 110 → width_t = ? https://github.com/soudai/isucon10-qualify/commit/4d6c3b885ff04a7cb06eb250988adabdff66090a

正規化をする CREATE TABLE isuumo.estate_features ( name VARCHAR(64) NOT NULL, estate_id
INTEGER NOT NULL, PRIMARY KEY (name, estate_id) ); -- CREATE TABLE isuumo.chair_features ( name VARCHAR(64) NOT NULL, chair_id INTEGER NOT NULL, PRIMARY KEY (name, chair_id) ); • 正規化をしてRDBMSに優しいテーブル構造にする • 特徴の元の検索クエリ features LIKE CONCAT('%', ?, '%') • CSVのLIKE検索からIN句へ SELECT chair_id AS id, COUNT(*) num FROM chair_features WHERE name IN (?) GROUP BY chair_id HAVING num = ? https://github.com/soudai/isucon10-qualify/blob/1be06d2540eb94244596e9a7b541f7c4caf4c14f/webapp/ruby/app.rb

RDBMSに合わせたINDEXを設定する      INDEXを制する者は検索を制する

空間INDEXを使う -- 位置検索用のカラムの追加 ALTER TABLE isuumo.estate ADD COLUMN point POINT;
-- データ登録 UPDATE isuumo.estate SET point=POINT(latitude, longitude); -- 空間INDEXはNOT NULLが必須でSRIDの設定も必須 ALTER TABLE issumo.estate MODIFY COLUMN point POINT SRID 0 NOT NULL; -- 空間INDEXを設定 ALTER TABLE issumo.estate ADD SPATIAL INDEX (point); • 同様に検索用カラムを追加 • MySQL 8の空間INDEXの特性に合わせてデータを登録 • 空間INDEXを設定する ※本番で設定できてないんだけどね

アルゴリズムで    クエリをシンプルにする  INDEXを制する者は検索を制する

アルゴリズムで解決 SELECT * FROM estate WHERE 　　　(door_width >= ? AND
door_height >= ?) 　OR (door_width >= ? AND door_height >= ?) 　OR (door_width >= ? AND door_height >= ?) 　OR (door_width >= ? AND door_height >= ?) 　OR (door_width >= ? AND door_height >= ?) 　OR (door_width >= ? AND door_height >= ?) ORDER BY popularity DESC, id ASC 　　　　　　　　　　　↓ SELECT * FROM estate WHERE 　　　(door_width >= ? AND door_height >= ?) 　OR (door_width >= ? AND door_height >= ?) ORDER BY popularity DESC, id ASC • 買った椅子が物件の入り口を通るかを調べたい • 最初のクエリはすべての辺の組み合わせで調べてた • 入り口を通るかどうかは一番小さい面だけを考えれば良い • つまり、一番長い辺は無視することでクエリがシンプルになる ※まだINDEX効いてないけどね

アルゴリズムで解決 SELECT * FROM ( SELECT * FROM estate WHERE
　　　door_width >= ? AND door_height >= ? UNION 　SELECT * FROM estate WHERE door_width >= ? AND door_height >= ? ) AS main ORDER BY popularity DESC, id ASC • ORはUNIONで書き換えれる • ORを無くせばINDEXを活用するクエリに書き換えることができる ※本番では時間切れで着手できず

ORDER BY狙い CREATE INDEX idx_popularity ON estate (popularity DESC); EXPLAIN
SELECT * FROM estate WHERE 　　　(door_width >= ? AND door_height >= ?) 　OR (door_width >= ? AND door_height >= ?) ORDER BY popularity DESC, id ASC LIMIT 20 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: estate partitions: NULL type: index possible_keys: null key: idx_popularity key_len: 4 ref: NULL rows: 20 filtered: 20.98 Extra: Using where; 1 row in set, 1 warning (0.00 sec) • 実は多くのクエリはORDER BY 狙いのINDEXで解決できる • MySQL 8以降ならばDESCも指定できるため、右のようなINDEX で解決できる • ORDER BY狙いのINDEXを活用できれば、WHEREに依存しないので効率が良い ※本番では時間切れで着手できず https://github.com/soudai/isucon10-qualify/blob/1be06d2540eb94244596e9a7b541f7c4caf4c14f/webapp/ruby/app.rb

ここまでそーだいさんの成果は無し      INDEXを制する者は検索を制する

他にも色々テクニックはあるので    ぜひ感想ブログを見てください  INDEXを制する者は検索を制する ISUCON10 オンライン予選関連エントリまとめ

あじぇんだ

チューニングには限界がある      ボトルネックを分散するには

チューニングには限界がある  ↓  ハードウェアの限界を分散する  ボトルネックを分散するには

参照の分散と更新の分散      ボトルネックを分散するには

本番では試せなかった    PostgreSQLの話をします  ボトルネックを分散するには本番はMySQLで戦った＆今日はPostgreSQLユーザ会なので！

参照の分散と更新の分散      ボトルネックを分散するには

参照の分散のアイディア    • レプリケーション  • マテビュー  • postgres_fdw  • citus 
ボトルネックを分散するには

ボトルネックを分散するには同期レプリケーションで 3台構成にしたら普通にイケるので割愛します

ボトルネックを分散するには今回は活用ポイントなさそうなので割愛します

テーブル単位でシャーディングしたい      ボトルネックを分散するには

テーブル単位でシャーディングしたい  ↓  しかしアプリは回収したくない  ボトルネックを分散するには

ボトルネックを分散するには PostgreSQL PostgreSQL estate chair アプリケーション estateにアクセスする場合は、いつもどおり

ボトルネックを分散するには PostgreSQL PostgreSQL estate chair fdw アプリケーション chairにアクセスする場合は、 postgres_fdw経由で別サーバのテーブルを参照し、結果だけを取得する

場合によっては    redis_fdwでも良いかもしれない  ボトルネックを分散するには

citusで王道シャーディング      ボトルネックを分散するには

ボトルネックを分散するには https://www.slideshare.net/noriyoshishinoda/lets-scaleout-postgresql-using-citus-japanese-123590324 今日も篠田さんに感謝しましょう

ボトルネックを分散するにはこんな感じにシャーディングできるのでは？

シャーディングのKey設計が難しいので    今回のケースでは難しい  ボトルネックを分散するには

全体的に参照を分散させるなら    レプリケーション  ボトルネックを分散するには

あじぇんだ

これらを8時間で対応するのが    ISUCON  まとめ難しいですね…自分もできませんでした

だからこそ、普段からの準備が大事です      まとめ

まとめ

「知識がある」と「実践できる」には    大きな絶対的な差がある  まとめ

これを機に知ったことは    ぜひ実践していきましょう  まとめ

知識＊経験 = できる！      まとめ

ご清聴ありがとうございました      まとめ

ISUCONの失敗から学ぶ パフォーマンスチューニングの勘所 / isucon10

ISUCONの失敗から学ぶ パフォーマンスチューニングの勘所 / isucon10

More Decks by soudai sone

Other Decks in Technology

Featured

Transcript

ISUCONの失敗から学ぶパフォーマンスチューニングの勘所 / isucon10

ISUCONの失敗から学ぶパフォーマンスチューニングの勘所 / isucon10