Calculating disk space usage per MySQL DB

Question

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?

Answer 1

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.

Answer 2

You can use this command to get information in GB:

mysql> select table_schema "DB name (table_schema)", 
sum((data_length+index_length)/1024/1024/1024) AS "DB size in GB" from 
information_schema.tables group by table_schema;
+-------------------------------------+-----------------+
| DB name (table_schema)              | DB size in GB   |
+-------------------------------------+-----------------+
| prod                                |     29.72448921 |
| information_schema                  |      0.00781248 |
| miscDB                              |      0.77330779 |
| mysql                               |      0.66873168 |
| test                                |     47.31449127 |
+-------------------------------------+-----------------+
5 rows in set (0.01 sec)

Adapted the answer from Aaron Brown to provide size in GB. See Aaron Brown's answer for more details.

OR to include free/reclaimable space, use:

mysql> SELECT table_schema "DB name",
sum( data_length + index_length ) / 1024 / 1024 "DB size in MB",
sum( data_free )/ 1024 / 1024 "free/reclaimable space in MB"
FROM information_schema.TABLES
GROUP BY table_schema; 

+--------------------+---------------+------------------------------+
| DB name            | DB size in MB | free/reclaimable space in MB |
+--------------------+---------------+------------------------------+
| prod               |          1.26 |                         0.03 |
| information_schema |         38.77 |                         3.75 |
| miscDB             |          0.00 |                         0.00 |
| mysql              |          0.00 |                         0.00 |
| test               |          0.00 |                         0.00 |
+--------------------+---------------+------------------------------+

Space can be reclaimed using OPTIMIZE TABLE command for InnoDB, MyISAM, and ARCHIVE tables.

See also How to Get True Size of MySQL Database? for more details.

Answer 3

In order for me to see where disk space is being used up (regardless if it's in a mysql table or not), I use my trusty "du" command. Here's an example of me finding where all the space is being eaten up from.

$ sudo du -cks /* | sort -rn
954881224   total
945218092   /mysql
5299904 /usr
1781376 /opt
1166488 /var
671628  /home
343332  /run
213400  /root
93476   /lib
30784   /boot
20652   /etc
15940   /bin
13708   /sbin
12388   /tmp
24  /mnt
16  /lost+found
4   /srv
4   /snap
4   /media
4   /lib64
0   /vmlinuz
0   /sys
0   /proc
0   /initrd.img
0   /dev

You can see that the majority of the space is being used by this folder. /mysql

That folder holds data tables. In order to see which tables are taking all the space you can proceed like this using the "human" or "-h" option. I like to do disk space management this way because sometimes you cannot even log into mysql because you don't know the password or user.

$ sudo du -chs /mysql/*
2.3M    /mysql/blacklist
18M /mysql/clientservices
2.5G    /mysql/data
4.0K    /mysql/doubleverify
137G    /mysql/ias
4.0K    /mysql/IAS
2.2G    /mysql/innodb
16K /mysql/lost+found
4.0K    /mysql/ml_centroids
16G /mysql/moat
4.0K    /mysql/test
4.0K    /mysql/tmp
4.0K    /mysql/var
282G    /mysql/verticaAdFees
4.0K    /mysql/verticaViewability
247G    /mysql/Whiteops
217G    /mysql/Whiteops_TLX
902G    total

You can see that all space is being hogged by a few tables holding many GiG's of data. Hope this helps.

Answer 4

I know this is old but someone may find this relevant.

In MySQL I use:

SELECT concat(table_schema) 'Database Name',
concat(round(SUM(data_length/power(1024,3)),2),'G') DATA,
concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX',
concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE',
concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED',
concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
GROUP BY table_schema;

Since my DB is InnoDB, this is just an estimate.

I compare this output to:

du -sch /location/of_Mysql/* | sort -hr | head -n20

Hope this helps you

Answer 5

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

SELECT * 
FROM information_schema.TABLES ;

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

SELECT 
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;

Answer 6

Best ( after doing apt-get install ncdu ):

cd "/var/lib/mysql" && ncdu

to get all the total size of Mysql databases in your VPS.

Answer 7

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.

Answer 8

Assuming that your MySQL host is running on Linux.

You can execute the query below to find the path where MySQL data is stored

select @@datadir;

Once you know where the data is stored, you can check disk usage with du command.

For example on Ubuntu sudo du -h /var/lib/mysql.

You would get something like this

980K    /var/lib/mysql/db_one
1.1M    /var/lib/mysql/mysql
8.0K    /var/lib/mysql/someother_db
1.2M    /var/lib/mysql/another_db
212K    /var/lib/mysql/performance_schema
1.7G    /var/lib/mysql

Source: Check storage used by MySQL on linux