Finding metrics that matter in MySQL.
Having now transitioned into a role that requires MySQL knowledge instead of Postgres knowledge, it is time to learn some new queries.
Total database size
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 ;
Free Space here is an interesting notion. This "free space" is the space available inside the engine's (InnoDB or MyISAM) table spaces. This is not a reflection of total available disk space.
Top 20 tables by size
SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 20;
If your database is small (relatively few tables), you can take out the limit clause and see all tables.
