Web Development

How much space is used by the database ?

This simple question has an OS answer, so if you didn't changed the default data directory you can do:

du -hs /var/lib/mysql/DATABASE_NAME

But if you're a user with no or limited shell rights you will have to do this within the mysql client. There is a great source of information in mysql that is currently not exploited. It's the database called INFORMATION_SCHEMA.

Follow up:

An exhaustive query could be:

set @size = 1024;
select TABLE_NAME,
	  round(DATA_LENGTH/@size) as DATA_LENGTH, 
	  round(INDEX_LENGTH/@size) as INDEX_LENGTH,
	  round(DATA_LENGTH/@size + INDEX_LENGTH/@size) as TABLE_DISK_SPACE,  
	  round(INDEX_LENGTH / DATA_LENGTH, 4) as INDEX_DATA_RATIO
from information_schema.TABLES
    where TABLE_SCHEMA = 'mysql';

You will get as an answer like this:

+---------------------------+-------------+--------------+...
| TABLE_NAME                | DATA_LENGTH | INDEX_LENGTH |...
+---------------------------+-------------+--------------+...
| columns_priv              |           0 |            1 |...
| db                        |           6 |            4 |...
| func                      |           0 |            1 |...
| help_category             |          20 |            3 |...
| help_keyword              |          73 |           15 |...
| help_relation             |           6 |           13 |...
| help_topic                |         263 |           17 |...
| host                      |           0 |            1 |...
| proc                      |           0 |            1 |...
| procs_priv                |           0 |            1 |...
| tables_priv               |           0 |            1 |...
| time_zone                 |           0 |            1 |...
| time_zone_leap_second     |           0 |            1 |...
| time_zone_name            |           0 |            1 |...
| time_zone_transition      |           0 |            1 |...
| time_zone_transition_type |           0 |            1 |...
| user                      |           1 |            2 |...
+---------------------------+-------------+--------------+...

+---------------------------+...+------------------+------------------+
| TABLE_NAME                |...| TABLE_DISK_SPACE | INDEX_DATA_RATIO |
+---------------------------+...+------------------+------------------+
| columns_priv              |...|                1 |             NULL |
| db                        |...|               10 |           0.6234 |
| func                      |...|                1 |             NULL |
| help_category             |...|               23 |           0.1469 |
| help_keyword              |...|               88 |           0.2041 |
| help_relation             |...|               19 |           2.0097 |
| help_topic                |...|              280 |           0.0646 |
| host                      |...|                1 |             NULL |
| proc                      |...|                1 |             NULL |
| procs_priv                |...|                1 |             NULL |
| tables_priv               |...|                1 |             NULL |
| time_zone                 |...|                1 |             NULL |
| time_zone_leap_second     |...|                1 |             NULL |
| time_zone_name            |...|                1 |             NULL |
| time_zone_transition      |...|                1 |             NULL |
| time_zone_transition_type |...|                1 |             NULL |
| user                      |...|                3 |           2.2069 |
+---------------------------+...+------------------+------------------+

Of course you will get NULLs when DATA_LENGTH is 0... but I think this will rarely happen. You have four columns the first one representing the data length, the second one representing the length of all indexes on that table, the third one representing the total disk space used by that table and the last one is an index per data ratio. The last column I use it to check the sanity of the indexes... and by sanity I do not mean if the indexes are usable but rather a check for insane indexes - many indexes, obsolete indexes, useless indexes and so on.

If the length of all your indexes are 2 - 3 times the data size or even more... I think there is a problem. You may have too many indexes. You may have unused indexes or insane large indexes... adding too many columns to an index will not add value. When I'm looking at this query I personally check, just to be sure, all tables where the ratio is above 1.5 ... just to be sure.

Happy querying !