I posted a simple INFORMATION_SCHEMA query to find largest MySQL tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:
Find total number of tables, rows, total data in index size for given MySQL Instance
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 | +--------+----------+---------+--------+------------+---------+ | 1538 | 1623.91M | 314.00G | 36.86G | 350.85G | 0.12 | +--------+----------+---------+--------+------------+---------+ 1 row in set (52.56 sec) |
Find the same data using some filter
I often use similar queries to find space used by particular table “type” in sharded environment when multiple tables with same structure and similar name exists:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 "%performance_log%"; +--------+---------+---------+-------+------------+---------+ | tables | rows | data | idx | total_size | idxfrac | +--------+---------+---------+-------+------------+---------+ | 120 | 370.29M | 163.97G | 0.00G | 163.97G | 0.00 | +--------+---------+---------+-------+------------+---------+ 1 row in set (0.03 sec) |
Find biggest databases
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SELECT count(*) tables, table_schema,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 GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; +--------+--------------------+-------+-------+-------+------------+---------+ | tables | table_schema | rows | data | idx | total_size | idxfrac | +--------+--------------------+-------+-------+-------+------------+---------+ | 48 | cacti | 0.01M | 0.00G | 0.00G | 0.00G | 0.72 | | 17 | mysql | 0.00M | 0.00G | 0.00G | 0.00G | 0.18 | | 4 | pdns | 0.00M | 0.00G | 0.00G | 0.00G | 1.00 | | 2 | test | 0.00M | 0.00G | 0.00G | 0.00G | 0.12 | | 16 | information_schema | NULL | 0.00G | 0.00G | 0.00G | NULL | +--------+--------------------+-------+-------+-------+------------+---------+ 5 rows in set (0.32 sec) |
Data Distribution by Storage Engines
You can change this query a bit and get most popular storage engines by number of tables or number of rows instead of data stored.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SELECT engine, 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 GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10; +------------+--------+---------+---------+--------+------------+---------+ | engine | tables | rows | data | idx | total_size | idxfrac | +------------+--------+---------+---------+--------+------------+---------+ | MyISAM | 1243 | 941.06M | 244.09G | 4.37G | 248.47G | 0.02 | | InnoDB | 280 | 682.82M | 63.91G | 32.49G | 96.40G | 0.51 | | MRG_MyISAM | 1 | 13.66M | 6.01G | 0.00G | 6.01G | 0.00 | | MEMORY | 14 | 0.00M | 0.00G | 0.00G | 0.00G | NULL | +------------+--------+---------+---------+--------+------------+---------+ 4 rows in set (14.02 sec) |
Trivial but handy.
Some very handy queries here, Peter. Thank you. This is going to my bookmarks.
These have defiantly gone into my snippets list, thanks.
Also, BTW…
Your syntax highlighter has a spelling error. When you switch to ‘PLAIN TEXT’, the option to switch back to the highlighted version is tagged ‘HILITED HTML’ – ought to be ‘HIGHLIGHTED HTML’.
ryan, that’s just an American slang version of the word, not really a misspelling.
Hum, learned two things today then 🙂
Anybody can let me know how can i get the total number of tables in database without using a SELECT query but by using a stored procedure?
Well what is the problem with select query ?
You can wrap it around by stored procedure if you need to.
Could you remove all the “->” in order to allow people copy-paste right from here?
Why does SUM(table_rows) fluctuate so wildly? I run this query:
SELECT CONCAT(SUM(table_rows)/1000000,’M’) AS no_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA = ”;
and no_rows was first 2.73M, then 2.83M, then 2.81M, then 2.78M, then 2.81M…?
Why does this vary?
It’s a restriction of InnoDB. See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html. It gives an approximate count, which fluctuates a lot.
This is really cool. Thanks.
Another vote for putting the queries on one line for copy and pasting.
excellent post, but one question. How to find total no. of indexes/index keys (not index size) and unique keys, ordered keys.I know you have the solution, please post it. Thanks in advance.
Easier to copy paste:
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;
Nice article! Really good to analyse data usage!
how to find only size of the table…?
Hi,
I wanna know how much size an index will occupy for a newly created data.There is no data yet,jus wanna estimate how many bytes the index will occupy depending on the data size.
For eg,
create table t
(name varchar(50),
place_id int(10),
lastdt_modifed timestmap) egine=innodb utf8;
for calculating approximate data size i use the formula :
(max row size * collation length(bytes)) * number of rows)
(i.e)–> ((61*4)*100)
{
max row size : name = (50+3) , place_id = ( 4) , lastdt_modified = (4) : Total = 61
For 100 rows = 24400 – > 24 KB
}
the size of one row calculated according to the above data is 61 Kb and for 100 rows it is 24400 Kb.
How do i calculate the index size for this table (per row as well as entire table).
Thanks.
Thanks for this handy mysql snippet.
Hi…Does MySQL support something similar to what we have in ttSize/TimesTen, which helps in estimating avg. record size with 10%, 20%,…..90%, 100% occupancy?
ttSize reference: http://gerardnico.com/wiki/timesten/ttsize#documentationreference