Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.

What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem? Is there a method for speeding up information_schema?

share|improve this question
up vote 15 down vote accepted

There are 3 scenarios.

  1. If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.
  2. If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh. It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side. This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.
  3. If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.

In any of the cases above, you can run the following query to get the information that you are looking for.

mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
| table_schema       | MB              |
| prod               | 298025.72448921 |
| information_schema |      0.00781248 |
| maatkit            |     70.77330779 |
| mysql              |      0.66873168 |
| test               |   4752.31449127 |
5 rows in set (0.01 sec)

If you have a very large number of tables, it can be slow, as you have already discovered.

share|improve this answer
I saw somewhere else that option 3 doesn't take into account VARCHAR sizes. – Joe Apr 21 '15 at 10:48

I would look for the size of the file on your data dictionnary. Its instantaneous and accurate.

Warning: According to the storage engine, indexes are stored within the main file or in another file don't forget to sum them up if needed.

share|improve this answer
Yeah, but where is that? – Magne Dec 10 '14 at 13:24

For getting info on the name of the table and number of records it has, the below query can be used,

FROM information_schema.TABLES ;

For getting info on databases on the servers with their respective size, the below query can be used,

TABLE_SCHEMA  AS `Database`,
SUM((data_length + index_length) / (1024 * 1024)) AS `Database_Size`
FROM information_schema.TABLES 
GROUP BY table_schema 
ORDER BY `Database_Size` DESC;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.