Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 20 '12 at 17:11

This question came from our site for professional and enthusiast programmers.

    
What about disks on that MySQL server? –  Eric Petroelje Dec 17 '12 at 20:23
    
120GB SSD. Sorry forgot to add that. –  user1911058 Dec 17 '12 at 20:30

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for the tips. I'll try that. The usual cpu usage for mysql is about 10% and ram usage is ~80%. It doesn't really go much over that. –  user1911058 Dec 17 '12 at 20:49
    
@user - honestly, your config looks pretty decent to me - nothing jumps out as problematic. I think the suggestions I made will help a bit, but I wouldn't expect anything dramatic. –  Eric Petroelje Dec 17 '12 at 20:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.