MySQL database too Large

I recently had a database that was growing at an alarming rate. I needed to determine which table was consuming the space to see if I could do some pruning.

During my research I was able tl locate the following command which worked wonderfully for my purposes.

Note: for this command to work you need a MySQL instance of at least version 5.0.

  • Logon to your MySQL instance
  • Enter "use information_schema" and press enter
  • Enter the following command and press enter
     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  10;

Once run, you should get output similar to the below. The sort is on the data length (DATA) column in a descending format, so the largest tables should be at the top.

+-------------------------------------+--------+--------+--------+------------+---------+
 | concat(table_schema,'.',table_name) | rows   | data   | idx    | total_size | idxfrac |
 +-------------------------------------+--------+--------+--------+------------+---------+
 | art87.link_out87                    | 37.25M | 14.83G | 14.17G | 29.00G     |    0.96 |
 | art87.article87                     | 12.67M | 15.83G | 4.79G  | 20.62G     |    0.30 |
 | art116.article116                   | 10.49M | 12.52G | 3.65G  | 16.18G     |    0.29 |
 | art84.article84                     | 10.10M | 10.11G | 3.59G  | 13.70G     |    0.35 |
 | art104.link_out104                  | 23.66M | 6.63G  | 6.55G  | 13.18G     |    0.99 |
 | art118.article118                   | 7.06M  | 10.49G | 2.68G  | 13.17G     |    0.26 |
 | art106.article106                   | 9.86M  | 10.19G | 2.76G  | 12.95G     |    0.27 |
 | art85.article85                     | 6.20M  | 9.82G  | 2.51G  | 12.33G     |    0.26 |
 | art91.article91                     | 8.66M  | 9.17G  | 2.66G  | 11.83G     |    0.29 |
 | art94.article94                     | 5.21M  | 10.10G | 1.69G  | 11.79G     |    0.17 |
 +-------------------------------------+--------+--------+--------+------------+---------+
 10 rows in set (2 min 29.19 sec)

This information was originally found here.

Leave a Reply