ZabbiX Slow: Scheduling zabbix housekeeper process when the load is low/night

[zabbixfk]

Hi All,

I would like to know, is there any way where i can schedule zabbix housekeeper process at night or some time when the loads on the server is slow. As of now, i see that consistently some or the other delete query is running on zabbix, and its taking lot of time, hence (looks like ) zabbix UI takes as much as 50-60+ seconds to respond to clicks.

Zabbix dashboard status is like this,

Number of hosts (monitored/not monitored/templates)	 :489,  425 / 19 / 45
Number of items (monitored/disabled/not supported)	         :15830, 15063 / 340 / 427
Number of triggers (enabled/disabled)[problem/unknown/ok] : 1292, 1087 / 205  [10 / 0 / 1077]
Number of users (online)                                                  : 34 , 6
Required server performance, new values per second            : 98.53

I have made below entries in zabbix server conf w.r.to houseKeeper settings

HousekeepingFrequency=12
MaxHousekeeperDelete=5500
DisableHousekeeping=0

I may not be correct in terms of configurations, please point to right place if you think above configs are wrong. I am kind a stuck at resolving this wait on the zabbix UI and load on the server issue, hence i am exposing most of the details of zabbix server. ( Pardon me if you think i am mixing up issues - i am not sure what is causing load, thought houseKeeper is causing it).

1). Zabbix Server ( 2.0.6) and DB ( mysql 5.1.69-log Source distribution ) , httpd ( 2.2.15 ) is running on the same machine with CentOS 6.4. Its H/W configs are, 8 Core, 36G RAM, 500GB HDD ( SAAS, RAID 5 configured)

2). Zabbix Conf Details

LogFile=/var/log/zabbix/zabbix_server.log
LogFileSize=50
DebugLevel=4
PidFile=/var/run/zabbix/zabbix_server.pid
DBName=zabbix
DBUser=zabbix
DBPassword=
StartPollers=45
StartTrappers=45
StartPingers=45
StartDiscoverers=3
StartSNMPTrapper=1
ListenIP=0.0.0.0
HousekeepingFrequency=12
MaxHousekeeperDelete=5500
SenderFrequency=300
CacheSize=1G
CacheUpdateFrequency=300
StartDBSyncers=15
HistoryCacheSize=128M
TrendCacheSize=1G
HistoryTextCacheSize=128M
Timeout=30
TrapperTimeout=120
UnreachablePeriod=120
AlertScriptsPath=/etc/zabbix/alert.d/
FpingLocation=/usr/local/sbin/fping
LogSlowQueries=1

- Cache, TrendCache, HistorySize cache are high, because long back zabbix crashed due these settings, and at this size i got zabbix working.
- Timeout is 30, as i have some servers/ n/w devices (snmp) are sending data lately due to n/w latency.
- Trapper, Pinger, Pollers are exactly 45 - okay this might be put in random number - need your suggestion on this.
- When zabbix starts it runs 184 zabbix_server process, and it varies later to 169-181.
- When taken dump, zabbix DB size is close to 27GB.

MySql Config. Yes i have enabled replication ( below is zabbixserver/master config).

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
slow_query_log_file=/var/log/mysql/slow-query.log
long_query_time = 30
log-queries-not-using-indexes=YES
innodb_lock_wait_timeout=500
innodb_locks_unsafe_for_binlog=1
innodb_buffer_pool_size=20G
innodb_file_per_table
max_allowed_packet = 8M
innodb_additional_mem_pool_size = 30M
innodb_thread_concurrency = 8
key_buffer_size = 60M
max_connections=290
table_cache=4096
query_cache_size = 96M
tmp_table_size = 32M
thread_cache_size = 64
sort_buffer_size = 12M
query_cache_limit=64M
thread_cache_size=512
wait_timeout=60
read_buffer_size=2M
read_rnd_buffer_size=8M
join_buffer_size=8M
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=zabbix
server-id=1
sync_binlog=1
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

