約1名で300台のMySQLを管理する技術

Slide 1

Slide 1 text

約1名で300台のMySQLを管理する技術～GMOメディアを支える技術をご紹介～ 2019/10/10 yoku0825 オープンソースカンファレンス2019.Enterprise

Slide 2

Slide 2 text

＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ GMO Media, Inc. ‐ 1/71

Slide 3

Slide 3 text

とある企業 2/71

Slide 4

Slide 4 text

会社の数だけDBのデザインパターンがある (c) インターネット界隈のことを調べるお http://takanoridayo.blog.shinobi.jp/Entry/426/ 3/71

Slide 5

Slide 5 text

MySQLの面倒を見る体制 DBAチーム現在3人体制 PostgreSQL, Oracleの面倒を見ている人もいるワーストの時期では稼働できたのが俺一人だった 4/71

Slide 6

Slide 6 text

MySQLの面倒を見る体制 DBAチームデータベース専任 ‐ インフラ兼務とかアプリケーション運用を兼ねずにデータベースのみ ‐ 現在3人体制 PostgreSQL, Oracleの面倒を見ている人もいるワーストの時期では稼働できたのが俺一人だった 5/71

Slide 7

Slide 7 text

MySQLの面倒を見る #とは障害対応 MySQLに関するサポート DBに特化した（広義の）インフラデザイン DBに特化したショット作業 6/71

Slide 8

Slide 8 text

MySQLの面倒を見る #とは障害対応 DBサーバーのシェルを使えるのはDBチームとインフラチームのみ ‐ AP起因でMySQLがぶん回ったりするものを含む ‐ H/W交換はお任せ、OSを再セットアップしてからが出番 ‐ MySQLに関するサポート「新しい開発環境のDBがほしいんですけど」 ‐ 「APサーバーが追加されたからユーザー追加してほしいんだけど」 ‐ 「クエリー遅いんですけど」 ‐ 7/71

Slide 9

Slide 9 text

MySQLの面倒を見る #とは DBに特化した（広義の）インフラデザインバックアップの頻度, 保管先, ..etc. + その実装 ‐ 監視, リソースモニタリング, ..etc. + その実装 ‐ mikasafabric for MySQL + MySQL Router ‐ メジャーバージョンアップの検証とか、Percona ServerとかMariaDBとか ‐ DBに特化したショット作業吊るしの ALTER TABLE 以外を使ったテーブル定義の更新 < 5.6 だったり、テーブルが大きすぎてレプリケーションが詰まったりするケース ‐ スロークエリーチューニング ‐ マイナーバージョンアップ ‐ 8/71

Slide 10

Slide 10 text

時期も由来も言語も違うアプリケーションがいっぱい… 9/71

Slide 11

Slide 11 text

つらい 10/71

Slide 12

Slide 12 text

MySQLを割と一人で300台面倒を見るために 11/71

Slide 13

Slide 13 text

省力化ただし悲惨な状態になってから手を付けたものだけではない 12/71

Slide 14

Slide 14 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 13/71

Slide 15

Slide 15 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 14/71

Slide 16

Slide 16 text

管理台帳ここにExcelの台帳があるじゃろ？会社の外から見られないじゃろ？ ‐ スプレッドシートに変わるじゃろ？結局開くのに時間がかかるわ、他の機能に使いまわせないわ、古い情報（マイナーバージョンアップしたのに古いバージョン番号とか）が載ってたりするじゃろ？ ‐ ある日それが嫌になったんじゃ 15/71

Slide 17

Slide 17 text

管理台帳データはMySQLに保管各サイト1台ずつ保管用のMySQLがあって、横串に見たい時用にグローバルアクセス可能な API Gateway + DynamoDBにキャッシュ ‐ 実体はPerlのスクリプトで、数台のインスタンスのMySQLに次々ログインして SHOW VARIABLES とか SHOW SLAVE STATUS とか諸々引っこ抜いてくる定期的(2/day)に登録済のmysqldをチェックバージョンアップ、レプリケーションの構成変更など、勝手に情報を更新 ‐ マスター切り替えがあっても定期実行スクリプトを手で流し直してやるだけで台帳が最新に ‐ 16/71

Slide 18

Slide 18 text

管理台帳 17/71

Slide 19

Slide 19 text

