I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance.

The box has 32GB of RAM, but most of those are used for running tests. I can probably spend another 64M for innodb_buffer_pool_size

The box has 8 cores with 24% utilization as well as SSD drive for MySQL.

Here's what I have so far for my.cnf:

innodb_buffer_pool_size = 256M
innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_io_capacity = 4000
innodb_doublewrite = 'OFF'
innodb_adaptive_flushing_method = keep_average

Anyone have other ideas what else can I turn off to gain performance?

  • Answer 1: Ugh, I made a mistake, it is actually MySQL 5.5.27-29.0-log. Answer 2: Yes.
    – didip
    Nov 27, 2012 at 22:37

1 Answer 1


At first glance, I would look over

To properly judge the first two values, you should run this query:

SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

This will tell you the ideal buffer pool size for the give dataset in MB. The innodb_buffer_pool_size should never be larger that 75% of installed RAM.

As for the log file size, I have two suggestions (pick one)

Suggestion 1: Set the innodb_log_file_size to be 25% of innodb_buffer_pool_size

Suggestion 2: If you want to set the log file size based on that actual amount of log file writes per hour, see my Aug 27, 2012 post Proper tuning for 30GB InnoDB table on server with 48GB RAM and my Feb 16, 2011 post How to safely change MySQL innodb variable 'innodb_log_file_size'?

As for innodb_log_buffer_size, you should max it out to 8M. The larger the innodb_log_buffer_size, the less flushing of transactions will occur.


Your original question was What's the most unsafe MySQL configuration?. The real answer to that is : a MySQL instance with no config file whatsoever !!! ;-)

  • Since you are using MySQL 5.5, please ignore my comments on innodb_thread_concurrency. Nov 27, 2012 at 22:40
  • I just removed my comments on innodb_thread_concurrency and updated my answer with a third option. Nov 27, 2012 at 22:45
  • Great answer. innodb_thread_concurrency is indeed 0 and innodb_concurrency_tickets is 500
    – didip
    Nov 27, 2012 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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