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)
