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

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

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