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.
/etc/security/limits.conf
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:

http://www.oracle.com/technology/pub/articles/rohrer_macosx.html

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.

erin