MySQL Forums
Forum List  »  Performance

Re: Server just hanging (Copying to tmp table)
Posted by: Erin ONeill
Date: September 15, 2005 04:31PM

Hey sorry work sucked me down a dark hole & I started school (b&w photography -- film based darkroom, lots of work).

I HAD THIS VERY PROBLEM!!! My production server is redhat fedora core 3. I have a solaris background and had to bone up on linux to translate the solaris knowledge into linux.

ulimit has a SYSTEM setting. My sys admins set that to some high number (why not unlimited? dunno). but they did NOT set the mysql user to have a larger ulimit. (and you know you gotta set this for the apache user too!). at the time I did not have access to the box except thru phpMyAdmin (Jay -- I'm on the box now -- with sudo ability -- whoo-hoo)

I had to ask very specific questions of the admins. This is what I asked them:

- what is the user that is running mysqld? (mysql)
- as the mysql user (su - mysql) run this command (from a bash shell in linux) ulimit -aH

Turns out mysql was set to 1024 (Jay swore this was my problem too and he was RIGHT). I had to figure out how to set this for linux:

1. vi /etc/sysctl.conf
ADD this line: fs.file-max = 406720 <-- this number should vary depending on the amount of physical ram. My sys admins were fond of this number and I'd already pissed them off so I used this number. :)
2. to make this system ulimit working now:
echo 406720 > /proc/sys/fs/file-max
3. To set up users ulimit. Ok first a bit of security tips. You do not want to allow ALL users to have high ulimits. Only your server apps need this. Apache, databases -- maybe tomcat servers? Be conservative with who you give these too. Also make sure you leave around 2000 file handles for the system.
mysql soft nofile 202860
mysql hard nofile 405720
mysql soft nproc 33792
mysql hard nproc 67584

BUT Mac X doesn't have this file! And it's not set up this way. I find that Oracle has GREAT docs for making the necessary tweaks on the OS to get a database working well. Here's their doc for Mac X:

And Mac X seems closer to Solaris in how you set up user's ulimit. In the .bashrc environmental file you add these lines:
# Set shell user limits
ulimit -c unlimited
ulimit -d unlimited
ulimit -s 65536

So add those lines to the mysql user's .bashrc file. Then do a su - mysql (you'll need to do this as root unless you know the pwd for your mysql shell user). Then do a ulimit -a. You should see your changes. Once you are sure you have it right, without any sort of typos then restart your MySQL server.

You must keep your table_cache number within a range that your OS & MySQL server will play well together. Currently, I'm at 1250. I add 5-10 to that number once a week. I haven't crashed the server since the ulimits got fixed. I know the db screams at 2400 for the table cache, so that's my goal number.

And Jay -- he's running phpBB a common app with lousy DB design. That's the one I keep bugging you about ... :)

Hope this helps and isn't too late.


Options: ReplyQuote

Written By
December 07, 2006 10:30AM
Re: Server just hanging (Copying to tmp table)
September 15, 2005 04:31PM
September 23, 2005 01:56AM
October 05, 2005 04:58AM
October 05, 2005 11:49PM
October 06, 2005 01:34AM
October 06, 2005 04:57PM
January 21, 2008 10:14AM
January 21, 2008 10:53AM
May 19, 2008 09:41AM
February 05, 2008 10:27AM
March 12, 2008 08:37AM
November 14, 2008 12:31AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.