Wednesday, July 25, 2012

get table sizes of MySQL database

mysql> SELECT count(*) tables,
    ->   concat(round(sum(table_rows)/1000000,2),'M') rows,
    ->   concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
    ->   concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
    ->   concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
    ->   round(sum(index_length)/sum(data_length),2) idxfrac
    -> FROM information_schema.TABLES;
+--------+---------+--------+--------+------------+---------+
| tables | rows    | data   | idx    | total_size | idxfrac |
+--------+---------+--------+--------+------------+---------+
|    389 | 325.92M | 74.15G | 26.63G | 100.78G    |    0.36 |
+--------+---------+--------+--------+------------+---------+
1 row in set, 14 warnings (15.45 sec)

To get the details for one table, using something like:

mysql> SELECT count(*) tables,
    ->        concat(round(sum(table_rows)/1000000,2),'M') rows,
    ->        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
    ->        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
    ->        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
    ->        round(sum(index_length)/sum(data_length),2) idxfrac
    ->        FROM information_schema.TABLES
    ->        WHERE  table_name like "users";
+--------+-------+-------+-------+------------+---------+
| tables | rows  | data  | idx   | total_size | idxfrac |
+--------+-------+-------+-------+------------+---------+
|      1 | 3.47M | 0.58G | 0.21G | 0.78G      |    0.36 |
+--------+-------+-------+-------+------------+---------+
1 row in set (0.27 sec)