(Last Updated On: November 28, 2018)

Are you trying to find which databases in your MySQL/MariaDB database server has large tablespace?. People have varying reasons for finding database sizes in MySQL/MariaDB. It could be for data truncation, Archiving, optimizations e.t.c.

In this how-to guide, I’ll share with you a query you can use in your MySQL/MariaDB database server to find the size of each Database.

SELECT
	COUNT(*) AS Total_Table_Count
	,table_schema
	,CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') AS Total_Row_Count
	,CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') AS Total_Table_Size
	,CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') AS Total_Table_Index
	,CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') Total_Size
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length+index_length)
DESC LIMIT 10;

This query will:

  • Find the number of tables in each database
  • Total number of rows in a database
  • Total table size and Index
  • Total table size in GB

Below is a sample output:

+-------------------+--------------------+-----------------+------------------+-------------------+------------+
| Total_Table_Count | table_schema       | Total_Row_Count | Total_Table_Size | Total_Table_Index | Total_Size |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
|               124 | b4gaags_db         | 1.72M           | 0.10G            | 0.06G             | 0.16G      |
|                33 | mutima             | 0.44M           | 0.10G            | 0.03G             | 0.13G      |
|                55 | gitea              | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                48 | kanboard           | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                31 | mysql              | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                76 | information_schema | NULL            | 0.00G            | 0.00G             | 0.00G      |
|                 3 | zourfs             | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                52 | performance_schema | 0.07M           | 0.00G            | 0.00G             | 0.00G      |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
8 rows in set (0.027 sec)

The ORDER BY DESC LIMIT 10 will show the database sizes from the largest to smallest. Adjust the LIMIT 10 to print more records.