Sui’s blog

仕事関係のことや趣味に関すること、ふと思ったことを書いていこうと思います。

MySQLサーバのディスク容量が少なくなった時に確認する項目

はじめに

担当しているシステムのMySQLサーバのディスク容量がそろそろ一杯になりそう。
良い機会なので、DBサーバでディスク容量の問題に直面した場合にチェックすべきことなどをまとめた。

※ 日本語の方が読みやすいのでMySQLのリファレンスへのリンクは5.6の日本語版。

DBサーバでディスク容量の問題に直面した場合にチェックすべきこと

  1. サービスで使っているデータベースのデータサイズを確認する。
  2. バイナリログのサイズを確認する。

サービスで使っているデータベースのデータサイズを確認する。

データベースのディスク使用状況確認用SQL

SELECT  
    FORMAT(SUM(data_length + index_length + data_free), 0)
FROM 
    information_schema.tables
WHERE
    table_schema=database();

テーブル毎のディスク使用状況確認用SQL

SELECT  
    table_name
    , engine
    , FORMAT(table_rows, 0)     AS table_rows
    , FORMAT(avg_row_length, 0) AS avg_row_length
    , FORMAT(data_length, 0)    AS data_Byte
    , FORMAT(index_length, 0)   AS index_Byte
    , FORMAT(data_free, 0)      AS data_free_Byte
    , FORMAT((data_length + index_length + data_free), 0) AS all_Byte
FROM 
    information_schema.tables
WHERE
    table_schema=database()
ORDER BY
    (data_length + index_length + data_free) DESC;

各カラムが何を示しているかはMySQLのリファレンスを参照してください。
MySQLリファレンス 13.7.5.37 SHOW TABLE STATUS 構文

フラグメンテーションを解消するSQL

OPTIMIZE TABLE table_name;
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE table_name; 

本番環境でいきなりこのSQLを実行するのは避けた方がよさそう。
STG環境でリハーサルして

  • どれくらい時間がかかるのか
  • DBサーバメモリは大丈夫か
  • バイナリログの容量はどれくらい増えるのか

などを確認し、影響度合いを把握しておく方がいいと思う。

あと、OPTIMIZE TABLEをオプションを付けて実行すると、バイナリログに内容を書き込まないらしい。
リファレンスによると、

デフォルトでは、サーバーは OPTIMIZE TABLE ステートメントをバイナリログに書き込み、それらがレプリケーションスレーブにレプリケートされるようにします。ロギングを抑制するには、オプションの NO_WRITE_TO_BINLOG キーワード、またはそのエイリアス LOCAL を指定します。

MySQLリファレンス 13.7.2.4 OPTIMIZE TABLE 構文 その他の考慮事項

本番環境ではバイナリログを残さないという選択肢はないかもしれないが、知識として持っておこう。

OPTIMIZE TABLEは使ったことないので別の方法はないか?

別案も考えてみた。

代替案 ダミーテーブルを作っておいて、既存テーブルと差し替え、差し替え後drop tableする。

手順

  1. xxx_tableからxxx_table_newを作る
  2. xxx_tableからxxx_table_newへデータを流し込む
  3. xxx_table → xxx_table_oldに名前を変える
  4. xxx_table_new → xxx_tableに名前を変える
  5. 動作確認
  6. xxx_table_oldをdropする

検証

# 全レコード数 (有効なレコード + 論理削除されたレコード)
mysql> SELECT COUNT(*) FROM dummy;
+----------+
| COUNT(*) |
+----------+
| 46882470 |
+----------+
1 row in set (23.86 sec)
 
# 有効なレコード数
mysql> SELECT COUNT(*) FROM dummy WHERE delete_flg = 0;
+----------+
| COUNT(*) |
+----------+
| 29244002 |
+----------+
1 row in set (1 min 15.16 sec)
 
