Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Answer 1: Ugh, I made a mistake, it is actually MySQL 5.5.27-29.0-log. Answer 2: Yes. –  didip Nov 27 '12 at 22:37

1 Answer 1

up vote 1 down vote accepted

At first glance, I would look over

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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