Find Database Sizes in MySQL/MariaDB Database Server

Posted on 76 views

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, data archiving, table optimizations among many other applications.

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.

Launch MySQL / MariaDB shell console:

$ mysql -u root -p

Run the query below:

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.

coffee

Gravatar Image
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.