Ubuntu – Good/Better config for MySQL on an EC2 Large Instance

amazon ec2innodbMySQLUbuntu

I have an EC2 Large instance dedicated to MySQL.

It will be serving a Joomla/Magento combo so it has a blend of InnoDB and MyISAM tables. I have only worked with MyISAM in the past and am therefore unfamiliar with the settings InnoDB uses. Experiments so far have been less than fruitful, as I keep causing the InnoDB engine to be disabled.

My instance is running Ubuntu 10.04 64 bit server edition and has ~7.5G of ram. MySQL is currently using ~0.6% of that, with somewhat poor performance. I would like to configure it to use as much of the system RAM as is reasonable.

Testing some settings I learned that the InnoDB logs can't collectively be larger than 4G.

Would anyone be able to provide some base InnoDB and MyISAM settings to get my started.

Thank you
Tim

Best Answer

I'm running the same sort of Mysql server on EC2. Don't touch /etc/mysql/my.cnf, but instead put config files into /etc/mysql/conf.d/ This will let you manage your changes for specific engines and give admins after you additional clues to how things were configured. Also it make it easy to backup any changes you've made as well as track them.

/etc/mysql/conf.d/innodb_mysql.cnf

[mysqld]    
# innodb settings 
innodb_additional_mem_pool_size = 12M
innodb_buffer_pool_size         = 2G
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_lock_wait_timeout        = 180
innodb_log_buffer_size          = 16M
innodb_open_files               = 512
innodb_thread_concurrency       = 0

Most of these settings are pretty standard and you should definitely look at Percona's recommendations. You could got as high as 6G on your system, but I'd start small. Also with myisam tables I'd probably stop at 4G. I would not touch the innodb log file sizes, there is very little gain to be had there. Increasing the log_buffer_size and addition_mem_pool also has very little return after an initial bump.

Also the settings above are for better performance and not for transaction consistency. Assuming you're running just a web app the above is fine, but not for a banking system.

/etc/mysql/conf.d/general_mysql.cnf

[mysqld]
# general settings
key_buffer = 384M

key_buffer is the most useful for myisam and Mysql in general The default it's only 16M which is very small on an 8GB machine. Again I'd start with a good jump and see if you get improvement. Keep in mind that myisam buffers and innodb buffers are not shared so they need to be collectively less than then amount of RAM you have. Later you might look at sort and read buffers as well.

Related Topic