Cannot get Zabbix server housekeeper below 100%

[abjornson]

Zabbix server version: 2.2.9
Hosts monitored (all via proxies): 359
Items monitored: 37325
NVPS: 217.75

The server is running on an AWS m2.2xlarge (4 CPU, 34 GB RAM) with a mySQL backend.

The instance is EBS optimized, and mySQL data is stored on an EBS volume of type gp2 (SSD, standard, not IOPS).

So here's the thing - I know that m2.2xlarge is massively over sized for this number of hosts. But still - No matter what I do, I cannot get my housekeeper to go below 100%, and the web UI continues to feel sluggish.







Zabbix config follows - Any help greatly appreciated!

# This is a configuration file for Zabbix Server process
# To get more information about Zabbix,
# visit http://www.zabbix.com

############ GENERAL PARAMETERS #################

### Option: NodeID
#	Unique NodeID in distributed setup.
#	0 - standalone server
#
# Mandatory: no
# Range: 0-999
# Default:
# NodeID=0

### Option: ListenPort
#	Listen port for trapper.
#
# Mandatory: no
# Range: 1024-32767
# Default:
# ListenPort=10051

### Option: SourceIP
#	Source IP address for outgoing connections.
#
# Mandatory: no
# Default:
# SourceIP=

### Option: LogFile
#	Name of log file.
#	If not set, syslog is used.
#
# Mandatory: no
# Default:
# LogFile=

LogFile=/var/log/zabbix/zabbix_server.log

### Option: LogFileSize
#	Maximum size of log file in MB.
#	0 - disable automatic log rotation.
#
# Mandatory: no
# Range: 0-1024
# Default:
# LogFileSize=1

LogFileSize=0

### Option: DebugLevel
#	Specifies debug level
#	0 - no debug
#	1 - critical information
#	2 - error information
#	3 - warnings
#	4 - for debugging (produces lots of information)
#
# Mandatory: no
# Range: 0-4
# Default:
# DebugLevel=3
DebugLevel=3

### Option: PidFile
#	Name of PID file.
#
# Mandatory: no
# Default:
# PidFile=/tmp/zabbix_server.pid

PidFile=/var/run/zabbix/zabbix_server.pid

### Option: DBHost
#	Database host name.
#	If set to localhost, socket is used for MySQL.
#	If set to empty string, socket is used for PostgreSQL.
#
# Mandatory: no
# Default:
# DBHost=localhost

DBHost=localhost

### Option: DBName
#	Database name.
#	For SQLite3 path to database file must be provided. DBUser and DBPassword are ignored.
#
# Mandatory: yes
# Default:
# DBName=

DBName=zabbix

### Option: DBSchema
#	Schema name. Used for IBM DB2.
#
# Mandatory: no
# Default:
# DBSchema=

### Option: DBUser
#	Database user. Ignored for SQLite.
#
# Mandatory: no
# Default:
# DBUser=

DBUser=zabbix

### Option: DBPassword
#	Database password. Ignored for SQLite.
#	Comment this line if no password is used.
#
# Mandatory: no
# Default:
# DBPassword=

DBPassword=XXXXXXXXXXXX

### Option: DBSocket
#	Path to MySQL socket.
#
# Mandatory: no
# Default:
# DBSocket=/tmp/mysql.sock

DBSocket=/var/run/mysqld/mysqld.sock

### Option: DBPort
#	Database port when not using local socket. Ignored for SQLite.
#
# Mandatory: no
# Range: 1024-65535
# Default (for MySQL):
# DBPort=3306

############ ADVANCED PARAMETERS ################

### Option: StartPollers
#	Number of pre-forked instances of pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartPollers=5

### Option: StartIPMIPollers
#	Number of pre-forked instances of IPMI pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartIPMIPollers=0

### Option: StartPollersUnreachable
#	Number of pre-forked instances of pollers for unreachable hosts (including IPMI).
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartPollersUnreachable=1

### Option: StartTrappers
#	Number of pre-forked instances of trappers.
#	Trappers accept incoming connections from Zabbix sender, active agents, active proxies and child nodes.
#	At least one trapper process must be running to display server availability and view queue
#	in the frontend.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartTrappers=5

### Option: StartPingers
#	Number of pre-forked instances of ICMP pingers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartPingers=1

### Option: StartDiscoverers
#	Number of pre-forked instances of discoverers.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartDiscoverers=1

### Option: StartHTTPPollers
#	Number of pre-forked instances of HTTP pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartHTTPPollers=1

### Option: StartTimers
#	Number of pre-forked instances of timers.
#	Timers process time-based trigger functions and maintenance periods.
#	Only the first timer process handles the maintenance periods.
#
# Mandatory: no
# Range: 1-1000
# Default:
# StartTimers=1

