Mysql error 28 no space left on the device

Question

I keep running in to the same problem with mysql with error code 28 which means there is not enough space left on the device.

Now the weird part is that there is enough space, here are some tips i found on google to find out if i have enough space. (please keep in mind that i'm newbie when it comes to linux systems)

du -sh /var/log/mysql

36K /var/log/mysql

df -h

Filesystem            Size  Used Avail Use% Mounted on
udev                  1.5G  4.0K  1.5G   1% /dev
tmpfs                 604M  224K  604M   1% /run
/dev/disk/by-uuid/7a06472d-4cc9-4383-93b0-4ecee9b21c04
                       24G   15G  8.4G  64% /
none                  5.0M     0  5.0M   0% /run/lock
none                  1.5G     0  1.5G   0% /run/shm
/dev/vda1             461M   36M  402M   9% /boot

I have been trying to solve this for so long now, but nothing helps, any kind of advice would help.

EDIT

The most weird part is that if i delete big file 200-800mb this error goes away for a very short time and then it returns again. I have actually removed around 10GB files in hopes of fixing this but it didnt help.

Answer 1

"no space left on the device" could also mean that the partition is out of inodes. Use df -i to check inode usage.

Edit: Each file, directory, and symbolic link requires one inode. So the idea is to remove some files from the / partition. It doesn't matter how large the files are. You can of course just pick some files on the partition and move them to another partition, if you can find some that are convenient to move.

It's unusual to run out of inodes, so it's possible you have some directory full of small or zero-length files somewhere that you're not aware of. Unfortunately, I don't know of an easy way to find this sort of thing. Running find / -xdev would print the name of every file and directory on the partition. You might be able to spot some suspicious directory full of empty files.

If you can add additional disk partitions to the system, you could pick some directory on the / partition and make it into a separate partition. Copy all of the files in that directory to the new partition, delete them from the / partition, then mount the new partition on top of the old (and now empty) directory.

The nuclear option would be to back up the / partition, wipe it, and rebuild it with more inodes. That's an advanced topic and I won't try to explain it here.

Answer 2

Selections on columns without indexes use up a large amount of temporary disk space. So you have the choice:

1) Check your temporary filesystem for sufficient space.

2) Check your SQL statement that it uses only columns that are in indexes.

3) Add the needed indexes.