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

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

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

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

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

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

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

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

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

1)1ΧϯϑΝϨϯεԭೄ ランサーズSREチームの業務 サーバー運⽤ 安定化 レスポンス改善 負荷改善 冗⻑化 セキュリティ対策 最新バージョン追従 リソース管理 新サービスローンチ⽀援 キャパシティプランニング ドメイン、SSL証明書、リポジトリ管理 サーバー費⽤削減 旧サービス、旧機能の削除 効率化 サーバーのコード化 定型作業の⾃動化 開発⽀援 開発環境構築 リリースシステム構築 旧ソースの削除 社内インフラ、情シス系 社内サーバー 社内LAN PC周り 定型作業の委譲 他 ●スタートアップで必要とされる業務 組織が拡⼤すれば 管理部に委譲できる クラウド化も選択肢

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

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

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

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

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

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

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

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

1)1ΧϯϑΝϨϯεԭೄ スロークエリの監視 ●CloudWatchにスロークエリログを転送 ○取得結果をSlackに通知 Aurora CloudWatch Lambda Slack スロークエリログ

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

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

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

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

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の検索が⼤半

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秒かかるクエリ

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)

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;

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

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

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'

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')

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

1)1ΧϯϑΝϨϯεԭೄ CakePHPのページング機能の弱点 ●COUNT⽂を必ず発⾏する SELECT COUNT(*) FROM proposals

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 これは軽い 激重

1)1ΧϯϑΝϨϯεԭೄ 代替ページングの実装 ●CakePHPのページング機能を避ける ○⾃前でページングを実装 ○負荷のかかるSQL発⾏処理を削除する 削除 削除 削除

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

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

1)1ΧϯϑΝϨϯεԭೄ SQLのCOUNT⽂をなくす ● 旧TOPページの仕事カテゴリ⼀覧

1)1ΧϯϑΝϨϯεԭೄ SQLのCOUNT⽂をなくす ● 旧TOPページの仕事カテゴリ⼀覧 ○ 数⼗個のCOUNT⽂

1)1ΧϯϑΝϨϯεԭೄ SQLのCOUNT⽂をなくす ●⽇次バッチで更新しキャッシュするように修正 ○DB1台削減できた

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

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

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

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

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つしか使えない点は考慮が必要 ■複雑な条件で検索し始めると⾏き詰まる

1)1ΧϯϑΝϨϯεԭೄ 全⽂検索エンジンへの移⾏ ●検索をCloudSearchに移⾏ ○ElasticSearchServiceでもOK EC2 Aurora Writer CloudSearch DB負荷が半減

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

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

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

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