Tweet
Tweet

Slide 1

Slide 1 text

MySQLパフォーマンス チューニングの基本 ー 実際のトラブルシュートから my.cnf まで Nov 10, 2018 @ OSC新潟 / まみやなおき (@mamy1326)

Slide 2

Slide 2 text

自己紹介 Name ：まみやなおき Twitter：@mamy1326（まみー） 　✔ 普段は PHPer 　✔ 2017年 MySQL が趣味 　✔ 2018年 DNSとネットワーク が趣味 　✔ 奥さまの旦那、娘のおとーちゃん u2

Slide 3

Slide 3 text

今日の目標 MySQLに親しみを持とう 仮説・検証・計測をしよう 運用・改善の大切さを認識しよう 1 2 3 u3

Slide 4

Slide 4 text

前提条件 MySQL 5.5 系 (お話は5.7準拠) メモリ 8 GB ストレージ 20 GB サーバ MySQL on EC2 (AWS) 時期 2017年1月 u4

Slide 5

Slide 5 text

おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u5

Slide 6

Slide 6 text

おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u6

Slide 7

Slide 7 text

トラブルは突然に ・ 落ちるDBサーバ ・ 放置するPM ・ アクセス数減らしお祈り ・ my.cnf 空っぽで怒髪天 実際にあった怖い話 u7

Slide 8

Slide 8 text

トラブルは突然に ・ 落ちるDBサーバ ・ 放置するPM ・ アクセス数減らしお祈り ・ my.cnf 空っぽで怒髪天 実際にあった怖い話 u8

Slide 9

Slide 9 text

my.cnf = MySQLの (ほぼ) 全設定 u9

Slide 10

Slide 10 text

空っぽ の my.cnf だと？ u10

Slide 11

Slide 11 text

てめえらの血は なに色だーーーっ！！ u11

Slide 12

Slide 12 text

my.cnf は デフォルトだと 役に立ちません u12

Slide 13

Slide 13 text

トラブルは突然に 入社して担当になる ✔ 状況を整理する 　　→zabbix導入、MySQL Tuner実行、index調査、etc… そもそもノンチューニング 　　→my.cnf デフォルトだった 怒りのチューニングが始まる u13

Slide 14

Slide 14 text

おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u14

Slide 15

Slide 15 text

承：状況把握、診断 要求性能（MySQLサーバ） u15 request 秒間1000 memory 使用率 80% 前後 storage 使用率 50% 未満

Slide 16

Slide 16 text

承：状況把握、診断 状況把握 request 秒間20 memory 使用率 25% 前後 storage 使用率 95% 以上 u16

Slide 17

Slide 17 text

承：状況把握、診断 診断：MySQL Tuner を使う ・ チューニングポイントの 診断 　　→my.cnfに何が不足かを抽出 ・ GNU GPL なので無償利用可能 　　→Perlのアプリケーション ・ インストールして すぐ使える 　　→設定不要！(GitHubから取得) u17

Slide 18

Slide 18 text

承：状況把握、診断 MySQL Tuner 利用方法 $ wget -O mysqltuner.zip https://github.com/ rackerhacker/MySQLTuner-perl/archive/ master.zip $ unzip mysqltuner.zip $ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root — pass='mamy1326' u18

Slide 19

Slide 19 text

承：状況把握、診断 MySQL Tuner 診断結果 (全体) -------- Recommendations ----------------------------- General recommendations: Enable the slow query log to troubleshoot badqueries Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) innodb_file_per_table=ON innodb_buffer_pool_size (>= 12G) if possible. innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible. u19

Slide 20

Slide 20 text

承：状況把握、診断 MySQL Tuner 診断結果 ① Enable the slow query log to troubleshoot bad queries ✔ スロークエリ出力設定を有効にして… ✔ クエリのトラブルシュートをしよう u20

Slide 21

Slide 21 text

承：状況把握、診断 MySQL Tuner 診断結果 ② query_cache_size (>= 8M) ✔ クエリキャッシュを設定しよう ✔ ただし MySQL 8.0 から無くなる機能 u21

Slide 22

Slide 22 text

MySQL Tuner 診断結果 ③ innodb_file_per_table=ON ✔ テーブルデータが共有領域なので ✔ テーブル個別領域に分けましょう u22 承：状況把握、診断

Slide 23

Slide 23 text

MySQL Tuner 診断結果 ④ innodb_buffer_pool_size (>= 12G) if possible. ✔ データとindexを キャッシュ に載せよう ✔ 物理メモリの 7〜8割 が基準 u23 承：状況把握、診断

Slide 24

Slide 24 text

MySQL Tuner 診断結果 ⑤ innodb_log_file_size should be equals to 1/4 of buffer pool ✔ InnoDBログファイルのサイズ ✔ バッファプールの 4分の1 に設定しよう u24 承：状況把握、診断

Slide 25

Slide 25 text

・ スロークエリログ出力 ・ クエリキャッシュ ・ テーブル個別領域 ・ InnoDBバッファプール ・ InnoDBログファイル 診断のまとめ u25 承：状況把握、診断

