My server is having some Mysql connection issues. I know that the number of queries has increased exponentially and I am also aware that the server is doing a lot more work. However, I am wondering if I can tweak some Mysql parameters to make things work better. Here are some details:
Mysql frequently runs out of connections. I close every connection after I am done with it, and I try to reduce or break up all my slow queries. The server averages about 10 queries a second and the number of threads hover around mid 90s all the time. I have reduced the max_timeout and the connect errors to try and reduce the number of threads but that does not seem to make much of a difference. I have tried to increase the number of max connections and that just seems to grind the server to a halt. I have tweaked the SQL as far as I can and have looked at optimising the use of SQL. Is there something else that I should be looking at? If this continues I might have to rewrite some of the apps that use the mysql database. Also, as an aside, are the max connections per database or per database server?
PS: The load is NOT being generated by WordPress. There is true load on the server from some other applications that I run in the background.
check your thread cache setting. see jeremy’s post: http://jeremy.zawodny.com/blog.....00173.html
Turned up the thread cache size. Time will tell. Thanks Ray, I will post an update soon.
High Performance MySQL, also by Zawodny, is a fantastic book that helped me with some issues we ran into with scaling Ping-O-Matic.
The difference with the cache is small. I will have to keep looking. I might just buy the book since I know it will come in handy sometime.
You’ll probably get the most benefit from optimizing the indicies and queries, at least that’s what I’ve found. If you can make the row length fixed that helps too.