MySQL / MariaDB Configuration Tips: Boost Zabbix Database!

Databases are the achilles’ heel of Zabbix! If not properly configured, they can lead to major problems! But, don’t worry; it’s your lucky day! In this tutorial, I will share with you the my MySQL / MariaDB configuration that I’ve fine-tuned over the years. You’re welcome!

There is good guide on Zabbix blog about MySQL / MariaDB optimization. However, in this tutorial I will provide a ready-to-go configuration with minimal changes needed on your end.

Zabbix MySQL / MariaDB Configuration Example

You can find MySQL/MariaDB configuration (.cnf) in default directory path “/etc/my.cnf” or in directory “/etc/my.cnf.d/“.

The MySQL configuration below is tweaked for a dedicated database server with 4 CPUs and 32GB RAM. Please make sure to adjust certain configuration parameters to match your specific server specifications!

[mysqld]  # MySQL Server Configuration
# Directories and File Paths
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Logging and Replication
skip-log-bin=true

# Performance and Optimization
skip_name_resolve
skip-performance-schema
optimizer_switch = 'index_condition_pushdown=off'
tmp-table-size = 96M
max-heap-table-size = 96M
key_buffer_size = 64M

# Connections and Threads
max_connections = 1250
thread_cache_size = 8
max_allowed_packet = 64M

# InnoDB Configuration
innodb-log-file-size = 128M
innodb-log-buffer-size = 128M
innodb-file-per-table = 1
innodb_buffer_pool_instances = 8
innodb_old_blocks_time = 1000
innodb_stats_on_metadata = off
innodb-flush-method = O_DIRECT
innodb-flush-log-at-trx-commit = 2
innodb_buffer_pool_size = 23G
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_io_capacity = 4000
innodb_io_capacity_max = 6000

# Timeouts
connect_timeout = 300
wait_timeout = 30000

# Caches and Limits
table_open_cache_instances = 16
table_open_cache               = 32012 
open_files_limit = 65535
max_connect_errors = 1000000

[client]  # MySQL Client Configuration
# Client Socket
socket=/data/mysql/mysql.sock

Later, we will go through every configuration parameter with our ChatGPT friend, but first, there are two things to remember!

Configuration parameter max_connections must be larger than the total number of all Zabbix server processes plus 150. You can use the command below to automatically check the number of Zabbix processes and add 150 to that number:

egrep "^Start.+=[0-9]" /etc/zabbix/zabbix_server.conf | awk -F "=" '{s+=$2} END {print s+150}'
295

The second most important parameter is innodb_buffer_pool_size, which determines how much memory can MySQL get for caching InnoDB tables and index data. This is the most important parameter in the configuration for a performance boost! You should set that parameter to 70% of system memory if you have dedicated server for the database.

I didn’t have any problems with memory, but if your Zabbix server crashes because of lack of memory, reduce “innodb_buffer_pool_size” and restart MySQL server.

Explaining Database Configuration Parameters

I asked ChatGPT to describe every parameter, defining its purpose, and providing the min/default/max possible values, and I must say it looks very good. If you notice any errors, please let me know in the comments section. Thanks!

a) Performance and Optimization

• skip_name_resolve:
  • Purpose: Disables DNS name resolution during client connections, improving performance when IP addresses are used.
  • Min/Default/Max Value: Not applicable; it’s a boolean parameter (true/false).

• skip-performance-schema:
  • Purpose: Disables the Performance Schema, which provides performance-related monitoring and statistics.
  • Min/Default/Max Value: Not applicable; it’s a boolean parameter (true/false).

• optimizer_switch:
  • Purpose: Enables or disables specific query optimization switches. In this case, it sets ‘index_condition_pushdown’ to ‘off’.
  • Min/Default/Max Value: Not applicable; it depends on the optimization switches available.

• tmp-table-size:
  • Purpose: Sets the maximum size of temporary tables in memory for each session.
  • Min/Default/Max Value: 0/16M/No limit (memory available to the server).

• max-heap-table-size:
  • Purpose: Sets the maximum size of MEMORY (HEAP) storage engine tables for each session.
  • Min/Default/Max Value: 0/16M/No limit (memory available to the server).

• key_buffer_size:
  • Purpose: Sets the size of the key buffer used for index blocks for MyISAM tables.
  • Min/Default/Max Value: 8M/128M/No limit (memory available to the server).

