ryan chewning dot net

These are some tweaks that we are using in our default MySQL installations at UC. There are some application specific changes that we will need to make but I will add those later.

Note these should be favorable for using both Innodb and MyISAM database engines together. There will be application specific tweaks on another post.

vi /etc/my.cnf or use your favorite text editor.

Find the following sections and make the changes:
key_buffer_size
This should be set to about 20%-25% of available memory on the box since we will not be using MyISAM exclusively. If we are, the number goes to 30%-40%.

innodedb_buffer_pool_size
For innodb tables this should be 70% – 80% of available memory. If we have MyISAM tables as well then set this closer to to 70%.

innodb_additional_mem_pool__size
This doesn’t really need to be adjusted. This is for additional memory that would be available to the innodb engine to do misc. tasks. Use SHOW INNODB STATUS to see how much is being used. If nothing else, bump to 20M.

innodb_log_file_size
Very important, especially for write intensive loads. 256M is a good size. Be careful though as this requires a longer start up time if you have to do a recovery.

innodb_log_buffer_size
Default size is alright. But if dealing with blobs a lot then bump to 16M. This memory is flushed every second though so too much memory is a waste.

innodb_flush_log_at_trx_commit
This is very tricky and needs to be thought out carefully before being set. efault value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

table_cache
Default value should be fine. Might even be able to be turned down to 1024.

thread_cache
Thread creation/destruction is ususally an expensive operation. Start at 16 and watch for fast growth of Threads_Created variable. If you have this. Start pushing the number up. Ideally we don’t want to be creating threads in normal operation.

query_cache_size
If your application is read intensive and you do not have application level caches (APC for php or memcache) this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled. Zabbix is a good example of an app that should not have this enabled.

innodb_flush_method
DO NOT USE O_DIRECT on any virtual machine that is using the SAN. There are reports that using O_DIRECT in a SAN backed MySQL server will slow a simple select by a factor of 3 times. Either fdatasync or O_DSYNC should be used. Leave at the default.

innodb_file_per_table
Insert this in the [mysqld] section so that all the innodb tables are in seperate files and less disk space is consumed. With innodb, once space is allocated in a file, the only way to get it back is to dump and reload the database.