Re: Server just hanging (More Information)

[Erin ONeill]

Hey Thomas -
Not sure how much time I'll have to go thru this, but wanted to let you know this was old style syntax for setting your variables:
set-variable = max_connections=300

I use this syntax:
max_connections=300

Also, I play with my variable settings on the fly. I have phpMyAdmin connection to my database (no login to the actually machine :( ). In phpMyAdmin I open a sql window and then type this:

SET GLOBAL max_connections=300;

When you set the key_buffer_size -- keep an eye on your server.

Here's some quick things I noticed until I can give this more indepth attention:

threads aren't being cached.

SET GLOBAL threads_cache = 64; <-- nice low number to start with!

I think your table cache (which I know can cause problems is a bit high if your ulimits and procs aren't unlimited) so bring this down. In the linux world 1240 is working for me, but looking at your stats I think 1000 might be better (you have 941 opened tables):

SET GLOBAL table_cache = 1000;

Some of your thread buffers are too high, others too low:
SET GLOBAL sort_buffer_size = 4194300;
SET GLOBAL read_buffer_size = 2097152;
SET GLOBAL read_rnd_buffer_size = 2097152;
SET GLOBAL join_buffer_size = 2097152;

And your key_buffer_size is too low for how much memory you have. Start with 256 MB and then run mysqlreport and see how much of your key_buffer you're using. If you're consistantly using 90%+ then raise it. Just a bit at a time:
SET GLOBAL key_buffer_size = 268440000;

Also mysqlreport will tell you how much of your query cache you're using. 100 mb is kinda low. You don't seem to be pruning (like I am!) but you also have a number of queries that aren't being cached. Do you use a lot of blob's in your tables?

I think 500MB for temp tables might be a bit high. I've got mine at 64MB. Maybe set it down and watch how many temp tables to disk you're writing. (mysqlreports will give you this info).

I'd try that.

It doesn't look like you're even close to using 300 max_connections. I'd try setting that to 100 for now.

I think a 500MB temp table size, with 300 max_connections throws you into a dangerous place. If you lower your thread buffers (and raise the too low ones a tiny bit) and keep your temp, query, key_buffers like you want -- you'll need about 10GB of RAM with 300 max_connections.

If you drop the 300 --> 100, lower your temp buffer a bit, raise your query cache a bit and put your key_buffer higher I think you'll be safer. Probably a MySQL engineer could better tweak this for Mac X, but these changes should get you started.

erin