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.

1 Answer
1

Here are some of my recommendations for InnoDB. In my experience, the buffer pool size is the most important because the more data you can keep in cache, the less time your system will spend using disk IOs.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_data_file_path
Here you can define the location of the ibdata file. If you can put the ibdata file on a separate disk that uses a seperate controller, it will reduce contention. Be careful with this option. Make sure that you read up on this variable before you use it. If your ibdata file needs more than 29G, MySQL will stop whatever its doing and wait for you to add space.

innodb_data_file_path = ibdata1:128M:autoextend:max:29G

This manages in what increments the ibdata file is increased. So if you put a really small number like 10MB, that would mean that innodb would spend a lot of time increasing its ibdata file if you had a lot of inserts.

innodb_autoextend_increment = 128M

where to put the transaction logs. If you can put the transaction logs on a different disk with a separate controller, this will also reduce contention

innodb_log_group_home_dir = /tlog/

What is the size of the log files

innodb_log_file_size = 500M

25% of the buffer pool size, allows large transactions to run without the need to write to the disk

innodb_log_buffer_size = 384M

I also define where the temp directory should go. Defines where MySQL creates temporary files. Yet another way to reduce contention.

I typically leave enough space to allow reindexing of the largest table on the server.

tmpdir=/opt/tmp/

And an error log is useful just in case something goes wrong and you need to trouble shoot.

log-error=/var/log/mysql.err

Also, if you have more than 4 cpus, these variables can be increased
innodb_thread_concurrency = 8
innodb_file_io_threads=4

This has nothing to do with innodb. There are other variables you can play with that have nothing to do with innodb. I added them at the bottom, but they were taken directly from a server I use for testing. Avoid just copying and pasting them. Reading MySQL's documentation based on the version your using is the best way to make the right choices.

Just one additional comment. There are a lot of graphical tools you can use to monitor your MySQL server remotely. I use Jet Profiler because it provides nice graphs and allows me to replay slow queries. Ultimately, the best server configuration in the world can't make up for badly written queries. Using a monitoring tool, can help you find those queries.
–
Craig EfreinDec 5 '11 at 7:09