How to accurately calculate the size of your MySQL database

In the last few days, I had to determine the disk usage of a client's database in a shared server. Curiously, GitHub copilot suggested a snippet that basically executes a command in linux that returns the disk usage of a given directory in bytes:

du -sh /var/lib/mysql/db1

This solution sounded pretty plausible for me as my software had to interact with some CLI commands and I thought that it would be better to calculate the size of the database considering the whole directory, as there are other databases in the same environment.

To my surprise, after deploying it, the numbers weren't realistic, a database which a size of about 2GB, appeared as only 300MB in my report, so definitely something was wrong. But it wasn't the command, indeed the directory /var/lib/mysql/db1 size was of only 300MB approximately.

So what's going on?

The /var/lib/mysql/${db_name} directory on a Linux system, where dbname corresponds to the name of your database, stores the physical files corresponding to the MySQL database's tables and indexes. While this directory can give you a rough estimate of the database size on disk, there are a few considerations to keep in mind when using it to determine the exact size of the database such as:

Storage Engine Differences

MySQL supports multiple storage engines (e.g., InnoDB, MyISAM), and they handle space allocation differently. For instance, InnoDB may allocate additional space for transaction logs, undo space, and other control structures, which might not directly correlate with the actual data size visible through SQL queries.

File Formats

Some tables might use compression or other storage optimizations, affecting the disk space used versus the logical data size.

Temporary Files

During operations like large queries, MySQL might create temporary tables on disk, which could temporarily inflate the size of the /var/lib/mysql/dbname directory.

Binary Logs

If binary logging is enabled, the logs are stored in the /var/lib/mysql directory but not inside the specific dbname folder. These logs record changes to the database and are used for replication and recovery. They can consume significant disk space but are not directly related to the size of the database's data and index files.

Accuracy

For a quick approximation, looking at the size of /var/lib/mysql/db1 can be useful, especially if you're interested in the total disk space consumed by the database. However, this method does not account for overhead, fragmentation, or free space within the files themselves.

Calculating the real size in bytes of your database

If you need an accurate measurement of the database size from within MySQL, it's better to use SQL queries. For example, to get the size of all the tables in a specific database in MySQL, you can use the following query to obtain the size in bytes:

SELECT 
    SUM(data_length + index_length) 
FROM 
    information_schema.tables 
WHERE 
    table_schema = 'your_database_name';

You can show the results as well in MB directly with SQL:

SELECT 
    SUM(data_length + index_length) / 1024 / 1024 AS total_size_in_mb 
FROM 
    information_schema.tables 
WHERE 
    table_schema = 'your_database_name';

Or process the result any way you want with your favorite programming language.

Happy coding ❤️!