MySQL Technology Cafe ~ DBRE 活動と information_schema ~

本日のお話し ● 対象 ○ MySQL に少し触れたことがある方 ○ MySQL がもっと好きになりたい方 ○ これから DBRE をやっていこうと思っている方 ● ディープな技術の話はできません ○ 気楽に聴いていただければ幸いです ● お願い ○ ガヤガヤしてくださいｗ 2

プロローグ ● 一人目の DBRE として 2022年02月に KINTO テクノロジーズに入社 ○ 一人目の 〇〇 あるある ■ 組織の箱はあるけど何をやるかは自分次第 ■ 意気揚々と乗り込んだ先にあったものは・・・ 3

自己紹介 4 mysql > SELECT * FROM me \G ********************* 1. row ********************* name: 粟田 啓介 nickname: あわっち twitter: @_awache role: DBRE, CCoE favorite: EXPLAIN (FORMAT=TRADITIONAL) 1 rows in set (0.00 sec)

最初の壁 ● 一人目の DBRE として入社も DBRE の認知度が皆無 ○ 知りたいことを知るということのハードルが高い ■ プロダクトにはプロダクトの歴史がある ■ そしてそこにはその歴史を作ってきたキーパーソンが存在している ● けど誰も分からない 5

DBRE として何ができるのか ● DBRE として良い影響を与えたいという思いはあるものの ○ 現状の Database の設定がどうなっているのか分からない ○ 現場が Database に課題を感じているのか不明 ○ 安易に稼働している Database サーバーにクエリを打つわけにもいかない ○ 誰に聞けばいいのか分からない 何から手をつけたらいいか分からない 6

Database の現状を知りたい。。。 ● 自分が知りたい情報が存在するのか、どこにあるのか分からない ○ 情報があったとしてもサービス毎に違うフォーマットになると学習期間が長くなり価 値提供が遅くなるのは辛い ○ 欲しい情報がなかったらそれを開発部署に作成してもらわないと自分が動けない、 という状態になるのはもっと嫌 7

DBRE として最初にやったこと ● Database の今の状態を正しく把握しドキュメントを自動生成すること ○ Schema 設計はどうなっているのか？ ○ DB パラメータは何が設定されているのか？ ○ DB のデータサイズはどれくらい？ ○ MySQL に登録されたユーザーとその権限は？ 8

Database ドキュメントの自動生成 ● Database ドキュメント作成をエンジニアに強制したくない (し、きっと現場のエンジニアもやりたくない) ● 必要な情報がないと自分の動きが遅くなる、という状態でいるのも嫌 ● 稼働している環境から情報を引っ張ってきて自動化し、活きた情報をいつで も得たい ● フォーマットも統一したい 9

具体的に抽出している情報 ● DDL 情報 (最初の話はここがメイン) ● MySQL のシステム変数 ● DB のデータサイズ ● 登録されているユーザー情報 10

DDL 情報 ● DB インスタンス内に存在する Schema 情報 ○ Schema 一覧 ○ Schema 内の TABLE、VIEW、PROCEDURE、TRIGGER ○ カラム情報、INDEX 情報、FK 情報 などなど 11

SCHEMA 一覧 12 mysql > SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | | world_x | +--------------------+ 7 rows in set (0.05 sec)

SCHEMA 一覧 13 mysql > SELECT -> SCHEMA_NAME as `Database`, -> DEFAULT_CHARACTER_SET_NAME, -> DEFAULT_COLLATION_NAME -> FROM -> information_schema.SCHEMATA; +--------------------+----------------------------+------------------------+ | Database | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +--------------------+----------------------------+------------------------+ | mysql | utf8mb4 | utf8mb4_0900_ai_ci | | information_schema | utf8 | utf8_general_ci | | performance_schema | utf8mb4 | utf8mb4_0900_ai_ci | | sys | utf8mb4 | utf8mb4_0900_ai_ci | | world | utf8mb4 | utf8mb4_0900_ai_ci | | sakila | utf8mb4 | utf8mb4_0900_ai_ci | | world_x | utf8mb4 | utf8mb4_0900_ai_ci | +--------------------+----------------------------+------------------------+ 7 rows in set (0.01 sec)

