What's the most unsafe MySQL configuration?

Question

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:

[mysqld]
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

At first glance, I would look over

• innodb_buffer_pool_size
• innodb_log_file_size
• innodb_log_buffer_size

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

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
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.

EPILOGUE

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 !!! ;-)