This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find the total occupied size of each Storage Engine of MySQL Server.
MySQL Provides different types of storage engines like: MyISAM, InnoDB, Memory.
I have seen few questions on MySQL Forum like how to migrate MyISAM tables into InnoDB engines and what would be the storage size difference.
Periodically, Database Administrators have to check the occupied size of each different MySQL Storage Engine which helps them to take decisions on internal migration or for any other reason.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT COUNT(*) AS TotalTableCount ,table_schema ,CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') AS TotalRowCount ,CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') AS TotalTableSize ,CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') AS TotalTableIndex ,CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') TotalSize FROM information_schema.TABLES GROUP BY ENGINE ORDER BY SUM(data_length+index_length) DESC LIMIT 10; |