Slide 26

Slide 26 text

・ スロークエリログ出力 ・ クエリキャッシュ ・ テーブル個別領域 ・ InnoDBバッファプール ・ InnoDBログファイル 診断のまとめ u26 承：状況把握、診断

Slide 27

Slide 27 text

おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u27

Slide 28

Slide 28 text

転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u28

Slide 29

Slide 29 text

転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u29

Slide 30

Slide 30 text

転：my.cnf 設定と解説 共有領域とは ・ 実データ を 1ファイル で扱う 　　→/var/lib/mysql/ibdata1 ・ ファイルサイズが増え続ける 　　→削除領域は減らず、再利用される u30

Slide 31

Slide 31 text

転：my.cnf 設定と解説 テーブル個別領域とは ・ 実データ を 分けて 扱う 　　→/var/lib/mysql/[DB名]/テーブル名.ibd, frm ・ 削除された分は解放できる 　　→ファイル肥大化を防げる u31

Slide 32

Slide 32 text

共有領域によるデメリット ファイルサイズが増え続ける ✔ データ削除しても容量が減らない 　　→ 削除データはファイル内で再利用される ✔ 追加するだけファイルサイズ増加 　　→ 増え続け、いずれ空き領域がなくなる ストレージ圧迫、サーバ停止 u32

Slide 33

Slide 33 text

承：状況把握、仮説、診断 状況把握 request 秒間20 memory 使用率 25% 前後 storage 使用率 95% 以上 u33

Slide 34

Slide 34 text

テーブル個別領域・設定内容 設定のポイント ・ 既存のテーブルには適用されない ・ 再起動後のCREATEから有効 ・ dump -> リストアが必要 [mysqld] innodb_file_per_table=ON u34

Slide 35

Slide 35 text

承：状況把握、仮説、診断 ・ ファイルディスクリプタ使用増加 　　→テーブル数が多い場合に注意（数千〜） ・ 大きな削除はロックの可能性 　　→バッファプールから削除、つまり… 　　→全てのページチェックされる ・ ただし現在は… テーブル個別領域のデメリット u35

Slide 36

Slide 36 text

承：状況把握、仮説、診断 ・ ファイルディスクリプタ使用増加 　　→HDD -> SSD で性能向上 　　→問題あれば他の技術を検討 ・ 大きな削除はロックの可能性 　　→DROP TABLE, TRUNCATEに限る 　　→計画的に削除すべき ・ 5.6.6 からデフォルトである テーブル個別領域のデメリット u36

Slide 37

Slide 37 text

テーブル個別領域・反映結果 共有領域からテーブル個別領域へ ✔ ストレージを圧迫しなくなった 　　→半分近く空いた ✔ 継続的に未使用領域を解放 　　→監視しつつ適切に解放できるようになった ストレージ容量問題が改善 u37

Slide 38

Slide 38 text

承：状況把握、仮説、診断 ✔ 未使用領域の解放 ✔ ストレージ圧迫問題解決 ✔ テーブル最適化が可能に ✔ デメリットは顕在化しなかった テーブル個別領域のまとめ u38

Slide 39

Slide 39 text

登壇者 給水ポイント u39

Slide 40

Slide 40 text

u40

Slide 41

Slide 41 text

転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u41

Slide 42

Slide 42 text

転：my.cnf 設定と解説 InnoDBバッファプールとは ・ キャッシュ領域 　　→データとindexが対象、ディスクI/O減少 ・ メモリの 70〜80% 　　→理想は全てのデータとindex ・ 読み書き 両方 に効果 　　→書き込みの速度も上がる u42

Slide 43

Slide 43 text

転：my.cnf 設定と解説 SELECT の動き ・ バッファプール を見る 　　→メモリから取ろうとする ・ なければ 実データ を読む 　　→ディスクI/Oが発生する ・ その後バッファプールに載せる 　　→データから吸い上げてくれる u43

Slide 44

Slide 44 text

転：my.cnf 設定と解説 INSERT, UPDATE, DELETE の動き ・ バッファプール に書く 　　→空きがなければ、古いページを押し出す ・ その後、ログファイル に書く 　　→commit反映待ちのログへ ・ 実データファイルへ書く 　　→実データ＋ログで完全なデータ u44

Slide 45

Slide 45 text

転：my.cnf 設定と解説 DROP TABLE の動き ・ バッファプール から削除 　　→テーブル全てのページ ・ その後、ログファイル に書く 　　→commit反映待ちのログへ ・ 実データファイルを削除 　　→順番は更新系と同じ u45

Slide 46

Slide 46 text

必ず バッファプールから 読み書き している u46

Slide 47

Slide 47 text

つまり バッファプールが データの原本 u47

Slide 48

Slide 48 text

バッファプール ・設定内容 設定のポイント ・ メモリ 8G の 75% = 6G ・ mysql再起動 で反映される ・ 割り当て状況を監視して運用を [mysqld] innodb_buffer_pool_size=6G u48

Slide 49

