2

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I get Error 28 (out of disk space) when I try to do pretty much anything with my database.

I have heard that due to the way that InnoDB manages tables, you have to do some extra work to reclaim space in certain situations after doing deletes/drops; however I did not think this was the case with MyISAM.

I have read that in some situations, after doing a massive delete from a table, optimizing the table can help free space, however since I have dropped the table, that is not an option.

What am I missing? What can I do to free the space?

I am using Centos 6.4 Final with MySql 5.5.25

EDIT: I learned what the problem was - Our servers are monitored by our hosting provider, and due to this, the result of df is not live, but rather reflects an infrequent snapshot of our server taken by the monitor. its insanely annoying.

migrated from stackoverflow.com Apr 14 '14 at 1:30

This question came from our site for professional and enthusiast programmers.

2

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root.

Here is what you can do to start investigating: Go to the OS and do this:

cd /var/lib/mysql
df -h .

If the root partition is 100% despite dropping 4GB,

  • your datadir is not var/lib/mysql
  • another table consumed up the space quickly
  • something else clogged the root partition

Who are the usual suspects ?

  • Binary Logs : You cannot run PURGE BINARY LOGS with a 100% full disk. You will have to locate the folder with the binary logs, delete the oldest ones, and mysqld will get moving.
  • Error Log : Just a text file. Suppose your error log is /var/log/mysqld.log. You run echo -n > /var/log/mysqld.log and the space from that file is reclaimed. I don't use rm since mysqld has an open file handle to the error log.
  • Slow Log : Locate the Slow Log and do the same thing as you did with the Error Log
  • Temp Tables : These are usually created in /tmp. If you see .MYD file in /tmp with old timestamps, you can manually delete them.

Whatever it is, find the offending files and delete/truncate them because mysqld will not crash on Error 28 (Out of Disk Space). The mysqld will pause and wait patiently for diskspace to be available (See my post "Site Offline" MySQL server failing to start and stop).

  • 1
    thanks. deleting the logs freed up enough space to make the server functional. it turns out the true space hog was an extremely bloated innodb table space, so after freeing up the space from the log I was able to properly dump the innodb tables, remove, and properly restore them using innodb_file_per_table=YES, so I could properly truncate the bloat-- On another note, the df -h initially reported 74GB used out of 79GB, but 100% usage, so I'm not entirely sure how that works, but either way, my database is functional again. – chiliNUT Apr 15 '14 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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