Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DBRE 活動と information_schema

_awache
July 28, 2022

DBRE 活動と information_schema

08-24(水) MySQL information_schemaとMySQL Shell For VS Code
https://oracle-code-tokyo-dev.connpass.com/event/254390/
での登壇資料です

_awache

July 28, 2022
Tweet

More Decks by _awache

Other Decks in Technology

Transcript

  1. 本日のお話し • 対象 ◦ MySQL に少し触れたことがある方 ◦ MySQL がもっと好きになりたい方 ◦

    これから DBRE をやっていこうと思っている方 • ディープな技術の話はできません ◦ 気楽に聴いていただければ幸いです • お願い ◦ ガヤガヤしてくださいw 2
  2. プロローグ • 一人目の DBRE として 2022年02月に KINTO テクノロジーズに入社 ◦ 一人目の

    〇〇 あるある ▪ 組織の箱はあるけど何をやるかは自分次第 ▪ 意気揚々と乗り込んだ先にあったものは・・・ 3
  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)
  4. 最初の壁 • 一人目の DBRE として入社も DBRE の認知度が皆無 ◦ 知りたいことを知るということのハードルが高い ▪

    プロダクトにはプロダクトの歴史がある ▪ そしてそこにはその歴史を作ってきたキーパーソンが存在している • けど誰も分からない 5
  5. DBRE として何ができるのか • DBRE として良い影響を与えたいという思いはあるものの ◦ 現状の Database の設定がどうなっているのか分からない ◦

    現場が Database に課題を感じているのか不明 ◦ 安易に稼働している Database サーバーにクエリを打つわけにもいかない ◦ 誰に聞けばいいのか分からない 何から手をつけたらいいか分からない 6
  6. DBRE として最初にやったこと • Database の今の状態を正しく把握しドキュメントを自動生成すること ◦ Schema 設計はどうなっているのか? ◦ DB

    パラメータは何が設定されているのか? ◦ DB のデータサイズはどれくらい? ◦ MySQL に登録されたユーザーとその権限は? 8
  7. DDL 情報 • DB インスタンス内に存在する Schema 情報 ◦ Schema 一覧

    ◦ Schema 内の TABLE、VIEW、PROCEDURE、TRIGGER ◦ カラム情報、INDEX 情報、FK 情報 などなど 11
  8. SCHEMA 一覧 12 mysql > SHOW DATABASES; +--------------------+ | Database

    | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | | world_x | +--------------------+ 7 rows in set (0.05 sec)
  9. 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)
  10. TABLE / VIEW 一覧 14 mysql-local > SHOW TABLES; +----------------------------+

    | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | : : +----------------------------+ 23 rows in set (0.02 sec)
  11. 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)
  12. 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)
  13. 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)
  14. 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)
  15. 22 車輪の再発明でもいい • もっと色々なことができる OSS で tbls というツールがあります ◦ https://github.com/k1LoW/tbls

    ▪ ぜひみなさんはこちらをw • 自分で手を動かすことによって得られたものをアウトプットしたい ◦ その欲求で動いた結果、更にいろんなツールを開発していくモチベーションに変 わった
  16. 23 調子に乗っていくつかツールを開発 ツール名 機能概要 mysqlvars Database の設定パラメータを一覧で取得 、my.cnf を動的に生成 mysqlddl

    Database 設計情報(ddl) の出力 mysqldatasize Database スキーマ単位の DB のサイズ、INDEX のサイズ、データのサイズがそれぞれ出力される mysqlusers Database に設定されているユーザーとその権限一覧を取得できる mysqlerd Database のスキーマ時同士の関係性 (ER図) を HTML に出力 (裏側で schemaspy を実行しているだけ)
  17. mysqldump を作ってみる • KTC のメイン DB は Collation が拡張されている ◦

    ローカル環境作るのに mysqldump (--no-data) -> import だと失敗する ▪ 5.7 系の COLLATION にはない utf8mb4_0900_ai_ci ▪ dump の結果を sed するのも。。。 information_schema の出番! 25
  18. information_schema から DDL を生成 • Local 用にいくつか変更 ◦ AUTO INCREMENT

    を除外 ◦ VIEW の DEFINER を除外 ◦ COLLATIONを 5.7 に合わせて(雑に)変更 26
  19. テスト 27 Information_schema から 必要な情報を取得 • SCHEMATA • TABLES •

    COLUMNS • TRIGGERS • KEY_COLUMN_USAGE • ROUTINES • VIEWS DROP - CREATE Information_schema から 情報を再取得 Information_schema から 取得した情報を比較 Before After で差分比較 • dump ファイル比較だと差分が出てしまう • Information_schema に入っている情報が同じならきっ とセーフという打算
  20. インターミッション • ここまでは主に Database ドキュメントの自動生成についてお話させていただ いた。。。がしかし・・・ ◦ 結局これでできたのは Database の現状を把握する手助けをすることだけ

    ◦ 現場に課題がなければリアクティブな動きしかできない ◦ 本当にそれでいいの・・・か? 今やっている僕の活動やこれからのアウトプットについて少々触れてみる (注) 未来の話です 30
  21. ガードレールという考え方 • ガードレールとは ◦ 可能な限り自由を確保しつつ、望ましくない領域のみ制限、または発見するソ リューション ▪ 主に Cloud Center

    of Excellence のアクティビティの中で表現されている ▪ DBRE としても同じ思想で活動ができるように昇華中 33 実践:Cloud Center of Excellence を中心としたクラウド戦略
  22. ガードレール構想と責任分界点イメージ 34 • Define: 計測/評価の範囲や内容を定義 ◦ 文書化 ◦ Script 化

    • Measure: 計測/評価を実施して結果を収集 ◦ Script 実行 • Analyze: 原因の分析・レポーティング ◦ 組織全体の可視性を高める • Improve: 不具合の改善・改善計画の作成 ◦ 問題に対してスムーズな対応を実施 • Control: 成果を確認し、定着を目指す ◦ サービスとして健全な状態が継続されている 定義 Define 計測/評価 Measure 分析 Analyze 改善 Improve 定着 Control D B R E プ ロ ダ ク ト 参考: DMAIC とは
  23. 要件を整理 • エラーレベルを定義 ◦ あれもこれもしたい欲求を抑える ▪ これを決めないと全部 Critical とか言い出しそう 35

    Level 定義 対応速度 Critical • セキュリティ事故に直結する可能性があるもの • クリティカルな異常に気付けない状態になっているもの 即時対応を実施 Error • サービスの信頼性やセキュリティに関係する事故が発生する可能性があるもの • Database 設計上の問題で、およそ 1 年以内に悪影響を及びす可能性があるもの 2 〜 3 営業日対応を実施 Warning • それだけではサービスの信頼性やセキュリティ事故に直結しないもの • セキュリティリスクを含むが影響が限定的なもの • Database 設計上の問題で、およそ 2 年以内に悪影響を及ぼす可能性があるもの 計画的対応を実施 Notice • 正常に動作しているが、記録しておきたい重要なもの 必要に応じて対応
  24. 自動化の軸 40 DB Instance TABLE Schema Column Data 各クラウドの API

    や performance_schema 等を利用 (クラウドの設定、設定パラメータ、USER など) Information_schema から情報を取得 Information_schema から情報を取得 Information_schema から情報を取得
  25. 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');
  26. 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') -> ;
  27. その他 • mysqlsh の Upgrade Checker にあるものを移植 ◦ upgrade_check.cc を見ながらコマンドに取り込み

    ▪ ほとんど information_schema で完結 (一部 performance_schema など) ▪ json で出力しているのでダッシュボード化など、どの様に料理するかは自分次第 44
  28. ガードレールとして機能させる • これらのクエリを定期的に実行して小さく早く治す ◦ KTC は基本的に (DEV, STG, PRO の)

    3ステージ制を取っているので Daily で回し ても PRO 環境に適用される手前で気付けるはず ◦ 気づいた後のアクションをどうするか、DBRE としてプロアクティブに活動することが できるかどうか、がこれからの僕たちの価値につながる(はず) 45
  29. まとめ • information_schema は DBRE 活動を行う上で有益な情報がたくさん ◦ テクニカルなスキルがなくとも割と簡単に扱える ▪ とりあえず試す、使えそうだったら使うのサイクルも回しやすい

    ◦ 簡単なことでも使い方次第で大きな武器になる ▪ 僕はガードレールという軸で活用していこうと考えている 是非みなさんのちょっとした使い方を教えてください♪ 46