My.cnf
Introduction
This page lists some performance tuning parameters which we recommend for tuning MySQL database services used for Open-Xchange installations.
We cannot guarantee this is an exhaustive list of required settings. So treat this list of tunings as probably required, but not necessarily sufficient settings for optimal MySQL performance.
Furthermore, as MySQL changes over time, settings which have been correct as of the time of writing may become incorrect later.
However, this list of settings is the result of internal performance testing and real world customer feedback, so it should be valid to some extent.
In the end, proper configuration of the database service for performance, but also consistency, durability and high availability is in the responsibility of the customer.
Configuration items
- You should adjust the
innodb_buffer_pool_size
parameter for reasonable memory usage. Our DB sizing is mainly memory-driven and this is where most of the memory goes. Our standard DB machine sizing assumption is 32 GB if MySQL dedicated memory on a 48 GB total memory machine. On such a machine, you would configure 32 GB for the innodb_buffer_pool size, being aware that MySQL does also require memory for other things, in particular there are some also per-connection related memory spendings, which can become substantial if you allow for a lot of maximum concurrent connections. Please watch your memory configuration carefully, use monitoring and tools like mysqltuner.pl.
- 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.
- 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 (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.
Additional explanations
- Query cache is to be switched off; as we found in our own benchmarks and as backed up by upstreams, this hurts performance in load situations with high concurrency. query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0, so we recommend also to switch that off.
Sample config files
For easy deployment we recommend not to edit existing the existing my.cnf file, rather assume the distro provides sane settings for most of the items and override items where needed.
As in MySQL there is a instance wins semantic in options file parsing, we propose to create a custom include directory "ox.conf.d", put our custom config files therein, and include this directory as latest directory in /etc/mysql/my.cnf.
To put in that directory, we have one main tuning file called tunings.cnf, one galera-related file if galera is in use, and one more galera host-specific file which contains per-host settings if using galera (separate in a file of its own for easier configuration management).
So, start with adding to the existing my.cnf at the very bottom:
!includedir /etc/mysql/ox.conf.d/
Create that directory and put in there the generic ox tunings file tunings.cnf:
[mysqld] bind-address = * #innodb_use_native_aio = 0 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 = 0 query_cache_type = 0 default_storage_engine = InnoDB innodb_buffer_pool_size = 32G # the default value in MySQL 5.6.6 and higher is 8 when innodb_buffer_pool_size is greater than or equal to 1GB. Otherwise, the default is 1. innodb_buffer_pool_instances = 32 innodb_data_file_path = ibdata1:128M:autoextend innodb_file_per_table = 1 # innodb_log_file_size should be 25% of the innodb_buffer_pool_size innodb_log_file_size = 4GB # default and recommended value is 2 innodb_log_files_in_group = 2 # adjust according to your storage #innodb_io_capacity = 1000 # we are unsure about this setting. Newer versions of MariaDB seem to be fine with low (=1) settings for this value. # Traditionally we encountered values up to 4x the number of cores. # Default seems to be number of cores, so let's stick the default # In the end, we need to leave this setting up to you: if you dont get full cpu utilization in cpu-bound situations, this might be a setting to increase. #thread_pool_size = 32 binlog_cache_size = 1M sync_binlog = 8 binlog_format = row character_set_server = utf8 collation_server = utf8_general_ci # This was default_table_type previous to MySQL 5.5 default_storage_engine = InnoDB innodb_autoinc_lock_mode = 2 # keep until 5.6, deprecated later innodb_locks_unsafe_for_binlog = 1
# we found this has huge impact on (galera) performance # default (consistent) setting of 1 greatly severs performance # in galera (or async master-slave) deployments, you might be ok with setting this to 0 or 2, # assuming our consistency / availability comes from replication / other cluster nodes innodb_flush_log_at_trx_commit = 0 # for performance testing systems, to not use excessive disk space #expire_logs_days = 1
If using galera, use the following galera configuration file wsrep.cnf:
[mysqld] # adjust for your distros SO location wsrep_provider=/usr/lib/libgalera_smm.so # this is the big winner and enables us to switch off OX's replication monitor wsrep_sync_wait=1 # pick a unique cluster name wsrep_cluster_name=devcluster # adjust for your IPs / hostnames wsrep_cluster_address=gcomm://10.20.29.68,10.20.29.69,10.20.29.70 # put this in host.cnf #wsrep_node_name=... #wsrep_node_address=... wsrep_sst_method=xtrabackup-v2 # wsrep_sst_auth if of format username:password # pick whatever you configured on the donor node wsrep_sst_auth=sstuser:... # galera-specific tunings wsrep_slave_threads = 32 pxc_strict_mode=ENFORCING
Galera-related host-specific settings go in host.cnf:
[mysqld] # the nodes hostname wsrep_node_name=... # and the IP of the wsrep relevant interface, if multiple wsrep_node_address=10.20.29.68