/tmp 100% full - but uses only 63K of 95MB

[Stream]

A strange thing happened to my /tmp partition today:

root@tom [/tmp]# du -sch /tmp
63K     /tmp
63K     total

root@tom [/tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/hda6            1012M  624M  337M  65% /
/dev/hda1              99M   47M   48M  50% /boot
/dev/hda5              53G  4.7G   46G  10% /home
none                  501M     0  501M   0% /dev/shm
/dev/hda7              95M   95M     0 100% /tmp
/dev/hda2             9.9G  5.3G  4.2G  56% /usr
/dev/hda3             6.9G  1.9G  4.7G  30% /var
/dev/tmpMnt            95M   95M     0 100% /tmp
/tmp                   95M   95M     0 100% /var/tmp

For some reason it thinks the /tmp partition is full (hence mysql etc. not working) - but there are only 63K of files in there. Anyone got a solution for this?

I've allready tried remounting it, upgrading upcp and restarted the server...

[jayh38]

Check inodes?
df -i

[dafut]

It may be that mysql is attempting to use /tmp. I had a similar issue before where a mysql process was apparently using /tmp although if I did an "ls -la /tmp" it didn't appear to be.

Solution was to have mysql write to a different temporary directory, which I created by adding:
tmpdir=/home/mysql

in my.cnf; creating the mysql directory in /home/ and giving it mysql.mysql owner.group ownership; then restarting mysql.

Don't know if that's your issue, but the similarity appears to be there.

[Stream]

Your explenation sounds right dafut.

One of my customers had a problem when removing some vBulletin addons causing the post-table to be unaccessible. After doing a repair on that table everything's been fine.

I should most probably change mysql's tmpdir though. Thanks for your input!

[dafut]

That would make sense; I was dealing with a "large" table optimization, which kept failing. Since I've moved mysql temp to a less restricted (size-wise) directory, I've not had any problems.

Best to you, Stream.

[chirpy]

Just for the future - If a partition doesn't contain enough data to account for the df result, it's usually due to deleted files that are still open. You can check for those using lsof:

lsof /tmp | grep deleted

Which would probably have shown you the guilty MySQL temporary files. 95MB is a tad tiny for most servers which you should normally have at 1GB.

[Stream]

95MB is a tad tiny for most servers which you should normally have at 1GB.

Thanks chirpy!

Is there an easy way to increase the size of /tmp?

[chirpy]

You could use the /scripts/securetmp script to create a larger /tmp (virtual) partition which is actually a file in /usr. However, before doing that you would need to dismount the existing /tmp and stop it from remounting on boot. It might be a bit tricky, but you could get away with:

umount -l /tmp

Then edit /etc/fstab and look for the line with /tmp in it and comment it out (put a # at the beginning of the line).

Then run:

/scripts/securetmp

You'll then have a 512MB /tmp partition.

[Vatoloco]

Just for the future - If a partition doesn't contain enough data to account for the df result, it's usually due to deleted files that are still open. You can check for those using lsof:

lsof /tmp | grep deleted

Which would probably have shown you the guilty MySQL temporary files. 95MB is a tad tiny for most servers which you should normally have at 1GB.

What is the correct way to go about closing those deleted files?

# lsof /tmp | grep deleted
mysqld  2509 mysql   88u   REG    8,6 950632448   21 /tmp/STwzJRVC (deleted)
mysqld  2509 mysql  141u   REG    8,6    655360   22 /tmp/STnofBHG (deleted)

My /tmp partition is only 1.1GB and I have a 3.5GB table in mysql I tried to optimize and it has been a running process for over 4 hours now so I don't think it is ever going to finish. I tried this before and ended up just having to restart mysql but then I lost the table. It was permanently "in use" so I just had to drop it and restore a backup.

It may be that mysql is attempting to use /tmp. I had a similar issue before where a mysql process was apparently using /tmp although if I did an "ls -la /tmp" it didn't appear to be.

Solution was to have mysql write to a different temporary directory, which I created by adding:
tmpdir=/home/mysql

in my.cnf; creating the mysql directory in /home/ and giving it mysql.mysql owner.group ownership; then restarting mysql.

Thanks for the information. I think this is what I'm going to have to do. I have a backup partition with plenty of free space so I'll try using that for the mysql tmpdir. Is there any downsides to doing it this way and not using the default /tmp location?