MySQLやMariaDBを利用する際に、データベースサイズや空き容量を確認する必要がある場合に利用できるSQLを紹介します。またテーブル毎のサイズ、インデックス毎のサイズ、テーブルに格納されているレコード数も算出できるSQLも紹介しています。
データベースを管理されている方はもちろん、開発者の方でも簡単にデータベース、テーブル、インデックス容量を確認出来ますので活用して頂ければと思います。
データベースサイズと空き容量を確認するSQL
MySQLとMariaDBのどちらでも、以下のSQLを実行することで、全データベース毎のサイズと空き容量を確認することが出来ます。
SELECT
table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 3) AS 'Database Size (MB)',
ROUND(SUM(data_free) / 1024 / 1024, 3) AS 'Free Space (MB)'
FROM
information_schema.tables
GROUP BY
table_schema
ORDER BY
2 DESC;このSQLをMySQLサーバーで実行すると、次のように各データベース毎のサイズと空き容量を確認することが出来ます。
MySQL [testdb]> SELECT
-> table_schema,
-> ROUND(SUM(data_length + index_length) / 1024 / 1024, 3) AS 'Database Size (MB)',
-> ROUND(SUM(data_free) / 1024 / 1024, 3) AS 'Free Space (MB)'
-> FROM
-> information_schema.tables
-> GROUP BY
-> table_schema
-> ORDER BY
-> 2 DESC;
+--------------------+--------------------+-----------------+
| TABLE_SCHEMA | Database Size (MB) | Free Space (MB) |
+--------------------+--------------------+-----------------+
| testdb | 3061.656 | 13.000 |
| mysql | 7.953 | 140.000 |
| sys | 0.016 | 0.000 |
| information_schema | 0.000 | 0.000 |
| performance_schema | 0.000 | 0.000 |
+--------------------+--------------------+-----------------+
5 rows in set (0.007 sec)同じようにこのSQLをMariaDBで実行すると、次のように各データベース毎のサイズと空き容量を確認することが出来ます。
MariaDB [testdb]> SELECT
-> table_schema,
-> ROUND(SUM(data_length + index_length) / 1024 / 1024, 3) AS 'Database Size (MB)',
-> ROUND(SUM(data_free) / 1024 / 1024, 3) AS 'Free Space (MB)'
-> FROM
-> information_schema.tables
-> GROUP BY
-> table_schema
-> ORDER BY
-> 2 DESC;
+---------------------+--------------------+-----------------+
| table_schema | Database Size (MB) | Free Space (MB) |
+---------------------+--------------------+-----------------+
| testdb | 278.813 | 11.000 |
| mysql | 2.602 | 0.000 |
| information_schema | 0.219 | 0.000 |
| infinidb_querystats | 0.047 | 0.000 |
| sys | 0.031 | 0.000 |
| performance_schema | 0.000 | 0.000 |
| calpontsys | 0.000 | 0.000 |
+---------------------+--------------------+-----------------+
7 rows in set (0.041 sec)テーブル・インデックスサイズとレコード数を確認するSQL
MySQLとMariaDBのどちらでも、以下のSQLを実行することで、各テーブル毎のサイズとインデックスサイズ、テーブルに格納されているレコード数を一度に確認することが出来ます。表示するサイズは全てメガバイト単位です。
SELECT
table_schema AS database_name,
table_name AS table_name,
engine AS engine,
table_rows AS table_rows,
ROUND(1.0 * data_length / 1024 / 1024, 2) AS data_size,
ROUND(index_length / 1024 / 1024, 2) AS index_size,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema')
ORDER BY
total_size DESC;このSQLをMySQLサーバーで実行すると、次のように各テーブル毎のサイズとインデックスサイズ、テーブルに格納されているレコード数を確認することが出来ます。
MariaDB [testdb]> SELECT
-> table_schema AS database_name,
-> table_name AS table_name,
-> engine AS engine,
-> table_rows AS table_rows,
-> ROUND(1.0 * data_length / 1024 / 1024, 2) AS data_size,
-> ROUND(index_length / 1024 / 1024, 2) AS index_size,
-> ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size
-> FROM information_schema.tables
-> WHERE table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema')
-> ORDER BY
-> total_size DESC;
+---------------+------------+-------------+------------+-----------+------------+------------+
| database_name | table_name | engine | table_rows | data_size | index_size | total_size |
+---------------+------------+-------------+------------+-----------+------------+------------+
| testdb | shipping | InnoDB | 1345700 | 145.70 | 124.84 | 270.55 |
| testdb | orders | InnoDB | 3827 | 0.42 | 0.36 | 0.78 |
| testdb | address | InnoDB | 3836 | 0.30 | 0.00 | 0.30 |
| testdb | warehouse | InnoDB | 18 | 0.02 | 0.05 | 0.06 |
| testdb | card | InnoDB | 0 | 0.02 | 0.03 | 0.05 |
| testdb | users | InnoDB | 0 | 0.02 | 0.03 | 0.05 |
| calpontsys | systable | Columnstore | 2000 | 0.00 | 0.00 | 0.00 |
| calpontsys | syscolumn | Columnstore | 2000 | 0.00 | 0.00 | 0.00 |
+---------------+------------+-------------+------------+-----------+------------+------------+
8 rows in set (0.003 sec)同じようにこのSQLをMariaDBで実行すると、次のように各データベース毎のサイズと空き容量を確認することが出来ます。
MySQL [testdb]> SELECT
-> table_schema AS database_name,
-> table_name AS table_name,
-> engine AS engine,
-> table_rows AS table_rows,
-> ROUND(1.0 * data_length / 1024 / 1024, 2) AS data_size,
-> ROUND(index_length / 1024 / 1024, 2) AS index_size,
-> ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size
-> FROM information_schema.tables
-> WHERE table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema')
-> ORDER BY
-> total_size DESC;
+---------------+------------------+--------+------------+-----------+------------+------------+
| database_name | table_name | engine | table_rows | data_size | index_size | total_size |
+---------------+------------------+--------+------------+-----------+------------+------------+
| testdb | api_request_logs | InnoDB | 30577 | 3044.08 | 13.64 | 3057.72 |
| testdb | users | InnoDB | 224 | 1.38 | 0.00 | 1.38 |
| testdb | negative_words | InnoDB | 5154 | 0.34 | 0.00 | 0.34 |
| testdb | posts | InnoDB | 536 | 0.14 | 0.08 | 0.22 |
| testdb | comments | InnoDB | 821 | 0.09 | 0.06 | 0.16 |
+---------------+------------------+--------+------------+-----------+------------+------------+
5 rows in set (0.004 sec)まとめ
MySQLとMariaDBのどちらでも使える、データベース容量と空き容量確認SQL、テーブル毎・インデックス毎の容量やレコード数を確認出来るSQLを紹介しました。このSQLを利用してデータベースの運用を楽にしていきましょう。
