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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  7.  質問です
    7

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  42. こりゃやべぇ…
    42

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  72. 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 full-size slide

  73. 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 full-size 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
    75
    (ついで)エラーログは
    明確にしましょう

    View full-size 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
    76
    ここで設定していれば
    DB、テーブル、カラムで
    指定する必要がない

    View full-size 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
    77
    Charaset同様
    Collationを設定しておく

    View full-size 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
    78
    ただし
    CHARACTER SETすると
    無視されるので注意

    View full-size 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
    79
    ここで設定していれば
    通信時にも
    文字化けを防止できる

    View full-size slide

  79. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  109. my.cnf
    設定まとめ
    110

    View full-size slide

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

    View full-size slide

  111. 反映手順
    112

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  115. 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 full-size slide

  116. 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 full-size 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
    119
    no index のクエリを
    ログに出す

    View full-size 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
    120
    no index のクエリを
    ログに出す間隔(分)

    View full-size 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
    121
    SELECT の
    行数指定

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  137. マスターの 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  142. マスターのステータス確認
    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 full-size slide

  143. マスターのステータス確認
    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 full-size slide

  144. マスターのステータス確認
    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 full-size slide

  145. マスターのステータス確認
    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 full-size slide

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

    View full-size slide

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

    View full-size slide

  148. スレーブの 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 full-size slide

  149. [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 full-size slide

  150. [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 full-size 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 に設定追加
    153
    レプリケーション事前準備
    スレーブでは
    これが必要(次ページで説明)

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  154. スレーブの 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 full-size slide

  155. スレーブの 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 full-size slide

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

    View full-size slide

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

    View full-size slide

  158. スレーブの確認 (ダメな例)
    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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  164. マスターのステータス確認【再掲】
    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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  173. 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 full-size slide

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

    View full-size slide

  175. [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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  179. マスターのステータス確認
    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 full-size slide

  180. スレーブ設定
    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 full-size slide

  181. スレーブ設定
    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 full-size slide

  182. スレーブ設定
    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 full-size slide

  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;
    レプリケーション開始
    186
    レプリケーション設定・実行
    レプリケーションを
    開始

    View full-size slide

  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;
    レプリケーション開始
    187
    レプリケーション設定・実行
    レプリケーションを
    開始
    設定したファイル、ポジ
    ションから同期実行

    View full-size slide

  185. レプリケーション実行確認
    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 full-size slide

  186. 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 full-size slide

  187. レプリケーション実行確認
    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 full-size slide

  188. レプリケーション実行確認
    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 full-size slide

  189. レプリケーション実行確認
    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 full-size slide

  190. レプリケーション実行確認
    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 full-size slide

  191. レプリケーション実行確認
    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 full-size slide

  192. レプリケーション実行確認
    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 full-size slide

  193. レプリケーション実行確認
    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 full-size slide

  194. レプリケーション実行確認
    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 full-size slide

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

    View full-size slide

  196. レプリケーション実行状況の監視
    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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  214. RDBに
    親しもう
    217

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide