Finding out largest tables on MySQL Server


Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:

SQL:

  1. 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 10;
  2. +————————————-+——–+——–+——–+————+———+
  3. | concat(table_schema,‘.’,table_name) | rows | DATA | idx | total_size | idxfrac |
  4. +————————————-+——–+——–+——–+————+———+
  5. | art87.link_out87 | 37.25M | 14.83G | 14.17G | 29.00G | 0.96 |
  6. | art87.article87 | 12.67M | 15.83G | 4.79G | 20.62G | 0.30 |
  7. | art116.article116 | 10.49M | 12.52G | 3.65G | 16.18G | 0.29 |
  8. | art84.article84 | 10.10M | 10.11G | 3.59G | 13.70G | 0.35 |
  9. | art104.link_out104 | 23.66M | 6.63G | 6.55G | 13.18G | 0.99 |
  10. | art118.article118 | 7.06M | 10.49G | 2.68G | 13.17G | 0.26 |
  11. | art106.article106 | 9.86M | 10.19G | 2.76G | 12.95G | 0.27 |
  12. | art85.article85 | 6.20M | 9.82G | 2.51G | 12.33G | 0.26 |
  13. | art91.article91 | 8.66M | 9.17G | 2.66G | 11.83G | 0.29 |
  14. | art94.article94 | 5.21M | 10.10G | 1.69G | 11.79G | 0.17 |
  15. +————————————-+——–+——–+——–+————+———+
  16. 10 rows IN SET (2 min 29.19 sec)

I do some converting and rounding to see number of rows in millions and data and index size in GB so I can save on counting zeros.
The last column shows how much does the index take compared to the data which is mainly for informational purposes but for MyISAM can also help you to size your key buffer compared to operating system cache.

I also use it to see which tables may be worth to review in terms of indexes. Large index size compared to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) or may be there is long primary key with Innodb tables. Of course it also could be perfectly fine tables but it is worth to look.

Changing the query a bit to look for different sorting order or extra data – such as average row length you can learn quite a lot about your schema this way.

It is also worth to note queries on information_schema can be rather slow if you have a lot of large tables. On this instance it took 2.5 minutes to run for 450 tables.

UPDATE: To make things easier I’ve added INFORMATION_SCHEMA to the query so it works whatever database you have active. It does not work with MySQL before 5.0 still of course

Advertisements
This entry was posted in MySql and tagged , , , , , by Purab Kharat. Bookmark the permalink.

About Purab Kharat

I am WordPress and Drupal Architect. I worked on many technologies like Ruby on Rails, Flex, PHP etc... I am Purab Kharat from India, Pune. I love to play chess and listen music and read books. I work in SY Smartsourcing Global Pvt Ltd in Princeton, NJ, USA. Now I am living at Plainsboro, 164 Hampshire Drive, Princeton Meadows, NJ. I have more then Ten years experience in Web Application Development (Specially in Php 5.0, Ruby on Rails, Mysql 5.0, RIA, Wordpress, Drupal, Joomla, Ecommerce)

Leave a Reply

Fill in your details below or click an icon to log in:

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