$30 off During Our Annual Pro Sale. View Details »

成長サービスのDB負荷問題を解決する

 成長サービスのDB負荷問題を解決する

Kanazawa Yuki

October 12, 2019
Tweet

More Decks by Kanazawa Yuki

Other Decks in Technology

Transcript

  1. 1)1ΧϯϑΝϨϯεԭೄ
    ϥϯαʔζגࣜձࣾ
    43&ʗ ۚᖒ༟ؽ
    成⻑サービスの
    DB負荷問題を解決する

    View Slide

  2. 1)1ΧϯϑΝϨϯεԭೄ
    ⾃⼰紹介

    View Slide

  3. 1)1ΧϯϑΝϨϯεԭೄ
    ⾃⼰紹介 3
    ⽒名︓⾦澤 裕毅
    出⾝︓宮城県仙台市
    ランサーズSRE
    略歴︓
    ⼤学時代はネットワークを専攻
    Windowsパッケージ開発(C++)
    ASP開発(Java)&インフラ(オンプレ)
    SNS開発(PHP)&インフラ(オンプレ)
    現在はランサーズのSRE(2013/11〜)

    View Slide

  4. 1)1ΧϯϑΝϨϯεԭೄ
    会社紹介

    View Slide

  5. 1)1ΧϯϑΝϨϯεԭೄ
    会社概要
    会社名︓ランサーズ株式会社
    設⽴︓2008年4⽉
    従業員︓約170名
    事業︓オンラインマッチング事業
    https://www.lancers.jp/
    所在地︓
    〒150-0002
    東京都渋⾕区渋⾕ 3-10-13
    TOKYU REIT 渋⾕Rビル 9F

    View Slide

  6. 1)1ΧϯϑΝϨϯεԭೄ
    利⽤技術
    React

    View Slide

  7. 1)1ΧϯϑΝϨϯεԭೄ
    ランサーズSREチームの業務
    サーバー運⽤
    安定化
    レスポンス改善
    負荷改善
    冗⻑化
    セキュリティ対策
    最新バージョン追従
    リソース管理
    新サービスローンチ⽀援
    キャパシティプランニング
    ドメイン、SSL証明書、リポジトリ管理
    サーバー費⽤削減
    旧サービス、旧機能の削除
    効率化
    サーバーのコード化
    定型作業の⾃動化
    開発⽀援
    開発環境構築
    リリースシステム構築
    旧ソースの削除
    社内インフラ、情シス系
    社内サーバー
    社内LAN
    PC周り
    定型作業の委譲

    View Slide

  8. 1)1ΧϯϑΝϨϯεԭೄ
    ランサーズSREチームの業務
    サーバー運⽤
    安定化
    レスポンス改善
    負荷改善
    冗⻑化
    セキュリティ対策
    最新バージョン追従
    リソース管理
    新サービスローンチ⽀援
    キャパシティプランニング
    ドメイン、SSL証明書、リポジトリ管理
    サーバー費⽤削減
    旧サービス、旧機能の削除
    効率化
    サーバーのコード化
    定型作業の⾃動化
    開発⽀援
    開発環境構築
    リリースシステム構築
    旧ソースの削除
    社内インフラ、情シス系
    社内サーバー
    社内LAN
    PC周り
    定型作業の委譲

    ●SREとして認知されている業務

    View Slide

  9. 1)1ΧϯϑΝϨϯεԭೄ
    ランサーズSREチームの業務
    サーバー運⽤
    安定化
    レスポンス改善
    負荷改善
    冗⻑化
    セキュリティ対策
    最新バージョン追従
    リソース管理
    新サービスローンチ⽀援
    キャパシティプランニング
    ドメイン、SSL証明書、リポジトリ管理
    サーバー費⽤削減
    旧サービス、旧機能の削除
    効率化
    サーバーのコード化
    定型作業の⾃動化
    開発⽀援
    開発環境構築
    リリースシステム構築
    旧ソースの削除
    社内インフラ、情シス系
    社内サーバー
    社内LAN
    PC周り
    定型作業の委譲

    ●ランサーズのSREチームが重視している業務
    SREチームを介さずに
    完結できる仕組みの構築

    View Slide

  10. 1)1ΧϯϑΝϨϯεԭೄ
    ランサーズSREチームの業務
    サーバー運⽤
    安定化
    レスポンス改善
    負荷改善
    冗⻑化
    セキュリティ対策
    最新バージョン追従
    リソース管理
    新サービスローンチ⽀援
    キャパシティプランニング
    ドメイン、SSL証明書、リポジトリ管理
    サーバー費⽤削減
    旧サービス、旧機能の削除
    効率化
    サーバーのコード化
    定型作業の⾃動化
    開発⽀援
    開発環境構築
    リリースシステム構築
    旧ソースの削除
    社内インフラ、情シス系
    社内サーバー
    社内LAN
    PC周り
    定型作業の委譲

    ●スタートアップで必要とされる業務
    組織が拡⼤すれば
    管理部に委譲できる
    クラウド化も選択肢

    View Slide

  11. 1)1ΧϯϑΝϨϯεԭೄ
    今回お話するテーマ
    サーバー運⽤
    安定化
    レスポンス改善
    負荷改善
    冗⻑化
    セキュリティ対策
    最新バージョン追従
    リソース管理
    新サービスローンチ⽀援
    キャパシティプランニング
    ドメイン、SSL証明書、リポジトリ管理
    サーバー費⽤削減
    旧サービス、旧機能の削除
    効率化
    サーバーのコード化
    定型作業の⾃動化
    開発⽀援
    開発環境構築
    リリースシステム構築
    旧ソースの削除
    社内インフラ、情シス系
    社内サーバー
    社内LAN
    PC周り
    定型作業の委譲

    View Slide

  12. 1)1ΧϯϑΝϨϯεԭೄ
    はじめに

    View Slide

  13. 1)1ΧϯϑΝϨϯεԭೄ

    サービスの成⻑に伴う負荷、レスポンス対策
    ●サービスが軌道に乗るとレスポンス、負荷対策が必要になってくる
    ○継続的に改善が必要
    ■放っておくと悪化する⼀⽅
    ●負荷、レスポンスの悪化要因
    ○アクセス数の増加
    ○データ数の増加
    ○サービスの機能追加

    View Slide

  14. 1)1ΧϯϑΝϨϯεԭೄ

    レスポンスの種類
    ●サーバーレスポンス
    ○極端に遅いページが1つでもあると
    ユーザーは不快に感じる
    ○DBが原因であることが多い
    ●ブラウザレスポンス
    ○CDN等で対策
    ○フロントエンジニアや
    デザイナーと協⼒して改善
    PHP
    MySQL
    DOM Processing
    Page rendering

    View Slide

  15. 1)1ΧϯϑΝϨϯεԭೄ

    ランサーズのサーバー構成
    EC2
    instance
    CloudSearch
    CloudFront Route 53 CloudFront
    ALB ALB API Gateway
    Lambda
    Auto
    Scaling App
    S3
    Aurora
    Reader
    Aurora
    Reader
    Aurora
    Writer
    Api
    ElastiCache
    Redis
    AI系API
    サムネイル表⽰
    仕事検索
    ランサー検索
    ランサーズ
    Batch
    PHP7
    CakePHP2.10
    Python3
    MySQL5.7

    View Slide

  16. 1)1ΧϯϑΝϨϯεԭೄ

    前提
    ●CakePHP2
    ○全てのテーブルに以下のカラムがある
    ■id
    ■created
    ■modified
    ●MySQL5.7
    ○(原則)1テーブルにつき1インデックスしか使えない
    ○B-Treeインデックス
    ■インデックスを利⽤しない検索(スキャン)
    ●O(n)
    ■インデックスを利⽤する検索
    ●O(logn)
    ●AWS
    ○RDS Aurora
    ○ElastiCache

    View Slide

  17. 1)1ΧϯϑΝϨϯεԭೄ
    スロークエリの計測

    View Slide

  18. 1)1ΧϯϑΝϨϯεԭೄ

    スロークエリの監視
    ●5秒以上かかるSQLをスローログに出⼒する場合
    ○slow_query_log = 1
    ■スロークエリログ出⼒を有効化
    ○long_query_time = 5
    ■スロークエリログに出⼒する秒数の閾値
    ●CloudWatchにスロークエリログを出⼒

    View Slide

  19. 1)1ΧϯϑΝϨϯεԭೄ

    スロークエリの監視
    ●CloudWatchにスロークエリログを転送
    ○取得結果をSlackに通知
    Aurora CloudWatch Lambda Slack
    スロークエリログ

    View Slide

  20. 1)1ΧϯϑΝϨϯεԭೄ
    SQLの最適化と
    インデックスチューニング

    View Slide

  21. 1)1ΧϯϑΝϨϯεԭೄ

    テーブルフルスキャンとインデックス検索の計算量
    ●MySQLの場合(B-Treeインデックス)
    ○インデックスを利⽤しない検索(テーブルフルスキャン)
    ■計算量︓O(n)
    ○インデックスを利⽤する検索
    ■計算量︓O(logn)
    X=10000のとき
    Y=10000
    X=10000のとき
    Y=9.21
    Y=X
    Y=logX

    View Slide

  22. 1)1ΧϯϑΝϨϯεԭೄ

    WHEREの左辺に式を含めない
    ●WHEREの左辺に式が含まれているとインデックスが効かない
    ○消費税を含めた⾦額で検索する例
    ●左辺をカラムのみにする
    SELECT
    *
    FROM
    payments
    WHERE
    amount - 100 < 10000
    SELECT
    *
    FROM
    payments
    WHERE
    amount < 10000 + 100

    View Slide

  23. 1)1ΧϯϑΝϨϯεԭೄ

    ソート対象カラムの置き換え
    ●CakePHPは各テーブルにidがつく(Primary Key)
    ○createdのソート順とidのソート順は基本的に⼀致する
    ●createdのソートをidのソートに置き換え
    SELECT
    *
    FROM
    works
    ORDER BY
    created DESC
    LIMIT 30
    SELECT
    *
    FROM
    works
    ORDER BY
    id DESC
    LIMIT 30

    View Slide

  24. 1)1ΧϯϑΝϨϯεԭೄ

    カーディナリティの低いインデックス
    ●カーディナリティの低いとインデックスの効果が出にくくなる
    ○⼩数派の検索が多いか⾒極める必要がある
    ●カーディナリティ2の例
    SELECT
    *
    FROM
    proposals
    WHERE
    deleted = 0
    SELECT
    *
    FROM
    proposals
    WHERE
    win = 1
    インデックス効果
    ほとんどなし
    インデックス効果
    あり
    ○削除フラグ(deleted)
    ■deleted = 0のレコードが⼤多数
    ■deleted = 0の検索が⼤半
    ○当選フラグ(win)
    ■win = 0のレコードが⼤多数
    ■win = 1の検索が⼤半

    View Slide

  25. 1)1ΧϯϑΝϨϯεԭೄ

    複合インデックスによる改善例
    SELECT
    *
    FROM
    payments
    WHERE
    created BETWEEN '2019-01-01 23:59:59' AND '2019-06-30 23:59:59'
    AND
    operation_type IN ('reward', 'external_reward')
    AND
    user_id = 122864
    ORDER BY
    id DESC
    ●本番環境で25秒かかるクエリ

    View Slide

  26. 1)1ΧϯϑΝϨϯεԭೄ

    スロークエリ改善のステップ
    ●インデックスの確認
    ●EXPLAINで調査
    ○user_idのインデックスが適⽤されてもrowsが12万超
    mysql> EXPLAIN SELECT
    -> *
    -> FROM
    -> payments
    -> WHERE
    -> created BETWEEN '2019-01-01 23:59:59' AND '2019-06-30 23:59:59'
    -> AND
    -> operation_type IN ('reward', 'external_reward')
    -> AND
    -> user_id = 122864
    -> ORDER BY
    -> id DESC;
    +----+-------------+---------+------------+------+--------------------------------+---------+---------+-------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+---------+------------+------+--------------------------------+---------+---------+-------+--------+----------+-------------+
    | 1 | SIMPLE | Payment | NULL | ref | user_id,operation_type_created | user_id | 4 | const | 120624 | 6.50 | Using where |
    +----+-------------+---------+------------+------+--------------------------------+---------+---------+-------+--------+----------+-------------+
    1 row in set, 1 warning (0.02 sec)
    mysql> SHOW INDEX FROM payments;
    +----------+------------+-------------------------+--------------+----------------+-------------+----------+--------+------+------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality | Sub_part | Packed | Null | Index_type |
    +----------+------------+-------------------------+--------------+----------------+-------------+----------+--------+------+------------+
    | payments | 0 | PRIMARY | 1 | id | 40525056 | NULL | NULL | | BTREE |
    | payments | 1 | user_id | 1 | user_id | 424991 | NULL | NULL | | BTREE |
    | payments | 1 | belong_id | 1 | belong_id | 1872714 | NULL | NULL | YES | BTREE |
    | payments | 1 | operation_type_created | 1 | operation_type | 21 | NULL | NULL | | BTREE |
    | payments | 1 | operation_type_created | 2 | created | 10692329 | NULL | NULL | YES | BTREE |
    | payments | 1 | coupon_id | 1 | coupon_id | 381 | NULL | NULL | YES | BTREE |
    | payments | 1 | modified | 1 | modified | 5868035 | NULL | NULL | YES | BTREE |
    +----------+------------+-------------------------+--------------+----------------+-------------+----------+--------+------+------------+
    7 rows in set (0.00 sec)

    View Slide

  27. 1)1ΧϯϑΝϨϯεԭೄ

    スロークエリ改善のステップ
    ●user_id, createdの複合インデックスを作成
    ●user_idの単⼀インデックスを削除
    ○↑の複合インデックスで代替できる
    ●EXPLAINを再確認
    ○rowsが120624→5274に減少
    ●実⾏時間
    ○25秒→1秒に改善
    +----+-------------+---------+------------+------+------------------------------------------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+---------+------------+------+------------------------------------------+---------+-------+------+----------+-------------+
    | 1 | SIMPLE | Payment | NULL | ref | user_id_created,oper.. | user_id_created | 4 | const | 5274 | 50.00 | Using where |
    +----+-------------+---------+------------+------+------------------------------------------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.02 sec)
    mysql> ALTER TABLE payments ADD INDEX user_id_created(user_id, created)
    mysql> ALTER TABLE payments DROP INDEX user_id;

    View Slide

  28. 1)1ΧϯϑΝϨϯεԭೄ

    (緊急⼿段)FORCE INDEX(USE INDEX)を使う
    ●CakePHP2の場合
    ○FROMの直後にUSE INDEXを付与することが可能
    ■公式にサポートされている⽅法ではないので注意
    ●注意点
    ○user_id_createdのインデックスを消すとエラーになる
    ■PRIMARYなら⼤丈夫
    ○MySQLの統計情報に関わらず同じインデックスを使い続ける
    ■特にFORCE INDEXの場合
    ●なるべくUSE INDEXにしておいた⽅が良い
    ■定期的に観測し、問題なくなったら削除する
    $this->Payment->find('first', [
    'conditions' => [
    'user_id' => $id,
    'created >' => date('Y-m-d', strtotime($format)),
    ],
    'joins' => ['USE INDEX(user_id_created)'],
    SELECT
    FROM payments USE INDEX (use_id_created)
    WHERE user_id = $id AND created > '2019-09-16'
    ORDER BY id
    LIMIT 1

    View Slide

  29. 1)1ΧϯϑΝϨϯεԭೄ
    SQLの分割

    View Slide

  30. 1)1ΧϯϑΝϨϯεԭೄ

    インデックスが効かなくなる例(UNIONクエリ)
    ●※status, createdにそれぞれ単⼀インデックスが付与されている場合
    ○2つに分割してそれぞれインデックスを効かせる
    SELECT
    id
    FROM
    results
    WHERE
    work_id = 1
    AND
    status IN ('worked', 'working')
    UNION
    SELECT
    id
    FROM
    results
    WHERE
    work_id = 1
    AND
    created > '2019-10-01'
    SELECT
    id
    FROM
    results
    WHERE
    work_id = 1
    AND
    status IN ('worked', 'working')
    SELECT
    id
    FROM
    results AS Result
    WHERE
    work_id = 1
    AND
    created > '2019-10-01'

    View Slide

  31. 1)1ΧϯϑΝϨϯεԭೄ

    インデックスが効かなくなる例(SQLで条件分岐)
    ●IF、CASEで条件を分岐しているパターン
    ○条件の処理はPHP側で⾏う
    SELECT
    *
    FROM
    payments
    WHERE
    user_id = 1136075
    AND
    CASE
    WHEN belong_to IN ('TimechargeInvoice', 'Milestone')
    THEN operation_type IN ('withholding_client')
    ELSE operation_type IN ('withholding_lancer')
    END;
    SELECT
    *
    FROM
    payments
    WHERE
    user_id = 1136075
    AND
    operation_type IN ('withholding_client')
    SELECT
    *
    FROM
    payments
    WHERE
    user_id = 1136075
    AND
    operation_type IN ('withholding_lancer')

    View Slide

  32. 1)1ΧϯϑΝϨϯεԭೄ
    代替ページングの実装

    View Slide

  33. 1)1ΧϯϑΝϨϯεԭೄ

    CakePHPのページング機能の弱点
    ●COUNT⽂を必ず発⾏する
    SELECT
    COUNT(*)
    FROM
    proposals

    View Slide

  34. 1)1ΧϯϑΝϨϯεԭೄ

    CakePHPのページング機能の弱点
    ●ページ後半の検索が重くなる
    ○LIMIT句のOFFSET値が上がるほど遅くなる
    SELECT
    *
    FROM
    proposals
    ORDER BY id DESC
    LIMIT 0,40
    SELECT
    *
    FROM
    proposals
    ORDER BY id DESC
    LIMIT 3467480, 40
    これは軽い
    激重

    View Slide

  35. 1)1ΧϯϑΝϨϯεԭೄ

    代替ページングの実装
    ●CakePHPのページング機能を避ける
    ○⾃前でページングを実装
    ○負荷のかかるSQL発⾏処理を削除する
    削除 削除 削除

    View Slide

  36. 1)1ΧϯϑΝϨϯεԭೄ
    キャッシュの導⼊

    View Slide

  37. 1)1ΧϯϑΝϨϯεԭೄ

    キャッシュの導⼊(Memcached、Redis)
    ●重いSQLの対策に使う場合
    ○WEBアクセスをそのままキャッシュしない
    ■タイムアウトがキャッシュされると⾯倒なことに。。。
    ○バッチで定期的に更新する
    ■WEBアクセス時はキャッシュを参照する
    ●SPOF(単⼀障害点)を考慮したい
    ○ElastiCache RedisはMultiAZをサポートしている
    ○Memcahedは独⾃のSPOF対策が必要
    EC2
    Aurora
    Writer
    ElastiCache
    Redis

    View Slide

  38. 1)1ΧϯϑΝϨϯεԭೄ

    SQLのCOUNT⽂をなくす
    ● 旧TOPページの仕事カテゴリ⼀覧

    View Slide

  39. 1)1ΧϯϑΝϨϯεԭೄ

    SQLのCOUNT⽂をなくす
    ● 旧TOPページの仕事カテゴリ⼀覧
    ○ 数⼗個のCOUNT⽂

    View Slide

  40. 1)1ΧϯϑΝϨϯεԭೄ

    SQLのCOUNT⽂をなくす
    ●⽇次バッチで更新しキャッシュするように修正
    ○DB1台削減できた

    View Slide

  41. 1)1ΧϯϑΝϨϯεԭೄ
    参照DBのスケールアウト

    View Slide

  42. 1)1ΧϯϑΝϨϯεԭೄ

    参照SQLをRead Replicaに分散する
    ●読み込みの負荷が⾼いサービスの場合
    ○Read Replicaに分散させることで回避可能
    ○Auroraなら15台まで増やせる
    ■RDS for MySQLは5台まで
    ●CakePHPはRead Replicaに切り替える機能はサポートされていない
    ○独⾃で実装する必要がある
    ●CakePHP2のプラグインが提供されている
    ○https://packagist.org/packages/connehito/cakephp2-master-replica
    EC2
    Aurora
    Reader
    Aurora
    Reader
    Aurora
    Writer

    View Slide

  43. 1)1ΧϯϑΝϨϯεԭೄ
    全⽂検索の導⼊

    View Slide

  44. 1)1ΧϯϑΝϨϯεԭೄ

    ⽂字列検索の問題
    ●LIKE検索は前⽅⼀致しかインデックスが効かない
    ○MySQLのFULL TEXT INDEXはスペース区切りしか対応していない
    SELECT
    *
    FROM
    works
    WHERE
    title LIKE '%PHP%'
    OR
    description LIKE '%PHP%'
    中間⼀致は
    インデックスが効かない

    View Slide

  45. 1)1ΧϯϑΝϨϯεԭೄ

    MySQLの全⽂検索
    SELECT
    *
    FROM
    works
    WHERE
    MATCH (title) AGAINST ('PHP')
    OR
    MATCH (description) AGAINST ('PHP')
    MySQL 5.6 MySQL 5.7
    EC2 on MySQL スペース区切り
    ※MroongaはN-Gram、Mecabをサポート
    スペース区切り
    N-Gram
    形態素解析(Mecab)
    RDS for MySQL スペース区切り
    スペース区切り
    N-Gram
    Aurora スペース区切り スペース区切り
    ●MATCH 〜 AGAINST構⽂でサポートされている全⽂検索機能
    ○※インデックスが1つしか使えない点は考慮が必要
    ■複雑な条件で検索し始めると⾏き詰まる

    View Slide

  46. 1)1ΧϯϑΝϨϯεԭೄ

    全⽂検索エンジンへの移⾏
    ●検索をCloudSearchに移⾏
    ○ElasticSearchServiceでもOK
    EC2
    Aurora
    Writer
    CloudSearch
    DB負荷が半減

    View Slide

  47. 1)1ΧϯϑΝϨϯεԭೄ
    最後に

    View Slide

  48. 1)1ΧϯϑΝϨϯεԭೄ
    会社概要

    View Slide

  49. 1)1ΧϯϑΝϨϯεԭೄ
    エンジニア採⽤してます︕
    ●エントリーはこちら︕
    ○https://www.wantedly.com/companies/lancers/projects

    View Slide

  50. 1)1ΧϯϑΝϨϯεԭೄ
    御静聴ありがとうございました︕

    View Slide