🌻

EmbulkでMySQLのメタ情報をBigQueryに集約し、可視化する

のりぃ

2020/12/27に公開

この記事は、OPENLOGI Advent Calendar 2020 23日目の記事です。

...ん？
投稿日付とあってないって？
いいんだよ、そんな細かいこと。
敢えて指摘しなきゃ誰も気づかないだろうに…

「勘のいいガキは嫌いだよ」
（画像省略）

序

数年単位で長いこと運用しているサービスでは、だんだんデータベースのデータ量が気になりますね。
ディスク容量が足りなくなったり、メモリに乗り切らなくなってパフォーマンスが落ちたり
MySQLだと、AutoIncrementなカラムのデータ型がうっかり32bitのintになってて、
気づけば上限（signedなら約21億、unsignedなら約42億）が近づいてたり。

そんなデータ量が気になる時に MySQLだと SHOW TABLE STATUS; を叩くと
各テーブルのレコード数（マニュアルにもありますが、この値はInnoDBエンジンの場合は近似値で、正確な値ではありません）、データ容量、現在のAutoIncrement値などがわかります。

が。

このデータはあくまで、そのクエリを叩いたタイミングでの値です。

DBを運用する立場としては、
「このテーブルは、どのくらいのペースでデータが増えているのだろうか？」
ということが気になるところです。

これがわかれば、だいたいいつ頃ストレージ増設が必要、とか、
このテーブルはデータ量肥大化してｋたいのでそろそろ何らかの対処が必要、
ということがある程度読めるようになります。

ようは、SHOW TABLE STATUS; の結果を定期的に叩いて、その結果をどっかに溜めておけばよいわけです。

で、ここまでの話は、実はインスパイアされた記事がありまして
MySQL界隈ではおなじみ yoku0825 さんのこのエントリです：

上記記事では、溜めたデータを別のMySQL DBに入れて、ASCIIグラフにしていますが
弊社では、バルクデータローダーのEmbulkと、
DWHとしてBigQueryを普段から活用しているので
この流れにMySQLのメタデータ収集も乗せればよいのでは？と考えました。

MySQLからBigQueryへメタデータを流すEmbulk設定ファイル

設定ファイルはこんな感じです。
SQLクエリは、ほぼ yoku0825さんの記事そのままですが、
自分は AutoIncrementの値も欲しかったので、それを追加しています。

mysql_meta_tables.yml

in:
  type: mysql
  host: somehost
  user: someuser
  password: somepassword
  database: information_schema
  options:
    useLegacyDatetimeCode: false
    serverTimezone: Asia/Tokyo
    characterEncoding: utf8mb4
  query: |
    SELECT
      table_schema,
      table_name,
      table_rows,
      data_length,
      index_length,
      data_free,
      auto_increment,
      NOW() AS last_update
    FROM
      tables
    WHERE
      table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') AND
      table_type = 'BASE TABLE'
out:
  type: bigquery
#  auth_method: 認証情報なのでブログ上では省略
  location: asia-northeast1
  compression: GZIP
  default_timezone: Asia/Tokyo
  project: awesome_project
  dataset: mysql_meta
  mode: append
  table: i_s_tables
  column_options:
  - name: table_schema
    type: STRING
  - name: table_name
    type: STRING
  - name: table_rows
    type: INTEGER
  - name: data_length
    type: INTEGER
  - name: index_length
    type: INTEGER
  - name: data_free
    type: INTEGER
  - name: auto_increment
    type: INTEGER
  - name: last_update
    type: TIMESTAMP

これを crontab で1日1回、定期的に実行します。

0 4 * * * cd /path/to/project; embulk run mysql_meta_tables.yml

すると、こんな感じでデータがとれます。

可視化

さらに、BigQueryなので、データポータルとも連携しやすいので可視化してみます。

まずは Table RowsのTOP20

Auto IncrementのTOP20

で、これだと、「日々どれだけ増えているか」がわかりずらいので
Dailyの推移をみたいのですが
そのままの実数でグラフを作ると、グラフが見にくくなるので、「前日との差分」値をグラフ化します。
そのために、BigQuery側に１つViewを作ります。
Window関数を使って、前日との差分もデータに含めています。

SELECT 
  DATE_SUB(DATE(last_update, 'Asia/Tokyo'), INTERVAL 1 DAY) AS target_date,
  table_schema,
  table_name,
  table_rows,
  table_rows - LAG(table_rows) OVER (PARTITION BY table_schema, table_name ORDER BY last_update) AS table_rows_delta,
  data_length,
  data_length - LAG(data_length) OVER (PARTITION BY table_schema, table_name ORDER BY last_update) AS data_length_delta,
  index_length,
  index_length - LAG(index_length) OVER (PARTITION BY table_schema, table_name ORDER BY last_update) AS index_length_delta,
  data_free,
  auto_increment,
  auto_increment - LAG(auto_increment) OVER (PARTITION BY table_schema, table_name ORDER BY last_update) AS auto_increment_delta
FROM `mysql_meta.i_s_tables`

これを利用した、AutoIncrement値の日々の増加量のグラフがこちら。

「増加量」のグラフなので、このグラフが右肩上がりだと、かなり急激にデータ量が増えてることになります。
横ばいならほぼ一定のペース。
12月20日がデータ増加量が少ないのは、日曜日で倉庫さんでの出庫作業が無いからです。

まとめ

MySQLの SHOW TABLE STATUS; の情報を Embulk経由でBigQueryへ流し
データポータルで可視化するところまでご紹介しました。

あとはこの情報をSlackに定期的に流したり
他のinformation_schemaやperformance_schemaの中の情報も
時系列でとっておくと有用そうな情報があるので
充実させていきたいな、と考えています。

OPENLOGI Tech BlogPublication

物流はこれから、テクノロジーによりダイナミックに変化する業界です。物を作る人とそれを欲しい人、その間の物流や配送がすべてネットワーク化された、需要と供給が最適化される次世代のインフラづくりを私たちは目指しています。

序

MySQLからBigQueryへメタデータを流すEmbulk設定ファイル

可視化

まとめ

Discussion