### Option: JavaGateway
#	IP address (or hostname) of Zabbix Java gateway.
#	Only required if Java pollers are started.
#
# Mandatory: no
# Default:
# JavaGateway=

### Option: JavaGatewayPort
#	Port that Zabbix Java gateway listens on.
#
# Mandatory: no
# Range: 1024-32767
# Default:
# JavaGatewayPort=10052

### Option: StartJavaPollers
#	Number of pre-forked instances of Java pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartJavaPollers=0

### Option: StartVMwareCollectors
#	Number of pre-forked vmware collector instances.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartVMwareCollectors=0

### Option: VMwareFrequency
#	How often Zabbix will connect to VMware service to obtain a new data.
#
# Mandatory: no
# Range: 10-86400
# Default:
# VMwareFrequency=60

### Option: VMwareCacheSize
#	Size of VMware cache, in bytes.
#	Shared memory size for storing VMware data.
#	Only used if VMware collectors are started.
#
# Mandatory: no
# Range: 256K-2G
# Default:
# VMwareCacheSize=8M

### Option: SNMPTrapperFile
#	Temporary file used for passing data from SNMP trap daemon to the server.
#	Must be the same as in zabbix_trap_receiver.pl or SNMPTT configuration file.
#
# Mandatory: no
# Default:
# SNMPTrapperFile=/tmp/zabbix_traps.tmp

### Option: StartSNMPTrapper
#	If 1, SNMP trapper process is started.
#
# Mandatory: no
# Range: 0-1
# Default:
# StartSNMPTrapper=0

### Option: ListenIP
#	List of comma delimited IP addresses that the trapper should listen on.
#	Trapper will listen on all network interfaces if this parameter is missing.
#
# Mandatory: no
# Default:
# ListenIP=0.0.0.0

# ListenIP=127.0.0.1

### Option: HousekeepingFrequency
#	How often Zabbix will perform housekeeping procedure (in hours).
#	Housekeeping is removing unnecessary information from history, alert, and alarms tables.
#	To prevent Housekeeper from being overloaded (for example, when history and trend periods
#	are greatly reduced), no more than 4xHousekeepingFrequency hours of outdated history are
#	deleted in one housekeeping cycle, for each item.
#
# Mandatory: no
# Range: 1-24
# Default:
# HousekeepingFrequency=1
HousekeepingFrequency=2

### Option: MaxHousekeeperDelete
#	The table "housekeeper" contains "tasks" for housekeeping procedure in the format:
#	[housekeeperid], [tablename], [field], [value].
#	No more than 'MaxHousekeeperDelete' rows (corresponding to [tablename], [field], [value])
#	will be deleted per one task in one housekeeping cycle.
#	SQLite3 does not use this parameter, deletes all corresponding rows without a limit.
#	If set to 0 then no limit is used at all. In this case you must know what you are doing!
#
# Mandatory: no
# Range: 0-1000000
# Default:
# MaxHousekeeperDelete=500

### Option: SenderFrequency
#	How often Zabbix will try to send unsent alerts (in seconds).
#
# Mandatory: no
# Range: 5-3600
# Default:
# SenderFrequency=30

### Option: CacheSize
#	Size of configuration cache, in bytes.
#	Shared memory size for storing host, item and trigger data.
#
# Mandatory: no
# Range: 128K-8G
# Default:
# CacheSize=8M
CacheSize=134217728

### Option: CacheUpdateFrequency
#	How often Zabbix will perform update of configuration cache, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# CacheUpdateFrequency=60

### Option: StartDBSyncers
#	Number of pre-forked instances of DB Syncers
#
# Mandatory: no
# Range: 1-100
# Default:
# StartDBSyncers=4
StartDBSyncers=64

### Option: HistoryCacheSize
#	Size of history cache, in bytes.
#	Shared memory size for storing history data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
# HistoryCacheSize=8M

### Option: TrendCacheSize
#	Size of trend cache, in bytes.
#	Shared memory size for storing trends data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
# TrendCacheSize=4M
TrendCacheSize=8M

### Option: HistoryTextCacheSize
#	Size of text history cache, in bytes.
#	Shared memory size for storing character, text or log history data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
# HistoryTextCacheSize=16M

### Option: ValueCacheSize
#	Size of history value cache, in bytes.
#	Shared memory size for caching item history data requests
#	Setting to 0 disables value cache.
#
# Mandatory: no
# Range: 0,128K-64G
# Default:
# ValueCacheSize=8M
#ValueCacheSize=16777216
ValueCacheSize=33554432
#ValueCacheSize=67108864
#ValueCacheSize=134217728