Slide 49 text

バッファプール ・反映結果 データとindexをキャッシュ ✔ 読み込みの高速化 　　→単純SELECTだけでなく、スロークエリも改善 ✔ 書き込みの高速化 　　→INSERT, UPDATE, DELETEも高速に さばけるプロセス数が増加！ u49

Slide 50

Slide 50 text

✔ データの原本 ✔ キャッシュを有効活用 ✔ 読み込み性能が向上 ✔ 書き込み性能も向上 バッファプールのまとめ u50 転：my.cnf 設定と解説

Slide 51

Slide 51 text

転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u51

Slide 52

Slide 52 text

転：my.cnf 設定と解説 InnoDBログファイルとは ・ 前もって書き込むログ 　　→commit結果（反映待ち）を溜めるファイル ・ データの耐久性を実現 　　→クラッシュしてもリカバリーできる ・ 書き込み系の速度向上 　　→index追加、カラム追加も速度向上 u52

Slide 53

Slide 53 text

転：my.cnf 設定と解説 大まかな commit時 の動き バッファ プールへ 書き込み InnoDB ログへ 書き込み 実ファイル へ 書き込み u53 プロセスの処理はここまで 非同期書き込み

Slide 54

Slide 54 text

転：my.cnf 設定と解説 InnoDBログのメリット ・ ディスクI/Oを節約して高速 　　→実ファイル反映を待たずにプロセス終了 ・ クラッシュリカバリに利用 　　→未反映commitを実ファイルに適用 u54

Slide 55

Slide 55 text

InnoDBログ ・設定内容 設定のポイント ・ バッファプールより少なく（1G程度） 　　→innodb_log_files_in_groupも忘れずに ・ 古いログファイル削除 (5.6.8未満) ・ mysql再起動 [mysqld] innodb_log_file_size=1G u55

Slide 56

Slide 56 text

✔ commit蓄積・ディスクI/O削減 ✔ クラッシュリカバリできる ✔ 書き込み性能も向上 ✔ サイズを適切にチューニング InnoDBログのまとめ u56 転：my.cnf 設定と解説

Slide 57

Slide 57 text

my.cnf 設定まとめ u57

Slide 58

Slide 58 text

① テーブル個別領域 　・ストレージ圧迫回避 ② InnoDBバッファプール 　・メモリ有効活用、読み書きが高速 ③ InnoDBログファイル 　・バッファプールと合わせて高速化 my.cnf設定のまとめ u58 転：my.cnf 設定と解説

Slide 59

Slide 59 text

おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u59

Slide 60

Slide 60 text

その前に 反映手順 をご紹介 u60

Slide 61

Slide 61 text

my.cnf 設定・反映手順 ① DBへのアクセスを停止 ② dump取得 ③ 更新後の my.cnf に差し替え ④ MySQL 再起動 ⑤ バックアップからリストア ⑥ 動作検証・経過観察 u61

Slide 62

Slide 62 text

パフォーマンスチューニング 総評 u62

Slide 63

Slide 63 text

結：設定結果のパフォーマンス ✔ 秒間 1000 アクセス 達成 ✔ ストレージ 圧迫解消 ✔ バッファプールとログで 高速化 ✔ スロークエリ 可視化・撲滅 ✔ サービスと事業が 軌道に my.cnf チューニング 総評 u63

Slide 64

Slide 64 text

おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u64

Slide 65

Slide 65 text

まとめ・課題 まとめ ・ my.cnf 設定 しよう！ 　　→適切なパフォーマンスを得る ・ 監視・計測 しよう！ 　　→サービスは成長する ・ 継続的に 改善 しよう！ u65

Slide 66

Slide 66 text

今日の目標 MySQLに親しみを持とう 仮説・検証・計測をしよう 運用・改善の大切さを認識しよう 1 2 3 u66

Slide 67

Slide 67 text

まとめ・課題 課題 ・継続監視の仕組み導入 　　→zabbix拡充、mackerelなど ・ レプリケーション 　　→参照、書き込みを分け、冗長構成 ・ リファクタリング 　　→プログラム改修、ERD設計しなおし u67

Slide 68

Slide 68 text

おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u68

Slide 69

Slide 69 text

さいごに 運用は大切 ・ 後から変更は大変 ・ インフラは他人事じゃない ・ 0 -> 1 以降も大事 ・ データの寿命はアプリより長い u69

Slide 70

Slide 70 text

データの寿命は アプリより長い u70

Slide 71

Slide 71 text

RDBに 親しもう u71

Slide 72

Slide 72 text

他人任せにせず 世界を広げて… u72

Slide 73

Slide 73 text

楽しい エンジニアライフを！ u73

Slide 74

Slide 74 text

u74

Slide 75

Slide 75 text

デフォルトダメ・ゼッタイ #f0f0f0 #666666 #e6855e #5ec84e #f0f0f0 　あいうえおかきくけこさしすせそ #e6855e 　あいうえおかきくけこさしすせそ #5ec84e 　あいうえおかきくけこさしすせそ