2023/9月に社内で実施したWebパフォーマンス改善ハンズオンのスライド資料になります。
WEBパフォーマンス改善ハンズオン
View Slide
本ハンズオンの目的本を読んでも分かった気になっちゃう。手を動かして経験してみよう!学ぶこと● パフォーマンス改善の進め方● パフォーマンス改善の方法○ nginx○ MySQL○ Linux(Ubuntu)※ApacheやPostgreSQLに知識の流用は可能
アジェンダ1日目● 各種環境ログイン● 座学①:モニタリング● モニタリング準備(ハンズオン)○ 各種ツールの説明○ 各種インストール手順等● 座学②:パフォーマンス改善● パフォーマンス改善(ハンズオン)○ 計測してみる○ 改善してみる○ ベンチマークサーバ使ってみる● 付録:その他改善まとめ2日目● ハンズオン(1時間)○ 各自private-isuの改善を行う● 結果発表(15分)○ 全チームのスコアを計測!● 感想(15分)
準備作業ハンズオンで利用する環境へのログイン方法について説明します。お手元のターミナルソフトにて、鍵認証方式にてハンズオン環境にSSH接続してください。以下、”RLogin”ツールでログインする場合の例エントリー名 :任意プロトコル :sshホスト名(サーバIPアドレス) :別途提示ログインユーザ名 :ubuntu[SSH認証鍵]をクリックして、別途配布するキーペアファイルを選択。
ログインできたら無事にログインできたら、自分の環境情報を少し確認してみましょう。# OS情報$ lsb_release -a$ cat /etc/os-release# ホスト名確認$ hostname# ホスト名を変更する場合$ sudo hostnamectl set-hostname {ホスト名}# IPアドレス(プライベート)$ ip -br -4 address# IPアドレス(グローバル)※AWSでは、サーバのインターフェイスに設定されている訳ではなくて、プライベートIPにマッピングされているため、通常のコマンドでは確認できない。$ curl http://checkip.amazonaws.com/(実行例)~# lsb_release -aNo LSB modules are available.Distributor ID: UbuntuDescription: Ubuntu 22.04.2 LTSRelease: 22.04Codename: jammy~# hostnameisucon-bench.server~# ip -br -4 addresslo UNKNOWN 127.0.0.1/8ens5 UP XXX.XX.XX.XX/20 metric 100~# curl http://checkip.amazonaws.com/XXX.YYY.ZZZ.AAA
座学①:モニタリング
モニタリングとは。とりあえず細かいことは置いといて「なんか、リソース状況とか見るんでしょ?」→ では、Linux環境における手動での内部監視を実施してみましょう(P.29~)● topコマンド :主にCPU利用率を確認したい(だけじゃない)● freeコマンド :メモリ利用状況を確認Tasks: 113 total, 1 running, 112 sleeping, 0 stopped, 0 zombie%Cpu(s): 0.0 us, 3.1 sy, 0.0 ni, 96.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 stMiB Mem : 3827.7 total, 2604.6 free, 605.3 used, 617.8 buff/cacheMiB Swap: 0.0 total, 0.0 free, 0.0 used. 2982.1 avail MemPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND1 root 20 0 166332 11516 8316 S 0.0 0.3 1:00.48 systemd2 root 20 0 0 0 0 S 0.0 0.0 0:00.40 kthreadd3 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_gp4 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_par_gp5 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 slub_flushwq6 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 netns8 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 kworker/0:0H-events_highpri10 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 mm_percpu_wq11 root 20 0 0 0 0 I 0.0 0.0 0:00.00 rcu_tasks_rude_kthread12 root 20 0 0 0 0 I 0.0 0.0 0:00.00 rcu_tasks_trace_kthread13 root 20 0 0 0 0 S 0.0 0.0 0:00.13 ksoftirqd/014 root 20 0 0 0 0 I 0.0 0.0 0:00.20 rcu_sched15 root rt 0 0 0 0 S 0.0 0.0 0:00.05 migration/0$ free -htotal used free shared buff/cache availableMem: 3.7Gi 605Mi 2.5Gi 0.0Ki 617Mi 2.9GiSwap: 0B 0B 0B
実際に負荷かけてみる①なんとなく正常な状態なのは分かるけど、実際に負荷をかけたらどうなるのか。(P.30)まずは、stressコマンドを使えるようにパッケージをインストール。$ sudo apt install stressそのあと、別ターミナル画面を開いて以下のように負荷をかける。(終了するときは、Ctrl+C)$ stress --cpu 2 → topコマンドで確認$ stress -m 2 --vm-bytes 512M --vm-hang 0 → freeコマンドで確認top - 16:38:37 up 4:40, 3 users, load average: 0.31, 0.07, 0.02Tasks: 120 total, 3 running, 117 sleeping, 0 stopped, 0 zombie%Cpu(s): 99.7 us, 0.3 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 stMiB Mem : 3827.7 total, 2449.1 free, 602.4 used, 776.2 buff/cacheMiB Swap: 0.0 total, 0.0 free, 0.0 used. 2983.4 avail MemPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND3578 root 20 0 3704 256 256 R 100.0 0.0 0:09.51 stress3577 root 20 0 3704 256 256 R 99.7 0.0 0:09.50 stress1 root 20 0 167804 13052 8316 S 0.0 0.3 1:01.46 systemd2 root 20 0 0 0 0 S 0.0 0.0 0:00.40 kthreadd$ free -htotal used free shared buff/cache availableMem: 3.7Gi 1.6Gi 1.4Gi 0.0Ki 776Mi 1.9GiSwap: 0B 0B 0B
実際に負荷かけてみる②負荷をかけた際のモニタリングツール実行結果の変化が確認できたと思います。この他にも、以下のようなツールを利用する事で様々なリソース状況を確認できます。❏ vmstat :CPU・メモリ・Disk I/O情報など❏ sysstat (sarコマンド) :CPU❏ w / uptime :ロードアベレージ❏ dstat :CPU・I/O・ネットワーク転送量※個別のインストールが必要なツールもありますubuntu@nakai-server:~$ vmstat 1 20 -tprocs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- -----timestamp-----r b swpd free buff cache si so bi bo in cs us sy id wa st JST0 0 0 2523680 30012 765032 0 0 16 9 111 193 1 0 99 0 0 2023-09-19 17:12:110 0 0 2523428 30012 765032 0 0 0 0 207 386 0 0 100 0 0 2023-09-19 17:12:120 0 0 2523428 30012 765032 0 0 0 0 206 387 0 0 100 0 0 2023-09-19 17:12:130 0 0 2523428 30012 765032 0 0 0 0 211 386 0 0 100 0 0 2023-09-19 17:12:140 0 0 2523428 30012 765032 0 0 0 0 221 394 0 0 100 0 0 2023-09-19 17:12:150 0 0 2523428 30012 765032 0 0 0 0 220 386 0 0 100 0 0 2023-09-19 17:12:160 0 0 2523428 30012 765032 0 0 0 0 228 407 0 0 100 0 0 2023-09-19 17:12:170 0 0 2523428 30012 765032 0 0 0 0 222 401 0 0 100 0 0 2023-09-19 17:12:182 0 0 1634624 30012 765032 0 0 0 0 316 419 2 17 82 0 0 2023-09-19 17:12:19 ★ここで負荷かけた0 0 0 1472840 30012 765032 0 0 0 0 224 390 1 3 97 0 0 2023-09-19 17:12:200 0 0 1472840 30012 765032 0 0 0 0 219 390 0 0 100 0 0 2023-09-19 17:12:210 0 0 1472840 30012 765032 0 0 0 0 224 390 0 0 100 0 0 2023-09-19 17:12:220 0 0 1472840 30012 765032 0 0 0 0 212 388 0 0 100 0 0 2023-09-19 17:12:23
リアルタイムな負荷状況は確認できたけど。システム運用上、手動でのリアルタイムな確認だけでは不十分です。╺ Webサービスのレスポンスが遅かったので原因を調査してほしい- 何がボトルネックだったのか原因追及が必要(CPU・Mem・Disk I/Oなど)- リソースではなく、ミドルウェアやアプリの設定パラメータが要因かも╺ 新機能リリース後にパフォーマンスがどう変化したか確認したい- 時系列でのパフォーマンスの推移をウォッチこういった場合に備えて、様々な種類のリソース情報を定期的に取得・蓄積しておく事が重要となります。モニタリングツールの導入(P.31~)● メトリクスを自動で収集し保存● 保存したメトリクスをWebブラウザ等で時系列順に表示● メトリクスが特定の閾値に達すると通知を行うメトリクス=その時の状態を定量的に示した値
モニタリングツールOSSのツールとしては、Nagios・Zabbix・Prometheus・・・などがある。以下はZabbixの場合です● WEBモニタリング機能含めたオールインワンパッケージ○ 他ではグラフィカル機能が脆弱なので、Grafanaなど外部ソフトと連携させるケースがほとんど○ オールインワンタイプならではの弊害も● 監視クライアントには、Zabbixエージェントを実装。(SNMPなども利用可能)管理者監視サーバ監視対象Zabbix-ServerWEB+PHPDatabase監視リクエストアラート通知各種設定、閲覧監視データ保存Zabbix-AgentSNMP収集データ送付シンプルチェック監視実行
改めて、モニタリングの意義。◀引用元:https://www.cybertrust.co.jp/zabbix/about-zabbix.html適切なモニタリングを実施する事で、以下のような事が可能となる(はず)。● 「通常とは異なる状態」を検知して修正に着手 ---> パフォーマンス改善● キャパシティプランニング(システム増強の判断材料)● コスト最適化(スペック過不足修正)※リソースは有限。お金かかる。
座学① まとめメトリクスの収集蓄積異常状態をリアルタイムで検知傾向分析リソース最適化 インフラコスト最適化キャパシティプランニングボトルネックの追跡・調査復旧対応システム改修ハンズオンのテーマ
座学②:パフォーマンス改善
そのまえにこの資料はハンズオン参加者全員に配ります。なので、メモは不要です。後で見返したり、ハンズオン2日目の時の参考資料にしてください。実務的な事を言えば、パフォーマンス改善の目標や目的も大事ですが、今回はそういった話は一旦除外します。パフォーマンス改善を実体験してもらうのが趣旨です。
パフォーマンス改善のフローよくみる水道管で説明する。通信網データベースパフォーマンス改善の基本はボトルネックとなっている場所を改修することです。図はWebの処理の流れの一部を簡略的に現したものです。この例だと、データベースをチューニングしても水の流れる量は変わりません。なので、パフォーマンス改善の流れは● 計測する(遅い場所を見つける)● チューニングする(改修する)● 計測する(効果測定する)この繰り返し作業になります。ボトルネック以外は触らないでくださいアプリ
Webサービスの構造理解● 構造を理解しないと改善方法は見えてきませんが、最初は荒い解像度での理解で問題なし● 計測して問題を見つけていく中で高い解像度で理解を深めていけば良い「DBのこのQueryが遅い!」とわかったなら、なぜ遅いのかを調べて理解すれば良い。図でいうなら、DBの裏にはDiskがあるが、Diskは1回のアクセスで何byte持ってこれるのか?秒間のIO速度は?なんて事は今は気にしなくて良い。(そして、そんな事は忘れてもよい)図:引用元「達人が教えるWebパフォーマンスチューニング」
ハンズオンの範囲● サーバのスペックを上げたり、台数増やすとかは範囲外です。● 簡単にでもOSのチューニングも部分的にいきたいが、付録に記載程度に留める事になるかも● nginx、PHP、MySQLのチューニングがメインとなります図:引用元「達人が教えるWebパフォーマンスチューニング」
private-isuチューニング開始
今からやる事パフォーマンス改善の準備及びフローを体験する● WebAppサーバにて計測の準備○ ログフォーマットの変更やPHPに変更 etc.● BenchMarkerサーバにログインして計測する○ BenchMarkerの実行● WebAppサーバに入って遅い原因を調査・修正○ ログを確認、設定の変更 etc.これを繰り返す
web server login● AWSにsshでログインしてください# ssh [email protected] -i ~/.ssh/isucon-key.pem● isuconユーザーに切り替えてください$ sudo su - isucon● Private IPを調べて下さい(後で使います)● WebブラウザでIscogramを開いて触ってみる○ URL: http://12.34.WEB.IP/ にアクセス○ ユーザ登録○ コメント投稿○ 画像投稿などなど# Private IP 確認$ hostname -I12.34.PRV.IP📄IPアドレスは各チームで違うので資料上は以下とします。WebServer:12.34.WEB.IPWebServerのPrivateIP:12.34.PRV.IPBenchmarkServer:56.78.BM.IP📄負荷をかける際に指定するIPです📄みんなログイン出来るまで待って下さい
PHP enable● PHPを有効化させてください# Ruby 無効化$ sudo systemctl stop isu-ruby$ sudo systemctl disable isu-ruby# nginx 設定を変更$ sudo rm /etc/nginx/sites-enabled/isucon.conf$ sudo ln -s /etc/nginx/sites-available/isucon-php.conf /etc/nginx/sites-enabled/# nginx 再起動$ sudo systemctl reload nginx# PHP 有効化$ sudo systemctl start php8.1-fpm$ sudo systemctl enable php8.1-fpm📄デフォルトではRuby📄PHPでやりたいチームはこちら
Go enable● Goを有効化させてください# ruby 無効化$ sudo systemctl stop isu-ruby$ sudo systemctl disable isu-ruby# Go 有効化$ sudo systemctl start isu-go$ sudo systemctl enable isu-go# エラーなどの出力$ sudo journalctl -f -u isu-go📄Goでやりたいチームはこちら
nginx log format● nginxのログフォーマットを変更# backup 作成 設定ミスった時の戻し用$ sudo cp -pr /etc/nginx/nginx.conf /etc/nginx/__nginx.conf.20230913# vimコマンドで設定書き換え$ sudo vim /etc/nginx/nginx.conf# access_logの指定を削除し、# 右の通り、log_formatとaccess_logを指定する# 変更した差分を確認し、意図した変更しかないかを確認する$ diff /etc/nginx/nginx.conf /etc/nginx/__nginx.conf.20230913💡後でログを解析するためlog_format json escape=json '{"time": "$time_iso8601",''"host": "$remote_addr",''"port": "$remote_port",''"method": "$request_method",''"uri": "$request_uri",''"status": "$status",''"body_bytes": "$body_bytes_sent",''"referer": "$http_referer",''"ua": "$http_user_agent",''"request_time": "$request_time",''"response_time": "$upstream_response_time"}';access_log /var/log/nginx/access.log json;
nginx log format● 設定を有効化し、ログを確認する# nginx シンタックスエラーが起きていない事を確認$ sudo nginx -tnginx: the configuration file /etc/nginx/nginx.conf syntax is oknginx: configuration file /etc/nginx/nginx.conf test is successful# logを削除(json形式じゃないlogが解析の邪魔となる)$ sudo rm /var/log/nginx/access.log# nginx 再起動(設定が有効化される)$ sudo systemctl reload nginx# logを確認しながらブラウザでアクセスしてみる$ sudo tail -f /var/log/nginx/access.log💡解析できるようにjson形式ではないログは消しておきましょう$ diff -u /etc/nginx/__nginx.conf.20230913 /etc/nginx/nginx.conf-- access_log /var/log/nginx/access.log;+ log_format json escape=json '{"time": "$time_iso8601",'+ '"host": "$remote_addr",'+ '"port": "$remote_port",'+ '"method": "$request_method",'+ '"uri": "$request_uri",'+ '"status": "$status",'+ '"body_bytes": "$body_bytes_sent",'+ '"referer": "$http_referer",'+ '"ua": "$http_user_agent",'+ '"request_time": "$request_time",'+ '"response_time":"$upstream_response_time"}';+ access_log /var/log/nginx/access.log json;error_log /var/log/nginx/error.log;
MySQL slow query output● slow queryを出力するように設定変更を行う# 設定変更前にバックアップ$ cd /etc/mysql/mysql.conf.d/$ sudo cp -pr mysqld.cnf __mysqld.cnf.20230914# vimで変更を行う$ sudo vim mysqld.cnf-> 変更内容は右を参照# MySQL 再起動$ sudo systemctl restart mysql.service💡long_query_time=0SQLのログ全て吐くぞ!# 変更差分は以下$ sudo diff -u __mysqld.cnf.20230914 mysqld.cnf# Here you can see queries with especially long duration-# slow_query_log = 1-# slow_query_log_file = /var/log/mysql/mysql-slow.log-# long_query_time = 2+slow_query_log = 1+slow_query_log_file = /var/log/mysql/mysql-slow.log+long_query_time = 0
web server monitoring● bench markerを走らせるので、監視状態にしましょう#負荷を監視$ toptop - 19:09:05 up 1:08, 1 user, load average: 0.00, 0.00, 0.00Tasks: 109 total, 1 running, 108 sleeping, 0 stopped, 0 zombie%Cpu(s): 0.3 us, 0.7 sy, 0.0 ni, 99.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 stMiB Mem : 3843.5 total, 2731.3 free, 614.4 used, 497.8 buff/cacheMiB Swap: 0.0 total, 0.0 free, 0.0 used. 2993.6 avail MemPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND22 root 20 0 0 0 0 S 0.3 0.0 0:00.04 ksoftirqd/1612 mysql 20 0 1785564 428168 35940 S 0.3 10.9 0:11.39 mysqld1 root 20 0 101952 12928 8276 S 0.0 0.3 0:02.74 systemd2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd3 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_gp💡ログは後からでも見れるが、詳細な負荷は今しか観れない📄99%暇してる状態ですね!
benchmarker server login & execute● benchmarkerを実行し、現在の状態を計測してみましょう# benchmarkerのサーバにログイン$ ssh [email protected] -i ~/.ssh/isucon-key.pem# isuconユーザに切り替え$ sudo su - isucon# benchmarkerのディレクトリに移動$ cd ~/private_isu.git/benchmarker/# benchmarker 実行 ※2分くらいかかります$ ./bin/benchmarker -u ./userdata -t http://{各自web serverのプライベートIP}/{"pass": true,"score": 741,"success": 853,"fail": 9,"messages": ["リクエストがタイムアウトしました (POST /login)","リクエストがタイムアウトしました (POST /register)"]}📄スコア741点!タイムアウトとかも発生している。計算式:成功(GET) x 1 + 成功(POST) x 2 + 成功(画像投稿) x 5- (サーバエラー(error) x 10 + 失敗(exception)数 x 20 + 遅延(POST) x 100)
web server monitoring● bench marker中の負荷状況を見てみましょう# 負荷を監視$ toptop - 19:32:48 up 1:32, 1 user, load average: 0.54, 0.12, 0.04Tasks: 108 total, 1 running, 107 sleeping, 0 stopped, 0 zombie%Cpu(s): 99.7 us, 0.3 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 stMiB Mem : 3843.5 total, 2715.8 free, 625.9 used, 501.8 buff/cacheMiB Swap: 0.0 total, 0.0 free, 0.0 used. 2982.0 avail MemPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND612 mysql 20 0 1790724 431264 35956 S 199.3 11.0 0:40.65 mysqld1880 ubuntu 20 0 10912 4016 3300 R 0.3 0.1 0:00.05 top1965 isucon 20 0 210960 23612 11880 S 0.3 0.6 0:00.02 php-fpm8.11 root 20 0 101952 12928 8276 S 0.0 0.3 0:02.77 systemd2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd3 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_gp4 root 0 -20 0 0 0 I 0.0 0.0 0:00.00 rcu_par_gp💡mysqlがやばそうだ...📄CPUのリソースが枯渇📄mysqlがボトルネックになっている
web server log analytics● alpをインストールして解析してみましょう。# 作業ディレクトリ変更$ cd /usr/local/src# unzipコマンドをインストール$ sudo apt-get install zip# alpをインストール$ sudo wget https://github.com/tkuchiki/alp/releases/download/v1.0.16/alp_linux_amd64.zip$ sudo unzip alp_linux_amd64.zip$ sudo install alp /usr/local/bin/alp💡grepとかでも解析できるけど、ツールがあると便利
web server log analytics● alpを確認$ cat /var/log/nginx/access.log | alp json -o count,method,uri,min,avg,max,sum -m "/image/.+,/posts/[0-9]+,/@.+" --sort=sum -r+-------+--------+--------------------+-------+-------+-------+---------+| COUNT | METHOD | URI | MIN | AVG | MAX | SUM |+-------+--------+--------------------+-------+-------+-------+---------+| 49 | GET | / | 0.452 | 6.288 | 9.984 | 308.092 || 532 | GET | /image/.+ | 0.000 | 0.546 | 8.652 | 290.544 || 14 | GET | /@.+ | 0.596 | 3.500 | 4.972 | 49.002 || 40 | POST | /login | 0.004 | 0.783 | 5.072 | 31.308 || 4 | GET | /posts | 6.804 | 7.659 | 8.428 | 30.636 || 25 | GET | /posts/[0-9]+ | 0.060 | 1.108 | 4.128 | 27.692 || 7 | POST | / | 0.004 | 0.922 | 4.280 | 6.452 || 4 | GET | /logout | 1.004 | 1.186 | 2.260 | 4.744 || 5 | POST | /register | 0.012 | 0.565 | 1.172 | 2.824 || 5 | POST | /comment | 0.008 | 0.466 | 1.980 | 2.328 |...(省略)...💡/ のアクセスが一番遅い平均で見てもかなり遅い📄TOPが遅い(一番改善の余地がでかい)📄画像取得や投稿も時間はかかっているが一番遅い場所を攻めるのが定石
MySQL log analytics● MySQL標準のログ解析$ sudo mysqldumpslow /var/log/mysql/mysql-slow.log -s t | headReading mysql slow query log from /var/log/mysql/mysql-slow.logCount: 1302 Time=0.20s (263s) Lock=0.00s (0s) Rows=2.8 (3650), isuconp[isuconp]@localhostSELECT * FROM `comments` WHERE `post_id` = 'S' ORDER BY `created_at` DESC LIMIT NCount: 1327 Time=0.06s (75s) Lock=0.00s (0s) Rows=1.0 (1327), isuconp[isuconp]@localhostSELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = 'S'Count: 25 Time=0.21s (5s) Lock=0.00s (0s) Rows=7.9 (198), isuconp[isuconp]@localhostSELECT * FROM `comments` WHERE `post_id` = 'S' ORDER BY `created_at` DESCCount: 49 Time=0.06s (3s) Lock=0.00s (0s) Rows=10001.7 (490085), isuconp[isuconp]@localhost💡コメント一覧の取得が実行回数が多く時間がかかっている❓理由は明確ではない📄2.8行返すのに3650行もスキャンしているのは何か臭う
MySQL log analytics (pt-query-digest)● percona-toolkitのインストール● pt-query-digestの実行# percona-toolkitのインストール$ sudo apt update$ sudo apt install percona-toolkit# pt-query-digestの実行$ sudo pt-query-digest --group-by fingerprint --order-by Query_time:sum /var/log/mysql/mysql-slow.log# 630ms user time, 20ms system time, 35.93M rss, 41.89M vsz# Current date: Tue Sep 19 17:05:50 2023# Hostname: ip-XXX-XX-XX-XX# Files: /var/log/mysql/mysql-slow.log# Overall: 9.16k total, 23 unique, 115.92 QPS, 4.35x concurrency _________# Time range: 2023-09-19T07:40:17 to 2023-09-19T07:41:36# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 344s 1us 290ms 38ms 230ms 73ms 113us# Lock time 10ms 0 55us 1us 1us 1us 1us# Rows sent 518.71k 0 9.77k 58.00 2.90 717.75 0.99# Rows examine 251.31M 0 97.68k 28.10k 97.04k 43.80k 0.99# Query size 2.48M 27 999.74k 283.50 80.10 13.63k 38.53
MySQL log analytics (pt-query-digest)● pt-query-digestによる解析# Profile# Rank Query ID Response time Calls R/Call V/M It# ==== ============================== ============== ===== ====== ===== ==# 1 0x624863D30DAC59FA168492821... 257.7752 75.0% 1276 0.2020 0.02 SELECT comments# 2 0x422390B42D4DD86C7539A5F45... 73.7715 21.5% 1298 0.0568 0.01 SELECT comments# MISC 0xMISC 12.3436 3.6% 6584 0.0019 0.0 <21 ITEMS>
MySQL log analytics (pt-query-digest)● pt-query-digestによる解析# Profile# Rank Query ID Response time Calls R/Call V/M It# ==== ============================== ============== ===== ====== ===== ==# 1 0x624863D30DAC59FA168492821... 257.7752 75.0% 1276 0.2020 0.02 SELECT comments# 2 0x422390B42D4DD86C7539A5F45... 73.7715 21.5% 1298 0.0568 0.01 SELECT comments# MISC 0xMISC 12.3436 3.6% 6584 0.0019 0.0 <21 ITEMS># Query 1: 16.15 QPS, 3.26x concurrency, ID 0x624863D30DAC59FA16849282195BE09F at byte 3269909# (省略)# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 13 1276# Exec time 74 258s 40ms 290ms 202ms 253ms 67ms 219ms# Lock time 20 2ms 1us 35us 1us 2us 1us 1us# Rows sent 0 3.50k 0 3 2.81 2.90 0.71 2.90# Rows examine 48 121.69M 97.66k 97.66k 97.66k 97.04k 0 97.04k# Query size 4 104.79k 82 85 84.09 84.10 1.21 80.10# (省略)# Tables# SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'¥G# SHOW CREATE TABLE `isuconp`.`comments`¥G# EXPLAIN /*!50100 PARTITIONS*/SELECT * FROM `comments` WHERE `post_id` = '9989' ORDER BY `created_at` DESC LIMIT 3¥G💡3500行を返すために1億2169万行をスキャンしている💡INDEXがない!
MySQL Explain● Explainを確認し、チューニングする# mysql に接続する$ mysql -u isuconp -p isuconp# 実行計画を確認するmysql> EXPLAIN SELECT * FROM `comments` WHERE `post_id` = '9989' ORDER BY`created_at` DESC LIMIT 3¥G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: commentspartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 99161filtered: 10.00Extra: Using where; Using filesort1 row in set, 1 warning (0.00 sec)📄possible_keys: INDEXの利用状況📄rows: スキャンされた数📄Extra:オプティマイザの実行計画
MySQL tuning● Commentsテーブルに有効なINDEXを付与する# INDEXを付与mysql> ALTER TABLE comments ADD INDEX post_id_idx(post_id, created_at DESC);# 実行計画を確認mysql> EXPLAIN SELECT * FROM `comments` WHERE `post_id` = '9989' ORDER BY `created_at` DESC LIMIT 3¥G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: commentspartitions: NULLtype: refpossible_keys: post_id_idxkey: post_id_idxkey_len: 4ref: constrows: 5filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)📄INDEXが使われており、スキャンされた行数も5行、Extra無し
benchmarker execute(再び)● 再度計測の準備● 再計測する# ログの退避$ sudo mv /var/log/mysql/mysql-slow.log /tmp/__mysql-slow.log.20230919-01# MySQL再起動(ログファイル再生成される)$ sudo systemctl restart mysql.service$ ./bin/benchmarker -u ./userdata -t http://12.34.PRV.IP/# 今回{"pass": true,"score": 23121,"success": 21410,"fail": 0,"messages": []}# 前回{"pass": true,"score": 741,"success": 853,"fail": 9,"messages": ["リクエストがタイムアウトしました (POST /login)","リクエストがタイムアウトしました (POST /register)"]}💡劇的に改善している💡計測前にlogの退避をする事で比較が可能!
2日目に対する Hint
web server monitoring● bench marker中の負荷状況を見てみましょう💡mysqlがやばそうだ...ubuntu@ip-172-31-19-62:~$ toptop - 18:25:26 up 24 min, 1 user, load average: 2.29, 0.55, 0.18Tasks: 111 total, 3 running, 108 sleeping, 0 stopped, 0 zombie%Cpu(s): 71.0 us, 24.7 sy, 0.0 ni, 0.2 id, 0.2 wa, 0.0 hi, 4.0 si, 0.0 stMiB Mem : 3843.5 total, 2133.5 free, 778.9 used, 931.2 buff/cacheMiB Swap: 0.0 total, 0.0 free, 0.0 used. 2826.3 avail MemPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND694 mysql 20 0 1800980 595296 36528 S 102.3 15.1 0:31.89 mysqld540 isucon 20 0 204816 18500 12484 R 14.3 0.5 0:03.96 php-fpm8.11258 isucon 20 0 204816 18060 12048 S 14.3 0.5 0:03.55 php-fpm8.11349 isucon 20 0 206864 20092 12052 S 13.3 0.5 0:03.54 php-fpm8.1539 isucon 20 0 210960 24628 12800 S 12.6 0.6 0:03.79 php-fpm8.1534 www-data 20 0 56540 7196 4680 S 12.3 0.2 0:02.99 nginx1465 isucon 20 0 206864 20112 12052 S 12.3 0.5 0:03.32 php-fpm8.1449 memcache 20 0 414456 7876 3516 S 3.0 0.2 0:01.08 memcached535 www-data 20 0 56200 7044 4680 S 1.0 0.2 0:00.41 nginx1156 ubuntu 20 0 10896 3996 3300 R 0.7 0.1 0:00.08 top13 root 20 0 0 0 0 R 0.3 0.0 0:00.05 ksoftirqd/0📄CPUのリソースが枯渇📄mysqlがボトルネックになっている
MySQL log analytics● pt-query-digestの実行# バイナリーデータがだらっと出るのでファイル出力$ sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/digest.txt# 出力を確認$ head -n100 /tmp/digest.txt# 8.6s user time, 40ms system time, 49.40M rss, 55.35M vsz# Current date: Tue Sep 19 18:31:34 2023# Hostname: ip-XXX-XX-XX-XX# Files: /var/log/mysql/mysql-slow.log# Overall: 142.72k total, 26 unique, 2.30k QPS, 2.85x concurrency ________# Time range: 2023-09-19T09:23:43 to 2023-09-19T09:24:45# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 177s 1us 186ms 1ms 2ms 9ms 113us# Lock time 219ms 0 17ms 1us 1us 50us 1us# Rows sent 10.28M 0 9.86k 75.52 2.90 844.71 0.99# Rows examine 48.49M 0 97.79k 356.27 9.83 4.37k 0.99# Query size 58.66M 27 1.14M 430.97 84.10 17.29k 38.53📄スキャン数と返却数は問題なさそう
MySQL log analytics● pt-query-digestの実行# Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ============================= ============= ===== ====== ===== ====# 1 0x4858CF4D8CAA743E839C127C... 80.9466 45.8% 911 0.0889 0.01 SELECT posts# 2 0x19759A5557089FD5B718D440... 18.0758 10.2% 12734 0.0014 0.00 SELECT posts# 3 0xCDEB1AFF2AE2BE51B2ED5CF0... 16.5464 9.4% 244 0.0678 0.01 SELECT comments# 4 0x7A12D0C8F433684C3027353C... 16.4759 9.3% 150 0.1098 0.01 SELECT posts# 5 0x396201721CD58410E070DA94... 15.1694 8.6% 61515 0.0002 0.00 SELECT users# 6 0x624863D30DAC59FA16849282... 6.6564 3.8% 23912 0.0003 0.00 SELECT comments# 7 0x422390B42D4DD86C7539A5F4... 6.3023 3.6% 25194 0.0003 0.00 SELECT comments# 8 0x009A61E5EFBD5A5E4097914B... 5.9607 3.4% 93 0.0641 0.04 INSERT posts# 9 0xAA65B65D6FEC3514934B1439... 4.7462 2.7% 244 0.0195 0.00 SELECT posts# MISC 0xMISC 5.7416 3.3% 17722 0.0003 0.0 <17 ITEMS>💡ボトルネックがコメント一覧ではなくなった📄postsのSELECTが重い様子
MySQL log analytics● pt-query-digestの実行# Query 1: 15.02 QPS, 1.31x concurrency, ID 0x4858CF4D8CAA743E839C127C71B69E75 at byte 64028201# (省略)# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 0 931# Exec time 46 81s 14ms 167ms 87ms 116ms 20ms 87ms# Lock time 0 1ms 0 44us 1us 1us 1us 1us# Rows sent 85 8.92M 9.77k 9.86k 9.81k 9.80k 49.50 9.80k# Rows examine 36 17.84M 19.53k 19.72k 19.63k 19.40k 0 19.40k# Query size 0 83.64k 92 92 92 92 0 92# (省略)# Tables# SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'¥G# SHOW CREATE TABLE `isuconp`.`posts`¥G# EXPLAIN /*!50100 PARTITIONS*/SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC¥G📄スキャン数と返却数に違和感はない📄EXPLAINをみてみるべき
MySQL log analytics● EXPLAINを確認し、チューニングしてみる# MySQLに接続$ mysql -u isuconp -p isuconp# 実行計画を確認mysql> EXPLAIN SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC¥G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: postspartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 9856filtered: 100.00Extra: Using filesort1 row in set, 1 warning (0.00 sec)📄INDEXが使われていない📄フルスキャンされている
MySQL log analytics● EXPLAINを確認し、チューニングしてみる🚫マネしてはいけません!# INDEXを追加mysql> ALTER TABLE posts ADD INDEX created_at_idx(created_at DESC);# EXPLAINを再度実行mysql> EXPLAIN SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC¥G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: postspartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 9856filtered: 100.00Extra: Using filesort1 row in set, 1 warning (0.00 sec)📄INDEXが使われていない📄フルスキャンされている📄何も改善していない
MySQL log analytics● EXPLAINを確認し、チューニングしてみる🚫マネしてはいけません!# EXPLAINを再度実行mysql> EXPLAIN SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` force index (created_at_idx)ORDER BY `created_at` DESC¥G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: postspartitions: NULLtype: indexpossible_keys: NULLkey: created_at_idxkey_len: 4ref: NULLrows: 9856filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)📄強制的にINDEX使う事は出来る(速くなってるかはわからない)
MySQL log analytics● EXPLAINを確認し、チューニングしてみる🚫マネしてはいけません!# EXPLAINを再度実行mysql> EXPLAIN SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` force index (created_at_idx)ORDER BY `created_at` DESC¥G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: postspartitions: NULLtype: indexpossible_keys: NULLkey: created_at_idxkey_len: 4ref: NULLrows: 9856filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)📄強制的にINDEX使う事は出来る📄0.00secなので、速くなってるかはわからない)📄オプティマイザ的にはオススメしないやり方📄別の回避策を探しましょう※全行取得する必要あるの?
次はどうする?
パフォーマンス改善のフロー(再び)よくみる水道管で説明する。通信網データベースパフォーマンス改善の基本はボトルネックとなっている場所を改修することです。図はWebの処理の流れの一部を簡略的に現したものです。この例だと、データベースをチューニングしても水の流れる量は変わりません。なので、パフォーマンス改善の流れは● 計測する(遅い場所を見つける)● チューニングする(改修する)● 計測する(効果測定する)この繰り返し作業になります。ボトルネック以外は触らないでくださいアプリ
1日目終了お疲れ様でした!
appendix
VSCodeからRemote SSHでLinuxにSSH接続● VSCode に Remote - SSH 拡張機能 をインストールします● ターミナルやPowerShellで ssh-keygen コマンドを実行ssh-keygen -t ed25519 ※保存先はデフォルトでOK。パスフレーズもなしでOK● 作成された ~/.ssh/id_ed25519.pub を開き中身をコピー● UbuntuサーバにターミナルからSSH接続します。● VSCode を開き、左下の緑色のアイコン(><)をクリック→「Remote-SSH Connetct to Host…」→「Configure SSH Hosts…」→「/Users/ユーザー名/.ssh/config」● 以下の内容を追記● 左下の緑色のアイコンから「Remote-SSH Connetct to Host…」で<接続先名>を選択● 「ファイル」→「フォルダーを開く」から任意のフォルダを選択sudo su - isucon #isuconユーザーへ切り替えmkdir .sshchmod 700 .sshvim ~/.ssh/authorized_keys# 先程作成した~/.ssh/id_ed25519.pub の内容をauthorized_keysにコピペ# 改行して各人それぞれのキーを保存してください 保存:wqchmod 600 .ssh/authorized_keysHost <接続先名> #ここが接続先名として保存されますHostName ec2-xx-xx-xx-xx.ap-northeast-1.compute.amazonaws.comUser isuconIdentityFile ~/.ssh/id_ed25519
2日目用のHINTネタバレ注意!見たくない人は見ないで!
Performance Hint● CPUやメモリが余っていませんか?○ もっと多くのプロセスを立ち上げて捌ける数が増やせるのでは?○ 今どこで処理がつまっているんでしょうか?● その処理はアプリを経由する必要がありますか?● 全件取得する必要があるのか?呼び出しているアプリ側の実装と比較してみましょう○ N+1問題やSQLが悪い場合、負荷自体はDBにかかります。でも見直すべきは要件に対してアプリ側の実装が悪くないのか?となるので注意。2日目はここから?● too many open filesとは?ERRORが発生したらぐぐってみましょう。ルーター nginx アプリ(PHP) MySQL データ: BLOB/ Text / Image静的ファイル: css/js/image
Performance Hint● プリペアードステートメントを多用していませんか?プラスに働くケースとそうでないケースがあります。SLOW QUERYを解析してみましょう。● MySQLに正しくINDEXが付与されていますか?EXPLAINで確認しましょう● N+1問題(ループ処理で都度SQL実行している)はありませんか?キャッシュを検討しましょう● 外部コマンドの呼び出しは遅い事があります。● SLOW Queryやログの出力も負荷がかかっています。不要なログ出力は止めましょう● memcachedへのアクセスもN+1問題になってしまうケースもあります。● PHPにはpreloadやJITコンパイラというものがあります● nginx junitが速いらしい?
参考資料● github: private-isu● 書籍:達人が教えるWebパフォーマンスチューニング● private-isuをdockerでセットアップした時のメモ(Dockerで試してみたい人向け)● 「PHP-FPM」から「NGINX Unit」に乗り換えるだけで約8倍も応答速度が改善