I chimed in on a few threads about 6.4.1 performance issues, I won't rehash that here except to say our forums were dead slow off and on. The past few weeks, we had to stop and restart MySQL every 12-18 hrs to clear out the processes as well.
I got great help from Jeremy's thread on Let's Tune MySQL in the MySQL forum here. Rick really stuck it out with us and was very patient and helpful.
Anyway, to get our 6.4.1 forums flying again, here's what I discovered.
First, log-bin was in our my.cnf. This writes every transaction to a flat file on disk - great for restoring in a disaster, terrible for server performance. Once removed, we got an immediate lift in the forums and on the server.
Second, 6.4.1 IMHO is very sensitive to a properly tuned my.cnf. Our 6.3.2 forums screamed w/ a near-stock my-large.cnf. In short, using Jeremy's thread to tweak ours, I solved the locked tables problem and significantly reduced server overhead. It required some experimenting with the key parameters in the file, but in the end it was time well spent. And I learned a lot.
Finally, we have another program bouncing against MySQL, phpAdsnew. The config of this program + bad entries in our my.cnf was causing MySQL to spawn new processes constantly and eventually hang. After reading up on this program and getting admin access, I found it had been configured to use persistent connections and there was no timeout on them. I disabled persistent connections Monday night, restarted MySQL, and our performance is as good as ever, server overhead is very low, and the MySQL stats are in that sweet spot.
So, thanks again Jeremy and especially Rick! and if anyone else is having problems with this version, you really need to use Jeremy's thread and understand your my.cnf and what's happening on your server.
(And now we are on 6.4.2, got our stickies fixed, and couldn't be happier!)
joe
