MySQL Forums
Forum List  »  Performance

Re: Server just hanging (More Information)
Posted by: Erin ONeill
Date: August 26, 2005 11:02AM

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:

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.


Options: ReplyQuote

Written By
Re: Server just hanging (More Information)
August 26, 2005 11:02AM
December 07, 2006 10:30AM
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.