My.cnf

Introduction

This page lists some parameters (MySQL 5.1) which might be worth to adjust on a MySQL instance running the Open-Xchange databases.

This is NOT a reference configuration but shows some possible settings. Please check the MySQL documentation for your version to understand the settings and test local changes carefully.

Configuration items

• You should adjust the innodb_buffer_pool_size parameter for better memory usage. This is where all the memory of large DB machines should go. For example, on a 32 GB RAM machine, this can go up to 24 GB.

• On bigger installations you should use innodb_file_per_table = 1, which is creating single files instead of one big blob. If you change this parameter after the database initialization you have to recreate (like dump/drop and re-import) the tables.

• Some benchmarks indicate that actually switching off the query cache helps performance. You may want to experiment with this. To switch it off, use query_cache_size=0, query_cache_type=0.

• It can help to put different parts of the mysql datadir (iblog, ibdata) on different filesystems / storage devices. This depends on your infrastructure. Settings herefore are datadir, innodb_data_home_dir, innodb_log_group_home_dir.

• If your storage is fast, thus it can handle a lot of IOPS, you may want to adjust the innodb_io_capacity setting, which defines a limit for the IOPS MySQL will create. The default is 200, which is sensible for single spindle disks. But if you have storage appliances with a lot of fast SAS drives, or even SSDs, this limit can be increased greatly.

Sample my.cnf file

#
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
user                           = mysql
# applies only when running as root
#memlock                        = 1

table_open_cache               = 3072
table_definition_cache         = 4096
max_heap_table_size            = 64M
tmp_table_size                 = 64M
max_connections                = 505
max_user_connections           = 500
max_allowed_packet             = 16M
thread_cache_size              = 32
query_cache_size               = 64M

# InnoDB
default_table_type             = InnoDB

# 80% of ram that is dedicated for the database (this needs to be adjusted to your system)
innodb_buffer_pool_size        = 14G
# number of CPU cores dedicated to the MySQL InnoDB backend 
innodb_buffer_pool_instances = 16 

innodb_data_file_path          = ibdata1:128M:autoextend
innodb_file_per_table          = 1
innodb_log_file_size           = 512M
innodb_log_files_in_group      = 2

# MyISAM
myisam_recover                 = backup,force

# Logging
log_warnings                   = 2
log_error                      = /var/log/mysql/error.log

slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log
long_query_time                = 1
log_queries_not_using_indexes  = 1
min_examined_row_limit         = 20

# Binary Log / Replication
server_id                      = 1
log-bin                        = mysql-bin
binlog_cache_size              = 1M 
sync_binlog                    = 8
binlog_format                  = row
expire_logs_days               = 7
max_binlog_size                = 128M 
relay-log                      = /var/log/mysql/slave-relay.log
relay-log-index                = /var/log/mysql/slave-relay-log.index 

[mysqldump]
quick
single-transaction
max_allowed_packet             = 16M

[mysql]
no_auto_rehash

[myisamchk]
key_buffer                     = 512M
sort_buffer_size               = 512M
read_buffer                    = 8M
write_buffer                   = 8M

[mysqld_safe]
open-files-limit               = 8192
log-error                      = /var/log/mysql/error.log