管理台帳正直何に保管しても良いしどんな言語で書いても良いと思うんだけど、定期ポーリングはした方が良い人間はついうっかり忘れる ‐ mysql コマンドラインクライアントや curl でアクセスするのでCLIフレンドリー普段ターミナルと仕事をしているので grep, awk と組み合わせてワンライナーにしやすいと嬉しい ‐ Ansibleのダイナミックインベントリーとか ‐ pecoと連携してssh接続先を検索するとか ‐ 監視への流用とか ‐ 18/71

Slide 20

Slide 20 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 19/71

Slide 21

Slide 21 text

死活（？）監視手が足りない時はあんまりプロアクティブに対応できないリアクティブ対応で可能な限り拾えるように設定忘れや設定変更はしたくない 20/71

Slide 22

Slide 22 text

死活（？）監視手が足りない時はあんまりプロアクティブに対応できない即対応しなくていいものをアラートにしない ‐ いずれ障害になるであろうものは早めにアラートにする ‐ リアクティブ対応で可能な限り拾えるように設定忘れや設定変更はしたくない 21/71

Slide 23

Slide 23 text

死活（？）監視手が足りない時はあんまりプロアクティブに対応できない即対応しなくていいものをアラートにしない閾値をチューニングしやすいようにスクリプトに ‐ いずれ障害になるであろうものは早めにアラートにする ‐ リアクティブ対応で可能な限り拾えるように設定忘れや設定変更はしたくない 22/71

Slide 24

Slide 24 text

死活（？）監視手が足りない時はあんまりプロアクティブに対応できない即対応しなくていいものをアラートにしない ‐ いずれ障害になるであろうものは早めにアラートにする swappingはスラッシングの予兆になり得る刺さるくらいの高トラフィックも一瞬でくる訳ではない ‐ リアクティブ対応で可能な限り拾えるように設定忘れや設定変更はしたくない 23/71

Slide 25

Slide 25 text

死活（？）監視手が足りない時はあんまりプロアクティブに対応できないリアクティブ対応で可能な限り拾えるように人間が(不)定期的に目で確認していた項目も、サーバーに負荷が出ないなら死活監視に組み込んでしまう ‐ 「次の監視タイミングできっともとに戻るだろう」というものも情報だけは取っておきたかったり…でもそのためにログインするの？ ‐ 設定忘れや設定変更はしたくない 24/71

Slide 26

Slide 26 text

アドオンした死活（？）監視 SHOW PROCESSLIST バックグラウンド以外で長時間動きっぱなしのクエリーがあれば検知暴走SELECTでバッファプールを食い荒らす奴とか高負荷系はこれが最初に鳴って（普段は1秒未満で返るやつが、リソース不足で数秒かかるようになってそれが積もって…）くれる ‐ 25/71

Slide 27

Slide 27 text

アドオンした死活（？）監視 AUTO_INCREMENT なカラムの使用量あふれると即死なわりに意外と監視されていない ‐ オススメ ‐ information_schema.tables と information_schema.columns を JOIN して得る sys.schema_auto_increment_columns なら一発で取れる ‐ テーブルの数が多いと逆にこれが負荷になるので、テーブル数と相談しながら監視するかしないか決めてる ‐ 26/71

Slide 28

Slide 28 text

アドオンした死活（？）監視 SHOW STATUS, SHOW VARIABLES Threads_connected / max_connections を監視 100%になると当然 Too many connections Percona Serverだとエラーログに出るんだけど監視ユーザーは Super 持ちなことが多くて意外と気付かない ‐ 27/71

Slide 29

Slide 29 text

アドオンした死活（？）監視 SHOW STATUS, SHOW VARIABLES read_only マスターの場合はOFFであるべきでスレーブの場合はONであるべき意外とうっかりミスが拾えていい ‐ 28/71

Slide 30

Slide 30 text

アドオンした死活（？）監視 SHOW SLAVE STATUS はMulti-Source Replication対応意外と対応されてなかった。。 ‐ マスター、スレーブの自動判定胸に手を当てて考えてみたら、俺は SHOW SLAVE STATUS の出力有無や SHOW PROCESSLIST の Binlog Dump スレッドの存在でマスターかスレーブか中間マスターなのか判断してるよね ‐ じゃあスクリプトにもそれやらせればいいんじゃないか ‐ 29/71