TABLE / VIEW 一覧 14 mysql-local > SHOW TABLES; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | : : +----------------------------+ 23 rows in set (0.02 sec)

TABLE / VIEW 一覧 15 mysql > SELECT -> TABLE_NAME, -> TABLE_TYPE, ENGINE, -> CASE -> WHEN -> TABLE_COLLATION IS NULL THEN NULL -> ELSE -> SUBSTRING_INDEX(TABLE_COLLATION, '_', 1) -> END as "TABLE_CHARSET", -> TABLE_COLLATION, -> CREATE_TIME, -> TABLE_COMMENT -> FROM -> information_schema.TABLES -> WHERE -> TABLE_SCHEMA = 'sakila'; +----------------------------+------------+--------+---------------+--------------------+---------------------+---------------+ | TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_CHARSET | TABLE_COLLATION | CREATE_TIME | TABLE_COMMENT | +----------------------------+------------+--------+---------------+--------------------+---------------------+---------------+ | actor | BASE TABLE | InnoDB | utf8mb4 | utf8mb4_0900_ai_ci | 2022-07-22 14:55:53 | | | actor_info | VIEW | NULL | NULL | NULL | 2022-07-22 14:55:54 | VIEW | | address | BASE TABLE | InnoDB | utf8mb4 | utf8mb4_0900_ai_ci | 2022-07-22 14:55:53 | | : : +----------------------------+------------+--------+---------------+--------------------+---------------------+---------------+ 23 rows in set (0.06 sec)

COLUMN 一覧 16 mysql > DESC sakila.actor; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | actor_id | smallint unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ 4 rows in set (0.05 sec)

COLUMN 一覧 17 mysql > SELECT -> COLUMN_NAME as `Field`, COLUMN_TYPE as `Type`, IS_NULLABLE as `NULL`, COLUMN_KEY as `Key`, COLUMN_DEFAULT as `DEFAULT`, EXTRA, -> CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_COMMENT -> FROM -> information_schema.COLUMNS -> WHERE -> COLUMNS.TABLE_SCHEMA = 'sakila' AND -> COLUMNS.TABLE_NAME = 'actor' -> ORDER BY -> TABLE_NAME, ORDINAL_POSITION; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+--------------------+--------------------+----------------+ | Field | Type | NULL | Key | DEFAULT | EXTRA | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_COMMENT | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+--------------------+--------------------+----------------+ | actor_id | smallint unsigned | NO | PRI | NULL | auto_increment | NULL | NULL | | | first_name | varchar(45) | NO | | NULL | | utf8mb4 | utf8mb4_0900_ai_ci | | | last_name | varchar(45) | NO | MUL | NULL | | utf8mb4 | utf8mb4_0900_ai_ci | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | NULL | NULL | | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+--------------------+--------------------+----------------+ 4 rows in set (0.03 sec)

