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

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

3c9c5f9a91cac73073db8bb2903bd968?s=47 mamy1326
February 02, 2019

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

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

3c9c5f9a91cac73073db8bb2903bd968?s=128

mamy1326

February 02, 2019
Tweet

Transcript

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

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

    自己紹介 2
  3. ✔ MySQLに 興味がある 人    →これから使いたい、知っておきたい ✔ Web アプリケーション エンジニア    →DBAではない、クエリは主にORM

    ✔ RDB操作は 主に ツール を使う    →DBeaver、MySQL Workbench など 想定オーディエンスのみなさま 3
  4. MySQL 5.7にやられないためにおぼえておいてほしいこと •https://www.slideshare.net/yoku0825/mysql-57-53449734 よくある誤解 4

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

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

  7.  質問です 7

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

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

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

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

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

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

  14. おしながき ✔ 起:突然のトラブル、把握、診断 ✔ 承:my.cnf 設定と解説 ✔ 転:レプリケーションで 冗長化 ✔

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

    結:設定結果のパフォーマンス ✔ まとめ、課題 ✔ おわりに 15
  16. トラブル会場(事件現場) MySQL 5.5 系 (お話は5.7準拠) メモリ 8 GB ストレージ 20

    GB サーバ MySQL on EC2 (AWS) 時期 2017年1月 16
  17. トラブルは突然に 実際にあった怖い話 17

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

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

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

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

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

    実際にあった怖い話 22
  23. my.cnf = MySQLの (ほぼ) 全設定 23

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

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

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

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

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

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

    スロークエリ頻発   →5分以上、1000万レコードフルスキャン ・ スレーブがない   →ダウンするとサービス停止 状況:ノンチューニング
  30. トラブルは突然に 入社して担当になる 30

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

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

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

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

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

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

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

      →zabbixを覚え、AWSを覚え、計測方法を覚える 楽しく なって 趣味に なる 37
  38. トラブル は 人を 成長 させる (-人-) 38

  39. おしながき ✔ 起:突然のトラブル、把握、診断 ✔ 承:my.cnf 設定と解説 ✔ 転:レプリケーションで 冗長化 ✔

    結:設定結果のパフォーマンス ✔ まとめ、課題 ✔ おわりに 39
  40. 起:状況把握、診断 要求性能(MySQLサーバ) 40 request 秒間1000 memory 使用率 80% 前後 storage

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

    95% 以上 41
  42. こりゃやべぇ… 42

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

    ・ プログラムに 問題あり   →N+1 などなど 43
  44. 解決のため my.cnf の設定項目を 知りたい 44

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

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

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

    GPL なので無償利用可能   →Perlのアプリケーション ・ インストールして すぐ使える   →設定不要!(8.0系には対応中の模様) 47
  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
  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 デフォルト設定値
  50. デフォルトの my.cnf に対して 診断実行 50

  51. 起:状況把握、診断 MySQL Tuner 利用方法 $ cd ~/ $ git clone

    git@github.com:major/ MySQLTuner-perl.git $ cd MySQLTuner-perl $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root -- pass='mamy1326' 51
  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
  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
  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
  55. 5.7.24, 8.0.13 は 参考 として作った環境 55

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

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

  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
  59. 起:状況把握、診断 MySQL Tuner 診断結果 ① Enable the slow query log

    to troubleshoot bad queries ✔ スロークエリ出力設定を有効にして… ✔ クエリのトラブルシュートをしよう 59
  60. 起:状況把握、診断 MySQL Tuner 診断結果 ② query_cache_size (>= 8M) ✔ クエリキャッシュを設定しよう

    ✔ ただし MySQL 8.0 から無くなる機能 ✔ 今から別の機能を検討しましょう  (いずれ8.0系にする日がきます) 60
  61. 【余談】 なぜ クエリキャッシュ が 廃止 になったか 61

  62. クエリキャッシュ廃止の理由 1/2 62 ・ クエリキャッシュの限界    →マルチコアマシンでの高スループットな負荷の場合      スケールしない ( MySQL

    5.6 から無効化 (2013年) ) ・ クライアントでキャッシュすべき    →DBサーバでクエリキャッシュ             = 性能向上はヒットするクエリだけ    →全般的な性能向上             = アプリケーションで適切にキャッシュ
  63. クエリキャッシュ廃止の理由 2/2 63 ・ 開発リソースと効果の対比    →MySQL 独自のクエリキャッシュ開発コスト      vs     実際のクエリキャッシュで得られる効果      =

        年々効果が薄くなってきた機能への投資をやめる
  64. クエリキャッシュの 余談おわり 64

  65. MySQL Tuner 診断結果 ③ innodb_file_per_table=ON ✔ テーブルデータが共有領域なので ✔ テーブル個別領域に分けましょう ✔

    5.6.6 以降ではデフォルトONです 65 起:状況把握、診断
  66. MySQL Tuner 診断結果 ④ innodb_buffer_pool_size (>= 12G) if possible. ✔

    データとindexを キャッシュ に載せよう ✔ 物理メモリの 7〜8割 が基準 ✔ 大きいメモリの場合は基準が変わる 66 起:状況把握、診断
  67. MySQL Tuner 診断結果 ⑤ innodb_log_file_size should be equals to 1/4

    of buffer pool ✔ InnoDBログファイルのサイズ ✔ バッファプールの 4分の1 に設定しよう 67 起:状況把握、診断
  68. ・ スロークエリログ出力 ・ クエリキャッシュ ・ テーブル個別領域 ・ InnoDBバッファプール ・ InnoDBログファイル

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

    診断のまとめ 69 起:状況把握、診断 +α
  70. 登壇者 給水ポイント その① 70

  71. 71

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

  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 悲しいね(懐メロ)
  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
  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 (ついで)エラーログは 明確にしましょう
  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、テーブル、カラムで 指定する必要がない
  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を設定しておく
  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すると 無視されるので注意
  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 ここで設定していれば 通信時にも 文字化けを防止できる
  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(以下略
  81. Charaset, Collation おまけの話 おしまい 81

  82. おしながき ✔ 起:突然のトラブル、把握、診断 ✔ 承:my.cnf 設定と解説 ✔ 転:レプリケーションで 冗長化 ✔

    結:設定結果のパフォーマンス ✔ まとめ、課題 ✔ おわりに 82
  83. 承:my.cnf 設定と解説 テーブル個別領域 InnoDBバッファプール InnoDBログファイル 1 2 3 83

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

  85. 承:my.cnf 設定と解説 共有領域とは ・ 実データ を 1ファイル で扱う   →/var/lib/mysql/ibdata1 ・

    ファイルサイズが増え続ける   →削除領域は減らず、再利用される 85
  86. 承:my.cnf 設定と解説 テーブル個別領域とは ・ 実データ を 分けて 扱う   →/var/lib/mysql/[DB名]/テーブル名.ibd, frm

    ・ 削除された分は解放できる   →ファイル肥大化を防げる 86
  87. 共有領域によるデメリット ファイルサイズが増え続ける ✔ データ削除しても容量が減らない   → 削除データはファイル内で再利用される ✔ 追加するだけファイルサイズ増加   → 増え続け、いずれ空き領域がなくなる

    ストレージ圧迫、サーバ停止 87
  88. 承:状況把握、仮説、診断 状況把握 request 秒間20 memory 使用率 25% 前後 storage 使用率

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

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

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

      →DROP TABLE, TRUNCATEに限る   →計画的に削除すべき ・ 5.6.6 からデフォルトである テーブル個別領域のデメリット 91
  92. テーブル個別領域・反映結果 共有領域からテーブル個別領域へ ✔ ストレージを圧迫しなくなった   →半分近く空いた ✔ 継続的に未使用領域を解放   →監視しつつ適切に解放できるようになった ストレージ容量問題が改善 92

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

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

  95. 承:my.cnf 設定と解説 InnoDBバッファプールとは ・ キャッシュ領域   →データとindexが対象、ディスクI/O減少 ・ メモリの 70〜80%   →理想は全てのデータとindex

    ・ 読み書き 両方 に効果   →書き込みの速度も上がる 95
  96. 承:my.cnf 設定と解説 SELECT の動き ・ バッファプール を見る   →メモリから取ろうとする ・ なければ

    実データ を読む   →ディスクI/Oが発生する ・ その後バッファプールに載せる   →データから吸い上げてくれる 96
  97. 承:my.cnf 設定と解説 INSERT, UPDATE, DELETE の動き ・ バッファプール に書く   →空きがなければ、古いページを押し出す

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

    その後、ログファイル に書く   →commit反映待ちのログへ ・ 実データファイルを削除   →順番は更新系と同じ 98
  99. 必ず バッファプールから 読み書き している 99

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

  101. バッファプール ・設定内容 設定のポイント ・ メモリ 8G の 75% = 6G

    ・ mysql再起動 で反映される ・ 割り当て状況を監視して運用を [mysqld] innodb_buffer_pool_size=6G 101
  102. バッファプール ・反映結果 データとindexをキャッシュ ✔ 読み込みの高速化   →単純SELECTだけでなく、スロークエリも改善 ✔ 書き込みの高速化   →INSERT, UPDATE,

    DELETEも高速に さばけるプロセス数が増加! 102
  103. ✔ データの原本 ✔ キャッシュを有効活用 ✔ 読み込み性能が向上 ✔ 書き込み性能も向上 バッファプールのまとめ 103

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

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

    書き込み系の速度向上   →index追加、カラム追加も速度向上 105
  106. 承:my.cnf 設定と解説 大まかな commit時 の動き バッファ プールへ 書き込み InnoDB ログへ

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

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

    mysql再起動 [mysqld] innodb_log_file_size=1G 108
  109. ✔ commit蓄積・ディスクI/O削減 ✔ クラッシュリカバリできる ✔ 書き込み性能も向上 ✔ サイズを適切にチューニング InnoDBログのまとめ 109

    承:my.cnf 設定と解説
  110. my.cnf 設定まとめ 110

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

    111 承:my.cnf 設定と解説
  112. 反映手順 112

  113. my.cnf 設定・反映手順 ① DBへのアクセスを停止 ② dump取得 ③ 更新後の my.cnf に差し替え

    ④ MySQL 再起動 ⑤ バックアップからリストア ⑥ 動作検証・経過観察 113
  114. 登壇者 給水ポイント その➁ 114

  115. 115

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

  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
  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 普通のスロークエリ設定
  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 のクエリを ログに出す
  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 のクエリを ログに出す間隔(分)
  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 の 行数指定
  122. slow query 関連 おまけの話 おしまい 122

  123. おしながき ✔ 起:突然のトラブル、把握、診断 ✔ 承:my.cnf 設定と解説 ✔ 転:レプリケーションで 冗長化 ✔

    結:設定結果のパフォーマンス ✔ まとめ、課題 ✔ おわりに 123
  124. 転:レプリケーションで冗長化 レプリケーションとは ・ データベースの レプリカ(複製)   →マスターとスレーブ ・ MySQLの 標準機能   →手軽に設定、手軽に稼働

    ・ ほぼ時間差なしに 同期   →ネットワーク、データ量、台数による 124
  125. 転:レプリケーションで冗長化 構成 (〜中規模) 125 マスター( 単一 ) スレーブ ( 1

    ) スレーブ ( n ) ・・・・
  126. 転:レプリケーションで冗長化 マスターの役割 ・ データを 更新 する側   →スレーブは参照する側 ・ 更新内容を スレーブが取り

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

      →イベント受取 -> マスターbinlog取得 -> 更新 ・ マスターに 昇格 できる   →マスターと同じ設定が必要 127
  128. 負荷分散 の実現 128 レプリケーションのメリット① 検索処理が重く、全体に影響   →書き込みと重たいSQLが同じサーバー ✔ 重い処理をスレーブへ向ける   →分析系の処理や、管理画面系や総件数取得など マスターへの影響が

    解消
  129. 可用性 の高い構成の実現 129 レプリケーションのメリット➁ ✔ スレーブでバイナリログを出力   →my.cnf に設定する マスターが 障害

    でダウン   →スレーブがないと解決までサービス停止 スレーブ -> マスターへ 昇格 できる
  130. バックアップが容易 130 レプリケーションのメリット③ マスターの 圧迫 (ストレージ・ネットワーク)   →バックアップデータの保存、転送 ✔ スレーブでバックアップ   →バッチなどで実行

    マスターへの影響が 解消
  131. レプリケーション 事前準備 131

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

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

    スレーブの設定 スレーブの確認 5
  134. レプリケーションユーザー作成 ・ マスター側に 作成   →スレーブから参照できるユーザー ・ マスターのバイナリログ 取得用   →スレーブから接続して取得 ・

    レプリケーション 専用ユーザー   →適切な権限設定、マスター更新しないように 134 レプリケーション事前準備
  135. ユーザー作成コマンド 135 レプリケーション事前準備 mysql(master)> CREATE USER mysql(master)> ’repl’@’[スレーブのホスト名]’ mysql(master)> IDENTIFIED

    BY PASSWORD ’***’; ・ 適切なユーザー名: ‘repl’ など ・ 適切なパスワード ・ マスターで実行
  136. ユーザー権限付与コマンド 136 レプリケーション事前準備 mysql(master)> GRANT REPLICATION SLAVE mysql(master)> ON *.*

    TO mysql(master)> ’repl’@’[スレーブのホスト名]’; ・ 適切な権限: ‘REPLICATION SLAVE’ ・ 必要であればDB名なども適切に ・ 先ほど作ったユーザーに対し実行
  137. レプリケーション事前準備 1 2 3 137 4 レプリケーションユーザー作成 マスターの設定 マスターバイナリログの確認 スレーブの設定

    スレーブの確認 5
  138. マスターの my.cnf に設定追加 138 レプリケーション事前準備 [mysqld] # スレーブに通知するバイナリログ(実際はナンバリングされる) log-bin=mysql-bin #

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

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

    レプリケーションを行う、サーバーのユニークID server-id=1001 # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 多くしすぎると ストレージ圧迫注意
  142. レプリケーション事前準備 1 2 3 142 4 レプリケーションユーザー作成 マスターの設定 マスターバイナリログの確認 スレーブの設定

    スレーブの確認 5
  143. マスターバイナリログ出力 ・ マスターの mysqld 再起動   →設定を反映し、バイナリログを出力 ・ commit 履歴 出力

      →履歴の差分をスレーブが取得する ・ ファイル名、位置情報を確認   →現在のファイル&どこまでcommitしたかの位置 143 レプリケーション事前準備
  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)
  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)
  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 が 実行された位置情報
  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) スレーブの設定で必要のため メモしておきましょう
  148. スレーブ作成で気をつけたいこと 148 レプリケーション事前準備 ✔ マスターの更新を 停止して dump   →マスター、スレーブで差分やズレをなくす ✔ ファイルと位置情報を

    合わせる   →マスター、スレーブはなるべく同一で開始 差分・抜け漏れなく開始 する
  149. レプリケーション事前準備 1 2 3 149 4 レプリケーションユーザー作成 マスターの設定 マスターバイナリログの確認 スレーブの設定

    スレーブの確認 5
  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
  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
  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 レプリケーション事前準備 スレーブでの バイナリログ出力設定
  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 レプリケーション事前準備 スレーブでは これが必要(次ページで説明)
  154. log_slave_updates 解説 1/2 マルチマスター用の設定 (多段構成) ・スレーブが他のスレーブのマスター   →バイナリログがないとマスター足り得ない ・この設定で バイナリログが出力 される

      →スレーブでバイナリログを出力するのに必要 [mysqld] log_slave_updates 154
  155. log_slave_updates 解説 2/2 マスター昇格に備える ・すぐ マスターに昇格できる   →バイナリログがないとマスター足り得ない ・有効化しても 無害 である

      →http://nippondanji.blogspot.com/2014/12/mysqlgtid.html [mysqld] log_slave_updates 155
  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権限は防げない)
  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 で解決
  158. レプリケーション事前準備 1 2 3 158 レプリケーションユーザー作成 マスターの設定 マスターバイナリログの確認 スレーブの設定 スレーブの確認

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

  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)
  161. スレーブのステータス 解説 ① スレーブの現在の状態 ・空文字=レプリケーション 停止   →なんらかの理由で止まっている ・正常だとメッセージ   →Waiting for

    master to send event など    マスターからのイベント待ち Slave_IO_State:[空文字] 161
  162. スレーブのステータス 解説 ➁ 接続しに行くマスター ・ホスト名、またはIPアドレス ・新規、変更の場合は 設定が必要   →マスターが変更された場合 Master_Host: [マスターのホスト名]

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

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

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

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

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

    168
  169. スレーブのステータス 解説 ⑧ マスターに比べ遅延した秒数 ・NULL=レプリケーション 停止 ・0 = 遅延なし ・差分が多いと

    秒数が増える   →重いクエリを流す、過去のdumpでスレーブ作る Seconds_Behind_Master: NULL 169
  170. スレーブのステータス 解説 ⑨ 接続対象のマスターのID ・マスターの my.cnf で 設定したID ・0 =

    接続 できていない   →この場合は何度もマスターが落ちていた結果 Master_Server_Id: 0 170
  171. マスターの my.cnf に設定追加【再掲】 171 レプリケーション事前準備 [mysqld] # スレーブに通知するバイナリログ(実際はナンバリングされる) log-bin=mysql-bin #

    レプリケーションを行う、サーバーのユニークID server-id=1001 # バイナリログファイルのローテート日数 set-variable=expire_logs_days=3 スレーブから見た マスターのID
  172. 順番に レプリケーションを 実行します 172

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

  174. 174

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

  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'  →暗黙的な属性付与で意図しない値になる
  177. Appendix:暗黙的な属性付与は非推奨 mysql> SELECT cuntomer_name -> FROM customers -> WHERE create_at

    != '0000-00-00 00:00:00' -> AND create_at < NOW(); 177 見たことありませんか…(震え声 ▶やむなく対応の地獄  →やむなくプログラムでなんとかする  →やむなくWHERE 苦 句でなんとかする
  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)
  179. 暗黙的な属性付与は 非推奨、なおまけの話 おしまい 179

  180. レプリケーション手順 ① マスターdump取得 ② マスターバイナリログ名、ポジション取得 ③ スレーブ mysqld 再起動 ④

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

    スレーブ DROP & CREATE DATABASE ⑤ スレーブにdumpをリストア ⑥ レプリケーション設定・実行 ➆ 実行状況を監視 181
  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) マスターの バイナリログファイル名と ポジション
  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;
  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; マスターへの接続
  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; スレーブに設定します
  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 レプリケーション設定・実行 レプリケーションを 開始
  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 レプリケーション設定・実行 レプリケーションを 開始 設定したファイル、ポジ ションから同期実行
  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
  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 レプリケーション設定・実行 マスターからの 更新イベント待ち = スレーブ稼働
  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 マスターの現在の バイナリログファイル名
  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スレッドが 読み取った位置
  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スレッド が動作している
  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が実行された位置
  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 マスターに比べ 遅延している秒数
  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 差分が 遅延している トランザクション
  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スレッド 共にエラーなし
  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
  198. レプリケーション実行状況の監視 198 レプリケーション設定・実行 mysql> SHOW SLAVE STATUS\G **************** 1. row

    ************** Read_Master_Log_Pos: 878873047 Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615
  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
  200. ① スレーブが稼働  ・複製を利用可能になった ② バックアップ環境完成  ・スレーブで mysqldump を実行できる ③ 更新、参照の分割が可能

     ・分析系の重いクエリをスレーブへ レプリケーションまとめ 200 転:レプリケーションで 冗長化
  201. おしながき ✔ 起:突然のトラブル、把握、診断 ✔ 承:my.cnf 設定と解説 ✔ 転:レプリケーションで 冗長化 ✔

    結:設定結果のパフォーマンス ✔ まとめ、課題 ✔ おわりに 201
  202. パフォーマンスチューニング 総評 202

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

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

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

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

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

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

    バッファプールとログで 高速化 ✔ レプリケーションで 冗長化 ✔ サービスと事業が 軌道に my.cnf チューニング 総評 208
  209. おしながき ✔ 起:突然のトラブル、把握、診断 ✔ 承:my.cnf 設定と解説 ✔ 転:レプリケーションで 冗長化 ✔

    結:設定結果のパフォーマンス ✔ まとめ、課題 ✔ おわりに 209
  210. まとめ・課題 まとめ ・ my.cnf 設定 しよう!   →適切なパフォーマンスを得る ・ 監視・計測 しよう!

      →サービスは成長する ・ 継続的に 改善 しよう! 210
  211. 今日の目標 振り返り 211

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

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

  214. おしながき ✔ 起:突然のトラブル、把握、診断 ✔ 承:my.cnf 設定と解説 ✔ 転:レプリケーションで 冗長化 ✔

    結:設定結果のパフォーマンス ✔ まとめ、課題 ✔ おわりに 214
  215. おわりに 運用は大切 ・ 後から変更は大変 ・ インフラは他人事じゃない ・ 0 -> 1

    以降も大事 ・ データの寿命はアプリより長い 215
  216. データの寿命は アプリより長い 216

  217. RDBに 親しもう 217

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

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

  220. 220

  221. 221

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

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