Upgrade to Pro — share decks privately, control downloads, hide ads and more …

初めてのMySQLパフォーマンスチューニングーデータベースは怖くない!/mysql-performance-tuning-basics-in-db-study-chugoku-chiho

mamy1326
February 02, 2019

 初めてのMySQLパフォーマンスチューニングーデータベースは怖くない!/mysql-performance-tuning-basics-in-db-study-chugoku-chiho

中国地方DB勉強会@岡山でお話する資料です。
MySQLのトラブル解析から my.cnf の設定方法、パラメータ解説、レプリケーションの実行手順、チューニング結果、そして伝えたい大事なことをお話します。

mamy1326

February 02, 2019
Tweet

More Decks by mamy1326

Other Decks in Technology

Transcript

  1. 初めてのMySQL
    パフォーマンスチューニング
    ー データベースは怖くない!
    Feb 2, 2019 @ 中国地方DB勉強会 in 岡山
    まみやなおき (@mamy1326)

    View Slide

  2. Name
    Twitter
     ✔ 2017年の趣味:MySQL
     ✔ 2018年の趣味:DNS
     ✔ 2019年の趣味:HTTP/3…HTTPS?
    :まみやなおき@PHPer
    :@mamy1326(まみー)
    自己紹介
    2

    View Slide

  3. ✔ MySQLに 興味がある 人
       →これから使いたい、知っておきたい
    ✔ Web アプリケーション エンジニア
       →DBAではない、クエリは主にORM
    ✔ RDB操作は 主に ツール を使う
       →DBeaver、MySQL Workbench など
    想定オーディエンスのみなさま
    3

    View Slide

  4. MySQL 5.7にやられないためにおぼえておいてほしいこと
    ●https://www.slideshare.net/yoku0825/mysql-57-53449734
    よくある誤解
    4

    View Slide

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

    View Slide

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

    View Slide

  7.  質問です
    7

    View Slide

  8. 8
    DBサーバー
    落とした こと
    あるひと

    View Slide

  9. 9
    安心(?)
    してください
    僕も あります

    View Slide

  10. 10
    毎秒 10 リクエスト で
    落ちて いた
    MySQLサーバーを

    View Slide

  11. 11
    基本的 な
    チューニング を
    通して

    View Slide

  12. 12
    1事業部を
    救った 結果

    View Slide

  13. 13
    MySQL たのしい!
    RDB たのしい!
    となった話をします

    View Slide

  14. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    14

    View Slide

  15. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    15

    View Slide

  16. トラブル会場(事件現場)
    MySQL
    5.5 系
    (お話は5.7準拠)
    メモリ 8 GB
    ストレージ 20 GB
    サーバ MySQL on EC2 (AWS)
    時期 2017年1月
    16

    View Slide

  17. トラブルは突然に
    実際にあった怖い話
    17

    View Slide

  18. トラブルは突然に
    ・ 毎週落ちるDBサーバ
    実際にあった怖い話
    18

    View Slide

  19. トラブルは突然に
    ・ 毎週落ちるDBサーバ
    ・ 放置するPM(数ヶ月)
    実際にあった怖い話
    19

    View Slide

  20. トラブルは突然に
    ・ 毎週落ちるDBサーバ
    ・ 放置するPM(数ヶ月)
    ・ アクセス数減らしお祈り
    実際にあった怖い話
    20

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  24. my.cnf
    設定しないと
    動かない
    (5 7 5)
    24

    View Slide

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

    View Slide

  26. トラブルは突然に
    26
    状況:ノンチューニング

    View Slide

  27. トラブルは突然に
    27
    状況:ノンチューニング
    ・ 秒間 10 アクセスでダウン
      →Web -> MySQLサーバーへのアクセス

    View Slide

  28. トラブルは突然に
    28
    状況:ノンチューニング
    ・ 秒間 10 アクセスでダウン
      →Web -> MySQLサーバーへのアクセス
    ・ スロークエリ頻発
      →5分以上、1000万レコードフルスキャン

    View Slide

  29. トラブルは突然に
    29
    ・ 秒間 10 アクセスでダウン
      →Web -> MySQLサーバーへのアクセス
    ・ スロークエリ頻発
      →5分以上、1000万レコードフルスキャン
    ・ スレーブがない
      →ダウンするとサービス停止
    状況:ノンチューニング

    View Slide

  30. トラブルは突然に
    入社して担当になる
    30

    View Slide

  31. トラブルは突然に
    入社して担当になる
    ✔ 状況を整理する
      →zabbix導入、MySQL Tuner実行、index調査、etc…
    31

    View Slide

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

    View Slide

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

    View Slide

  34. トラブルに感謝 (-人-)
    孤軍奮闘トラブルシュート
    34

    View Slide

  35. トラブルに感謝 (-人-)
    孤軍奮闘トラブルシュート
    ✔ RDB未経験 -> 基礎習得
      →my.cnf 設定項目を調べ尽くす
    35

    View Slide

  36. トラブルに感謝 (-人-)
    孤軍奮闘トラブルシュート
    ✔ RDB未経験 -> 基礎習得
      →my.cnf 設定項目を調べ尽くす
    鎮火させ、継続して計測
      →zabbixを覚え、AWSを覚え、計測方法を覚える
    36

    View Slide

  37. トラブルに感謝 (-人-)
    孤軍奮闘トラブルシュート
    ✔ RDB未経験 -> 基礎習得
      →my.cnf 設定項目を調べ尽くす
    鎮火させ、継続して計測
      →zabbixを覚え、AWSを覚え、計測方法を覚える
    楽しく なって 趣味に なる
    37

    View Slide

  38. トラブル は
    人を 成長 させる
    (-人-)
    38

    View Slide

  39. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    39

    View Slide

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

    View Slide

  41. 起:状況把握、診断
    状況把握(MySQLサーバ)
    request 秒間20
    memory 使用率 25% 前後
    storage 使用率 95% 以上
    41

    View Slide

  42. こりゃやべぇ…
    42

    View Slide

  43. 起:状況把握、診断
    その他 の把握事項
    ・ ストレージ 100%で落ちてた
      →sshできない時も(別途解決)
    ・ クレーム頻発
      →謝罪しまくり
    ・ プログラムに 問題あり
      →N+1 などなど
    43

    View Slide

  44. 解決のため
    my.cnf の設定項目を
    知りたい
    44

    View Slide

  45. 起:状況把握、診断
    診断:MySQL Tuner を使う
    ・ チューニングポイントの 診断
      →my.cnfに何が不足かを抽出
    45

    View Slide

  46. 起:状況把握、診断
    診断:MySQL Tuner を使う
    ・ チューニングポイントの 診断
      →my.cnfに何が不足かを抽出
    ・ GNU GPL なので無償利用可能
      →Perlのアプリケーション
    46

    View Slide

  47. 起:状況把握、診断
    診断:MySQL Tuner を使う
    ・ チューニングポイントの 診断
      →my.cnfに何が不足かを抽出
    ・ GNU GPL なので無償利用可能
      →Perlのアプリケーション
    ・ インストールして すぐ使える
      →設定不要!(8.0系には対応中の模様)
    47

    View Slide

  48. 起:状況把握、診断
    診断対象の my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    expire_logs_days=3
    log-bin=mysql-bin
    server-id=1001
    48

    View Slide

  49. 起:状況把握、診断
    診断対象の my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    expire_logs_days=3
    log-bin=mysql-bin
    server-id=1001
    49
    my.cnf
    デフォルト設定値

    View Slide

  50. デフォルトの
    my.cnf に対して
    診断実行
    50

    View Slide

  51. 起:状況把握、診断
    MySQL Tuner 利用方法
    $ cd ~/
    $ git clone [email protected]:major/
    MySQLTuner-perl.git
    $ cd MySQLTuner-perl
    $ chmod 755 mysqltuner.pl
    $ perl mysqltuner.pl --user root --
    pass='mamy1326'
    51

    View Slide

  52. 起:状況把握、診断
    MySQL Tuner 診断結果 (5.5)
    -------- 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.
    52

    View Slide

  53. 起:状況把握、診断(参考)
    MySQL Tuner 診断結果 (5.7.24)
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
    Control warning line(s) into /var/log/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-
    resolve=1
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/
    2TcGgtU
    Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    innodb_buffer_pool_size (>= 3.2G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of
    buffer pool size.
    53

    View Slide

  54. 起:状況把握、診断(参考)
    MySQL Tuner 診断結果 (8.0.13)
    -------- Recommendations -----------------------------
    General recommendations:
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be
    inaccurate
    Configure your accounts with ip or subnets only, then update your
    configuration with skip-name-resolve=1
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read
    this: https://bit.ly/2TcGgtU
    Variables to adjust:
    innodb_buffer_pool_size (>= 3.4G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size
    equals to 25% of buffer pool size.
    54

    View Slide

  55. 5.7.24, 8.0.13 は
    参考 として作った環境
    55

    View Slide

  56. トラブっていた のは
    5.5 の環境
    56

    View Slide

  57. なので
    MySQL 5.5 の
    診断結果をベースに
    解説 します
    57

    View Slide

  58. 起:状況把握、診断
    MySQL Tuner 診断結果 (5.5)
    -------- 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.
    58

    View Slide

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

    View Slide

  60. 起:状況把握、診断
    MySQL Tuner 診断結果 ②
    query_cache_size (>= 8M)
    ✔ クエリキャッシュを設定しよう
    ✔ ただし MySQL 8.0 から無くなる機能
    ✔ 今から別の機能を検討しましょう
     (いずれ8.0系にする日がきます)
    60

    View Slide

  61. 【余談】
    なぜ
    クエリキャッシュ が
    廃止 になったか
    61

    View Slide

  62. クエリキャッシュ廃止の理由 1/2
    62
    ・ クエリキャッシュの限界
       →マルチコアマシンでの高スループットな負荷の場合
         スケールしない ( MySQL 5.6 から無効化 (2013年) )
    ・ クライアントでキャッシュすべき
       →DBサーバでクエリキャッシュ
                = 性能向上はヒットするクエリだけ
       →全般的な性能向上
                = アプリケーションで適切にキャッシュ

    View Slide

  63. クエリキャッシュ廃止の理由 2/2
    63
    ・ 開発リソースと効果の対比
       →MySQL 独自のクエリキャッシュ開発コスト
         vs
        実際のクエリキャッシュで得られる効果
         =
        年々効果が薄くなってきた機能への投資をやめる

    View Slide

  64. クエリキャッシュの
    余談おわり
    64

    View Slide

  65. MySQL Tuner 診断結果 ③
    innodb_file_per_table=ON
    ✔ テーブルデータが共有領域なので
    ✔ テーブル個別領域に分けましょう
    ✔ 5.6.6 以降ではデフォルトONです
    65
    起:状況把握、診断

    View Slide

  66. MySQL Tuner 診断結果 ④
    innodb_buffer_pool_size (>= 12G) if
    possible.
    ✔ データとindexを キャッシュ に載せよう
    ✔ 物理メモリの 7〜8割 が基準
    ✔ 大きいメモリの場合は基準が変わる
    66
    起:状況把握、診断

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  70. 登壇者
    給水ポイント
    その①
    70

    View Slide

  71. 71

    View Slide

  72. 給水しながら
    おまけの話
    72

    View Slide

  73. Appendix:Charaset, Collation
    mysql> show variables like "chara%";
    +--------------------------+----------------------------+
    | Variable_name | Value |
    +--------------------------+----------------------------+
    | character_set_client | utf8mb4 |
    | character_set_connection | utf8mb4 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | utf8mb4 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    73
    悲しいね(懐メロ)

    View Slide

  74. Appendix:Charaset, Collation
    [mysqld]
    # mysqld ログ出力設定
    log-error = /var/log/mysql/error.log
    # 新規作成するデータベースのCharaset
    character-set-server = utf8mb4
    # 新規作成するデータベースのCharaset
    collation_server = utf8mb4_bin
    [client]
    # クライアント内の文字処理とサーバーとの接続のCharaset
    loose-default-character-set = utf8mb4
    74

    View Slide

  75. Appendix:Charaset, Collation
    [mysqld]
    # mysqld ログ出力設定
    log-error = /var/log/mysql/error.log
    # 新規作成するデータベースのCharaset
    character-set-server = utf8mb4
    # 新規作成するデータベースのCharaset
    collation_server = utf8mb4_bin
    [client]
    # クライアント内の文字処理とサーバーとの接続のCharaset
    loose-default-character-set = utf8mb4
    75
    (ついで)エラーログは
    明確にしましょう

    View Slide

  76. Appendix:Charaset, Collation
    [mysqld]
    # mysqld ログ出力設定
    log-error = /var/log/mysql/error.log
    # 新規作成するデータベースのCharaset
    character-set-server = utf8mb4
    # 新規作成するデータベースのCharaset
    collation_server = utf8mb4_bin
    [client]
    # クライアント内の文字処理とサーバーとの接続のCharaset
    loose-default-character-set = utf8mb4
    76
    ここで設定していれば
    DB、テーブル、カラムで
    指定する必要がない

    View Slide

  77. Appendix:Charaset, Collation
    [mysqld]
    # mysqld ログ出力設定
    log-error = /var/log/mysql/error.log
    # 新規作成するデータベースのCharaset
    character-set-server = utf8mb4
    # 新規作成するデータベースのCharaset
    collation_server = utf8mb4_bin
    [client]
    # クライアント内の文字処理とサーバーとの接続のCharaset
    loose-default-character-set = utf8mb4
    77
    Charaset同様
    Collationを設定しておく

    View Slide

  78. Appendix:Charaset, Collation
    [mysqld]
    # mysqld ログ出力設定
    log-error = /var/log/mysql/error.log
    # 新規作成するデータベースのCharaset
    character-set-server = utf8mb4
    # 新規作成するデータベースのCharaset
    collation_server = utf8mb4_bin
    [client]
    # クライアント内の文字処理とサーバーとの接続のCharaset
    loose-default-character-set = utf8mb4
    78
    ただし
    CHARACTER SETすると
    無視されるので注意

    View Slide

  79. Appendix:Charaset, Collation
    [mysqld]
    # mysqld ログ出力設定
    log-error = /var/log/mysql/error.log
    # 新規作成するデータベースのCharaset
    character-set-server = utf8mb4
    # 新規作成するデータベースのCharaset
    collation_server = utf8mb4_bin
    [client]
    # クライアント内の文字処理とサーバーとの接続のCharaset
    loose-default-character-set = utf8mb4
    79
    ここで設定していれば
    通信時にも
    文字化けを防止できる

    View Slide

  80. Appendix:Charaset, Collation
    mysql> show variables like 'char%';
    +--------------------------+----------------------------+
    | Variable_name | Value |
    +--------------------------+----------------------------+
    | character_set_client | utf8mb4 |
    | character_set_connection | utf8mb4 |
    | character_set_database | utf8mb4 |
    | character_set_filesystem | binary |
    | character_set_results | utf8mb4 |
    | character_set_server | utf8mb4 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    80
    嬉しい楽s(以下略

    View Slide

  81. Charaset, Collation
    おまけの話
    おしまい
    81

    View Slide

  82. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    82

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  110. my.cnf
    設定まとめ
    110

    View Slide

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

    View Slide

  112. 反映手順
    112

    View Slide

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

    View Slide

  114. 登壇者
    給水ポイント
    その➁
    114

    View Slide

  115. 115

    View Slide

  116. 給水しながら
    おまけの話➁
    116

    View Slide

  117. Appendix:slow query 関連
    [mysqld]
    # slow log 出力設定
    slow_query_log = ON
    # slow.log の場所
    slow_query_log_file = /var/log/mysql/slow.log
    # 出力対象秒数
    long_query_time = 3
    # no index のクエリも出すかどうか
    log_queries_not_using_indexes = 1
    # no index をログに出力する間隔(分)
    log_throttle_queries_not_using_indexes = 1
    # スロークエリで、SELECT行数が指定数以下のクエリもログに出力
    min_examined_row_limit = 0
    117

    View Slide

  118. Appendix:slow query 関連
    [mysqld]
    # slow log 出力設定
    slow_query_log = ON
    # slow.log の場所
    slow_query_log_file = /var/log/mysql/slow.log
    # 出力対象秒数
    long_query_time = 3
    # no index のクエリも出すかどうか
    log_queries_not_using_indexes = 1
    # no index をログに出力する間隔(分)
    log_throttle_queries_not_using_indexes = 1
    # スロークエリで、SELECT行数が指定数以下のクエリもログに出力
    min_examined_row_limit = 0
    118
    普通のスロークエリ設定

    View Slide

  119. Appendix:slow query 関連
    [mysqld]
    # slow log 出力設定
    slow_query_log = ON
    # slow.log の場所
    slow_query_log_file = /var/log/mysql/slow.log
    # 出力対象秒数
    long_query_time = 3
    # no index のクエリも出すかどうか
    log_queries_not_using_indexes = 1
    # no index をログに出力する間隔(分)
    log_throttle_queries_not_using_indexes = 1
    # スロークエリで、SELECT行数が指定数以下のクエリもログに出力
    min_examined_row_limit = 0
    119
    no index のクエリを
    ログに出す

    View Slide

  120. Appendix:slow query 関連
    [mysqld]
    # slow log 出力設定
    slow_query_log = ON
    # slow.log の場所
    slow_query_log_file = /var/log/mysql/slow.log
    # 出力対象秒数
    long_query_time = 3
    # no index のクエリも出すかどうか
    log_queries_not_using_indexes = 1
    # no index をログに出力する間隔(分)
    log_throttle_queries_not_using_indexes = 1
    # スロークエリで、SELECT行数が指定数以下のクエリもログに出力
    min_examined_row_limit = 0
    120
    no index のクエリを
    ログに出す間隔(分)

    View Slide

  121. Appendix:slow query 関連
    [mysqld]
    # slow log 出力設定
    slow_query_log = ON
    # slow.log の場所
    slow_query_log_file = /var/log/mysql/slow.log
    # 出力対象秒数
    long_query_time = 3
    # no index のクエリも出すかどうか
    log_queries_not_using_indexes = 1
    # no index をログに出力する間隔(分)
    log_throttle_queries_not_using_indexes = 1
    # スロークエリで、SELECT行数が指定数以下のクエリもログに出力
    min_examined_row_limit = 0
    121
    SELECT の
    行数指定

    View Slide

  122. slow query 関連
    おまけの話
    おしまい
    122

    View Slide

  123. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    123

    View Slide

  124. 転:レプリケーションで冗長化
    レプリケーションとは
    ・ データベースの レプリカ(複製)
      →マスターとスレーブ
    ・ MySQLの 標準機能
      →手軽に設定、手軽に稼働
    ・ ほぼ時間差なしに 同期
      →ネットワーク、データ量、台数による
    124

    View Slide

  125. 転:レプリケーションで冗長化
    構成 (〜中規模)
    125
    マスター( 単一 )
    スレーブ
    ( 1 )
    スレーブ
    ( n )
    ・・・・

    View Slide

  126. 転:レプリケーションで冗長化
    マスターの役割
    ・ データを 更新 する側
      →スレーブは参照する側
    ・ 更新内容を スレーブが取り に来る
      →スレーブへ更新イベントを通知
    ・ 複数のスレーブ を持てる
      →単一でも複数でもマスター設定変更不要
    126

    View Slide

  127. 転:レプリケーションで冗長化
    スレーブの役割
    ・ データを 参照 する側
      →スレーブは参照する側
    ・ マスターのバイナリログを 取得
      →イベント受取 -> マスターbinlog取得 -> 更新
    ・ マスターに 昇格 できる
      →マスターと同じ設定が必要
    127

    View Slide

  128. 負荷分散 の実現
    128
    レプリケーションのメリット①
    検索処理が重く、全体に影響
      →書き込みと重たいSQLが同じサーバー
    ✔ 重い処理をスレーブへ向ける
      →分析系の処理や、管理画面系や総件数取得など
    マスターへの影響が 解消

    View Slide

  129. 可用性 の高い構成の実現
    129
    レプリケーションのメリット➁
    ✔ スレーブでバイナリログを出力
      →my.cnf に設定する
    マスターが 障害 でダウン
      →スレーブがないと解決までサービス停止
    スレーブ -> マスターへ 昇格 できる

    View Slide

  130. バックアップが容易
    130
    レプリケーションのメリット③
    マスターの 圧迫 (ストレージ・ネットワーク)
      →バックアップデータの保存、転送
    ✔ スレーブでバックアップ
      →バッチなどで実行
    マスターへの影響が 解消

    View Slide

  131. レプリケーション
    事前準備
    131

    View Slide

  132. レプリケーション事前準備
    レプリケーションユーザー作成
    マスターの設定
    マスターバイナリログの確認
    スレーブの設定
    スレーブの確認
    1
    2
    3
    132
    4
    5

    View Slide

  133. レプリケーション事前準備
    1
    2
    3
    133
    4
    2
    レプリケーションユーザー作成
    マスターの設定
    マスターバイナリログの確認
    スレーブの設定
    スレーブの確認
    5

    View Slide

  134. レプリケーションユーザー作成
    ・ マスター側に 作成
      →スレーブから参照できるユーザー
    ・ マスターのバイナリログ 取得用
      →スレーブから接続して取得
    ・ レプリケーション 専用ユーザー
      →適切な権限設定、マスター更新しないように
    134
    レプリケーション事前準備

    View Slide

  135. ユーザー作成コマンド
    135
    レプリケーション事前準備
    mysql(master)> CREATE USER
    mysql(master)> ’repl’@’[スレーブのホスト名]’
    mysql(master)> IDENTIFIED BY PASSWORD ’***’;
    ・ 適切なユーザー名: ‘repl’ など
    ・ 適切なパスワード
    ・ マスターで実行

    View Slide

  136. ユーザー権限付与コマンド
    136
    レプリケーション事前準備
    mysql(master)> GRANT REPLICATION SLAVE
    mysql(master)> ON *.* TO
    mysql(master)> ’repl’@’[スレーブのホスト名]’;
    ・ 適切な権限: ‘REPLICATION SLAVE’
    ・ 必要であればDB名なども適切に
    ・ 先ほど作ったユーザーに対し実行

    View Slide

  137. レプリケーション事前準備
    1
    2
    3
    137
    4
    レプリケーションユーザー作成
    マスターの設定
    マスターバイナリログの確認
    スレーブの設定
    スレーブの確認
    5

    View Slide

  138. マスターの my.cnf に設定追加
    138
    レプリケーション事前準備
    [mysqld]
    # スレーブに通知するバイナリログ(実際はナンバリングされる)
    log-bin=mysql-bin
    # レプリケーションを行う、サーバーのユニークID
    server-id=1001
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3

    View Slide

  139. マスターの my.cnf に設定追加
    139
    レプリケーション事前準備
    [mysqld]
    # スレーブに通知するバイナリログ(実際はナンバリングされる)
    log-bin=mysql-bin
    # レプリケーションを行う、サーバーのユニークID
    server-id=1001
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    commitを通知後
    スレーブが接続して取得
    (/var/lib/mysql/mysql-bin.XXXXXX)

    View Slide

  140. マスターの my.cnf に設定追加
    140
    レプリケーション事前準備
    [mysqld]
    # スレーブに通知するバイナリログ(実際はナンバリングされる)
    log-bin=mysql-bin
    # レプリケーションを行う、サーバーのユニークID
    server-id=1001
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    スレーブから見た
    マスターのID

    View Slide

  141. マスターの my.cnf に設定追加
    141
    レプリケーション事前準備
    [mysqld]
    # スレーブに通知するバイナリログ(実際はナンバリングされる)
    log-bin=mysql-bin
    # レプリケーションを行う、サーバーのユニークID
    server-id=1001
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    多くしすぎると
    ストレージ圧迫注意

    View Slide

  142. レプリケーション事前準備
    1
    2
    3
    142
    4
    レプリケーションユーザー作成
    マスターの設定
    マスターバイナリログの確認
    スレーブの設定
    スレーブの確認
    5

    View Slide

  143. マスターバイナリログ出力
    ・ マスターの mysqld 再起動
      →設定を反映し、バイナリログを出力
    ・ commit 履歴 出力
      →履歴の差分をスレーブが取得する
    ・ ファイル名、位置情報を確認
      →現在のファイル&どこまでcommitしたかの位置
    143
    レプリケーション事前準備

    View Slide

  144. マスターのステータス確認
    144
    レプリケーション事前準備
    mysql> SHOW MASTER STATUS\G
    ********* 1. row *********
    File: mysql-bin.000075
    Position: 588201136
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    1 row in set (0.01 sec)

    View Slide

  145. マスターのステータス確認
    145
    レプリケーション事前準備
    mysql> SHOW MASTER STATUS\G
    ********* 1. row *********
    File: mysql-bin.000075
    Position: 588201136
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    1 row in set (0.01 sec)
    commitを通知後
    スレーブが接続して取得
    (/var/lib/mysql/mysql-bin.XXXXXX)

    View Slide

  146. マスターのステータス確認
    146
    レプリケーション事前準備
    mysql> SHOW MASTER STATUS\G
    ********* 1. row *********
    File: mysql-bin.000075
    Position: 588201136
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    1 row in set (0.01 sec)
    マスターで commit が
    実行された位置情報

    View Slide

  147. マスターのステータス確認
    147
    レプリケーション事前準備
    mysql> SHOW MASTER STATUS\G
    ********* 1. row *********
    File: mysql-bin.000075
    Position: 588201136
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    1 row in set (0.01 sec)
    スレーブの設定で必要のため
    メモしておきましょう

    View Slide

  148. スレーブ作成で気をつけたいこと
    148
    レプリケーション事前準備
    ✔ マスターの更新を 停止して dump
      →マスター、スレーブで差分やズレをなくす
    ✔ ファイルと位置情報を 合わせる
      →マスター、スレーブはなるべく同一で開始
    差分・抜け漏れなく開始 する

    View Slide

  149. レプリケーション事前準備
    1
    2
    3
    149
    4
    レプリケーションユーザー作成
    マスターの設定
    マスターバイナリログの確認
    スレーブの設定
    スレーブの確認
    5

    View Slide

  150. スレーブの my.cnf に設定追加
    150
    レプリケーション事前準備
    [mysqld]
    # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値)
    server-id=1002
    # マスター昇格のため、バイナリログを出力
    log-bin=mysql-bin
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    # スレーブのバイナリログ出力設定(上記だけでは出力されない)
    log_slave_updates=1
    # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない)
    read_only=1

    View Slide

  151. [mysqld]
    # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値)
    server-id=1002
    # マスター昇格のため、バイナリログを出力
    log-bin=mysql-bin
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    # スレーブのバイナリログ出力設定(上記だけでは出力されない)
    log_slave_updates=1
    # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない)
    read_only=1
    スレーブの my.cnf に設定追加
    151
    レプリケーション事前準備
    スレーブの
    サーバID

    View Slide

  152. [mysqld]
    # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値)
    server-id=1002
    # マスター昇格のため、バイナリログを出力
    log-bin=mysql-bin
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    # スレーブのバイナリログ出力設定(上記だけでは出力されない)
    log_slave_updates=1
    # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない)
    read_only=1
    スレーブの my.cnf に設定追加
    152
    レプリケーション事前準備
    スレーブでの
    バイナリログ出力設定

    View Slide

  153. [mysqld]
    # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値)
    server-id=1002
    # マスター昇格のため、バイナリログを出力
    log-bin=mysql-bin
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    # スレーブのバイナリログ出力設定(上記だけでは出力されない)
    log_slave_updates=1
    # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない)
    read_only=1
    スレーブの my.cnf に設定追加
    153
    レプリケーション事前準備
    スレーブでは
    これが必要(次ページで説明)

    View Slide

  154. log_slave_updates 解説 1/2
    マルチマスター用の設定 (多段構成)
    ・スレーブが他のスレーブのマスター
      →バイナリログがないとマスター足り得ない
    ・この設定で バイナリログが出力 される
      →スレーブでバイナリログを出力するのに必要
    [mysqld]
    log_slave_updates
    154

    View Slide

  155. log_slave_updates 解説 2/2
    マスター昇格に備える
    ・すぐ マスターに昇格できる
      →バイナリログがないとマスター足り得ない
    ・有効化しても 無害 である
      →http://nippondanji.blogspot.com/2014/12/mysqlgtid.html
    [mysqld]
    log_slave_updates
    155

    View Slide

  156. スレーブの my.cnf に設定追加
    156
    レプリケーション事前準備
    [mysqld]
    # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値)
    server-id=1002
    # マスター昇格のため、バイナリログを出力
    log-bin=mysql-bin
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    # スレーブのバイナリログ出力設定(上記だけでは出力されない)
    log_slave_updates=1
    # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない)
    read_only=1
    スレーブで更新を禁止
    (SUPER権限は防げない)

    View Slide

  157. スレーブの my.cnf に設定追加
    157
    レプリケーション事前準備
    [mysqld]
    # スレーブのサーバIDを設定(全てのマスタスレーブでユニーク値)
    server-id=1002
    # マスター昇格のため、バイナリログを出力
    log-bin=mysql-bin
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    # スレーブのバイナリログ出力設定(上記だけでは出力されない)
    log_slave_updates=1
    # レプリケーション以外でのスレーブの更新を禁止(ただしSUPER権限は防げない)
    read_only=1
    MySQL 5.7.8 以降では
    super_read_only で解決

    View Slide

  158. レプリケーション事前準備
    1
    2
    3
    158
    レプリケーションユーザー作成
    マスターの設定
    マスターバイナリログの確認
    スレーブの設定
    スレーブの確認
    5
    4

    View Slide

  159. トラブっていた
    スレーブの実例
    159

    View Slide

  160. スレーブの確認 (ダメな例)
    160
    レプリケーション事前準備
    mysql> SHOW SLAVE STATUS\G
    **************** 1. row ****************
    Slave_IO_State:
    Master_Host: [Ϛελʔͷϗετ໊]
    Master_User: repl
    Master_Log_File: mysql-bin.000009
    Read_Master_Log_Pos: 303456264
    Slave_IO_Running: No
    Slave_SQL_Running: No
    Seconds_Behind_Master: NULL
    Master_Server_Id: 0
    1 row in set (0.01 sec)

    View Slide

  161. スレーブのステータス 解説 ①
    スレーブの現在の状態
    ・空文字=レプリケーション 停止
      →なんらかの理由で止まっている
    ・正常だとメッセージ
      →Waiting for master to send event など
       マスターからのイベント待ち
    Slave_IO_State:[空文字]
    161

    View Slide

  162. スレーブのステータス 解説 ➁
    接続しに行くマスター
    ・ホスト名、またはIPアドレス
    ・新規、変更の場合は 設定が必要
      →マスターが変更された場合
    Master_Host: [マスターのホスト名]
    162

    View Slide

  163. スレーブのステータス 解説 ③
    マスターに接続するユーザー名
    ・マスターで作成したユーザー名
      →先ほど作った専用ユーザー
    Master_User: repl
    163

    View Slide

  164. スレーブのステータス 解説 ➃
    マスターから取得するバイナリログ
    ・開始時に 合わせる
      →バイナリログはローテートされる
    ・長時間止まると ズレる
      →この例は実際にズレた状態
    Master_Log_File: mysql-bin.000009
    164

    View Slide

  165. スレーブのステータス 解説 ➄
    マスターから読み取った最後の位置
    ・基本的にマスターと イコール
      →重いクエリを流すと遅れることはある
    ・長時間止まると ズレる
      →この例は実際にズレた状態
    Read_Master_Log_Pos: 303456264
    165

    View Slide

  166. マスターのステータス確認【再掲】
    166
    レプリケーション事前準備
    mysql> SHOW MASTER STATUS\G
    ********* 1. row *********
    File: mysql-bin.000075
    Position: 588201136
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    1 row in set (0.01 sec)
    大きくズレている

    View Slide

  167. スレーブのステータス 解説 ➅
    I/Oスレッドの状態
    ・No=バイナリログを読み込めていない
      →停止が長くファイル名がズレている
    ・Yes=読み込めている
    Slave_IO_Running: No
    167

    View Slide

  168. スレーブのステータス 解説 ➆
    SQLスレッドの状態
    ・No=スレーブのDBが 更新されていない
      →そもそもバイナリログ読めていない
    ・Yes=DB更新されている
    Slave_SQL_Running: No
    168

    View Slide

  169. スレーブのステータス 解説 ⑧
    マスターに比べ遅延した秒数
    ・NULL=レプリケーション 停止
    ・0 = 遅延なし
    ・差分が多いと 秒数が増える
      →重いクエリを流す、過去のdumpでスレーブ作る
    Seconds_Behind_Master: NULL
    169

    View Slide

  170. スレーブのステータス 解説 ⑨
    接続対象のマスターのID
    ・マスターの my.cnf で 設定したID
    ・0 = 接続 できていない
      →この場合は何度もマスターが落ちていた結果
    Master_Server_Id: 0
    170

    View Slide

  171. マスターの my.cnf に設定追加【再掲】
    171
    レプリケーション事前準備
    [mysqld]
    # スレーブに通知するバイナリログ(実際はナンバリングされる)
    log-bin=mysql-bin
    # レプリケーションを行う、サーバーのユニークID
    server-id=1001
    # バイナリログファイルのローテート日数
    set-variable=expire_logs_days=3
    スレーブから見た
    マスターのID

    View Slide

  172. 順番に
    レプリケーションを
    実行します
    172

    View Slide

  173. 登壇者
    給水ポイント
    その➂
    173

    View Slide

  174. 174

    View Slide

  175. 給水しながら
    おまけの話➂
    175

    View Slide

  176. Appendix:暗黙的な属性付与は非推奨
    [Warning] TIMESTAMP with implicit DEFAULT
    value is deprecated. Please use
    —explicit_defaults_for_timestamp server
    option (see documentation for more details).
    176
    暗黙的な属性付与は非推奨
    ▶いわゆるゼロタイムスタンプ
     →'0000-00-00 00:00:00'
     →暗黙的な属性付与で意図しない値になる

    View Slide

  177. Appendix:暗黙的な属性付与は非推奨
    mysql> SELECT cuntomer_name
    -> FROM customers
    -> WHERE create_at != '0000-00-00 00:00:00'
    -> AND create_at < NOW();
    177
    見たことありませんか…(震え声
    ▶やむなく対応の地獄
     →やむなくプログラムでなんとかする
     →やむなくWHERE 苦 句でなんとかする

    View Slide

  178. [mysqld]
    # timestamp型の暗黙的なデフォルト値を使わない
    explicit_defaults_for_timestamp = ON
    178
    Appendix:暗黙的な属性付与は非推奨
    ▶設定
    ▶確認
    mysql> show variables like 'explicit_defaults_for_timestamp';
    +---------------------------------+-------+
    | Variable_name | Value |
    +---------------------------------+-------+
    | explicit_defaults_for_timestamp | ON |
    +---------------------------------+-------+
    1 row in set (0.00 sec)

    View Slide

  179. 暗黙的な属性付与は
    非推奨、なおまけの話
    おしまい
    179

    View Slide

  180. レプリケーション手順
    ① マスターdump取得
    ② マスターバイナリログ名、ポジション取得
    ③ スレーブ mysqld 再起動
    ④ スレーブ DROP & CREATE DATABASE
    ⑤ スレーブにdumpをリストア
    ⑥ レプリケーション設定・実行
    ➆ 実行状況を監視
    180

    View Slide

  181. レプリケーション手順
    ① マスターdump取得
    ② マスターバイナリログ名、ポジション取得
    ③ スレーブ mysqld 再起動
    ④ スレーブ DROP & CREATE DATABASE
    ⑤ スレーブにdumpをリストア
    ⑥ レプリケーション設定・実行
    ➆ 実行状況を監視
    181

    View Slide

  182. マスターのステータス確認
    182
    レプリケーション設定・実行
    mysql> SHOW MASTER STATUS\G
    ********* 1. row *********
    File: mysql-bin.000075
    Position: 588201136
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    1 row in set (0.01 sec)
    マスターの
    バイナリログファイル名と
    ポジション

    View Slide

  183. スレーブ設定
    183
    レプリケーション設定・実行
    mysql> STOP SLAVE;
    mysql> RESET SLAVE;
    mysql> CHANGE MASTER TO
    -> MASTER_HOST = '192.168.1.xxx',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'xxxxx',
    -> MASTER_LOG_FILE = 'mysql-bin.000075',
    -> MASTER_LOG_POS = 588201136;
    mysql> START SLAVE;

    View Slide

  184. スレーブ設定
    184
    レプリケーション設定・実行
    mysql> STOP SLAVE;
    mysql> RESET SLAVE;
    mysql> CHANGE MASTER TO
    -> MASTER_HOST = '192.168.1.xxx',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'xxxxx',
    -> MASTER_LOG_FILE = 'mysql-bin.000075',
    -> MASTER_LOG_POS = 588201136;
    mysql> START SLAVE;
    マスターへの接続

    View Slide

  185. スレーブ設定
    185
    レプリケーション設定・実行
    mysql> STOP SLAVE;
    mysql> RESET SLAVE;
    mysql> CHANGE MASTER TO
    -> MASTER_HOST = '192.168.1.xxx',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'xxxxx',
    -> MASTER_LOG_FILE = 'mysql-bin.000075',
    -> MASTER_LOG_POS = 588201136;
    mysql> START SLAVE;
    スレーブに設定します

    View Slide

  186. mysql> STOP SLAVE;
    mysql> RESET SLAVE;
    mysql> CHANGE MASTER TO
    -> MASTER_HOST = '192.168.1.xxx',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'xxxxx',
    -> MASTER_LOG_FILE = 'mysql-bin.000075',
    -> MASTER_LOG_POS = 588201136;
    mysql> START SLAVE;
    レプリケーション開始
    186
    レプリケーション設定・実行
    レプリケーションを
    開始

    View Slide

  187. mysql> STOP SLAVE;
    mysql> RESET SLAVE;
    mysql> CHANGE MASTER TO
    -> MASTER_HOST = '192.168.1.xxx',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'xxxxx',
    -> MASTER_LOG_FILE = 'mysql-bin.000075',
    -> MASTER_LOG_POS = 588201136;
    mysql> START SLAVE;
    レプリケーション開始
    187
    レプリケーション設定・実行
    レプリケーションを
    開始
    設定したファイル、ポジ
    ションから同期実行

    View Slide

  188. レプリケーション実行確認
    188
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001

    View Slide

  189. mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    レプリケーション実行確認
    189
    レプリケーション設定・実行
    マスターからの
    更新イベント待ち

    スレーブ稼働

    View Slide

  190. レプリケーション実行確認
    190
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    マスターの現在の
    バイナリログファイル名

    View Slide

  191. レプリケーション実行確認
    191
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    マスターのバイナリログ
    から、I/Oスレッドが
    読み取った位置

    View Slide

  192. レプリケーション実行確認
    192
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    I/Oスレッド、SQLスレッド
    が動作している

    View Slide

  193. レプリケーション実行確認
    193
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    I/Oスレッドが読み取った
    SQLが実行された位置

    View Slide

  194. レプリケーション実行確認
    194
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    マスターに比べ
    遅延している秒数

    View Slide

  195. レプリケーション実行確認
    195
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    差分が
    遅延している
    トランザクション

    View Slide

  196. レプリケーション実行確認
    196
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    I/Oスレッド、SQLスレッド
    共にエラーなし

    View Slide

  197. レプリケーション実行確認
    197
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    ***************** 1. row *****************
    Slave_IO_State: Waiting for master to send event
    Master_Log_File: mysql-bin.000075
    Read_Master_Log_Pos: 878873047
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Master_Server_Id: 1001
    マスターの
    サーバーID

    View Slide

  198. レプリケーション実行状況の監視
    198
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    **************** 1. row **************
    Read_Master_Log_Pos: 878873047
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615

    View Slide

  199. レプリケーション実行状況の監視
    199
    レプリケーション設定・実行
    mysql> SHOW SLAVE STATUS\G
    **************** 1. row **************
    Read_Master_Log_Pos: 878873047
    Exec_Master_Log_Pos: 605504154
    Seconds_Behind_Master: 244615
    mysql> SHOW SLAVE STATUS\G
    **************** 1. row **************
    Read_Master_Log_Pos: 879066686
    Exec_Master_Log_Pos: 879066686
    Seconds_Behind_Master: 0

    View Slide

  200. ① スレーブが稼働
     ・複製を利用可能になった
    ② バックアップ環境完成
     ・スレーブで mysqldump を実行できる
    ③ 更新、参照の分割が可能
     ・分析系の重いクエリをスレーブへ
    レプリケーションまとめ
    200
    転:レプリケーションで 冗長化

    View Slide

  201. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    201

    View Slide

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

    View Slide

  203. 結:設定結果のパフォーマンス
    my.cnf チューニング 総評
    203

    View Slide

  204. 結:設定結果のパフォーマンス
    ✔ 秒間 1000 アクセス 達成
    my.cnf チューニング 総評
    204

    View Slide

  205. 結:設定結果のパフォーマンス
    ✔ 秒間 1000 アクセス 達成
    ✔ ストレージ 圧迫解消
    my.cnf チューニング 総評
    205

    View Slide

  206. 結:設定結果のパフォーマンス
    ✔ 秒間 1000 アクセス 達成
    ✔ ストレージ 圧迫解消
    ✔ バッファプールとログで 高速化
    my.cnf チューニング 総評
    206

    View Slide

  207. 結:設定結果のパフォーマンス
    ✔ 秒間 1000 アクセス 達成
    ✔ ストレージ 圧迫解消
    ✔ バッファプールとログで 高速化
    ✔ レプリケーションで 冗長化
    my.cnf チューニング 総評
    207

    View Slide

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

    View Slide

  209. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    209

    View Slide

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

    View Slide

  211. 今日の目標
    振り返り
    211

    View Slide

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

    View Slide

  213. まとめ・課題
    課題
    ・継続監視の仕組み導入
      →zabbix拡充、mackerelなど
    ・ リファクタリング
      →プログラム改修、ERD設計しなおし
    213

    View Slide

  214. おしながき
    ✔ 起:突然のトラブル、把握、診断
    ✔ 承:my.cnf 設定と解説
    ✔ 転:レプリケーションで 冗長化
    ✔ 結:設定結果のパフォーマンス
    ✔ まとめ、課題
    ✔ おわりに
    214

    View Slide

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

    View Slide

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

    View Slide

  217. RDBに
    親しもう
    217

    View Slide

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

    View Slide

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

    View Slide

  220. 220

    View Slide

  221. 221

    View Slide

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

    View Slide