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

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