Getting the size of MySql databases and tables

Getting accurate results about the size and growth of your database tables enabled you to make smart decisions about partitioning and management of storage. I can guarantee that no-one wants to run out of storage on a database server.

Show the largest database from a database server

This has the assumption that you have access to the root account on the database server. You can normally look inside /var/lib/mysql to get an idea of the overall disk space, however, you will likely get more reliable results from querying the information_schema table inside MySql.

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;

+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| information_schema |      0.16 |
| big                | 299455.13 |
| average            | 104646.16 |
| small              |      1.10 |
+--------------------+-----------+

As you can see, we have 3 databases (aparts from information_schema which is not a real database). We have a big one, an average one and a small one. Making decisions about size is something specific to the projects you run, however, I normally assume that if you have Gigabytes of data, you start to need to spend more time finess-ing the server and its performance.

Show the largest tables on a particular database

In order to make correct decisions about which tables are the issue causing ones, you need to be able to judge the size of each table. This is done by calculating the data_length as a number (for megabytes). As you can see from the output below, the only big table is very_big_table. and it could possibly do with some optimization of partitioning of data to make it reliable in the future.

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

+----------------------+-----------+
| Table                | Size (MB) |
+----------------------+-----------+
| very_big_table       | 294904.69 |
| slightly_big_table   |   3501.97 |
| average_sized_table  |    510.53 |
| small_table          |    178.72 |
| bedside_table        |     98.19 |
+----------------------+-----------+