MySQL Metrics that matter.

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.

More updates as I find fun new queries.