Save 37% off PRO during our Black Friday Sale! »

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

3c9c5f9a91cac73073db8bb2903bd968?s=47 mamy1326
November 10, 2018

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

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

3c9c5f9a91cac73073db8bb2903bd968?s=128

mamy1326

November 10, 2018
Tweet

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

     あいうえおかきくけこさしすせそ