My.cnf: Difference between revisions

From Open-Xchange
No edit summary
No edit summary
 
(14 intermediate revisions by 5 users not shown)
Line 1: Line 1:
== Introduction ==
== 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 page lists some performance tuning parameters which we recommend for tuning MySQL database services used for Open-Xchange installations.


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.
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.


=== Configuration items ===
Furthermore, as MySQL changes over time, settings which have been correct as of the time of writing may become incorrect later.


* You should adjust the <code>innodb_buffer_pool_size</code> 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.
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.
 
=== Performance items ===
 
* You should adjust the <code>innodb_buffer_pool_size</code> 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 <code>innodb_file_per_table = 1</code>, 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.
* On bigger installations you should use <code>innodb_file_per_table = 1</code>, 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 <code>query_cache_size=0</code>, <code>query_cache_type=0</code>.
* 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 <code>datadir</code>, <code>innodb_data_home_dir</code>, <code>innodb_log_group_home_dir</code>.
 
* If your storage is fast (handle a lot of IOPS), you may want to adjust the <code>innodb_io_capacity</code> 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.
 
=== Functional items ===
 
* 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. [https://dev.mysql.com/doc/refman/5.7/en/query-cache.htm The 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.
* Starting with App Suite 7.10.0, <code>character_set_server</code> must be set to <code>utf8mb4</code> and <code>collation_server</code> to <code>utf8mb4_general_ci</code>. For older versions it must be <code>utf8</code> and <code>utf8_general_ci</code> respectively.
* Starting with MySQL 5.7 <code>innodb_strict_mode</code> must be disabled.
* Starting with MySQL 5.6 and MariaDB 10.1 <code>sql_mode</code> must be configured according to belows matrix.
 
==== SQL mode matrix ====
 
The default for the <code>sql_mode</code> setting changes regularly with MariaDB and MySQL releases and is not even consistent anymore between the two derivates. SQL modes affect how data and queries are handled at runtime. Enabling strict modes might lead to errors in terms of failing queries or even update tasks. We strongly recommend the following configuration to avoid according runtime errors:
 
{| class="wikitable"
|-
! scope="col"| sql_mode
! scope="col"| App Suite 7.8.4
! scope="col"| App Suite 7.10.x
|-
! scope="row"| MySQL 5.6
| <code>NO_ENGINE_SUBSTITUTION</code>
| <code>NO_ENGINE_SUBSTITUTION</code>
|-
! scope="row"| MySQL 5.7
| <code>NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</code>
| <code>NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY</code>
|-
! scope="row"| MariaDB >= 10.1
| <code>NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</code>
| <code>NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</code>
|-
|}


* 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 <code>datadir</code>, <code>innodb_data_home_dir</code>, <code>innodb_log_group_home_dir</code>.
=== 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 [https://dev.mysql.com/doc/refman/5.7/en/option-files.html last 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).


* If your storage is fast, thus it can handle a lot of IOPS, you may want to adjust the <code>innodb_io_capacity</code> 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.
So, start with adding to the existing my.cnf at the very bottom:


=== Sample my.cnf file ===
!includedir /etc/mysql/ox.conf.d/


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.
Create that directory and put in there the generic ox tunings file /etc/mysql/ox.conf.d/tunings.cnf:


#
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
  [mysqld]
  [mysqld]
  socket          = /var/run/mysqld/mysqld.sock
  bind-address = *
port            = 3306
user                          = mysql
# applies only when running as root
#memlock                        = 1
   
   
  table_open_cache              = 3072
  #innodb_use_native_aio = 0
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
table_open_cache = 3072
  default_table_type            = InnoDB
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
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
   
   
  # 80% of ram that is dedicated for the database (this needs to be adjusted to your system)
  # we are unsure about this setting. Newer versions of MariaDB seem to be fine with low (=1) settings for this value.
  innodb_buffer_pool_size        = 14G
  # Traditionally we encountered values up to 4x the number of cores.
  # number of CPU cores dedicated to the MySQL InnoDB backend
  # Default seems to be number of cores, so let's stick the default
  innodb_buffer_pool_instances = 16
# 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
   
   
  innodb_data_file_path          = ibdata1:128M:autoextend
  binlog_cache_size = 1M
  innodb_file_per_table          = 1
  sync_binlog = 8
  innodb_log_file_size          = 512M
  binlog_format = row
innodb_log_files_in_group      = 2
   
   
  # MyISAM
  character_set_server = utf8mb4
  myisam_recover                = backup,force
  collation_server = utf8mb4_general_ci
   
   
  # Logging
  # default since MySQL 5.5.5 (was MyISAM previously)
log_warnings                  = 2
  default_storage_engine = InnoDB
  log_error                      = /var/log/mysql/error.log
   
   
  slow_query_log                = 1
  innodb_autoinc_lock_mode = 2
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
  # keep until 5.6, deprecated later
  server_id                      = 1
  innodb_locks_unsafe_for_binlog = 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]
  # we found this has huge impact on (galera) performance
  quick
  # default (consistent) setting of 1 greatly severs performance
  single-transaction
  # in galera (or async master-slave) deployments, you might be ok with setting this to 0 or 2,
  max_allowed_packet            = 16M
  # assuming our consistency / availability comes from replication / other cluster nodes
innodb_flush_log_at_trx_commit = 0
   
   
  [mysql]
  # for performance testing systems, to not use excessive disk space
  no_auto_rehash
  #expire_logs_days = 1
   
   
  [myisamchk]
  # MySQL 5.7.7 has changed the default to 1. Disable it explicitly to prevent from errors based on invalid data stored by former App Suite or MySQL versions.
  key_buffer                    = 512M
  innodb_strict_mode = 0
sort_buffer_size              = 512M
read_buffer                    = 8M
write_buffer                  = 8M
   
   
  [mysqld_safe]
  # The following value refers to App Suite 7.10 on top of MySQL 5.7. For other combinations see the sql mode matrix at http://oxpedia.org/wiki/index.php?title=My.cnf.
open-files-limit              = 8192
sql_mode = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY
log-error                      = /var/log/mysql/error.log


If using galera, use the following galera configuration file <code>/etc/mysql/ox.conf.d/wsrep.cnf</code>. See the comments in that file for values to be adjusted.


=== Note for MySQL version 5.5 or higher ===
[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=...
# For some MariaDB versions, xtrabackup-v2 no longer works, instead use "mariabackup"
# (needs to be installed separately, e.g. via the mariadb-backup-10.2 package)
# see upstream documentation for details:
# https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/#xtrabackup
#
# wsrep_sst_method=mariabackup
wsrep_sst_method=xtrabackup-v2


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.
# 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
# finally, enable wsrep: required for some MariaDB versions
wsrep_on=ON -- Enable wsrep replication (MariaDB starting 10.1.1)


Replace:
Galera-related host-specific settings go in /etc/mysql/ox.conf.d/host.cnf:


  default_table_type            = InnoDB
  [mysqld]
 
# the nodes hostname
With
wsrep_node_name=...
 
# and the IP of the wsrep relevant interface, if multiple
  default_storage_engine        = InnoDB
  wsrep_node_address=10.20.29.68

Latest revision as of 14:23, 17 January 2020

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.

Performance 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.

Functional items

  • 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. The 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.
  • Starting with App Suite 7.10.0, character_set_server must be set to utf8mb4 and collation_server to utf8mb4_general_ci. For older versions it must be utf8 and utf8_general_ci respectively.
  • Starting with MySQL 5.7 innodb_strict_mode must be disabled.
  • Starting with MySQL 5.6 and MariaDB 10.1 sql_mode must be configured according to belows matrix.

SQL mode matrix

The default for the sql_mode setting changes regularly with MariaDB and MySQL releases and is not even consistent anymore between the two derivates. SQL modes affect how data and queries are handled at runtime. Enabling strict modes might lead to errors in terms of failing queries or even update tasks. We strongly recommend the following configuration to avoid according runtime errors:

sql_mode App Suite 7.8.4 App Suite 7.10.x
MySQL 5.6 NO_ENGINE_SUBSTITUTION NO_ENGINE_SUBSTITUTION
MySQL 5.7 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
MariaDB >= 10.1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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 last 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 /etc/mysql/ox.conf.d/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
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 = utf8mb4
collation_server = utf8mb4_general_ci

# default since MySQL 5.5.5 (was MyISAM previously)
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

# MySQL 5.7.7 has changed the default to 1. Disable it explicitly to prevent from errors based on invalid data stored by former App Suite or MySQL versions.
innodb_strict_mode = 0

# The following value refers to App Suite 7.10 on top of MySQL 5.7. For other combinations see the sql mode matrix at http://oxpedia.org/wiki/index.php?title=My.cnf.
sql_mode = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY

If using galera, use the following galera configuration file /etc/mysql/ox.conf.d/wsrep.cnf. See the comments in that file for values to be adjusted.

[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=...

# For some MariaDB versions, xtrabackup-v2 no longer works, instead use "mariabackup"
# (needs to be installed separately, e.g. via the mariadb-backup-10.2 package)
# see upstream documentation for details: 
# https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/#xtrabackup
#
# wsrep_sst_method=mariabackup
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

# finally, enable wsrep: required for some MariaDB versions
wsrep_on=ON -- Enable wsrep replication (MariaDB starting 10.1.1) 

Galera-related host-specific settings go in /etc/mysql/ox.conf.d/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