# 論理削除されたレコード
mysql> SELECT COUNT(*) FROM dummy WHERE delete_flg IS NULL;
+----------+
| COUNT(*) |
+----------+
| 17638468 |
+----------+
1 row in set (1 min 11.63 sec)
 
 
# 論理削除されているレコードを物理削除 (フラグメンテーションを疑似的につくる)
mysql> DELETE FROM dummy WHERE delete_flg IS NULL;
Query OK, 17638468 rows affected (36 min 38.17 sec)
 
 
# 確認
mysql> SELECT 
    ->     table_name
    ->     , engine
    ->     , FORMAT(table_rows, 0)     AS table_rows
    ->     , FORMAT(avg_row_length, 0) AS avg_row_length
    ->     , FORMAT(data_length, 0)    AS data_Byte
    ->     , FORMAT(index_length, 0)   AS index_Byte
    ->     , FORMAT(data_free, 0)      AS data_free_Byte
    ->     , FORMAT((data_length + index_length + data_free), 0) AS all_Byte
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     table_schema=database()
    -> ORDER BY
    ->     (data_length + index_length + data_free) DESC
    -> LIMIT 1;
+--------------------+--------+------------+----------------+---------------+---------------+----------------+----------------+
| table_name         | engine | table_rows | avg_row_length | data_Byte     | index_Byte    | data_free_Byte | all_Byte       |
+--------------------+--------+------------+----------------+---------------+---------------+----------------+----------------+
| dummy              | InnoDB | 29,398,547 | 192            | 5,654,904,832 | 7,365,050,368 | 153,092,096    | 13,173,047,296 |
+--------------------+--------+------------+----------------+---------------+---------------+----------------+----------------+
1 row in set (0.09 sec)

# dummy_newを作る
mysql> CREATE TABLE dummy_new LIKE dummy;
Query OK, 0 rows affected (0.45 sec)
 
# データを流す
mysql> INSERT INTO dummy_new SELECT * FROM dummy;
Query OK, 29244002 rows affected (1 hour 2 min 59.00 sec)
Records: 29244002  Duplicates: 0  Warnings: 0
 
 
# ディスク容量を確認
mysql> SELECT 
    ->     table_name
    ->     , engine
    ->     , FORMAT(table_rows, 0)     AS table_rows
    ->     , FORMAT(avg_row_length, 0) AS avg_row_length
    ->     , FORMAT(data_length, 0)    AS data_Byte
    ->     , FORMAT(index_length, 0)   AS index_Byte
    ->     , FORMAT(data_free, 0)      AS data_free_Byte
    ->     , FORMAT((data_length + index_length + data_free), 0) AS all_Byte
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     table_schema=database()
    -> ORDER BY
    ->     (data_length + index_length + data_free) DESC
    -> LIMIT 2;
+------------------------+--------+------------+----------------+---------------+---------------+----------------+----------------+
| table_name             | engine | table_rows | avg_row_length | data_Byte     | index_Byte    | data_free_Byte | all_Byte       |
+------------------------+--------+------------+----------------+---------------+---------------+----------------+----------------+
| dummy                  | InnoDB | 29,398,547 | 192            | 5,654,904,832 | 7,365,050,368 | 763,363,328    | 13,783,318,528 |
| dummy_new              | InnoDB | 29,283,623 | 102            | 3,012,558,848 | 4,084,023,296 | 4,194,304      | 7,100,776,448  |
+------------------------+--------+------------+----------------+---------------+---------------+----------------+----------------+
2 rows in set (0.00 sec)

効果ありそう。
これで効果があるのならば、MySQLdumpしてimportする方法でも効果がありそう。
MySQLのdumpファイルはdrop table -> create table -> insert みたいな流れなので、多分フラグメンテーションを解消できると思う。

どのアプローチを採用するかは各チームで話し合ってください。

バイナリログのサイズを確認する。

確認用SQL

SHOW BINARY LOGS;

バイナリログを消すためのSQL

PURGE BINARY LOGS TO 'xxx';

MySQLのリファレンスによると、バイナリログには下記2つの重要な目的がある。

レプリケーションについて、マスターレプリケーションサーバー上のバイナリログは、スレーブサーバーに送信されるデータ変更のレコードを提供します。マスターサーバーは、そのバイナリログに格納されているイベントをそのスレーブに送信し、スレーブはこれらのイベントを実行して、マスター上で実行されたものと同じデータ変更を実行します。
・ある特定のデータリカバリ操作には、バイナリログの使用が必要です。バックアップがリストアされたあと、バックアップが実行されたあとに記録されたバイナリログ内のイベントが再実行されます。これらのイベントは、データベースをバックアップのポイントから最新の状態に持って行きます。

MySQLリファレンス 5.2.4 バイナリログ

そのため、削除に関しては慎重に判断しましょう。