Mysql Server optimization tips needed! (InnoDB)

innodbMySQLoptimization

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.

Best Answer

A few suggestions:

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.
Turn off the slow query logging
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.

Related Solutions

Mysql – SQL queries break our game! (Back-end server is at capacity)

What idexes do you have on this table? If the dbms engine is doing a full table scan, it will completely load a cpu and take a long time. If you have a 2 cpu system and all your data is in RAM, then you'll see 50% cpu usage.

Please create and index, such as

CREATE INDEX BlueBoxEngineDB.TransferIdx1
ON BlueBoxEngineDB.Transfer(Amount,FromUserId,Status);

and then run again your query. It should complete in a glimpse.

MySQL the.cnf Optimization

Look at this line

[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G

The sum of all your MyISAM indexes is 131.6G

You should lower key_buffer_size to 140G. This will give back 110G.

I have a little suggestion for you. You may want to consider using a dedicate keycache for each large table. See my Nov 16, 2012 post Cache all mysql table

Look at this line

[!!] InnoDB data size / buffer pool: 2.2G/2.0G

innodb_buffer_pool_size could be raised to 2560M (That's 2.5G)

Look at these lines

read_buffer_size=2G
sort_buffer_size = 2G

Why is so big? If you change both of these to 32M, you can increase max_connections. In fact, do the following right now:

SET @ThrityTwoMeg = 1024 * 1024 * 32
SET GLOBAL read_buffer_size = @ThrityTwoMeg;
SET GLOBAL sort_buffer_size = @ThrityTwoMeg;
SET GLOBAL join_buffer_size = @ThrityTwoMeg;

Then, go rerun mysqltuner.pl and you will see a dramatic difference from 4.1G per thread

BTW go change join_buffer_size to 32M in my.cnf

Give it a Try !!!

UPDATE 2013-04-11 16:53 EDT

I just thought of another change you can make to increase SELECT speed against MyISAM tables. For every MyISAM you need to read a lot from, do this to the table:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

Believe it or not, this will bloat the table 80-100% in size, but SELECTs will go 20-25% faster. I have written about this many times. Here is just few of my posts:

Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?

Best Answer

Related Solutions

Mysql – SQL queries break our game! (Back-end server is at capacity)

MySQL the.cnf Optimization

UPDATE 2013-04-11 16:53 EDT

Related Question