Nasser Heidari

2014-02-26

MySQL: Which table is eating my Disk Space?!!!

Filed under: Linux — Nasser Heidari @ 12:45

If you are using mysql with innodb engine without files per table, and want to know which table is eating your disk space… here is the the way I have checked today:

mysql> SELECT CONCAT(table_schema, '.', table_name), 
         CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, 
         CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, 
         CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, 
         CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, 
         ROUND(index_length / data_length, 2) idxfrac 
FROM information_schema.TABLES 
         ORDER BY data_length + index_length 
         DESC 
         LIMIT 30;

Also if you want to find Size of each Database the:

mysql> select table_schema,
 concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') SIZE 
FROM TABLES 
 GROUP BY table_schema 
 ORDER BY sum(data_length+index_length) 
 desc;

:)

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: