MySql memory consumed and free

1. Space consumed by databases in Mysql

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;
Output for above query will be like

+--------------------+----------------------+
| Data Base Name | Data Base Size in MB |
+--------------------+----------------------+
| information_schema | 0.00390625 |
| db1_cms | 1413.66538239 |
| db2_cms | 231.38169670 |
| db3_orig | 91.97235012 |
| db4 | 10.72843552 |
| mysql | 0.48348236 |
| db5 | 19.01077652 |
+--------------------+----------------------+
7 rows in set (6.36 sec)


2. Memory consumed by databases and available memory 



SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;


Output for above query will be like
+--------------------+----------------------+------------------+
| Data Base Name | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| information_schema | 0.00390625 | 0.00000000 |
| db1_cms | 1413.66538239 | 0.00280762 |
| db2_cms | 231.38169670 | 0.00024033 |
| db3_cms_orig | 91.97235012 | 0.00000000 |
| db4_engine | 10.72843552 | 0.00335693 |
| mysql | 0.48348236 | 0.00000000 |
| db5_cms | 19.01077652 | 0.03170013 |
+--------------------+----------------------+------------------+
7 rows in set (0.14 sec)


3. Memory consumed by table

SELECT table_schema,
table_name,
ROUND(data_length/1024/1024,2) total_size_mb
FROM information_schema.tables
WHERE table_name = 'table_name' AND table_schema = 'database_name';