And below is the mysqltuner output.

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 27G (Tables: 103)
[!!] Total fragmented tables: 103

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 3h 34m 39s (315M q [241.388 qps], 1M conn, TX: 192B, RX: 49B)
[--] Reads / Writes: 43% / 57%
[--] Total buffers: 20.2G global + 30.2M per thread (290 max threads)
[OK] Maximum possible memory usage: 28.8G (81% of installed RAM)
[OK] Slow queries: 0% (110K/315M)
[OK] Highest usage of available connections: 81% (235/290)
[OK] Key buffer size / total MyISAM indexes: 60.0M/91.0K
[OK] Key buffer hit rate: 100.0% (18M cached / 5 reads)
[OK] Query cache efficiency: 25.4% (35M cached / 138M selects)
[!!] Query cache prunes per day: 25406
[OK] Sorts requiring temporary tables: 0% (141 temp sorts / 10M sorts)
[OK] Temporary tables created on disk: 2% (1M on disk / 40M total)
[OK] Thread cache hit rate: 99% (235 created / 1M connections)
[OK] Table cache hit rate: 98% (505 open / 512 opened)
[OK] Open file limit used: 0% (43/8K)
[OK] Table locks acquired immediately: 100% (360M immediate / 360M locks)
[!!] InnoDB data size / buffer pool: 27.4G/20.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    long_query_time (<= 10)
    query_cache_size (> 96M)
    innodb_buffer_pool_size (>= 27G)

It would be great if you guys can suggest where i am doing wrong.
Any suggestion w.r.to housekeeper scheduling and tuning zabbix server/mysql or to make sure server responds to clicks or any other suggestions are greatly helpful.

Thanks

[Colttt]

go down with the DBSyncers to the default value!! (max +2)

[tchjts1]

go down with the DBSyncers to the default value!! (max +2)

I definitely agree with this! It would only be an unusual circumstance where you would need to change this from the default of 4. And probably only if you are monitoring thousands and thousands of hosts.

[tchjts1]

It would be great if you guys can suggest where i am doing wrong.
Any suggestion w.r.to housekeeper scheduling and tuning zabbix server/mysql or to make sure server responds to clicks or any other suggestions are greatly helpful.

Thanks

Please take a look at this post, specifically the last paragraph and the graphs after it. This is going to help you tune your setup more than the mysql tuning script. https://www.zabbix.com/forum/showthread.php?t=41219

If you post your graphs as in my post, please show a 24 hour period so we can get a clear picture of patterns. A 1 hour view is no good.

[zabbixfk]

Zabbix Slow

Thank you all for the reply.
I did made DBSyncers to 4. Still there is no improvement ( it still takes 50+ seconds to responds to clicks) .
It wold be of great help if you could guide me in resolving this.
Below are the graphs i could get from Zabbix UI for 3 days -

Zabbix Cache usage process


Zabbix Data gathering process


Zabbix Internal Process Busy


Zabbix Server perforamance



Thanks.

[steveboyson]

I would recommend to set

StartPollersUnreachable=7

at least if it's set to default (which is 1).

and if it helps I suggest to check why you have to increase this (e.g. why do you have a significant number of unreachable hosts).

[tchjts1]

If you notice the text values at the bottoms of the graphs,you'll see you have several which have been hitting 100% in the 3 day view you provided. I personally have my configurations set so that my metrics are generally running below 30%, except for housekeeper which will always hit 100% when it runs.

Besides what Steve mentioned, I would also increase StartPollers= by 10 more than what you have now.

(Edit) And notice how long your housekeeper is running! It starts at 0900 and runs to the current point in time of that graph... it is running for more than 24 hours straight!
You are much better off having the housekeeper settings at their default. Mine is set to run every hour, and runs for maybe 10 minutes per hour. I have Max Delete Rows parameter set at 500.

[zabbixfk]

ZabbiX Slow: Scheduling zabbix housekeeper process when the load is low/night

Thank you all for the reply.

I did changed StartUnreachable Poller settings to 7 and DBSyncer settings. But no help. (UI loading still takes time).
Also changed HosueKeeper frequency to 1 ( earlier it was 12 ), and items to 500 ( it was 5500 ). Let's see how it goes.
And by the way, did not understand housekeeper scheduling part - am i missing something here. Please elaborate.

Thanks.