### Option: NodeNoEvents
#	If set to '1' local events won't be sent to master node.
#	This won't impact ability of this node to propagate events from its child nodes.
#
# Mandatory: no
# Range: 0-1
# Default:
# NodeNoEvents=0

### Option: NodeNoHistory
#	If set to '1' local history won't be sent to master node.
#	This won't impact ability of this node to propagate history from its child nodes.
#
# Mandatory: no
# Range: 0-1
# Default:
# NodeNoHistory=0

### Option: Timeout
#	Specifies how long we wait for agent, SNMP device or external check (in seconds).
#
# Mandatory: no
# Range: 1-30
# Default:
# Timeout=3
Timeout=15

### Option: TrapperTimeout
#	Specifies how many seconds trapper may spend processing new data.
#
# Mandatory: no
# Range: 1-300
# Default:
# TrapperTimeout=300

### Option: UnreachablePeriod
#	After how many seconds of unreachability treat a host as unavailable.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnreachablePeriod=45

### Option: UnavailableDelay
#	How often host is checked for availability during the unavailability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnavailableDelay=60

### Option: UnreachableDelay
#	How often host is checked for availability during the unreachability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnreachableDelay=15

### Option: AlertScriptsPath
#	Full path to location of custom alert scripts.
#	Default depends on compilation options.
#
# Mandatory: no
# Default:
#AlertScriptsPath=${datadir}/zabbix/alertscripts
AlertScriptsPath=/usr/lib/zabbix/alertscripts

### Option: ExternalScripts
#	Full path to location of external scripts.
#	Default depends on compilation options.
#
# Mandatory: no
# Default:
# ExternalScripts=${datadir}/zabbix/externalscripts

ExternalScripts=/usr/lib/zabbix/externalscripts

### Option: FpingLocation
#	Location of fping.
#	Make sure that fping binary has root ownership and SUID flag set.
#
# Mandatory: no
# Default:
# FpingLocation=/usr/sbin/fping

FpingLocation=/usr/bin/fping

### Option: Fping6Location
#	Location of fping6.
#	Make sure that fping6 binary has root ownership and SUID flag set.
#	Make empty if your fping utility is capable to process IPv6 addresses.
#
# Mandatory: no
# Default:
# Fping6Location=/usr/sbin/fping6

Fping6Location=/usr/bin/fping6

### Option: SSHKeyLocation
#	Location of public and private keys for SSH checks and actions.
#
# Mandatory: no
# Default:
# SSHKeyLocation=

### Option: LogSlowQueries
#	How long a database query may take before being logged (in milliseconds).
#	Only works if DebugLevel set to 3 or 4.
#	0 - don't log slow queries.
#
# Mandatory: no
# Range: 1-3600000
# Default:
# LogSlowQueries=0

### Option: TmpDir
#	Temporary directory.
#
# Mandatory: no
# Default:
# TmpDir=/tmp

### Option: StartProxyPollers
#	Number of pre-forked instances of pollers for passive proxies.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartProxyPollers=1
StartProxyPollers=50

### Option: ProxyConfigFrequency
#	How often Zabbix Server sends configuration data to a Zabbix Proxy in seconds.
#	This parameter is used only for proxies in the passive mode.
#
# Mandatory: no
# Range: 1-3600*24*7
# Default:
# ProxyConfigFrequency=3600

### Option: ProxyDataFrequency
#	How often Zabbix Server requests history data from a Zabbix Proxy in seconds.
#	This parameter is used only for proxies in the passive mode.
#
# Mandatory: no
# Range: 1-3600
# Default:
# ProxyDataFrequency=1

### Option: AllowRoot
#	Allow the server to run as 'root'. If disabled and the server is started by 'root', the server
#	will try to switch to user 'zabbix' instead. Has no effect if started under a regular user.
#	0 - do not allow
#	1 - allow
#
# Mandatory: no
# Default:
# AllowRoot=0

### Option: Include
#	You may include individual files or all files in a directory in the configuration file.
#	Installing Zabbix will create include directory in /usr/local/etc, unless modified during the compile time.
#
# Mandatory: no
# Default:
# Include=

# Include=/usr/local/etc/zabbix_server.general.conf
# Include=/usr/local/etc/zabbix_server.conf.d/

####### LOADABLE MODULES #######

### Option: LoadModulePath
#	Full path to location of server modules.
#	Default depends on compilation options.
#
# Mandatory: no
# Default:
# LoadModulePath=${libdir}/modules

### Option: LoadModule
#	Module to load at server startup. Modules are used to extend functionality of the server.
#	Format: LoadModule=<module.so>
#	The modules must be located in directory specified by LoadModulePath.
#	It is allowed to include multiple LoadModule parameters.
#
# Mandatory: no
# Default:
# LoadModule=

