Login or Register to Ask a Question and Join Our Community


Emergency UNIX and Linux Support

MySql server hangs due to /tmp full

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Homework and Emergencies Emergency UNIX and Linux Support MySql server hangs due to /tmp full
# 1  
Old 12-04-2013
MySql server hangs due to /tmp full
Dear community,
my sql and apache server (with CMW installed) hangs due to /tmp full:

Code:
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:
Code:
-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  
Old 12-04-2013
Code:
$ grep /tmp /etc/mysql/my.cnf

tmpdir                                          = /tmp/

$

change it to whatever you require and restart mysql.
# 3  
Old 12-04-2013
THanks for reply.
I change the mysql tmp directory to /sqltemp
Code:
drwxrwxrwx   2 root root    4096 Dec  4 22:16 sqltemp

But now I have:
Code:
#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.... Smilie

I put in my.cnf
/sqltmp instead of /sqltmp Smilie

Thanks for simple tips, now the server is up again, I hope it will be stable.
This User Gave Thanks to Lord Spectre For This Post:
Corona688
# 4  
Old 12-04-2013
I might suggest this:

Code:
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  
Old 12-04-2013
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:

Code:
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 Smilie
# 6  
Old 12-04-2013
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?
This User Gave Thanks to Corona688 For This Post:
babinlonston
# 7  
Old 12-04-2013
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Solaris

Logging events of /tmp full

Hi everybody, few days ago we had a big issue with one of our solaris10 server. Suddenly while my colleague was working on it for some troubleshooting he realized that the performance started to degrade. At the end it reached the point that was not even possible to login usng the local console... (7 Replies)
Discussion started by: bdegiovanni
7 Replies

2. AIX

PowerHA(HACMP) full vg loss - cluster hangs on release_vg_fs event

Hello, AIX 6.1 TL7 SP6 POwerHA 6.1 SP10 I was experimenting with new hacmp build. It's 3-node cluster build on AIX 6.1 lpars. It contains Ethernet and diskhb networks. Shared vg disk is SAN disk. Two nodes see disk using vscsi, third node sees disk using npiv. Application is db2 server. ... (4 Replies)
Discussion started by: vilius
4 Replies

3. Solaris

Server Hangs

I have a Sun-Fire-V210, the Server hangs for sometime and evn console wont be abled to access and after sometime the server comes back active. the messages file shows this , whats causing this ? # tail -500 messages Jan 25 07:02:16 xxxxxxxxxxxxx Corrupt label; wrong magic number Jan 25... (2 Replies)
Discussion started by: Sojourner
2 Replies

4. Solaris

Solaris full /tmp - du and df different swap NOT filled

Hello all, The issue is # df -h /tmp Filesystem size used avail capacity Mounted on swap 4.0G 4.0G 8.7M 100% /tmp # du -sh /tmp/ 87M /tmp By now you probably will say that this is open file destriptor issue. Well no, nothing... (2 Replies)
Discussion started by: click
2 Replies

5. AIX

\tmp Directory is full up to 99%.

Dear All, We are on AIX OS, /tmp directory is filled up to 99% percent, Please suggest, How to get free space for "/tmp"? which files can be deleted from /tmp? and How to delete it? is there any commands..... Thanks in advance, Its very urgent, Helpful answers will be appreciated, Please... (7 Replies)
Discussion started by: kak
7 Replies

6. AIX

/tmp file system full

Hi, I would like to know if /tmp file system is full, wheather it will affect the peformance of application installed on AIX. if Memory and CPU are not heavily utilized. Regards, Manoj. (1 Reply)
Discussion started by: manoj.solaris
1 Replies

7. AIX

/tmp full

The /tmp is 100% full, I found there are the following big files/directory: 1301500 syslog.out.58 166692 vac 158552 install.dir.2928686 158552 install.dir.2236636 110980 install.dir.2887698 /tmp/vac have some files like : .toc ... (3 Replies)
Discussion started by: rainbow_bean
3 Replies

8. Solaris

/tmp: File system full, swap space limit exceeded

Can you help. My server sunning solaris 9 on x86 platform pretty much hung for a few hours... I could not use telnet or ssh to the box - it kept refusing connection. A few hours later - I was able to log in again. The server has not rebooted but here are the first errors in the messages log... (5 Replies)
Discussion started by: frustrated1
5 Replies

9. AIX

/tmp full

good morning The /tmp filesystem is full at 99 % I have do a "rm" but the size is the same. so i think that a process is always alive, but how can i do to know it ? (because I have deleted some file in /tmp) thank you (9 Replies)
Discussion started by: pascalbout
9 Replies

10. UNIX for Dummies Questions & Answers

/tmp filesystem full

I am running AIX 3 4. When I do a df I get: Filesystem 512-blocks Free %Used Iused %Iused Mounted on /dev/hd4 32768 10232 69% 1309 16% / /dev/hd2 917504 86360 91% 19744 18% /usr /dev/hd9var 131072 67712 49% 617 ... (11 Replies)
Discussion started by: szodiac
11 Replies
Login or Register to Ask a Question