Search code examples
mysqldiskspace

Server disk space seems incorrect, why?

I had huge problems with the watchdog module for Drupal, it was creating an unbelievable amount of entries to the database. MySQL stopped working because of this. I rolled back to an earlier point when I had some space and was able to log in and remove the module. I ran the SQL command

SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

So when watchdog was eating all of the disc space I was getting the response like this

Query result before watchdog removed

queryresults before

Anyway I remove the module and the table goes with it. I run the same query and I don't see the table there again.

Query result after watchdog removed

enter image description here

I go to the console and enter the command

df -h

Console result after watchdog removed

console result df -h

So the console is saying that there is still a huge amount of memory used up. Why is this? should I be worried about it?

Solution

  • Without too much info to go on, Im going to say you have hit a "bug"

    http://bugs.mysql.com/bug.php?id=1341

    Taking data from this answer How to shrink/purge ibdata1 file in MySQL

    as you want to reclaim the space from ibdata1 you actually have to delete the file:

    • Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
    • Drop all databases except the above 2 databases
    • Stop mysql
    • Delete ibdata1 and ib_log files
    • Start mysql
    • Restore from dump

    Before you restore you probably want to enable the innodb_file_per_table option in your config file

    The instructions above can actually be limited to JUST the innodb ones to make the process slightly safer