MySql server hangs due to /tmp full

Linux and Unix-Like Emergency UNIX and Linux Support
1

Dear community,
my sql and apache server (with CMW installed) hangs due to /tmp full:

root@cms:~# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             224G   27G  186G  13% /
tmpfs                 3.9G     0  3.9G   0% /lib/init/rw
udev                  3.9G  128K  3.9G   1% /dev
tmpfs                 3.9G     0  3.9G   0% /dev/shm
/dev/sda2              89M   15M   69M  18% /boot
/dev/sda6             1.9G  1.8G    1M  100% /tmp

This happens because I have a very large database and CMS create a TMP sql files like:

-rw-rw---- 1 mysql    mysql    96030188 Dec  4 22:04 #sql_7ebe_1.MYD
-rw-rw---- 1 mysql    mysql        1024 Dec  4 22:04 #sql_7ebe_1.MYI

This is absolutely normal and the tmp tables will speedup the sql queries.
The problem is when the /tmp becomes full, the mysql server hangs because it can't write file anymore. Now, is there a way to avoid /tmp to becomes full?
Or at least make mysql server to write tmp file to other place?

Please help, currently my server is offline.

Thanks
Lucas

2 
$ grep /tmp /etc/mysql/my.cnf

tmpdir                                          = /tmp/

$

change it to whatever you require and restart mysql.

3

THanks for reply.
I change the mysql tmp directory to /sqltemp

drwxrwxrwx   2 root root    4096 Dec  4 22:16 sqltemp

But now I have:

#service mysql start
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

---------- Post updated at 04:26 PM ---------- Previous update was at 04:22 PM ----------

Wops, my mistake.... :rolleyes:

I put in my.cnf
/sqltmp instead of /sqltmp :mad:

Thanks for simple tips, now the server is up again, I hope it will be stable.

1 Like
4

I might suggest this:

chown mysql:mysql /sqltemp
chmod 770 /sqltemp

Beware, though, the consequences of a full / are much worse than the consequences of a full /tmp !

5

Thanks for suggestion, but as you can see I don't have enought space on other mounts. Is there a way to use a portion of / ??
BTW, Now I have this situation:

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             224G   31G  182G  15% /
tmpfs                 3.9G     0  3.9G   0% /lib/init/rw
udev                  3.9G  128K  3.9G   1% /dev
tmpfs                 3.9G     0  3.9G   0% /dev/shm
/dev/sda2              89M   15M   69M  18% /boot
/dev/sda6             1.9G  115M  1.7G   7% /tmp

But again MySQL hangs :frowning:

6

You cannot move around part of a partition.

You already are using /. /sqltemp becomes part of the / partition unless you bind it somewhere else. That should be 180 gigabytes of room. Just how much space do you require?

1 Like
7

Honestly I don't know how much space MySQL need for temp tables. It may depends on how many users are connected to the blog.

BTW, now I put offline the site, so users can't connect, and now it works. So, the tmp issue is resolved, but seems I have other issues because if I open the blog and the users connect to it, MySQL hangs again.

8

If / is not full, you have space...

Hangs, or is busy? Log into the mysql commandline client and 'show processlist'. There may be some big queries holding things up.