$30 off During Our Annual Pro Sale. View Details »

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

mamy1326
November 10, 2018

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

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

mamy1326

November 10, 2018
Tweet

More Decks by mamy1326

Other Decks in Programming

Transcript

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  10. 空っぽ の
    my.cnf だと?
    u10

    View Slide

  11. てめえらの血は
    なに色だーーーっ!!
    u11

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  39. 登壇者
    給水ポイント
    u39

    View Slide

  40. u40

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  53. 転:my.cnf 設定と解説
    大まかな commit時 の動き
    バッファ
    プールへ
    書き込み
    InnoDB
    ログへ
    書き込み
    実ファイル

    書き込み
    u53
    プロセスの処理はここまで 非同期書き込み

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  57. my.cnf
    設定まとめ
    u57

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  71. RDBに
    親しもう
    u71

    View Slide

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

    View Slide

  73. 楽しい
    エンジニアライフを!
    u73

    View Slide

  74. u74

    View Slide

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

    View Slide