nginx tmp file folder runing out of diskspace

Question

I get mysql diskspace error

Can't create/write to file '/tmp/#sql_777_0.MYI' (Errcode: 28)

mainly because my ngnix server is writing file into the tmp folder which doesn't get clean up..

i added this command as per instructions on the nginx manual to the crontab but doesn't seems to be doing the trick, (i don't understand what it does too)

0 */1 * * *   /usr/sbin/tmpwatch -am 1 /tmp/nginx_client

then i had to do this commands mannually

cd /tmp/nginx_client
find -name * | xargs rm

i need to know what

1. should i do to automate this clean up ?
2. is there way to increase the /tmp/ -> /var/tmp/ size without reformatting or doing any dangerous things ?
3. Can i change the location of the MYSQL -> TMP files ?

Answer 1

You are better off changing the location of the mysql tmp directory. You can set the following in the my.cnf file

[mysqld]
tmpdir=/some/path/to/tmpdir

Make sure that the mysql user has read/write to this directory also. If you want to create a larger partition you can always dd a file.. mkfs that file and mount it as a partition. That is if you want to set a cap so the mysql tmp files don't take up all the free space.

Your bigger issue might be your queries though if they are using that much space in your tmp location you have some massive joins going on that might be impacting performance.