Ipsa scientia potestas est

A few friends have been asking me what my my.cnf looks like for a master database, using InnoDB.  I’ve decided to just post it, removing only the bits that would compromise security.  Ultimately this config is the most versatile one I’ve come up with to date.  Just so everyone understands, the scale I was working with was about a ~250G. Server is a Dell 2950, 16G RAM, 15kRPM 146GB SAS drives in a hardware RAID 10, CentOS x86_64, with patched kernel for optimal Dell support, with /var/lib/mysql on its own partition, using XFS as the filesystem. It blazes, blazes like you wouldn’t believe. Even with application servers pounding the crap out of this system, we hardly ever see load above 1.5. yes, 1.5.

Heres the config with comments:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
datadir = /var/lib/mysql/
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
key_buffer = 256M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 32M
thread_cache_size = 32
query_cache_size = 512M
# Dual procs, dual core, 4*2=8
thread_concurrency = 8
max_connections = 1000

# dont make mistakes with engine
default-storage-engine = InnoDB

log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
relay-log=relay-bin

log_slave_updates = OFF

# we’re the master
server-id = 1

# heres one you should use: innodb_file_per_table
# ultimately this makes innodb create a file on disk for each table you create.
# this is incredibly useful if you have to re-claim disk space. if you don’t use this
# and you drop a table, the ibdata1 doesn’t shrink. you lose the diskspace.
innodb_file_per_table
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
# now i could put the logs on a different partition/disk, but it hasn’t been a bottleneck so far
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_log_files_in_group = 2
# You can set .._buffer_pool_size up to 50 - 80 %
# we have more than 8G on the box.
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 512M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1024M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Now this setup isn’t for everyone, and I left out the replication bits. You will undoubtedly have to tweak all the memory utilization settings for your own config, but this one works for me. Really the moral of this story is, use the following if you can help it:

• CentOS 64bit
• RAID (preferably 10 for speed/performance)
• Partition strictly for MySQL using XFS as the filesystem
• InnoDB with table per/file

Enjoy.