Find Database Sizes in MySQL/MariaDB Database Server

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.

https://www.computingpost.com/find-database-sizes-in-mysql-mariadb-database-server/?feed_id=576&_unique_id=6325427da6bab