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:
-
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;
-
+-------------------------------------+--------+--------+--------+------------+---------+
-
| 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)
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 
del.icio.us
digg17 Comments
I’m sorry, but where does ‘TABLES’ come from? Do I miss something here? Is it something I’m supposed to know, but is not mentioned in the article?
I run this query against the ‘mysql’ database on 5.0.51, but mysql.TABLES cannot be found. Where should I look for the missing link?
- Unomi -
Comment :: February 4, 2008 @ 8:53 am
Unomi,
This is a query against information_schema.TABLES. The information_schema database was added at v5.0
Matt,
Comment :: February 4, 2008 @ 9:19 am
Unomi, you need to use information_schema:
mysql> use information_schema;
Comment :: February 4, 2008 @ 9:21 am
Great stuff, Peter! I encourage you to add this to the Forge snippets repository!
Cheers,
Jay
Comment :: February 4, 2008 @ 11:04 am
Same error here…
Comment :: February 4, 2008 @ 12:19 pm
Awesome Peter, thanks!
Comment :: February 4, 2008 @ 10:44 pm
Frank, thanks for the tip
It’s working now.
Comment :: February 5, 2008 @ 5:46 am
Thanks all for the tip. It’s new for me, but I wanted to have this query running… Everyday a new day to learn something!
- Unomi -
Comment :: February 6, 2008 @ 12:46 am
Example works perfectly.
Great website and great ideas, keep working dude.
Comment :: February 6, 2008 @ 3:36 pm
Peter,
Why exactly is it taking 2 min 29.19 sec to extract that data?! I thought it’s readily available in the dictionary. And I don’t believe it’s taking a lot of processing power to transform, concatenate and order the results.
Also, this is a thing MySQL AB should work on to fix it (among other several thousands of not-fixed-yet bugs).
Comment :: February 12, 2008 @ 12:07 pm
There is no readily available data dictionary in MySQL. The information schema actually have to scan through all database and gets stats from each of the tables and put them into temporary table to have them available to you. Sometimes it may restrict the scan ie if you look for given table but in this case it has to scan through all tables.
Comment :: February 12, 2008 @ 12:20 pm
Thanks Peter, this was a great help.
Comment :: February 13, 2008 @ 9:10 am
[...] purpose so I can easily find it later, plus it is quite handy in a way it presents information: PLAIN TEXT [...]
Pingback :: February 15, 2008 @ 12:23 am
[...] "Finding out largest tables on MySQL server" [...]
Pingback :: February 28, 2008 @ 11:34 am
I have the same question when it takes more than half minute during check my all database, as gigiduru mentioned.
Thanks peter.
btw, a tips:
If you want to show tables only in a certain database, add a WHERE clause in the SQL
WHERE table_schema = ‘db_name’
Comment :: March 4, 2008 @ 7:35 pm
[...] posted a simple INFORMATION_SCHEMA query to find largest tables last month and it got a good response. [...]
Pingback :: March 17, 2008 @ 7:28 pm
[...] Finding out largest tables on MySQL Server [...]
Pingback :: August 8, 2008 @ 3:05 am