[Atsushi]

What OS distribution and version are using?
What version of MySQL are using?
What kind tuning are setting for MySQL?

[abjornson]

Server is running Ubuntu 14.04.2 LTS (64 bit)

mySQL version is 5.5.43

Here is my my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global varions,
# - "~/.my.cnf" to set user-specific varions.
#
# One can use all long varions that the program supports.
# Run program with --help to get a list of available varions and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /opt/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
max_allowed_packet = 64M
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /opt/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# log_slow_queries	= /opt/log/mysql/mysql-slow.log

#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
log_bin			= /opt/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related varions. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
single-transaction

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[Colttt]

WTF?!?!?

why did you have 64db syncers?

pleas go back to 4... at you will see all works fine

[abjornson]

Hi @Coltt

Thank you for the suggestion - I have dialed dbSyncers back to 4....it seems now my busy historySyncers have gone to 100% and are staying there.

So now both my housekeeper and my historySyncers are at 100% all the time.

I believe this is why i increased dBSyncers in the first place.

Suggestions?

[Colttt]

ok than go up to 6 max 8 dbsyncers.. this is more than enough

what is with oiwait? and cpuload?

[kloczek]

You must start using partitioned history*/trends* tables and stop using housekeeper.
Try to have look on google for "zabbix partitioning mysql"

Best way to partition tables on prod is use slave DB and do partitioning on slave. When you will have partitioned tables on slave just switch to this DB backend as master.

[c.mammoli]

Your my.cnf is the default one I think. No matter how much ram you throw at it, you are not going to use it.

Start tuning mysql: https://www.zabbix.com/forum/showthread.php?t=12407

[abjornson]

@c.mammoli - Many thanks for the reference to that thread! I had actually just found it today through some independent searching. I implemented the adjustment to innodb_file_per_table and have seen a dramatic improvement. I will continue to peruse that thread and implement some more fixes.

@kloczek - I've seen a lot of references to partitioning the tables, but I got a bit confused by some of the changes around this in zabbix between 1.8 and 2.0. I'm not a dba by any means, so I feel a bit lost on some of the database tuning at times. Is the zabbix best practice still to partition the tables? Is this recommended in all situations?

@colttt - The innodb_file_per_table adjustment had a huge impact on my cpu load. I was previously at 1-1.5, and this change has dropped it to .45. This also improved my iowait from 45% before the change to 25%-30% after.

[Atsushi]

Do you have to specify how much the size of innodb_buffer_pool_size?
When the specified innodb_buffer_pool_size is small,
even if equipped with a lot of memory, it will not be able to make effective use of the memory.

Please check value for innodb_buffer_pool_size.

ex.
mysql> SHOW VARIABLES LIKE "innodb_buffer%";

[abjornson]

I saw in the tuning thread mentioned by @c.mammoli that the recommended value for innodb_buffer_pool_size is 80% of RAM.

I did not go quite this high. I am currently in an AWS server with 32GB RAM, but I'd like to think about reducing to the next smaller instance to save costs.

My problem was that I was using most of the default mysql settings....so my innodb_buffer_pool_size was at the default, something like 144MB only.

For now, I increased innodb_buffer_pool_size to 4GB, so in my.cnf:

innodb_buffer_pool_size=4294967296

I may try to increase innodb_buffer_pool_size further....but first I'm going to try the other fixes in that thread.

[kloczek]

My problem was that I was using most of the default mysql settings....so my innodb_buffer_pool_size was at the default, something like 144MB only.

Nope as long as you are still using zabbix housekeeper. In such case you problem is that you are still using unpartitioned history*/trends* tables.
Whatever you will do with mysql settings you cannot beat this that delete huge number of history*/trends* rows by delete query (this is what housekeper does) takes huge number of physical IOs when deleting oldest data by dropping oldest partitions takes only few of them (a whole day).

Increase innodb_buffer_pool_size may give you bigger cache to decrease latency of selects but it will do almost nothing in case insert or delete/update queries.

[Colttt]

@colttt - The innodb_file_per_table adjustment had a huge impact on my cpu load. I was previously at 1-1.5, and this change has dropped it to .45. This also improved my iowait from 45% before the change to 25%-30% after.

you still have an iowait 25-30% ?if so, i think thats a bottleneck..

you can also use tuning-primer to check you mysql-config:

http://www.day32.com/MySQL/

[Atsushi]

If the innodb_buffer_pool_size is small, you will have access to the disk every time for select.
In such a state, it is not a better performance can be obtained.

Under the influence of the disk access may have increased the processing load of the house keeper.

It may not be the most certain effective measures, but it is one of the important measures.