Slide 31

Slide 31 text

アドオンした死活（？）監視問題があった時のログを詳細に残す WARNING, CRITICALならその時の生ログ（判定につかったステートメントの結果の戻り）を、時間と検知したアラートを添えてテキストファイルに吐かせる ‐ 「昨夜のアレなんだったんだろ？」を調査もできる ‐ あとから資料を作る時にも使える（こんなアラートを検知した時、こんな出力をしてたんだよ、的な） ‐ See also, 後追いできるリソース監視 30/71

Slide 32

Slide 32 text

yt-healthcheck $ yt-healthcheck -h 172.17.0.1 -u user_name -p'password' -P 3306 yoku0825/ytkit: Yoku-san no Tool KIT 31/71

Slide 33

Slide 33 text

yt-healthcheck item master slave intermidiate fabric SHOW PROCESSLIST o o o - Too many connections o o o - AUTO_INCREMENT o - o - read_only OFF ON OFF - latest_deadlock o - o - SHOW SLAVE STATUS - o o - mikasafabric - - - o https://github.com/yoku0825/ytkit/blob/master/lib/Ytkit/ HealthCheck.pm#L104-L136 32/71

Slide 34

Slide 34 text

死活（？）監視手が足りない時はあんまりプロアクティブに対応できないリアクティブ対応で可能な限り拾えるように設定忘れや設定変更はしたくない管理台帳と連携して「台帳に追加されたら勝手に監視が始まる」 ‐ マスターとスレーブは自動判定 ( yt-healthcheck の機能として実装) ‐ 33/71

Slide 35

Slide 35 text

管理台帳 with 死活（？）監視 $ admin xx-db-s12 192.168.140.112 3306 /data/mysql/ 5.6.40-log 192.168.140.101: 3306 ACTIVE xx-bk11 192.168.140.121 3306 /data/mysql/ 5.6.40-log 192.168.140.101: 3306 ACTIVE xx-db-m11 192.168.140.101 3306 /data/mysql/ 5.6.40-log master ACTIVE xx-bk11 192.168.140.121 3307 /data/admintool/ 8.0.11 master ACTIVE 34/71

Slide 36

Slide 36 text

管理台帳 with 死活（？）監視 $ type admin admin () { export MYSQL_PWD="$admintool_password"; mysql --default-character-set=utf8 -h${admintool_host} -P${admintool_port} -u${admint ool_user} -sse "SELECT * FROM AdminView.instance_list" } 35/71

Slide 37

Slide 37 text

管理台帳 with 死活（？）監視 $ admin | awk '{print $2, $3}' | while read ip port ; do > yt-healthcheck -h $ip -P $port -u $username -p $password > done OK on xx-db-s12: (slave) OK on xx-bk11: (slave) WARNING on xx-db-m11: table xx.some_log_table uses auto_increment column hogehoge_id 52.4 5%(1126261308/2147483648)" (master) OK on xx-bk11: (master) 36/71

Slide 38

Slide 38 text

死活（？）監視を充実させると多少雑にサイジングしてもワーニングのうちに回収できるケースが増えるサイジングにかける時間を減らせる ‐ 予め必要そうな情報を残すようにしておく障害 ⇒ 再現待ち ⇒ 情報収集 ⇒ 対処のサイクルを短くする ‐ OSSにしておくことで一度踏んだ轍(= 「これも監視しておくべきだった」) をどんどん追加する気分になる 37/71

Slide 39

Slide 39 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 38/71

Slide 40

Slide 40 text

ログ監視エラーログを監視しているところは少ない（俺調べ）が、まあ拾っておくと幸せになれるものもいくつかあるフィルタリングして即Slackに放り投げているので、ポーリング監視に比べてレスポンスは圧倒的に速い ‐ 死活（？）監視だけで全てをカバーしようと頑張るより、黙ってログをパースした方が幸せになれることもあるそんなにオススメはしない ‐ 俺には、このスタイルが合っていたというだけだと思う ‐ 39/71

Slide 41

Slide 41 text