COLUMN 一覧 (FK付き) 18 mysql > SELECT COLUMN_NAME as `Field`, COLUMN_TYPE as `Type`, IS_NULLABLE as `NULL`, COLUMN_KEY as `Key`, COLUMN_DEFAULT as `DEFAULT`, EXTRA, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_COMMENT, -> ( -> SELECT -> CASE WHEN REFERENCED_TABLE_NAME IS NOT NULL THEN CONCAT(REFERENCED_TABLE_NAME, '.', REFERENCED_COLUMN_NAME) ELSE NULL END -> FROM -> information_schema.KEY_COLUMN_USAGE -> WHERE -> KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA = 'sakila' AND KEY_COLUMN_USAGE.TABLE_NAME = COLUMNS.TABLE_NAME AND KEY_COLUMN_USAGE.COLUMN_NAME = COLUMNS.COLUMN_NAME AND -> KEY_COLUMN_USAGE.CONSTRAINT_NAME != 'PRIMARY' AND KEY_COLUMN_USAGE.POSITION_IN_UNIQUE_CONSTRAINT is NOT NULL -> ) as Parent, -> ( -> SELECT -> CASE WHEN GROUP_CONCAT(TABLE_NAME) IS NOT NULL THEN GROUP_CONCAT(CONCAT(COLUMNS.TABLE_SCHEMA, '.', TABLE_NAME)) ELSE NULL END -> FROM -> information_schema.KEY_COLUMN_USAGE -> WHERE -> KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA = 'sakila' AND REFERENCED_TABLE_NAME = COLUMNS.TABLE_NAME AND REFERENCED_COLUMN_NAME = COLUMNS.COLUMN_NAME -> ) as Child -> FROM -> information_schema.COLUMNS -> WHERE -> COLUMNS.TABLE_SCHEMA = 'sakila' AND -> COLUMNS.TABLE_NAME = 'actor' -> ORDER BY -> TABLE_NAME, ORDINAL_POSITION; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+--------------------+--------------------+----------------+--------+-------------------+ | Field | Type | NULL | Key | DEFAULT | EXTRA | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_COMMENT | Parent | Child | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+--------------------+--------------------+----------------+--------+-------------------+ | actor_id | smallint unsigned | NO | PRI | NULL | auto_increment | NULL | NULL | | NULL | sakila.film_actor | | first_name | varchar(45) | NO | | NULL | | utf8mb4 | utf8mb4_0900_ai_ci | | NULL | NULL | | last_name | varchar(45) | NO | MUL | NULL | | utf8mb4 | utf8mb4_0900_ai_ci | | NULL | NULL | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | NULL | NULL | | NULL | NULL | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+--------------------+--------------------+----------------+--------+-------------------+ 4 rows in set (0.09 sec)

information_schema 面白っ！！ ● 本来の目的からどんどん外れてしまいましたが ○ 何気なく打っているコマンドがちょっと便利になる ○ 自分の中で何となくこうゆうものだ、と思っていたことが形になって分かる ○ ちょっと便利になることが積み重なるとちょっとできた気になる 19

とはいえこれだけでは活用するのは難しい ● クエリが複雑になると少しでも変更することは苦行 ○ どの SCHEMA のどの TABLE、など考え始めるとミスが発生する ● 毎回稼働している DB に対してクエリ叩くことも苦行 ○ 一つのテーブルならともかく全てのテーブルだと大変・・・ 20

ドキュメント作成をコマンド化 ● 自分が使いたい時にすぐに使える ● コマンド化することでミスが起こりづらい ● DB に接続できさえすればローカルからでも実行可能 ● プロジェクトの他のメンバーに渡したい ● アウトプットが統一されるので見るポイントを絞って戦いにいける ● 情報が足りないならばそのタイミングで自由に追加したい 21

22 車輪の再発明でもいい ● もっと色々なことができる OSS で tbls というツールがあります ○ https://github.com/k1LoW/tbls ■ ぜひみなさんはこちらをｗ ● 自分で手を動かすことによって得られたものをアウトプットしたい ○ その欲求で動いた結果、更にいろんなツールを開発していくモチベーションに変 わった

23 調子に乗っていくつかツールを開発 ツール名 機能概要 mysqlvars Database の設定パラメータを一覧で取得 、my.cnf を動的に生成 mysqlddl Database 設計情報(ddl) の出力 mysqldatasize Database スキーマ単位の DB のサイズ、INDEX のサイズ、データのサイズがそれぞれ出力される mysqlusers Database に設定されているユーザーとその権限一覧を取得できる mysqlerd Database のスキーマ時同士の関係性 （ER図） を HTML に出力 (裏側で schemaspy を実行しているだけ)

24 アウトプット Markdown JSON DDL

mysqldump を作ってみる ● KTC のメイン DB は Collation が拡張されている ○ ローカル環境作るのに mysqldump (--no-data) -> import だと失敗する ■ 5.7 系の COLLATION にはない utf8mb4_0900_ai_ci ■ dump の結果を sed するのも。。。 information_schema の出番！ 25

