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

88f4e84b94fe07cddbd9e6479d689192?s=47 soudai sone
September 16, 2020

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

第30回 中国地方DB勉強会とOSC広島2020での登壇資料です。

# isucon
http://isucon.net/

# 中国地方DB勉強会
https://dbstudychugoku.github.io/
# OSC広島
https://event.ospn.jp/osc2020-online-hiroshima/

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

September 16, 2020
Tweet

Transcript

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

  2. ISUCON?
 
 
 What is it?

  3. What is it?

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

  5. What is it?

  6. 現実は厳しい…
 
 
 What is it?

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

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


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


    あじぇんだ
  10. 自己紹介
 曽根 壮大(35歳)
 Have Fun Tech LLC 代表社員
 
 そ 

    ね   たけ とも
 • 日本PostgreSQLユーザ会 勉強会分科会 担当
 • 3人の子供がいます(長女、次女、長男)
 • 技術的にはWeb/LL言語/RDBMSが好きです
 • コミュニティが好き
  11. None
  12. 本書きました


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


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

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

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

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

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

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

  20. ISUUMOの悪夢

  21. ISUUMOの悪夢

  22. ISUUMOの悪夢

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

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


    あじぇんだ
  25. 早速テーブルを見ていきましょう
 
 
 INDEXを制する者は検索を制する

  26. 設定された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万弱
  27. 意外とデータが少ない?
 
 
 INDEXを制する者は検索を制する

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

  29. $ grep physical.id /proc/cpuinfo | sort -u | wc -l

    1 
 INDEXを制する者は検索を制する 1コアしかない!2020年だぞ!? ちなみにメモリは2GBでした
  30. 限られた環境で高速化するために
 
 まずはINDEXを活用しよう
 INDEXを制する者は検索を制する

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

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

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

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

  35. 計算結果を用意する (省略) ︙ 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
  36. 正規化をする 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
  37. RDBMSに合わせたINDEXを設定する
 
 
 INDEXを制する者は検索を制する

  38. 空間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を設定する ※本番で設定できてないんだけどね
  39. アルゴリズムで
 
 クエリをシンプルにする
 INDEXを制する者は検索を制する

  40. アルゴリズムで解決 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効いてないけどね
  41. アルゴリズムで解決 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を活用する クエリに書き換えることができる ※本番では時間切れで着手できず
  42. 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
  43. ここまでそーだいさんの成果は無し
 
 
 INDEXを制する者は検索を制する

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

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


    あじぇんだ
  46. チューニングには限界がある
 
 
 ボトルネックを分散するには

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

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

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

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

  51. 参照の分散のアイディア
 
 • レプリケーション
 • マテビュー
 • postgres_fdw
 • citus


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


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


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


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

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

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

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

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

  60. 参照の分散のアイディア
 
 • レプリケーション
 • マテビュー
 • postgres_fdw
 • citus


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

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

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

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

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

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


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

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

  69. まとめ

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

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

  72. 知識 * 経験 = できる!
 
 
 まとめ

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