CodeNote.net

MySQL Query to Check Data Size of DB and Tables

Tadashi Shigeoka ·  Tue, July 6, 2021

I’ll introduce queries to check the data and index sizes of databases and tables in MySQL.

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 month

Source: 料金 - 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.

Reference Information