MySQLパフォーマンスチューニングの基本ー実際のトラブルシュートから my.cnf までー/MySQL_Performance_Tuning_Basics_in_OSC_Niigata

2018年11月10日(土) にOSC新潟でお話しする
MySQLパフォーマンスチューニングの基本
　ー実際のトラブルシュートから my.cnf までー
の登壇資料です

Transcript

1. MySQLパフォーマンス チューニングの基本 ー 実際のトラブルシュートから my.cnf まで Nov 10, 2018 @ OSC新潟 / まみやなおき (@mamy1326)
2. 自己紹介 Name ：まみやなおき Twitter：@mamy1326（まみー） 　✔ 普段は PHPer 　✔ 2017年 MySQL が趣味 　✔ 2018年 DNSとネットワーク が趣味 　✔ 奥さまの旦那、娘のおとーちゃん u2
3. 今日の目標 MySQLに親しみを持とう 仮説・検証・計測をしよう 運用・改善の大切さを認識しよう 1 2 3 u3
4. 前提条件 MySQL 5.5 系 (お話は5.7準拠) メモリ 8 GB ストレージ 20 GB サーバ MySQL on EC2 (AWS) 時期 2017年1月 u4
5. おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u5
6. おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u6
7. トラブルは突然に ・ 落ちるDBサーバ ・ 放置するPM ・ アクセス数減らしお祈り ・ my.cnf 空っぽで怒髪天 実際にあった怖い話 u7
8. トラブルは突然に ・ 落ちるDBサーバ ・ 放置するPM ・ アクセス数減らしお祈り ・ my.cnf 空っぽで怒髪天 実際にあった怖い話 u8
9. my.cnf = MySQLの (ほぼ) 全設定 u9
10. 空っぽ の my.cnf だと？ u10
11. てめえらの血は なに色だーーーっ！！ u11
12. my.cnf は デフォルトだと 役に立ちません u12
13. トラブルは突然に 入社して担当になる ✔ 状況を整理する 　　→zabbix導入、MySQL Tuner実行、index調査、etc… そもそもノンチューニング 　　→my.cnf デフォルトだった 怒りのチューニングが始まる u13
14. おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u14
15. 承：状況把握、診断 要求性能（MySQLサーバ） u15 request 秒間1000 memory 使用率 80% 前後 storage 使用率 50% 未満
16. 承：状況把握、診断 状況把握 request 秒間20 memory 使用率 25% 前後 storage 使用率 95% 以上 u16
17. 承：状況把握、診断 診断：MySQL Tuner を使う ・ チューニングポイントの 診断 　　→my.cnfに何が不足かを抽出 ・ GNU GPL なので無償利用可能 　　→Perlのアプリケーション ・ インストールして すぐ使える 　　→設定不要！(GitHubから取得) u17
18. 承：状況把握、診断 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
19. 承：状況把握、診断 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
20. 承：状況把握、診断 MySQL Tuner 診断結果 ① Enable the slow query log to troubleshoot bad queries ✔ スロークエリ出力設定を有効にして… ✔ クエリのトラブルシュートをしよう u20
21. 承：状況把握、診断 MySQL Tuner 診断結果 ② query_cache_size (>= 8M) ✔ クエリキャッシュを設定しよう ✔ ただし MySQL 8.0 から無くなる機能 u21
22. MySQL Tuner 診断結果 ③ innodb_file_per_table=ON ✔ テーブルデータが共有領域なので ✔ テーブル個別領域に分けましょう u22 承：状況把握、診断
23. MySQL Tuner 診断結果 ④ innodb_buffer_pool_size (>= 12G) if possible. ✔ データとindexを キャッシュ に載せよう ✔ 物理メモリの 7〜8割 が基準 u23 承：状況把握、診断
24. MySQL Tuner 診断結果 ⑤ innodb_log_file_size should be equals to 1/4 of buffer pool ✔ InnoDBログファイルのサイズ ✔ バッファプールの 4分の1 に設定しよう u24 承：状況把握、診断
25. ・ スロークエリログ出力 ・ クエリキャッシュ ・ テーブル個別領域 ・ InnoDBバッファプール ・ InnoDBログファイル 診断のまとめ u25 承：状況把握、診断
26. ・ スロークエリログ出力 ・ クエリキャッシュ ・ テーブル個別領域 ・ InnoDBバッファプール ・ InnoDBログファイル 診断のまとめ u26 承：状況把握、診断
27. おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u27
28. 転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u28
29. 転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u29
30. 転：my.cnf 設定と解説 共有領域とは ・ 実データ を 1ファイル で扱う 　　→/var/lib/mysql/ibdata1 ・ ファイルサイズが増え続ける 　　→削除領域は減らず、再利用される u30
31. 転：my.cnf 設定と解説 テーブル個別領域とは ・ 実データ を 分けて 扱う 　　→/var/lib/mysql/[DB名]/テーブル名.ibd, frm ・ 削除された分は解放できる 　　→ファイル肥大化を防げる u31
32. 共有領域によるデメリット ファイルサイズが増え続ける ✔ データ削除しても容量が減らない 　　→ 削除データはファイル内で再利用される ✔ 追加するだけファイルサイズ増加 　　→ 増え続け、いずれ空き領域がなくなる ストレージ圧迫、サーバ停止 u32
33. 承：状況把握、仮説、診断 状況把握 request 秒間20 memory 使用率 25% 前後 storage 使用率 95% 以上 u33
34. テーブル個別領域・設定内容 設定のポイント ・ 既存のテーブルには適用されない ・ 再起動後のCREATEから有効 ・ dump -> リストアが必要 [mysqld] innodb_file_per_table=ON u34
35. 承：状況把握、仮説、診断 ・ ファイルディスクリプタ使用増加 　　→テーブル数が多い場合に注意（数千〜） ・ 大きな削除はロックの可能性 　　→バッファプールから削除、つまり… 　　→全てのページチェックされる ・ ただし現在は… テーブル個別領域のデメリット u35
36. 承：状況把握、仮説、診断 ・ ファイルディスクリプタ使用増加 　　→HDD -> SSD で性能向上 　　→問題あれば他の技術を検討 ・ 大きな削除はロックの可能性 　　→DROP TABLE, TRUNCATEに限る 　　→計画的に削除すべき ・ 5.6.6 からデフォルトである テーブル個別領域のデメリット u36
37. テーブル個別領域・反映結果 共有領域からテーブル個別領域へ ✔ ストレージを圧迫しなくなった 　　→半分近く空いた ✔ 継続的に未使用領域を解放 　　→監視しつつ適切に解放できるようになった ストレージ容量問題が改善 u37
38. 承：状況把握、仮説、診断 ✔ 未使用領域の解放 ✔ ストレージ圧迫問題解決 ✔ テーブル最適化が可能に ✔ デメリットは顕在化しなかった テーブル個別領域のまとめ u38
39. 登壇者 給水ポイント u39
40. u40
41. 転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u41
42. 転：my.cnf 設定と解説 InnoDBバッファプールとは ・ キャッシュ領域 　　→データとindexが対象、ディスクI/O減少 ・ メモリの 70〜80% 　　→理想は全てのデータとindex ・ 読み書き 両方 に効果 　　→書き込みの速度も上がる u42
43. 転：my.cnf 設定と解説 SELECT の動き ・ バッファプール を見る 　　→メモリから取ろうとする ・ なければ 実データ を読む 　　→ディスクI/Oが発生する ・ その後バッファプールに載せる 　　→データから吸い上げてくれる u43
44. 転：my.cnf 設定と解説 INSERT, UPDATE, DELETE の動き ・ バッファプール に書く 　　→空きがなければ、古いページを押し出す ・ その後、ログファイル に書く 　　→commit反映待ちのログへ ・ 実データファイルへ書く 　　→実データ＋ログで完全なデータ u44
45. 転：my.cnf 設定と解説 DROP TABLE の動き ・ バッファプール から削除 　　→テーブル全てのページ ・ その後、ログファイル に書く 　　→commit反映待ちのログへ ・ 実データファイルを削除 　　→順番は更新系と同じ u45
46. 必ず バッファプールから 読み書き している u46
47. つまり バッファプールが データの原本 u47
48. バッファプール ・設定内容 設定のポイント ・ メモリ 8G の 75% = 6G ・ mysql再起動 で反映される ・ 割り当て状況を監視して運用を [mysqld] innodb_buffer_pool_size=6G u48
49. バッファプール ・反映結果 データとindexをキャッシュ ✔ 読み込みの高速化 　　→単純SELECTだけでなく、スロークエリも改善 ✔ 書き込みの高速化 　　→INSERT, UPDATE, DELETEも高速に さばけるプロセス数が増加！ u49
50. ✔ データの原本 ✔ キャッシュを有効活用 ✔ 読み込み性能が向上 ✔ 書き込み性能も向上 バッファプールのまとめ u50 転：my.cnf 設定と解説
51. 転：my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 u51
52. 転：my.cnf 設定と解説 InnoDBログファイルとは ・ 前もって書き込むログ 　　→commit結果（反映待ち）を溜めるファイル ・ データの耐久性を実現 　　→クラッシュしてもリカバリーできる ・ 書き込み系の速度向上 　　→index追加、カラム追加も速度向上 u52
53. 転：my.cnf 設定と解説 大まかな commit時 の動き バッファ プールへ 書き込み InnoDB ログへ 書き込み 実ファイル へ 書き込み u53 プロセスの処理はここまで 非同期書き込み
54. 転：my.cnf 設定と解説 InnoDBログのメリット ・ ディスクI/Oを節約して高速 　　→実ファイル反映を待たずにプロセス終了 ・ クラッシュリカバリに利用 　　→未反映commitを実ファイルに適用 u54
55. InnoDBログ ・設定内容 設定のポイント ・ バッファプールより少なく（1G程度） 　　→innodb_log_files_in_groupも忘れずに ・ 古いログファイル削除 (5.6.8未満) ・ mysql再起動 [mysqld] innodb_log_file_size=1G u55
56. ✔ commit蓄積・ディスクI/O削減 ✔ クラッシュリカバリできる ✔ 書き込み性能も向上 ✔ サイズを適切にチューニング InnoDBログのまとめ u56 転：my.cnf 設定と解説
57. my.cnf 設定まとめ u57
58. ① テーブル個別領域 　・ストレージ圧迫回避 ② InnoDBバッファプール 　・メモリ有効活用、読み書きが高速 ③ InnoDBログファイル 　・バッファプールと合わせて高速化 my.cnf設定のまとめ u58 転：my.cnf 設定と解説
59. おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u59
60. その前に 反映手順 をご紹介 u60
61. my.cnf 設定・反映手順 ① DBへのアクセスを停止 ② dump取得 ③ 更新後の my.cnf に差し替え ④ MySQL 再起動 ⑤ バックアップからリストア ⑥ 動作検証・経過観察 u61
62. パフォーマンスチューニング 総評 u62
63. 結：設定結果のパフォーマンス ✔ 秒間 1000 アクセス 達成 ✔ ストレージ 圧迫解消 ✔ バッファプールとログで 高速化 ✔ スロークエリ 可視化・撲滅 ✔ サービスと事業が 軌道に my.cnf チューニング 総評 u63
64. おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u64
65. まとめ・課題 まとめ ・ my.cnf 設定 しよう！ 　　→適切なパフォーマンスを得る ・ 監視・計測 しよう！ 　　→サービスは成長する ・ 継続的に 改善 しよう！ u65
66. 今日の目標 MySQLに親しみを持とう 仮説・検証・計測をしよう 運用・改善の大切さを認識しよう 1 2 3 u66
67. まとめ・課題 課題 ・継続監視の仕組み導入 　　→zabbix拡充、mackerelなど ・ レプリケーション 　　→参照、書き込みを分け、冗長構成 ・ リファクタリング 　　→プログラム改修、ERD設計しなおし u67
68. おしながき ✔ 起：トラブルは突然に ✔ 承：状況把握、診断 ✔ 転：my.cnf 設定と解説 ✔ 結：設定結果のパフォーマンス ✔ まとめ、課題 ✔ さいごに u68
69. さいごに 運用は大切 ・ 後から変更は大変 ・ インフラは他人事じゃない ・ 0 -> 1 以降も大事 ・ データの寿命はアプリより長い u69
70. データの寿命は アプリより長い u70
71. RDBに 親しもう u71
72. 他人任せにせず 世界を広げて… u72
73. 楽しい エンジニアライフを！ u73
74. u74
75. デフォルトダメ・ゼッタイ #f0f0f0 #666666 #e6855e #5ec84e #f0f0f0 　あいうえおかきくけこさしすせそ #e6855e 　あいうえおかきくけこさしすせそ #5ec84e 　あいうえおかきくけこさしすせそ