643 Views
July 28, 22
スライド概要
Qiita や Zenn でいろいろ書いてます。 https://qiita.com/hmatsu47 https://zenn.dev/hmatsu47 MySQL 8.0 の薄い本 : https://github.com/hmatsu47/mysql80_no_usui_hon Aurora MySQL v1 → v3 移行計画 : https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book https://speakerdeck.com/hmatsu47
社内スピードアップコンテスト お焚き上げ編 吉祥寺.pm30【オンライン】 2022/7/28 まつひさ(hmatsu47)
自己紹介 松久裕保(@hmatsu47) ● https://qiita.com/hmatsu47 名古屋で Web インフラのお守り係をしています 最近は Aurora MySQL v1 → v3 移行してます ○ Zenn の本: https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book 2
本日のネタ ● 前回発表で話した社内スピードアップコンテストですが ○ https://speakerdeck.com/hmatsu47/she-nei-desupidoatupukontesutokai-cui-nitiaozhan-sitahua ○ (前回話したとおり)消化不良な感じで終了したので ○ 出題内容のお焚き上げをする …という話です (一部省略あり) 3
出題内容 ● こちらで公開 ○ https://github.com/hmatsu47/kuso-code-samples の kusocode3 ■ ○ https://github.com/hmatsu47/kusocode1-front-app ■ ○ 動作確認用フロントエンド(React) https://github.com/hmatsu47/kusocode-bench ■ ○ 出題(Java Servlet) ベンチマーク(Go) https://github.com/hmatsu47/kusocode-rank-app ■ ランキング表示(Vue) 4
スピードアップ対象 ● Web API(Java 8) ○ ただの Servlet ○ ①画像一覧の表示 ■ 画像は 25 種類 ■ DL 数カウンタ付き ■ DL 数の降順で表示 ○ ②画像ダウンロード ○ ③カウンター初期化 5
レギュレーション(競技は 1.5 時間) ● (AWS)EC2(t3a.small)上のアプリケーション修正可 ○ API で入出力するデータ形式の変更は不可 ○ ダウンロード画像フォーマットの変更も不可 ● EC2 上のミドルウェア設定変更・入れ替え可 ● DB のスキーマ・テーブル設計・初期データ変更可 ○ Aurora MySQL v1 t3.small ● EC2 インスタンスおよび DB インスタンス変更不可 6
出題のポイント ● 主な問題点 ○ ヒープメモリ容量の設定ミス ○ テーブル構造の欠陥 ○ 非効率な DB データ取得 ■ N+1、無駄な行・列の取得 ● 何の対処もしないと ○ 100 点前後で Fail(注:途中 Fail してもそこまでのスコアは有効) 7
順に修正:①ヒープメモリ容量の設定ミス ● 主な問題点 ○ ヒープメモリ容量の設定ミス ○ テーブル構造の欠陥 ○ 非効率な DB データ取得 ■ N+1、無駄な行・列の取得 8
JVM のヒープメモリ ● t3a.small(2GiB)の場合デフォルト最大値 512MiB ● 設定ミスで初期値・最大値が 128MiB に ○ -Xms128m -Xmx128m ○ これを初期値・最大値とも 1.25 GiB 程度に ■ ついでに GC も変えると得点がわずかに UP(パラレル GC → G1GC) ● この程度まで上げると 1,000 〜数千点程度は行く? ○ ただしコードを直さないと GC 頻度が高く、後半で OOM も 9
順に修正:②テーブル構造の欠陥 ● 主な問題点 ○ ヒープメモリ容量の設定ミス ○ テーブル構造の欠陥 ○ 非効率な DB データ取得 ■ N+1、無駄な行・列の取得 10
count_log テーブル ● 中途半端な構造 ○ 1DL ごとに 1 行ある ○ access_count 列がある ■ カウント 0 の行が 1,000 行 ■ ↑ゴミデータ? ○ 行数でカウント? ○ 列値をカウントアップ? ■ ↑どっち狙い? 11
修正方法(案) ● ①行数でカウントする ○ 無駄なデータを全て消す ○ access_count 列を消す ● ②列値をカウントアップする ○ 25 行だけ残して無駄なデータを消す ○ picture_id 列を消す(主キーである id 列を使う) ○ access_count 列をカウントアップに使う 12
修正方法(案) ● ③列値をカウントアップする(別方式) ○ pictureテーブルに access_count 列を追加 ■ 一覧表示時 JOIN が不要に ○ あとは②同様に列値をカウントアップ ■ BLOB はオフページに行くとはいえ更新時負荷が気になる・このテーブルを 壊すと競技終了に追い込まれかねないのでボツ 13
修正方法(案) ● ③列値をカウントアップする(別方式) ○ pictureテーブルに access_count 列を追加 ■ 一覧表示時 JOIN が不要に ○ あとは②同様に列値をカウントアップ ■ BLOB はオフページに行くとはいえ更新時負荷が気になる・このテーブルを 壊すと競技終了に追い込まれかねないのでボツ 14
修正方法(案) ● ③列値をカウントアップする(別方式) ○ pictureテーブルに access_count 列を追加 ■ 一覧表示時 JOIN が不要に ○ あとは②同様に列値をカウントアップ ■ BLOB はオフページに行くとはいえ更新時負荷が気になる・このテーブルを 壊すと競技終了に追い込まれかねないのでボツ 15
順に修正:③非効率な DB データ取得 ● 主な問題点 ○ ヒープメモリ容量の設定ミス ○ テーブル構造の欠陥 ○ 非効率な DB データ取得 ■ N+1、無駄な行・列の取得 16
再度 picture テーブルを見てみる ● 画像が BLOB で入っている ○ ファイルに移して列を削除? ○ 容量は 1GiB ちょっと ■ キャッシュもギリギリ可? ○ 一覧表示で image 列は不要 17
一覧表示のコード(ListItem.java)を見てみる
CountLogDAO countDAO = new CountLogDAO();
PictureDAO pictureDAO = new PictureDAO();
StringBuffer sb = new StringBuffer();
(中略)
List<AccessCount> countList = countDAO.findTopCount();
sb.append("[");
for (AccessCount accessCount: countList) {
int pictureId = accessCount.getPictureId();
Picture picture = pictureDAO.find(pictureId);
String title = picture.getTitle();
String description = picture.getDescription();
int count = accessCount.getAccessCount();
(後略)
● 無駄な N+1(ループ処理)
○
countDAO.findTopCount() を直せば SQL 1 つで必要な全データが取れそう
18
呼び出し先(CountLogDAO.java)を見てみる
public List<AccessCount> findTopCount() {
(中略)
StringBuffer sb = new StringBuffer();
sb.append("SELECT picture_id, SUM(access_count) AS count_sum FROM count_log");
sb.append(" WHERE picture_id IN (");
sb.append("
SELECT id FROM picture");
sb.append(" ) ");
sb.append(" GROUP BY picture_id");
sb.append(" ORDER BY count_sum DESC, picture_id ASC");
(後略)
● 不思議かつ無駄なサブクエリとの組み合わせ方をしている
● picture テーブルと count_log テーブルを JOIN すれば一発
○
方法①なら picture に count_log を LEFT JOIN & GROUP BY & COUNT(*)
○
方法②なら picture と count_log を 1:1 で INNER JOIN
19
画像ダウンロード関連のコードを見てみる
・GetImage.java
countDAO.incrementCount(pictureId);
Picture picture = pictureDAO.find(pictureId);
String image = Base64.getEncoder().encodeToString(picture.getImage());
sb.append("{");
sb.append("\"pictureId\":" + String.valueOf(pictureId) + ",");
sb.append("\"image\":\"" + image + "\"");
sb.append("}");
(後略)
・PictureDAO.java(find)
ps = db.prepareStatement("SELECT * FROM picture WHERE id = ?");
● 方法②なら count_log テーブルは UPDATE する
○
UPDATE picture.count_log SET access_count = access_count + 1
● id・image 列以外の情報は不要
○
title・description 列を外す
20
ここまでの修正で ● 方法①なら 8,500 点+α(G1GC 化で 9,000 点+α) ○ count_log テーブルに INSERT →ロックが軽い ■ 実質 AUTO_INCREMENT 値の生成時ロックのみ ○ 一覧表示の COUNT(*) スキャン行数が多い(どんどん増える) ● 方法②なら 9,500 点前後(G1GC 化で 10,000 点+α) ○ count_log テーブルに UPDATE →行ロック競合が発生 ○ 一覧表示のスキャン行数は少ない(25 行✖ 2 から増えない) 21
他の修正 ● StringBuffer → StringBuilder ● 接続プーリングを DBCP2 から HikariCP に変える ● Java 11 or 17 に上げる ○ Java 11 で 1.5 倍くらい、17 で 1.6 倍くらいスコアが上がる ● picture テーブルを Caffeine でキャッシュする ○ 方法② & Java 17 との組み合わせで 17,500 点前後 ■ Java 8 のままだと Fail する(GC 性能の問題?) 22
ベンチマーク ● 主な仕様 ○ 60 秒間に成功したリクエストについてスコアを加算 ■ 途中で Fail した場合はその時点までのスコアが結果となる ○ 最初に一覧データ取得の所要時間を計測してスレッド数を決定 ■ 2 〜 20 の範囲 ○ その後は各スレッドで一覧データ取得と一覧最下行にある画像の ダウンロードを繰り返す ■ それぞれ +10 点、+ 2 点 23
ベンチマーク(Goroutine によるスレッド処理)
// 計測時間の起点を取得
now := time.Now()
(中略)
// 設定スレッド数でリクエストを流す
var wg sync.WaitGroup
wg.Add(threads)
i := 0
for i < threads {
go func() {
// スレッド毎の初期値を設定
thscore := 0
thlastcount := -1
thmessage := ""
(中略)
for thmessage == "" && time.Since(now).Seconds() < 60 {
(ここにチェック本体を記述)
}
// 結果を返す(スコア加算・メッセージ返却)
mu.Lock()
defer mu.Unlock()
defer wg.Done()
result.Score += thscore
if thmessage != "" {
result.Message = thmessage
}
}()
i++
}
wg.Wait()
24
ベンチマーク ● チェック内容(主なもの・タイムアウトは 10 秒) ○ 一覧データ:25 行あるか? 項目値は正しいか? (初回のみ)カウンタは全て 0 か? (それ以降)カウンタは降順か? 25 行の合計値が前回の応答時より大きいか? ○ 画像ダウンロード:ID・画像データは正しいか? 25
ベンチマーク ● Go で作った理由 ○ 高速 ■ Java は JIT の問題がある ○ ビルド&デプロイが楽 ■ ワン(シングル)バイナリ 26
実施のメリット(まとめ的なもの) ● 社内事情に合わせて出題できる ○ と言いつつ自社では消化不良のまま失敗しましたが ● 簡単な出題でも意外と盛り上がる ○ 時間に追われると意外と良い判断&対処ができなくて焦る ● 出題者が一番学習効果が高い ○ 曖昧な理解だとそもそも問題が作れない・スコア設定ができない 27
出題を 夏の夜空へ お焚き上げ 28