Recently I had a server that was running low on free disk space, after a bit of digging around I found out that the Mysql database on that particular machine was taking up the bulk of the usable disk space.
Given the fact that this was a shared Mysql instance, I needed to determine which databases were consuming the most amount of space. In order to calculate the total amount of space being used we need to take both the size of the data and all the indexes into account.
I used the following SELECT query, which will return the size of all databases(data + indexes) in MB.
SELECT table_schema "Database Name", sum( data_length + index_length) / 1024 / 1024 "Database Size(MB)" FROM information_schema.TABLES GROUP BY table_schema ;
Running the query above will result in output similar to this:
+--------------------+-------------------+ | Database Name | Database Size(MB) | +--------------------+-------------------+ | movies | 3772.06922913 | | tmp | 101.08132978 | | bikes | 57.04234117 | | information_schema | 0.00781250 | | mysql | 0.60790825 | +--------------------+-------------------+
In this case we can clearly see that the ‘movies’ database is consuming the most space. At this point we may want to dig a little deeper and look at the size of each table within the ‘movies’ database, to see where in particular the space is being used.
In order to get some more detail we can use the following SELECT query:
SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size(MB)" FROM information_schema.TABLES WHERE table_schema = "movies";
Running the query above will result in output similar to this:
+-----------------------------+------------+-------------+--------------+----------+ | table_name | table_rows | data_length | index_length | Size(MB) | +-----------------------------+------------+-------------+--------------+----------+ | Id | 1 | 16384 | 0 | 0.02 | | Teaser | 1 | 16384 | 0 | 0.02 | | TeaserLog | 21767 | 3177586576 | 392192 | 3030.76 | | TeaserChild | 912602 | 48873472 | 33112064 | 78.19 | | Director1 | 460722 | 57229312 | 13156352 | 67.13 | | Director2 | 2044044 | 87801856 | 0 | 83.73 | | City | 286134 | 17367040 | 17858560 | 33.59 | | City_alt_spelling | 1086 | 65536 | 65536 | 0.13 | | City_backup | 148811 | 13123584 | 0 | 12.52 | | City_misspelling_log | 166589 | 9977856 | 0 | 9.52 | | City_save | 148618 | 13123584 | 0 | 12.52 | +-----------------------------+------------+-------------+--------------+----------+ 11 rows in set (0.14 sec)
Based on the output from this SQL query we are able to see that the ‘TeaserLog’ table is using up the majority of space within the ‘movies’ database.