Tuning Zabbix to improve its performance (II)

Let's continue with the last article about tuning Zabbix to improve its performance. First of all, I am going to set the suitable kernel parameters into the sysctl.conf file.

root@zbx01:~# cat /etc/sysctl.conf
...
# Maximum percentage of physical memory usage before going to swap
vm.swappiness = 10

# Number of open files for all processes
fs.file-max = 407020

# Minimum, default and maximum size of the send/receive buffer used by each TCP socket
net.ipv4.tcp_wmem = 8192        87380   16777216
net.ipv4.tcp_rmem = 8192        87380   16777216

# Maximum number of queued connection requests which have still not received an ACK (three-way handshake)
net.ipv4.tcp_max_syn_backlog = 2048

# Number of seconds to wait for a final FIN packet before the socket is forcibly closed
net.ipv4.tcp_fin_timeout = 25

# Number of seconds a connection needs to be idle before TCP begins sending out keep-alive probes
net.ipv4.tcp_keepalive_time = 1200

# Maximum TCP send window
net.core.wmem_max = 16777216

# Maximum TCP receive window
net.core.rmem_max = 16777216

# Maximum size in bytes of a message queue
kernel.msgmnb = 65536

# Maximum size for a message text
kernel.msgmax = 65536

# Maximum size in bytes for a shared memory segment
kernel.shmmax = 68719476736

# System wide maximum of shared memory pages
kernel.shmall = 4294967296

Then I am going to fit the values of MySQL by means of its configuration file. This part is really important if you want to achieve a good performance.

In order to adjust them, I have been following the status of the database throughout several weeks, by using tuning tools such as MySQL Performance Tuning Primer Script or MySQLTuner.

root@zbx01:~# cat /etc/mysql/my.cnf
...
# Size of the buffer used for index blocks
key_buffer = 16M

# Maximum size of one packet or any generated/intermediate string
max_allowed_packet = 16M

# Number of threads the server should cache for reuse
thread_cache_size = 64

# Maximum allowed number of simultaneous client connections
max_connections = 256

# Number of open tables for all threads
table_cache = 1024

# Number of table definitions that can be stored in the definition cache
table_definition_cache = 1024

# Do not cache results that are larger than this number of bytes
query_cache_limit = 16M

# Amount of memory allocated for caching query results
query_cache_size = 1024M

# Minimum size (in bytes) for blocks allocated by the query cache
query_cache_min_res_unit = 512

# 0: do not cache
# 1: cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE
# 2: cache results only for cacheable queries that begin with SELECT SQL_CACHE
query_cache_type = 1

# Slow queries are logged
log_slow_queries = /var/log/mysql/mysql-slow.log

# If a query takes longer than this value (seconds), the server logs the query
long_query_time = 5

# Queries that are expected to retrieve all rows are logged
log-queries-not-using-indexes

# Size in bytes of the memory buffer that InnoDB uses to cache data and indexes of its tables
innodb_buffer_pool_size = 4096M

With respect to MySQL, stand out that it is also important to defragment the query cache to enhance its utilization, by carrying out a "flush query cache" on the database. In my installation, I have seen that the optimum period is every hour.

root@zbx01:~# crontab -e
...
0 */1 * * * mysql -u root -pxxxxxx -e "flush query cache"

And finally, I have changed certain parameters from the Zabbix configuration file. The most important variable is related to the pre-forked pollers.

If this number is not enough, your Zabbix server will not be able to save all monitored data and you will find lack of many values. This is due to if the server runs out of sufficient processes to attend the requests, they will be ruled out and not registered.

root@zbx01:~# cat /etc/zabbix/zabbix_server.conf
...
# Number of pre-forked instances of pollers
StartPollers=96

# Shared memory size for storing hosts and items data
CacheSize=64M

# Shared memory size for storing history data
HistoryCacheSize=8M

# Shared memory size for storing trends data.
TrendCacheSize=8M

# Shared memory size for storing character, text or log history data
HistoryTextCacheSize=8M

Regarding Housekeeping, I have not modified any default parameter. In this way, the housekeeping procedure runs every hour and deletes all unnecessary values into the database.

If you note that your server does not work properly because it is using up lots of resources (CPU, memory, I/O) in this task, you will have to fit these options.

root@zbx01:~# cat /etc/zabbix/zabbix_server.conf
...
# Housekeeping is removing unnecessary information from history, alert, and alarm tables
# HousekeepingFrequency=1

# No more than MaxHousekeeperDelete rows will be deleted per one task in one housekeeping cycle
# MaxHousekeeperDelete=500

# Enable/disable housekeeping
# DisableHousekeeping=0