Re: optimization

[Erin ONeill]

Hey -- I've been working on optimizing a server for almost 2 weeks now. I've looked at your settings and put it into the formula:
GLOBAL buffers + (max_connections*(per thread buffers)) = a bit less than your RAM.

You're really lucky that you have 8GB of ram, but in doing the math with your configuration I come out to 16 GB of ram (and I'm sure I let out some buffer or two). You may want to reconsider how high you're putting your thread buffers (sort_buffer, join buffer, read_buffer, read_rnd_buffer, myiasm_sort_buffer). You only have 100 max connections which seems kinda low for a heavily used bulletin board. Are you monitoring that?

Also, we saw immediate and DRAMATIC performance speed with setting the Query Cache. Our's is 96MB but we don't have the amount of RAM you have. You could easily set that to 256M if you lowered your thread buffers. Also, we've seen dramatic improvement by cacheing threads. Ours is set to 64, again we have less RAM than you do.

You want to keep an eye on table cacheing (seems that can bring down you MySQL server -- I know this from first hand experience!). But it really really improves speed. After you change the other settings you may want to play with that one, but I recommend doing it on a staging server first or at a time when your server crashing won't be a big problem.

Good luck and let us know what worked for you!