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

This file does not contain all the configuration items discussed in the previous section. Use this as a starting point and adjust it, particularly considering the configuration items discussed in the previous section.

#
[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