How to Calculate and List Sizes of MySQL / MariaDB Databases

Monitoring the size of your MySQL or MariaDB databases is crucial for optimizing database performance, managing storage capacity, and planning for future growth.

In this short guide, we will show you how to calculate and list the sizes of your MySQL or MariaDB databases. By following these steps, you will have a better understanding of the storage requirements for each database and be able to make more informed decisions about database management.

Step 1: Log in to MySQL or MariaDB

First, open a terminal and log in to your MySQL or MariaDB server using the following command. Replace “your_username” with your MySQL or MariaDB username:

mysql -u your_username -p

Enter your password when prompted. Once logged in, you will see the MySQL or MariaDB command prompt.

Step 2: List Database Sizes

To list the sizes of all databases on your server, run the following SQL query:

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

This query retrieves information from the information_schema database, calculates the total size for each database (including data length and index length), and displays the results in megabytes (MB). The output will show a list of databases and their respective sizes.

For example:

MariaDB [(none)]>
SELECT table_schema AS "DB Name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

+--------------------+-------------+
| DB Name            | Size (MB)   |
+--------------------+-------------+
| wordpressdb        | 36.79408455 |
| information_schema |  0.14062500 |
| mysql              |  0.62723351 |
| oscommercedb       |  1.42187500 |
| performance_schema |  0.00000000 |
+--------------------+-------------+
5 rows in set (0.02 sec)

MariaDB [(none)]>

Step 3: List Individual Table Sizes

If you want to get a more detailed view of the storage requirements for each table within a specific database, you can run the following SQL query. Replace “your_database” with the name of the database you want to analyze:

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "your_database";

This query will display a list of tables within the specified database and their corresponding sizes in megabytes (MB).

Commands and Their Functions:

• mysql -u your_username -p – Logs in to the MySQL or MariaDB server.

Conclusion

In this guide, we have shown you how to calculate and list the sizes of MySQL or MariaDB databases and their individual tables. By regularly monitoring your database sizes, you can optimize performance, manage storage capacity, and plan for future growth. This information will help you make more informed decisions about database management and resource allocation.

Please feel free to leave comments and suggest improvements to this guide. Your feedback is valuable and helps us improve our content for our audience.