DB容量、テーブル容量の確認方法

DBを運用していると「DB全体で何MB利用しているのだろう?」「各テーブルごとに何MB利用しているのだろう?」といったことを確認する必要がでてきます。ここでは、容量を確認する方法について紹介します。

DB容量の確認

下記クエリでDB容量を確認できます。

SELECT table_schema, 
       floor(SUM(data_length + index_length) / 1024 / 1024) AS ALL_MB,
       floor(SUM((data_length) / 1024 / 1024)) AS DATA_MB,
       floor(SUM((index_length) / 1024 / 1024)) AS INDEX_MB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY sum(data_length + index_length) DESC;
mysql> SELECT table_schema, 
    ->        floor(SUM(data_length + index_length) / 1024 / 1024) AS ALL_MB,
    ->        floor(SUM((data_length) / 1024 / 1024)) AS DATA_MB,
    ->        floor(SUM((index_length) / 1024 / 1024)) AS INDEX_MB
    -> FROM information_schema.tables
    -> GROUP BY table_schema
    -> ORDER BY sum(data_length + index_length) DESC;
+--------------------+--------+---------+----------+
| table_schema       | ALL_MB | DATA_MB | INDEX_MB |
+--------------------+--------+---------+----------+
| sample_db          |     45 |      35 |       10 |
| mysql              |      2 |       2 |        0 |
| information_schema |      0 |       0 |        0 |
| sys                |      0 |       0 |        0 |
| performance_schema |      0 |       0 |        0 |
+--------------------+--------+---------+----------+
9 rows in set (0.07 sec)

テーブル容量の確認

下記クエリでテーブル容量を確認できます。

SELECT table_name, 
       engine AS DBエンジン, 
       table_rows AS 行数,
       avg_row_length AS 平均レコード長,
       floor((data_length+index_length) / 1024 / 1024) AS ALL_MB,
       floor(data_length / 1024 / 1024) AS DATA_MB,
       floor(index_length / 1024 / 1024) AS INDEX_MB
FROM information_schema.tables
WHERE table_schema = database()
ORDER BY (data_length + index_length) DESC;

例として、mysqlデーターベース内の各テーブル容量を確認してみます。

mysql> use mysql;
mysql> SELECT table_name, 
    ->        engine AS DBエンジン, 
    ->        table_rows AS 行数,
    ->        avg_row_length AS 平均レコード長,
    ->        floor((data_length+index_length) / 1024 / 1024) AS ALL_MB,
    ->        floor(data_length / 1024 / 1024) AS DATA_MB,
    ->        floor(index_length / 1024 / 1024) AS INDEX_MB
    -> FROM information_schema.tables
    -> WHERE table_schema = database()
    -> ORDER BY (data_length + index_length) DESC;
+---------------------------+----------------+--------+-----------------------+--------+---------+----------+
| table_name                | DBエンジン     | 行数   | 平均レコード長        | ALL_MB | DATA_MB | INDEX_MB |
+---------------------------+----------------+--------+-----------------------+--------+---------+----------+
| help_topic                | InnoDB         |    601 |                  2644 |      1 |       1 |        0 |
| innodb_index_stats        | InnoDB         |   1232 |                   345 |      0 |       0 |        0 |
| proc                      | MyISAM         |     48 |                  6261 |      0 |       0 |        0 |
| help_keyword              | InnoDB         |    717 |                   137 |      0 |       0 |        0 |
| help_relation             | InnoDB         |   1522 |                    43 |      0 |       0 |        0 |
| innodb_table_stats        | InnoDB         |    249 |                   197 |      0 |       0 |        0 |
| help_category             | InnoDB         |     40 |                   409 |      0 |       0 |        0 |
| time_zone                 | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| plugin                    | InnoDB         |      1 |                 16384 |      0 |       0 |        0 |
| time_zone_transition_type | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| time_zone_transition      | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| gtid_executed             | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| slave_worker_info         | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| slave_relay_log_info      | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| slave_master_info         | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| servers                   | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| engine_cost               | InnoDB         |      2 |                  8192 |      0 |       0 |        0 |
| server_cost               | InnoDB         |      6 |                  2730 |      0 |       0 |        0 |
| time_zone_name            | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| time_zone_leap_second     | InnoDB         |      0 |                     0 |      0 |       0 |        0 |
| tables_priv               | MyISAM         |      2 |                   947 |      0 |       0 |        0 |
| proxies_priv              | MyISAM         |      1 |                   837 |      0 |       0 |        0 |
| db                        | MyISAM         |      2 |                   488 |      0 |       0 |        0 |
| user                      | MyISAM         |      3 |                   128 |      0 |       0 |        0 |
| columns_priv              | MyISAM         |      0 |                     0 |      0 |       0 |        0 |
| procs_priv                | MyISAM         |      0 |                     0 |      0 |       0 |        0 |
| event                     | MyISAM         |      0 |                     0 |      0 |       0 |        0 |
| func                      | MyISAM         |      0 |                     0 |      0 |       0 |        0 |
| ndb_binlog_index          | MyISAM         |      0 |                     0 |      0 |       0 |        0 |
| slow_log                  | CSV            |      2 |                     0 |      0 |       0 |        0 |
| general_log               | CSV            |      2 |                     0 |      0 |       0 |        0 |
+---------------------------+----------------+--------+-----------------------+--------+---------+----------+
31 rows in set (0.00 sec)