mmm, sorry Brett, upping the max connections doesn't fix it.
Muhammad, as you noted you have a slow query that's slowing things down and making things back up. It cascades. One gets slow and sucks memory/disc and slows everything behind it.
The way to fix it is to get these waiting clients in and out faster, not let more of them pile up which will only crash the server.
A few things to look at -
Do you need to optimize ALL of the tables every night? Are you doing very many message deletes that your posts table would become fragmented daily?
Changing forums to only show last 6 months was a big step in the right direction. That view/query is the biggy, the one that is stomping on you. Cut it down to even less time and you'll see even more difference. (I won't mention pruning here, ok?). I would bet that if you left it at all posts you're using the disc for sorting instead of RAM given that you have 512 megs of RAM and 265k records.
Make that 2 weeks and you'll see a fair difference. Remember, most people don't look at posts older than that and if they want to they can change their settings when they need to, but why have the default more than that?
Of course adding more memory and tinkering with my.cnf would always help because then you can sort in memory instead of on disc, but that's usually less expensive to say than it is to do.
You want to cut down the amount of data the servr is reading and sorting then returning if you can and you've identified the largest ways to do so. <img border="0" title="" alt="[Smile]" src="images/icons/smile.gif" />
<img src="http://navaho.infopop.cc/golf.gif" alt=" - " />
