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.
Recommended Books:
