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

by soudai sone

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

ISUCON?      What is it?

Slide 3

Slide 3 text

What is it?

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

What is it?

Slide 6

Slide 6 text

現実は厳しい…      What is it?

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

本書きました 

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

ISUUMOの悪夢

Slide 21

Slide 21 text

ISUUMOの悪夢

Slide 22

Slide 22 text

ISUUMOの悪夢

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

設定された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万弱

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

計算結果を用意する (省略) ︙ 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

Slide 36

Slide 36 text

正規化をする 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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

空間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を設定する ※本番で設定できてないんだけどね

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

アルゴリズムで解決 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効いてないけどね

Slide 41

Slide 41 text

アルゴリズムで解決 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を活用するクエリに書き換えることができる ※本番では時間切れで着手できず

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

参照の分散のアイディア    ● レプリケーション  ● マテビュー  ● postgres_fdw  ● citus  ボトルネックを分散するには同期レプリケーションで 3台構成にしたら普通にイケるので割愛します

Slide 53

Slide 53 text

参照の分散のアイディア    ● レプリケーション  ● マテビュー  ● postgres_fdw  ● citus  ボトルネックを分散するには今回は活用ポイントなさそうなので割愛します

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

まとめ

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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