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 <bcantw...@firescope.com>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 (http://openquery.com) Exceptional services for MariaDB and MySQL at a fixed budget -- Follow @openquery on Twitter to stay up to date