ログ監視 [Note] Slave: received end packet from server due to dump thread being killed on master server_uuidカブりとかで出るやつ（I/Oスレッドが無限に再起動するやつ） ‐ mysqld_safe Number of processes running now: 0 OOM Killerに亭主を殺されたり、SEGVでmysqldが死んだりした時はまずこれが出る。 mysqld_safeを使ってると瞬間的に再起動されちゃって死活監視に引っかからないことがあるので。 ‐ [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. binlog_format = STATEMENT でレプリケーションアンセーフな関数使っちゃった時に ‐ 今はもう binlog_format = STATEMENT 撲滅したので二度と出ることはないような気もする ‐ 40/71

Slide 42

Slide 42 text

ログ監視しばしば「おう、お前こんなログ吐くのな」というものと出会える人間、思いも至らないものは監視できないけど、MySQLが吐くログのうち害がないもの以外… という条件で通知させていると稀によくある ‐ フィルタリングの仕組みを合わせて整備しておかないとあっという間にあふれる ‐ mysqld_safeだけならかなり前から syslog オプションがあるのでそっちで拾うのもいい 41/71

Slide 43

Slide 43 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 42/71

Slide 44

Slide 44 text

コンフィグテンプレートエンジン RDS for MySQLとかってインスタンスのフレーバーに合わせてある程度の項目を自動で設定してくれるよねオンプレのMySQLだろうと「秘伝のタレ」とか「このサイズならこれくらい」の目分量はある目分量があるなら、自動設定させられるんでは 43/71

Slide 45

Slide 45 text

コンフィグテンプレートエンジン例にしやすかったのでPHPにしましたが実際に使っているのはPerlです [mysqld] .. server-id= =$server_id?> = isset($opt["slave"]) ? "read_only" : ""?> report-host= =gethostname()?> innodb_buffer_pool_size= =(int) ($memsize_kb * 0.6)?>K innodb_log_file_size= =(int) ($memsize_kb * 0.6 * 0.2)?>K .. 44/71

Slide 46

Slide 46 text

コンフィグテンプレートエンジンメジャーバージョン単位でテンプレートを作っておく罠いパラメーターは一度回避すれば二度と食らわない ‐ パラメーター名の変更とかに追従できる ‐ 凝ったことはしない職人芸が必要そうなところとかまで自動入力しようとすると大変 ‐ 今まで手入力してたものの8割カバーできれば十二分 45/71

Slide 47

Slide 47 text

コンフィグテンプレートエンジン report-host [mysql] セクションの prompt server-id 各種ファイル名のパスとかシンプルに物理メモリ量だけで決める innodb_buffer_pool_size もちろん一度出力した後はただの my.cnf だから自由に調整が利く 46/71

Slide 48

Slide 48 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 47/71

Slide 49

Slide 49 text

mikasafabric for MySQL もとはMySQL FabricというHAフレームワーク高可用性とデータ・シャーディングを実現できるMySQL Fabricとは？ | Think IT（シンクイット） ‐ MySQL Fabricを使うと、マスターのサーバーに障害が発生した際のフェイルオーバー処理を自動化できるだけでなく、フェイルオーバーによってMySQL サーバーの構成が変更された場合でも、アプリケーションからMySQLサーバーへの接続先を切り替える必要がありません。アプリケーションを変更する必要無く、そのまま使い続けられます。 48/71

Slide 50

Slide 50 text

MySQL Fabric ただし2017/07にEOL… https://www.mysql.com/jp/support/eol-notice.html ‐ 2016/08の時点で既にフォークしてパッチを当てていた mikasafabric for MySQLをオープンソースライセンスで公開しました | GMOメディアエンジニアブログ ‐ 49/71

Slide 51

Slide 51 text

mikasafabric for MySQL マスターとスレーブが気軽に入れ替えられる構成アプリケーションからは127.0.0.1のmysqlrouterに接続しているようにしか見えない ‐ mysqlrouterから本物のマスター/スレーブにルーティングされるので、アプリケーションからは透過的にフェイルオーバーできる ‐ 手動のpromoteなら2～3秒でスイッチオーバーが完了する ‐ スレーブをバージョンアップ ⇒ スイッチオーバー ⇒ スレーブになったもともとのマスターをバージョンアップ ‐ 50/71

Slide 52

Slide 52 text

mikasafabric for MySQL 51/71

Slide 53

Slide 53 text

mikasafabric for MySQL もともとは Master/Slave 1台ずつでSlaveがホットスタンバイのやつを管理するためにMySQL Fabricにパッチを当てていた現在、最大でフツーのAsync Mater/Slave 構成20台くらいをさばかせているもちろん連絡はするけど DBAだけでスイッチオーバーが完遂できるのは気分的にすごく楽 52/71

Slide 54

Slide 54 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 53/71

Slide 55

Slide 55 text

後追いできるリソース監視基本はCPU使用率やアプリ側の監視でスタートするけど調査の過程で「あー、この項目のポーリングしてればよかった」ってことがよくあるかといって常日頃見るわけでもないので、わざわざ収集しておくのも結構しんどい ‐ See also, アドオンした死活（？）監視 ⇒ 問題があった時のログを詳細に残す 54/71

Slide 56

Slide 56 text

PMP for Cacti 55/71

Slide 57

Slide 57 text

yt-collect $ yt-collect -h 172.17.0.1 -u user_name -p'password' -P 3306 --output=sql --sql-update yoku0825/ytkit: Yoku-san no Tool KIT 56/71

Slide 58

Slide 58 text

yt-collect + re:dash クエリーダイジェストごとのrows_examined推移 57/71

Slide 59

Slide 59 text

yt-collect + re:dash テーブルサイズの推移 58/71

Slide 60

Slide 60 text

yt-collect 普段はただデータを保管（MySQLに突っ込んでる）するだけで、見たくなったら re:dashのコンテナー起動してSQL叩いてグラフにする実際に見たくなった時に「どう見るか」を考える ‐ 59/71

Slide 61

Slide 61 text

後追いできるリソース監視 yoku0825/anemoeater スローログを遡って分割して投入することでグラフを表示 Dockerコンテナと組み合わせて「見たくなったら起動」「見終わったら停止」の運用レス可視化 ‐ スローログさえ残っていればいつでも任意の時点のグラフを見られる 60/71

Slide 62

Slide 62 text

anemoeater 61/71

Slide 63

Slide 63 text

yt-binlog-groupby $ mysqlbinlog -vv /path/to/binlog | yt-binlog-groupby --cell=10m --group-by=time,table 170720 15:10 d1.t1 2 170720 15:20 d1.t1 1 170724 14:20 mysqlslap.t1 644 170724 14:50 t1 644 170724 15:40 t1 644 170724 20:00 d1.t1 2 170724 20:00 t2 1 170814 18:20 d1.t1 1 62/71

Slide 64

Slide 64 text

yt-binlog-groupby yoku0825/ytkit: Yoku-san no Tool KIT バイナリーログをGROUP BYするためのスクリプト(time, table, statement)に対応 ‐ バイナリーログさえ残っていればいつでも任意の時間帯でどのテーブルがホットスポットになっていたかわかる ‐ 63/71

Slide 65

Slide 65 text

あとこまごましたことバイナリーログ, リレーログ, エラーログ, スローログの名前は指定するホスト名に依存させない ‐ ファイル名を固定することで、psshやtmuxとの相性が良くなって効率アップ ‐ 64/71

Slide 66

Slide 66 text

あとこまごましたことテーブルサイズは大きくしすぎない, 無駄なスレーブを作らないログテーブル、パーティショニングじゃなくてテーブルごと、月ごとにテーブル分けるのだってまだまだメリットがある ‐ テーブルサイズが十分小さくて数秒で終わるなら、あるいは数十分でも遅れていいスレーブだけなら、サービスを止めずにInnoDBのオンラインALTER TABLEで本当にオンライン ‐ 65/71

Slide 67

Slide 67 text

あとこまごましたことバージョンはなるべく新しい方がいい特にモニタリング方面 ‐ 4.0とか5.0とかもう耐えられないざくざくサポートを切ると作りこみが楽 ‐ OSのバージョンも統一できるとコマンド違わなくて楽 ‐ 66/71

Slide 68

Slide 68 text

省力化管理台帳死活（？）監視ログ監視コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視あとこまごましたこと 67/71

Slide 69

Slide 69 text

OSSは組み合わせ一つ一つは大したことではなくても、組み合わせて使うことで大きな歯車を回すことができる追加のライセンス費用がかからないOSSなのでたくさん組み合わせることが可能 ‐ パレートの法則は俺に合っていたし大概のケースで上手くいくと思う 68/71