Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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/

soudai sone

September 16, 2020
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. 自己紹介
 曽根 壮大(35歳)
 Have Fun Tech LLC 代表社員
 
 そ 

    ね   たけ とも
 • 日本PostgreSQLユーザ会 勉強会分科会 担当
 • 3人の子供がいます(長女、次女、長男)
 • 技術的にはWeb/LL言語/RDBMSが好きです
 • コミュニティが好き
  2. 設定された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万弱
  3. $ grep physical.id /proc/cpuinfo | sort -u | wc -l

    1 
 INDEXを制する者は検索を制する 1コアしかない!2020年だぞ!? ちなみにメモリは2GBでした
  4. 計算結果を用意する (省略) ︙ 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
  5. 正規化をする 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
  6. 空間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を設定する ※本番で設定できてないんだけどね
  7. アルゴリズムで解決 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効いてないけどね
  8. アルゴリズムで解決 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を活用する クエリに書き換えることができる ※本番では時間切れで着手できず
  9. 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
  10. 参照の分散のアイディア
 
 • レプリケーション
 • マテビュー
 • postgres_fdw
 • citus


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


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