I’ll introduce queries to check the data and index sizes of databases and tables in MySQL.
Background: Want to Check MySQL Data Size for Cost Estimation
To estimate the database storage cost for Amazon Aurora MySQL, I researched how to check various data sizes of MySQL databases and tables.
Storage cost $0.12 USD per GB per monthSource: 料金 - Amazon Aurora | AWS
Query to Check Database Capacity
The query to check the capacity of each database in MySQL is as follows.
Please switch the comment out for the SET @unit= part to the unit line you want to check.
SET @unit=1024; #kb
# SET @unit=1024*1024; #mb
# SET @unit=1024*1024*1024; #gb
SELECT
table_schema, sum(data_length)/@unit AS db_size
FROM
information_schema.tables
GROUP BY
table_schema
ORDER BY
sum(data_length+index_length) DESC;
Query Execution Results: Database Capacity Check
+-----------------------+-----------+
| table_schema | db_size |
+-----------------------+-----------+
| mysql | 7665.2344 |
| yourapp_development | 416.0000 |
| yourapp_test | 400.0000 |
| information_schema | 160.0000 |
| sys | 16.0000 |
| performance_schema | 0.0000 |
+-----------------------+-----------+
6 rows in set (0.43 sec)
Query to Check Capacity of Each Table
The query to check the capacity of each table in MySQL is as follows.
Please switch the comment out for the SET @unit= part to the unit line you want to check.
SET @unit=1024; #kb
# SET @unit=1024*1024; #mb
# SET @unit=1024*1024*1024; #gb
SELECT
table_name, engine, table_rows AS tbl_rows,
avg_row_length AS rlen,
floor((data_length+index_length)/@unit) AS all_size,
floor((data_length)/@unit) AS data_size,
floor((index_length)/@unit) AS index_size
FROM
information_schema.tables
WHERE
table_schema=database()
ORDER BY
(data_length+index_length) DESC;
Query Execution Results: Capacity Check for Each Table
- all_size: Total capacity
- data_size: Data capacity
- index_size: Index capacity
+---------------------------+--------+----------+-------+----------+-----------+------------+
| table_name | engine | tbl_rows | rlen | all_size | data_size | index_size |
+---------------------------+--------+----------+-------+----------+-----------+------------+
| user | InnoDB | 1 | 16384 | 32 | 16 | 16 |
+---------------------------+--------+----------+-------+----------+-----------+------------+
That’s all from the Gemba.