MySQL Forums

OOOOh right away I see you have some problems with some of your settings. The most important are KEY_BUFFER and the QUERY_CACHE_SIZE -- although there are others that help improve speed.

You have 4 GB of ram and your key_buffer = 8 M?? Your key_buffer should be approaching 512M-1GB! So I'd change that one. Then Like Jay says, you need your query_buffer_size set (mine's 128m and I'd like to push it higher)

My situation is a LOT of short connections (bullentin board/forums type thing). I found a great increase in speed with setting up my table_cache and my thread_cache. The table_cache is limited by your OS (I managed to crash the mysql server by setting table_cache too large, it's currently 1240. This made a HUGE difference in speed. When it was set to 2400 the db screamed until it burned! :)). Thread_cache has been helpful.

Then I noticed that your thread buffers seem awfully small. You've got 4 gb you might want to set the sort_buffers to 4m, read_buffers & join_buffer to 2m to start. Your myiasm_sort_buffer is really large at 2gb. I'm not sure about that setting? I've got mine set to 64m (perhaps I need to raise mine?? anyone??).

The important thing to remember is that the buffers are calculated:
global buffers + (thread buffers * connections) = RAM (be sure to leave some ram for your OS).

Global buffers: key_buffer, innodb_buffer_pool, innodb_log_buffer,innodb_additional_mem_pool, net_buffer
Thread_buffers: sort_buffer, myisam_sort_buffer, read_buffer, join_buffer, read_rnd_buffer

Am I forgetting any buffers??? anyone?

Also your wait_timeout is set REALLY high. I have mine at 20 but then I have a lot of short bursts of connections and it's recommended to set this low under that sort of situation. I'm also NOT using pconnect. All the reading I've done on pconnect (persistent connections) vs connect have made me lean towards connect. I'm still reading up on this one.

Really setting just the key_buffer, query_cache_size, table_cache, thread_cache, wait_timeout can improve the speed dramatically. Turn on slow_query_log and slog thru that to find which queries need tweaking.... That'll help quite a bit too!

erin