I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. The game server is connecting our dedicated mysql server which is located in the same network. It's using mysql connection pooling. Current settings for the pooling are:
We've about 300-400 threads connected on peaktime and most of our queries (~70%) are usually updates/inserts. So most of the tables are set to InnoDB. Sometimes we get some massive lag and the game server says that it lost the connection to the mysql server. So we are now trying to figure out is there something wrong with our mysql server configuration. The reason why It's now showing more reads is because our game server hasn't been working properly due mysql problems.
Here's our current my.cnf:
And here's "show engine InnoDB status" output:
Our mysql server:
- Ram 16gb
- Cpu Intel(R) Core(TM) i5-2500 CPU @ 3.30GHz
- OS Linux Debian 64-bit
- 120GB SSD
I'm in need of some suggestions on how to improve our mysql server configuration.