🌻

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

私たちは身近な社会課題である物流の未来を変えるべく、「物流版AWS」をコンセプトとしたプラットフォームの開発、データの活用によって、業界全体の最適化を目指す会社です。

序

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

可視化

まとめ

Discussion