High performance Innodb Mysql server configuration

By Luis Freitas | 1 comment

This week i had a urgent job about recovering a huge Innodb Mysql database with 2.4 million records and 2GB in size and with 160GB on 107 418 Jpeg files with IPTC and EXIF info associated with. I had to prepare and configure a new server with this hardware:

• 4 GB RAM
• 64 Bit system
• 3 GHz Core 2 processor

After load testing i found the perfect tuned my.cnf for this database with hight volume entries daily:

File my.cnf

# MySQL configuration
# InnoDB database with 3 MyISAM tables linked

port		= 3306
socket		= /var/run/mysql/mysql.sock

# MySQL server
[mysqld]
port		= 3306
socket		= /var/run/mysql/mysql.sock
datadir	= /var/lib/mysql
skip-locking
key_buffer_size = 16M
max_allowed_packet = 2M
table_open_cache = 128
sort_buffer_size = 1024K
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 1024K
myisam_sort_buffer_size = 8M
query_cache_size = 16M

# Added values after load testing
thread_cache_size = 4
tmp_table_size = 128M
max_heap_table_size = 128M
table_cache = 512
join_buffer_size = 512

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

# binary logging format
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Innodb specifics
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
# 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
# innodb_additional_mem_pool_size generally not needed after tests
innodb_additional_mem_pool_size = 8M
# log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
# innodb_file_per_table to avoid defragmentation but when optimizing will lock tables
innodb_file_per_table = 1

# The safe_mysqld script
[safe_mysqld]
log-error	= /var/log/mysql/mysqld.log
socket		= /var/run/mysql/mysql.sock

!include_dir /etc/mysql

[mysqldump]
socket		= /var/run/mysql/mysql.sock
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 60M
sort_buffer_size = 60M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log

# Please give new suggestions if you think you can optimize performance even more.