b) Connections and Threads

• max_connections:
  • Purpose: Limits the maximum number of simultaneous client connections.
  • Min/Default/Max Value: 1/151/100000

• thread_cache_size:
  • Purpose: Sets the number of threads MySQL can keep waiting in a cache to reuse for client connections.
  • Min/Default/Max Value: 0/9/100000

• max_allowed_packet:
  • Purpose: Sets the maximum size of a packet or a BLOB/TEXT field sent to or received from the server.
  • Min/Default/Max Value: 1024/16M/1073741824 (1GB)

c) InnoDB Configuration

• innodb-log-file-size:
  • Purpose: Sets the size of each InnoDB log file.
  • Min/Default/Max Value: 5M/48M/Unlimited (limited by the file system)

• innodb-log-buffer-size:
  • Purpose: Sets the size of the buffer used for InnoDB redo log entries.
  • Min/Default/Max Value: 1M/16M/Unlimited (limited by the memory available to the server)

• innodb-file-per-table:
  • Purpose: Enables or disables the use of a separate tablespace file for each InnoDB table.
  • Min/Default/Max Value: Not applicable; it’s a boolean parameter (true/false).

• innodb_buffer_pool_instances:
  • Purpose: Sets the number of InnoDB buffer pool instances to divide the buffer pool into for multi-threaded performance.
  • Min/Default/Max Value: 1/8/64

• innodb_old_blocks_time:
  • Purpose: Specifies how long in milliseconds an InnoDB block remains in the “old” sublist before being moved to the “new” sublist.
  • Min/Default/Max Value: 1000/1000/Unlimited

• innodb_stats_on_metadata:
  • Purpose: Enables or disables automatic InnoDB statistics gathering during metadata statements.
  • Min/Default/Max Value: Not applicable; it’s a boolean parameter (on/off).

• innodb-flush-method:
  • Purpose: Specifies the method used to flush data to the InnoDB data files.
  • Min/Default/Max Value: Not specified; depends on the available flush methods.

• innodb-flush-log-at-trx-commit:
  • Purpose: Controls how InnoDB flushes the log buffer to the log file on each transaction commit.
  • Min/Default/Max Value: 0/1/2

• innodb_buffer_pool_size:
  • Purpose: Sets the size of the InnoDB buffer pool, which holds data and indexes for InnoDB tables.
  • Min/Default/Max Value: 128M/128M/Unlimited (limited by the memory available to the server)

• innodb_read_io_threads:
  • Purpose: Sets the number of background InnoDB I/O threads for read operations.
  • Min/Default/Max Value: 1/4/64

• innodb_write_io_threads:
  • Purpose: Sets the number of background InnoDB I/O threads for write operations.
  • Min/Default/Max Value: 1/4/64

• innodb_io_capacity:
  • Purpose: Sets the number of I/O operations per second the InnoDB background I/O thread can perform.
  • Min/Default/Max Value: 100/200/2**64-1

• innodb_io_capacity_max:
  • Purpose: Sets the maximum number of I/O operations per second the InnoDB background I/O thread can perform.
  • Min/Default/Max Value: 2000/2000/2**64-1

d) Timeouts

• connect_timeout:
  • Purpose: Sets the number of seconds the server waits for a client to establish a connection before terminating it.
  • Min/Default/Max Value: 2/10/31536000 (1 year)

• wait_timeout:
  • Purpose: Sets the number of seconds the server waits for activity on an interactive connection before closing it.
  • Min/Default/Max Value: 2/28800/31536000 (1 year)

e) Caches and Limits

• table_open_cache_instances:
  • Purpose: Sets the number of table cache instances to divide the table cache into for multi-threaded performance.
  • Min/Default/Max Value: 1/1/64

• table_open_cache
  • Purpose: The number of open tables for all threads.
  • Min/Default/Max Value: 1/4000/524288

• open_files_limit:
  • Purpose: Limits the maximum number of files that MySQL can keep open.
  • Min/Default/Max Value: 1024/65535/Unlimited

• max_connect_errors:
  • Purpose: Limits the number of interrupted connection attempts a host can make to the server.
  • Min/Default/Max Value: 1/100/18446744073709551615 (unlimited)