information_schema から DDL を生成 ● Local 用にいくつか変更 ○ AUTO INCREMENT を除外 ○ VIEW の DEFINER を除外 ○ COLLATIONを 5.7 に合わせて(雑に)変更 26

テスト 27 Information_schema から 必要な情報を取得 ● SCHEMATA ● TABLES ● COLUMNS ● TRIGGERS ● KEY_COLUMN_USAGE ● ROUTINES ● VIEWS DROP - CREATE Information_schema から 情報を再取得 Information_schema から 取得した情報を比較 Before After で差分比較 ● dump ファイル比較だと差分が出てしまう ● Information_schema に入っている情報が同じならきっ とセーフという打算

何でこんなことしたのか ● 本番環境に近いローカル環境を渡したかった ○ 稼働環境は 5.7 系なのにローカル環境は 8.0 とかありそう ■ 予期しない(無駄な)バグの混入を減らしたい 28

ローカル環境の提供 ● MySQL のパラメータから my.cnf を自動生成 ● DDL を作成 ● docker-compose に配置 29 👍👍

インターミッション ● ここまでは主に Database ドキュメントの自動生成についてお話させていただ いた。。。がしかし・・・ ○ 結局これでできたのは Database の現状を把握する手助けをすることだけ ○ 現場に課題がなければリアクティブな動きしかできない ○ 本当にそれでいいの・・・か？ 今やっている僕の活動やこれからのアウトプットについて少々触れてみる (注) 未来の話です 30

次なる壁 ● Database に対する課題のうち、優先的に行うことは何か？ ○ DBRE という立場から見たら改善したいと思うことは山盛り ○ 現場の立場から見たら今困ってないから課題になっていない DBRE と現場の GAP 31

やるべきことの優先順位付け ● Database はアプリケーションの一つのコンポーネント ○ ビジネスが継続的に成長し続けることが最も大切 ○ あれもこれも直して、だとエンジニアは疲弊する 32

ガードレールという考え方 ● ガードレールとは ○ 可能な限り自由を確保しつつ、望ましくない領域のみ制限、または発見するソ リューション ■ 主に Cloud Center of Excellence のアクティビティの中で表現されている ■ DBRE としても同じ思想で活動ができるように昇華中 33 実践：Cloud Center of Excellence を中心としたクラウド戦略

ガードレール構想と責任分界点イメージ 34 ● Define: 計測/評価の範囲や内容を定義 ○ 文書化 ○ Script 化 ● Measure: 計測/評価を実施して結果を収集 ○ Script 実行 ● Analyze: 原因の分析・レポーティング ○ 組織全体の可視性を高める ● Improve: 不具合の改善・改善計画の作成 ○ 問題に対してスムーズな対応を実施 ● Control: 成果を確認し、定着を目指す ○ サービスとして健全な状態が継続されている 定義 Define 計測/評価 Measure 分析 Analyze 改善 Improve 定着 Control D B R E プ ロ ダ ク ト 参考: DMAIC とは

要件を整理 ● エラーレベルを定義 ○ あれもこれもしたい欲求を抑える ■ これを決めないと全部 Critical とか言い出しそう 35 Level 定義 対応速度 Critical ● セキュリティ事故に直結する可能性があるもの ● クリティカルな異常に気付けない状態になっているもの 即時対応を実施 Error ● サービスの信頼性やセキュリティに関係する事故が発生する可能性があるもの ● Database 設計上の問題で、およそ 1 年以内に悪影響を及びす可能性があるもの 2 〜 3 営業日対応を実施 Warning ● それだけではサービスの信頼性やセキュリティ事故に直結しないもの ● セキュリティリスクを含むが影響が限定的なもの ● Database 設計上の問題で、およそ 2 年以内に悪影響を及ぼす可能性があるもの 計画的対応を実施 Notice ● 正常に動作しているが、記録しておきたい重要なもの 必要に応じて対応

