Mysql Server optimization tips needed! (InnoDB)

Question

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:

minsize=80
maxsize=400

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:
http://pastebin.com/dF0a5ubR

And here's "show engine InnoDB status" output:
http://pastebin.com/fmmad02q

Mysqltuner:
http://pastebin.com/pDTXSGTj

tuning-primer:
http://pastebin.com/89PVGqyC

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.

Answer 1

A few suggestions:

1. You might want to increase the size of your log files. They are currently set to 250MB a piece. A larger value there (perhaps a gig or two) should help reduce IO a bit.

2. Turn off the slow query logging

3. Decrease the buffer pool size a bit (or get more memory). You might be starving threads for memory by using everything for the buffer pool.