List:General Discussion« Previous MessageNext Message »
From:Walter Heck Date:February 19 2011 4:56pm
Subject:Re: Super active table optimization
View as plain text  
1. Which version of MySQL? Judging from your my.cnf it's not MariaDB 5.2 or
Percona Server? They have very good enhancement, particularly in the high
concurrency innodb atmosphere. InnoDB is a lot more tunabl;e in those
versions. Switching from a stock mysql version is adrop-in replacement, so
if tested properly should be a very simple operation.

2. Try makign all fields NOT NULL, that should increase performance as well,
although I'm not sure how much that would help.

On Sat, Feb 19, 2011 at 16:59, Bryan Cantwell <bcantwell@stripped>wrote:

> # cat /etc/my.cnf|grep -i innodb
Just because i like nagging: uuoc :)

> innodb_buffer_pool_size = 768M
With 16G RAM you should set this as high as you can, up to 60-75% of your
memory. This of course depends on the storage engine the rest of your db
uses. Aim to be mostly InnoDB and you can set this very high

> innodb_flush_log_at_trx_commit = 2
Have you read the consequences of setting this to 2? Look up the manual and
make an infromed decision as it can severely affect what happens if your
server dies.

> innodb_flush_method=O_DIRECT
This depends on what kind of hardware you have. With a RAID array with write
cache, you can set it to O-DIRECT. In other environments it can adversely
affect performance.

> 16G swap

This is insane :) If MySQL starts swapping, you're dead in the water anyway.
Typically, anything over 1G or so is too much.

> 8 CPU
How many cores? The more cores, the more use you will get from using one of
the non-stock MySQL versions, as they have been optimised for concurrency
and are much more tunable.

Shameless plug: if you want more on-topic help, you should look into letting
us (I work for Open Query) help you. We can then research your whole
environment and have much better advice that is not limited to this scope.
Other companies that do similar services include Percona, SkySQL and
FromDual to name a few :)

Have fun!

Walter Heck
Engineer @ Open Query (
Exceptional services for MariaDB and MySQL at a fixed budget
Follow @openquery on Twitter to stay up to date

Super active table optimizationBryan Cantwell19 Feb
  • Re: Super active table optimizationWalter Heck19 Feb
  • Re: Super active table optimizationReindl Harald19 Feb