Database Tuning and Optimization for zends/mysql/mariadb

Applies To

• MySQL (community), ZenDS, MariaDB
• Not compatible with MySQL/ZenDS 5.5.15 and earlier

Summary

Zenoss ships ZenDS/MariaDB with default configurations in place. To configure a properly tuned and healthy Zenoss system, you must customize the default settings for your environment. This KB describes the parameter changes that have been shown, in the field, to have the most positive impact.

Procedure

Each database deployment has a configuration file (zends.cnf or my.cnf) with various fields/values that must be adjusted to attain stable database performance. In the following sample file (in this case, zends.cnf) you will see certain fields that Zenoss recommends changing from their default value are highlighted (yellow). For the recommended value for your instance, consult the specific named section within Tuning Suggestions, below.

NOTE: For every setting specifically called out below, if a line is commented out, it will not be used by MySQL/ZenDS/MariaDB.  If you change a setting, you must also confirm that the line itself is not commented out.

Sample Configuration

#---------------------------------------------------------------------------
# Zenoss DataStore (ZenDS) Sample Configuration
#---------------------------------------------------------------------------

[mysqld]
socket = /var/lib/zends/zends.sock
pid-file = /var/run/zends/zends.pid
basedir = /opt/zends
datadir = /opt/zends/data
port = 13306
user = zenoss
innodb_file_per_table
skip_external_locking
plugin-load = thread_pool.so
thread_pool_size = 32
query_cache_size = 0
query_cache_type = OFF
max_allowed_packet = 64M
wait_timeout = 7200
max_connections = 1000

innodb_log_file_size = 512M
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=1800
innodb_adaptive_hash_index = OFF
innodb_file_format = Barracuda
innodb_purge_threads = 1
innodb_max_purge_lag = 0
# Set innodb_buffer_pool_size to > zodb, zenoss_zep disk size
innodb_buffer_pool_size = 8192M 
# If ZenDS is on a virtual machine, uncomment the line below
#innodb_spin_wait_delay = 0
# Set innodb_buffer_pool_instances to CPU cores* (subtract 1 if VM)
innodb_buffer_pool_instances = 1

[client]
socket = /var/lib/zends/zends.sock
user = zenoss

[mysql]
max_allowed_packet = 64M
prompt = "zends> "

[mysqldump]
max_allowed_packet = 64M

Tuning Suggestions

Use the following explanations to configure these setting(s):

• wait_timeout = 7200

  In an environment where zencheckdbstats shows a high history list length value, we recommend changing this value from the default of 86400 to 7200. This value will reduce the amount of history that all SQL transactions need to reference when performing work.

  NOTES:

  • Restarting ZenDS resets the counters that zencheckdbstats displays when run. Because of this, it can require multiple days for the results of zencheckdbstats to reach steady state
     
  • If a line is commented out, it will not be used by MySQL/ZenDS/MariaDB. If you change this setting, you must also confirm that the line itself is not commented out.

  ---

• max_connections = 1000

  We recommend that this be set to 1000 to provide sufficient connection resources for any workload.

  NOTE: If a line is commented out, it will not be used by MySQL/ZenDS/MariaDB. If you change this setting, you must also confirm that the line itself is not commented out.

  ---

• innodb_adaptive_hash_index = OFF

  This line should always be uncommented, and set to OFF.

  ---

• innodb_max_purge_lag = 0

  We recommend that this be set to 0.
  If the amount of backlogged purge operations exceeds this number, ZenDS introduces a 300ms slowdown on every transaction that adversly affects DB performance.

  NOTE: If a line is commented out, it will not be used by MySQL/ZenDS/MariaDB. If you change this setting, you must also confirm that the line itself is not commented out.

  ---

• innodb_buffer_pool_size = 8192M

  Adjust this value to keep the steady state buffer pool use below 90%.
  Use the toolbox tool zencheckdbstats to determine the buffer pool used percentage (%). Adjust (increase) this value to compensate.

  For example: if the used % value is 120% and the current setting is 4g, increase the "innodb_buffer_pool_size" setting to 6g for an additional safety margin.

  NOTES:

  • Restarting ZenDS resets the counters. Because of this, it can require multiple days to reach steady state.
     
  • If a line is commented out, it will not be used by MySQL/ZenDS/MariaDB. If you change this setting, you must also confirm that the line itself is not commented out.

  ---

• innodb_buffer_pool_instances = 1

  This value should be number of CPU cores on a standalone DB, or the value - 1 for a virtualized host.

  NOTE: If a line is commented out, it will not be used by MySQL/ZenDS/MariaDB. If you change this setting, you must also confirm that the line itself is not commented out.

  ---