具体的な内容整理 ● 責任領域を意識する ○ おおよそ自力でできる範囲を意識してガイドライン作成を検討 ■ グレーゾーンとか実際たくさんあるものの・・・意識することが重要 36 DB Instance TABLE Schema Column Data DBRE 確認領域 プロダクト領域

具体的なガイドライン設定 37 自信がないのでどこかで壁打ちしてくれる方お願いしますｗ

各プロダクトのアジリティと自律性を尊重する ● 細かなルールを設定しない ○ ガードレールでアラートが上がった場合にはエラーレベルに応じて対応をすること だけを共通認識化、浸透させる ■ 対応する、しない、に対して感情は無い方がいい ● やらない理由を考えるよりできる理由を考えた方が建設的 ● 対応方法に対する感情は大切 ■ これを実現するためには Executive Sponsor の強力な後押しが必要 ● まだここまでいけてないのでこれから頑張る 38

発見的ガードレールに昇華 ● 望ましくない設定がされたタイミングで通知して欲しい ○ 後戻りによるダメージが少ない ○ ドキュメントに起こすだけではおそらく誰も対応しない ■ いずれドキュメントは陳腐化される 39

自動化の軸 40 DB Instance TABLE Schema Column Data 各クラウドの API や performance_schema 等を利用 (クラウドの設定、設定パラメータ、USER など) Information_schema から情報を取得 Information_schema から情報を取得 Information_schema から情報を取得

Primary Key の無いテーブルを調べる 41 mysql > SELECT -> TABLE_SCHEMA, -> TABLE_NAME -> FROM -> information_schema.TABLES -> WHERE -> TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'tmp') AND -> TABLE_TYPE = 'BASE TABLE' AND -> TABLE_NAME NOT IN ( -> SELECT -> TABLE_NAME -> FROM -> information_schema.KEY_COLUMN_USAGE -> WHERE -> CONSTRAINT_NAME = 'PRIMARY' AND -> TABLE_TYPE = 'BASE TABLE' AND -> TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'tmp');

utf8(mb3) の設定された Schema, Column 一覧を取得 42 mysql > SELECT -> SCHEMA_NAME, -> CONCAT('schema''s default character set: ', DEFAULT_CHARACTER_SET_NAME) -> FROM -> information_schema.SCHEMATA -> WHERE -> SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'tmp') AND -> DEFAULT_CHARACTER_SET_NAME in ('utf8', 'utf8mb3') -> UNION -> SELECT -> CONCAT(TABLE_SCHEMA, ".", TABLE_NAME, ".", COLUMN_NAME), -> CONCAT('column''s default character set: ', CHARACTER_SET_NAME) as WARNING -> FROM -> information_schema.COLUMNS -> WHERE -> TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'tmp') AND -> CHARACTER_SET_NAME in ('utf8', 'utf8mb3') -> ;

予約語が設定された情報を取得 43

その他 ● mysqlsh の Upgrade Checker にあるものを移植 ○ upgrade_check.cc を見ながらコマンドに取り込み ■ ほとんど information_schema で完結 (一部 performance_schema など) ■ json で出力しているのでダッシュボード化など、どの様に料理するかは自分次第 44

ガードレールとして機能させる ● これらのクエリを定期的に実行して小さく早く治す ○ KTC は基本的に (DEV, STG, PRO の) 3ステージ制を取っているので Daily で回し ても PRO 環境に適用される手前で気付けるはず ○ 気づいた後のアクションをどうするか、DBRE としてプロアクティブに活動することが できるかどうか、がこれからの僕たちの価値につながる(はず) 45

まとめ ● information_schema は DBRE 活動を行う上で有益な情報がたくさん ○ テクニカルなスキルがなくとも割と簡単に扱える ■ とりあえず試す、使えそうだったら使うのサイクルも回しやすい ○ 簡単なことでも使い方次第で大きな武器になる ■ 僕はガードレールという軸で活用していこうと考えている 是非みなさんのちょっとした使い方を教えてください♪ 46

“ SELECT “QUESTIONS” FROM you; 47

“ SELECT “THANK YOU” FROM me; 48

49 The End.