jDempster

Calculating MySQL table sizes

Sometimes we need to know how much disk-space a table is using. Or which table is using the most disk-space. Here’s a handy query.

Here’s a handy query which uses the information_schema tables MySQL provides.

It can take MySQL sometime to build the information up required to produce the results.

SELECT
  CONCAT(table_schema, '.', table_name) AS table_name,
  CONCAT(ROUND(data_length / (1024 *1024), 2) , 'M') AS data_length,
  CONCAT(ROUND(index_length / (1024 *1024), 2) , 'M') AS index_length,
  CONCAT(ROUND((data_length + index_length ) / (1024 *1024), 2), 'M') AS total_size,
  CURRENT_DATE AS last_update

FROM information_schema.tables
WHERE table_schema = 'database_name'
ORDER BY ROUND((data_length + index_length) / (1024 *1024), 2) DESC;

Note; Deleting tables and getting a smaller size from the query doesn’t necessarily mean that the disk space will be freed. InnoDB keeps the disk space used and marks as available in it’s table-space.

Warming/Loading tables into MySQL Cache

After restarting MySQL you’re left with a cold machine. No cache. Putting this directly back into use can leave it struggling to load data from disk again.

One way of helping out is to give it some time to load table data back into that vast amount of RAM it has.

One way I’ve found that’s helped me is to create a temporary table, alter it’s engine into a black hole then select everything from an existing table into it.

CREATE TEMPORARY TABLE blackholeMyTable ENGINE=BLACKHOLE SELECT * FROM MyTable;

This cause MySQL to go though the works of loading the data from disk to insert into a blackhole. Thus being put into RAM.