My.cnf
From Open-Xchange
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 = 0 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
Note for MySQL version 5.5 or higher
A small change for the above sample configuration file needed when using MySQL ver. 5.5.x or higher, e.g. used by Percona XtraDB Cluster / Galera.
Replace:
default_table_type = InnoDB
With
default_storage_engine = InnoDB