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

2019/10/10 OpenSourceConference 2019.Enterprise
https://www.ospn.jp/osc2019.enterprise/

Transcript

1. 約1名で300台のMySQLを管理する技術 ～GMOメディアを支える技術をご紹介～ 2019/10/10 yoku0825 オープンソースカンファレンス2019.Enterprise
2. ＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ GMO Media, Inc. ‐ 1/71
3. とある企業 2/71
4. 会社の数だけDBのデザインパターンがある (c) インターネット界隈のことを調べるお http://takanoridayo.blog.shinobi.jp/Entry/426/ 3/71
5. MySQLの面倒を見る体制 DBAチーム 現在3人体制 PostgreSQL, Oracleの面倒を見ている人もいる ワーストの時期では稼働できたのが俺一人だった 4/71
6. MySQLの面倒を見る体制 DBAチーム データベース専任 ‐ インフラ兼務とかアプリケーション運用を兼ねずにデータベースのみ ‐ 現在3人体制 PostgreSQL, Oracleの面倒を見ている人もいる ワーストの時期では稼働できたのが俺一人だった 5/71
7. MySQLの面倒を見る #とは 障害対応 MySQLに関するサポート DBに特化した（広義の）インフラデザイン DBに特化したショット作業 6/71
8. MySQLの面倒を見る #とは 障害対応 DBサーバーのシェルを使えるのはDBチームとインフラチームのみ ‐ AP起因でMySQLがぶん回ったりするものを含む ‐ H/W交換はお任せ、OSを再セットアップしてからが出番 ‐ MySQLに関するサポート 「新しい開発環境のDBがほしいんですけど」 ‐ 「APサーバーが追加されたからユーザー追加してほしいんだけど」 ‐ 「クエリー遅いんですけど」 ‐ 7/71
9. MySQLの面倒を見る #とは DBに特化した（広義の）インフラデザイン バックアップの頻度, 保管先, ..etc. + その実装 ‐ 監視, リソースモニタリング, ..etc. + その実装 ‐ mikasafabric for MySQL + MySQL Router ‐ メジャーバージョンアップの検証とか、Percona ServerとかMariaDBとか ‐ DBに特化したショット作業 吊るしの ALTER TABLE 以外を使ったテーブル定義の更新 < 5.6 だったり、テーブルが大きすぎてレプリケーションが詰まったりするケース ‐ スロークエリーチューニング ‐ マイナーバージョンアップ ‐ 8/71
10. 時期も由来も言語も違うアプリケーションがいっぱい… 9/71
11. つらい 10/71
12. MySQLを割と一人 で300台面倒を見る ために 11/71
13. 省力化 ただし悲惨な状態になってから手 を付けたものだけではない 12/71
14. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 13/71
15. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 14/71
16. 管理台帳 ここにExcelの台帳があるじゃろ？ 会社の外から見られないじゃろ？ ‐ スプレッドシートに変わるじゃろ？ 結局開くのに時間がかかるわ、他の機能に使いまわせないわ、古い情報（マイナーバージョン アップしたのに古いバージョン番号とか）が載ってたりするじゃろ？ ‐ ある日それが嫌になったんじゃ 15/71
17. 管理台帳 データはMySQLに保管 各サイト1台ずつ保管用のMySQLがあって、横串に見たい時用にグローバルアクセス可能な API Gateway + DynamoDBにキャッシュ ‐ 実体はPerlのスクリプトで、数台のインスタンスのMySQLに次々ログインして SHOW VARIABLES とか SHOW SLAVE STATUS とか諸々引っこ抜いてくる 定期的(2/day)に登録済のmysqldをチェック バージョンアップ、レプリケーションの構成変更など、勝手に情報を更新 ‐ マスター切り替えがあっても定期実行スクリプトを手で流し直してやるだけで台帳が最新に ‐ 16/71
18. 管理台帳 17/71
19. 管理台帳 正直何に保管しても良いしどんな言語で書いても良いと思うんだけど、定期ポーリ ングはした方が良い 人間はついうっかり忘れる ‐ mysql コマンドラインクライアントや curl でアクセスするのでCLIフレンドリー 普段ターミナルと仕事をしているので grep, awk と組み合わせてワンライナーにしやすいと嬉し い ‐ Ansibleのダイナミックインベントリーとか ‐ pecoと連携してssh接続先を検索するとか ‐ 監視への流用とか ‐ 18/71
20. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 19/71
21. 死活（？）監視 手が足りない時はあんまりプロアクティブに対応できない リアクティブ対応で可能な限り拾えるように 設定忘れや設定変更はしたくない 20/71
22. 死活（？）監視 手が足りない時はあんまりプロアクティブに対応できない 即対応しなくていいものをアラートにしない ‐ いずれ障害になるであろうものは早めにアラートにする ‐ リアクティブ対応で可能な限り拾えるように 設定忘れや設定変更はしたくない 21/71
23. 死活（？）監視 手が足りない時はあんまりプロアクティブに対応できない 即対応しなくていいものをアラートにしない 閾値をチューニングしやすいようにスクリプトに ‐ いずれ障害になるであろうものは早めにアラートにする ‐ リアクティブ対応で可能な限り拾えるように 設定忘れや設定変更はしたくない 22/71
24. 死活（？）監視 手が足りない時はあんまりプロアクティブに対応できない 即対応しなくていいものをアラートにしない ‐ いずれ障害になるであろうものは早めにアラートにする swappingはスラッシングの予兆になり得る 刺さるくらいの高トラフィックも一瞬でくる訳ではない ‐ リアクティブ対応で可能な限り拾えるように 設定忘れや設定変更はしたくない 23/71
25. 死活（？）監視 手が足りない時はあんまりプロアクティブに対応できない リアクティブ対応で可能な限り拾えるように 人間が(不)定期的に目で確認していた項目も、サーバーに負荷が出ないなら死活監視に組み込ん でしまう ‐ 「次の監視タイミングできっともとに戻るだろう」というものも情報だけは取っておきたかっ たり…でもそのためにログインするの？ ‐ 設定忘れや設定変更はしたくない 24/71
26. アドオンした死活（？）監視 SHOW PROCESSLIST バックグラウンド以外で長時間動きっぱなしのクエリーがあれば検知 暴走SELECTでバッファプールを食い荒らす奴とか 高負荷系はこれが最初に鳴って（普段は1秒未満で返るやつが、リソース不足で数秒かかるようになってそれが積 もって…）くれる ‐ 25/71
27. アドオンした死活（？）監視 AUTO_INCREMENT なカラムの使用量 あふれると即死なわりに意外と監視されていない ‐ オススメ ‐ information_schema.tables と information_schema.columns を JOIN して得る sys.schema_auto_increment_columns なら一発で取れる ‐ テーブルの数が多いと逆にこれが負荷になるので、テーブル数と相談しながら監視するかしな いか決めてる ‐ 26/71
28. アドオンした死活（？）監視 SHOW STATUS, SHOW VARIABLES Threads_connected / max_connections を監視 100%になると当然 Too many connections Percona Serverだとエラーログに出るんだけど 監視ユーザーは Super 持ちなことが多くて意外と気付かない ‐ 27/71
29. アドオンした死活（？）監視 SHOW STATUS, SHOW VARIABLES read_only マスターの場合はOFFであるべきで スレーブの場合はONであるべき 意外とうっかりミスが拾えていい ‐ 28/71
30. アドオンした死活（？）監視 SHOW SLAVE STATUS はMulti-Source Replication対応 意外と対応されてなかった。。 ‐ マスター、スレーブの自動判定 胸に手を当てて考えてみたら、俺は SHOW SLAVE STATUS の出力有無や SHOW PROCESSLIST の Binlog Dump スレッドの存在でマスターかスレーブか中間マスターなのか判断してるよね ‐ じゃあスクリプトにもそれやらせればいいんじゃないか ‐ 29/71
31. アドオンした死活（？）監視 問題があった時のログを詳細に残す WARNING, CRITICALならその時の生ログ（判定につかったステートメントの結果の戻り） を、時間と検知したアラートを添えてテキストファイルに吐かせる ‐ 「昨夜のアレなんだったんだろ？」を調査もできる ‐ あとから資料を作る時にも使える（こんなアラートを検知した時、こんな出力をしてたんだ よ、的な） ‐ See also, 後追いできるリソース監視 30/71
32. 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
33. 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
34. 死活（？）監視 手が足りない時はあんまりプロアクティブに対応できない リアクティブ対応で可能な限り拾えるように 設定忘れや設定変更はしたくない 管理台帳と連携して「台帳に追加されたら勝手に監視が始まる」 ‐ マスターとスレーブは自動判定 ( yt-healthcheck の機能として実装) ‐ 33/71
35. 管理台帳 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
36. 管理台帳 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
37. 管理台帳 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
38. 死活（？）監視を充実させると 多少雑にサイジングしてもワーニングのうちに回収できるケースが増える サイジングにかける時間を減らせる ‐ 予め必要そうな情報を残すようにしておく 障害 ⇒ 再現待ち ⇒ 情報収集 ⇒ 対処 のサイクルを短くする ‐ OSSにしておくことで一度踏んだ轍(= 「これも監視しておくべきだった」) をど んどん追加する気分になる 37/71
39. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 38/71
40. ログ監視 エラーログを監視しているところは少ない（俺調べ） が、まあ拾っておくと幸せになれるものもいくつかある フィルタリングして即Slackに放り投げているので、ポーリング監視に比べてレスポンスは圧倒 的に速い ‐ 死活（？）監視だけで全てをカバーしようと頑張るより、黙ってログをパースした 方が幸せになれることもある そんなにオススメはしない ‐ 俺には、このスタイルが合っていたというだけだと思う ‐ 39/71
41. ログ監視 [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
42. ログ監視 しばしば「おう、お前こんなログ吐くのな」というものと出会える 人間、思いも至らないものは監視できないけど、MySQLが吐くログのうち害がないもの以外… という条件で通知させていると稀によくある ‐ フィルタリングの仕組みを合わせて整備しておかないとあっという間にあふれる ‐ mysqld_safeだけならかなり前から syslog オプションがあるのでそっちで拾うの もいい 41/71
43. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 42/71
44. コンフィグテンプレートエンジン RDS for MySQLとかってインスタンスのフレーバーに合わせてある程度の項目を 自動で設定してくれるよね オンプレのMySQLだろうと「秘伝のタレ」とか「このサイズならこれくらい」の 目分量はある 目分量があるなら、自動設定させられるんでは 43/71
45. コンフィグテンプレートエンジン 例にしやすかったのでPHPにしましたが実際に使っているのはPerlです <?php $ipaddr= getHostByName(getHostName()); $server_id= ip2long($ipaddr) & 65535; $memsize_kb= exec("head -1 /proc/meminfo | awk '{print $2}'"); ?> [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
46. コンフィグテンプレートエンジン メジャーバージョン単位でテンプレートを作っておく 罠いパラメーターは一度回避すれば二度と食らわない ‐ パラメーター名の変更とかに追従できる ‐ 凝ったことはしない 職人芸が必要そうなところとかまで自動入力しようとすると大変 ‐ 今まで手入力してたものの8割カバーできれば十二分 45/71
47. コンフィグテンプレートエンジン report-host [mysql] セクションの prompt server-id 各種ファイル名のパスとか シンプルに物理メモリ量だけで決める innodb_buffer_pool_size もちろん一度出力した後はただの my.cnf だから自由に調整が利く 46/71
48. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 47/71
49. mikasafabric for MySQL もとはMySQL FabricというHAフレームワーク 高可用性とデータ・シャーディングを実現できるMySQL Fabricとは？ | Think IT（シンクイッ ト） ‐ MySQL Fabricを使うと、マスターのサーバーに障害が発生した際のフェイル オーバー処理を自動化できるだけでなく、フェイルオーバーによってMySQL サーバーの構成が変更された場合でも、アプリケーションからMySQLサー バーへの接続先を切り替える必要がありません。アプリケーションを変更する 必要無く、そのまま使い続けられます。 48/71
50. MySQL Fabric ただし2017/07にEOL… https://www.mysql.com/jp/support/eol-notice.html ‐ 2016/08の時点で既にフォークしてパッチを当てていた mikasafabric for MySQLをオープンソースライセンスで公開しました | GMOメディア エンジ ニアブログ ‐ 49/71
51. mikasafabric for MySQL マスターとスレーブが気軽に入れ替えられる構成 アプリケーションからは127.0.0.1のmysqlrouterに接続しているようにしか見えない ‐ mysqlrouterから本物のマスター/スレーブにルーティングされるので、アプリケーションから は透過的にフェイルオーバーできる ‐ 手動のpromoteなら2～3秒でスイッチオーバーが完了する ‐ スレーブをバージョンアップ ⇒ スイッチオーバー ⇒ スレーブになったもともとのマスターを バージョンアップ ‐ 50/71
52. mikasafabric for MySQL 51/71
53. mikasafabric for MySQL もともとは Master/Slave 1台ずつでSlaveがホットスタンバイのやつを管理するた めにMySQL Fabricにパッチを当てていた 現在、最大でフツーのAsync Mater/Slave 構成20台くらいをさばかせている もちろん連絡はするけど DBAだけでスイッチオーバーが完遂できる のは気分的に すごく楽 52/71
54. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 53/71
55. 後追いできるリソース監視 基本はCPU使用率やアプリ側の監視でスタートするけど 調査の過程で「あー、この項目のポーリングしてればよかった」ってことがよくあ る かといって常日頃見るわけでもないので、わざわざ収集しておくのも結構しんどい ‐ See also, アドオンした死活（？）監視 ⇒ 問題があった時のログを詳細に残す 54/71
56. PMP for Cacti 55/71
57. 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
58. yt-collect + re:dash クエリーダイジェストごとのrows_examined推移 57/71
59. yt-collect + re:dash テーブルサイズの推移 58/71
60. yt-collect 普段はただデータを保管（MySQLに突っ込んでる）するだけで、見たくなったら re:dashのコンテナー起動してSQL叩いてグラフにする 実際に見たくなった時に「どう見るか」を考える ‐ 59/71
61. 後追いできるリソース監視 yoku0825/anemoeater スローログを遡って分割して投入することでグラフを表示 Dockerコンテナと組み合わせて「見たくなったら起動」「見終わったら停止」の運用レス可視 化 ‐ スローログさえ残っていればいつでも任意の時点のグラフを見られる 60/71
62. anemoeater 61/71
63. 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
64. yt-binlog-groupby yoku0825/ytkit: Yoku-san no Tool KIT バイナリーログをGROUP BYするためのスクリプト(time, table, statement)に対応 ‐ バイナリーログさえ残っていればいつでも任意の時間帯でどのテーブルがホットスポットに なっていたかわかる ‐ 63/71
65. あとこまごましたこと バイナリーログ, リレーログ, エラーログ, スローログの名前は指定する ホスト名に依存させない ‐ ファイル名を固定することで、psshやtmuxとの相性が良くなって効率アップ ‐ 64/71
66. あとこまごましたこと テーブルサイズは大きくしすぎない, 無駄なスレーブを作らない ログテーブル、パーティショニングじゃなくてテーブルごと、月ごとにテーブル分けるのだっ てまだまだメリットがある ‐ テーブルサイズが十分小さくて数秒で終わるなら、あるいは数十分でも遅れていいスレーブだ けなら、サービスを止めずにInnoDBのオンラインALTER TABLEで本当にオンライン ‐ 65/71
67. あとこまごましたこと バージョンはなるべく新しい方がいい 特にモニタリング方面 ‐ 4.0とか5.0とかもう耐えられない ざくざくサポートを切ると作りこみが楽 ‐ OSのバージョンも統一できるとコマンド違わなくて楽 ‐ 66/71
68. 省力化 管理台帳 死活（？）監視 ログ監視 コンフィグテンプレートエンジン mikasafabric for MySQL 後追いできるリソース監視 あとこまごましたこと 67/71
69. OSSは組み合わせ 一つ一つは大したことではなくても、組み合わせて使うことで大きな歯車を回すこ とができる 追加のライセンス費用がかからないOSSなのでたくさん組み合わせることが可能 ‐ パレートの法則は俺に合っていたし大概のケースで上手くいくと思う 68/71
70. そして 69/71
71. 手を抜くための手 間を惜しまない 70/71
72. Any Questions and/or Suggestions? 71/71