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

by mamy1326

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Name Twitter 　✔ 2017年の趣味：MySQL 　✔ 2018年の趣味：DNS 　✔ 2019年の趣味：HTTP/3…HTTPS？：まみやなおき＠PHPer ：@mamy1326（まみー）自己紹介 2

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

　質問です 7

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

9 安心（？）してください僕もあります

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

12 1事業部を救った結果

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

こりゃやべぇ… 42

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

起：状況把握、診断 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

Slide 52

Slide 52 text

起：状況把握、診断 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

Slide 53

Slide 53 text

起：状況把握、診断（参考） 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

Slide 54

Slide 54 text

起：状況把握、診断（参考） 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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

Slide 76

Slide 76 text

Slide 77

Slide 77 text

Slide 78

Slide 78 text

Slide 79

Slide 79 text

Slide 80

Slide 80 text

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

承：my.cnf 設定と解説大まかな commit時の動きバッファプールへ書き込み InnoDB ログへ書き込み実ファイルへ書き込み 106 プロセスの処理はここまで非同期書き込み

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

my.cnf 設定まとめ 110

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

反映手順 112

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

115

Slide 116

Slide 116 text

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

Slide 117

Slide 117 text

Appendix：slow query 関連 [mysqld] # slow log 出力設定 slow_query_log = ON # slow.log の場所 slow_query_log_ﬁle = /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

Slide 118

Slide 118 text

Slide 119

Slide 119 text

Slide 120

Slide 120 text

Slide 121

Slide 121 text

Slide 122

Slide 122 text

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

Slide 123

Slide 123 text

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

Slide 124

Slide 124 text

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

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

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

Slide 127

Slide 127 text

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

Slide 128

Slide 128 text

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

Slide 129

Slide 129 text

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

Slide 130

Slide 130 text

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

Slide 131

Slide 131 text

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

Slide 132

Slide 132 text

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

Slide 133

Slide 133 text

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

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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

Slide 136

Slide 136 text

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

Slide 137

Slide 137 text

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

Slide 138

Slide 138 text

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

Slide 139

Slide 139 text

マスターの 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)

Slide 140

Slide 140 text

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

Slide 141

Slide 141 text

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

Slide 142

Slide 142 text

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

Slide 143

Slide 143 text

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

Slide 144

Slide 144 text

マスターのステータス確認 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)

Slide 145

Slide 145 text

マスターのステータス確認 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)

Slide 146

Slide 146 text

マスターのステータス確認 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 が実行された位置情報

Slide 147

Slide 147 text

マスターのステータス確認 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) スレーブの設定で必要のためメモしておきましょう

Slide 148

Slide 148 text

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

Slide 149

Slide 149 text

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

Slide 150

Slide 150 text

スレーブの 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

Slide 151

Slide 151 text

[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

Slide 152

Slide 152 text

[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 レプリケーション事前準備スレーブでのバイナリログ出力設定

Slide 153

Slide 153 text

[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 レプリケーション事前準備スレーブではこれが必要（次ページで説明）

Slide 154

Slide 154 text

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

Slide 155

Slide 155 text

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

Slide 156

Slide 156 text

スレーブの 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権限は防げない)

Slide 157

Slide 157 text

スレーブの 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 で解決

Slide 158

Slide 158 text

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

Slide 159

Slide 159 text

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

Slide 160

Slide 160 text

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

Slide 161

Slide 161 text

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

Slide 162

Slide 162 text

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

Slide 163

Slide 163 text

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

Slide 164

Slide 164 text

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

Slide 165

Slide 165 text

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

Slide 166

Slide 166 text

マスターのステータス確認【再掲】 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) 大きくズレている

Slide 167

Slide 167 text

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

Slide 168

Slide 168 text

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

Slide 169

Slide 169 text

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

Slide 170

Slide 170 text

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

Slide 171

Slide 171 text

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

Slide 172

Slide 172 text

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

Slide 173

Slide 173 text

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

Slide 174

Slide 174 text

174

Slide 175

Slide 175 text

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

Slide 176

Slide 176 text

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' 　→暗黙的な属性付与で意図しない値になる

Slide 177

Slide 177 text

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

Slide 178

Slide 178 text

[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)

Slide 179

Slide 179 text

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

Slide 180

Slide 180 text

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

Slide 181

Slide 181 text

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

Slide 182

Slide 182 text

マスターのステータス確認 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) マスターのバイナリログファイル名とポジション

Slide 183

Slide 183 text

スレーブ設定 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;

Slide 184

Slide 184 text

スレーブ設定 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; マスターへの接続

Slide 185

Slide 185 text

スレーブ設定 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; スレーブに設定します

Slide 186

Slide 186 text

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

Slide 187

Slide 187 text

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

Slide 188

Slide 188 text

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

Slide 189

Slide 189 text

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 レプリケーション設定・実行マスターからの更新イベント待ち＝スレーブ稼働

Slide 190

Slide 190 text

レプリケーション実行確認 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 マスターの現在のバイナリログファイル名

Slide 191

Slide 191 text

レプリケーション実行確認 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スレッドが読み取った位置

Slide 192

Slide 192 text

レプリケーション実行確認 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スレッドが動作している

Slide 193

Slide 193 text

レプリケーション実行確認 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が実行された位置

Slide 194

Slide 194 text

レプリケーション実行確認 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 マスターに比べ遅延している秒数

Slide 195

Slide 195 text

レプリケーション実行確認 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 差分が遅延しているトランザクション

Slide 196

Slide 196 text

レプリケーション実行確認 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スレッド共にエラーなし

Slide 197

Slide 197 text

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

Slide 198

Slide 198 text

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

Slide 199

Slide 199 text

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

Slide 200

Slide 200 text

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

Slide 201

Slide 201 text

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

Slide 202

Slide 202 text

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

Slide 203

Slide 203 text

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

Slide 204

Slide 204 text

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

Slide 205

Slide 205 text

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

Slide 206

Slide 206 text

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

Slide 207

Slide 207 text

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

Slide 208

Slide 208 text

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

Slide 209

Slide 209 text

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

Slide 210

Slide 210 text

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

Slide 211

Slide 211 text

今日の目標振り返り 211

Slide 212

Slide 212 text

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

Slide 213

Slide 213 text

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

Slide 214

Slide 214 text

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

Slide 215

Slide 215 text

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

Slide 216

Slide 216 text

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

Slide 217

Slide 217 text

RDBに親しもう 217

Slide 218

Slide 218 text

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

Slide 219

Slide 219 text

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

Slide 220

Slide 220 text

220

Slide 221

Slide 221 text

221

Slide 222

Slide 222 text

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