Zabbix 1.6 Database Issues

[ataylo13]

I having an issue with my 1.6 installation. I am monitoring ~2000 servers. I have my polling interval at 60s for most all items being monitored. I have the database running on a 4 core with 16G of ram server and disks are on the SAN. I have the zabbix server running on a VM with 2 cores and 4G of ram.

When I take a look at the queue size it doesn't seem that the database is able to keep up. I am trying to figure out where to start looking to find the problem. Nothing is complaining or logging errors to the log files on zabbix or in mysql.

Thoughts opinions?

Database Server:
Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
mysql-server-4.1.20-2.RHEL4.1.0.1

my.cnf:
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/mysql/data
socket=/logs/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).

#from my-innodb-heavy-4G
back_log = 50
max_connections = 200
max_allowed_packet = 512M
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
long_query_time = 2
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 12G
innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
#innodb_flush_method=O_DIRECT
innodb_log_buffer_size = 8M
innodb_log_file_size = 1900M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /mysql/logs/
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table


old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
table_cache = 2048

results of mysqladmin -u root -p debug
Memory status:
Non-mmapped space allocated from system: 34119680
Number of free chunks: 13
Number of fastbin blocks: 0
Number of mmapped regions: 20
Space in mmapped regions: 993091584
Maximum total allocated space: 0
Space available in freed fastbin blocks: 0
Total allocated space: 33729776
Total free space: 389904
Top-most, releasable space: 134000
Estimated memory (with thread stack): 1028784128


Status information:

Current dir: /mysql/data/
Running threads: 63 Stack size: 196608
Current locks:
lock: 0x2dc7afaa90:

lock: 0x2dc7a2a9f0:

lock: 0x2dc7a1a980:

lock: 0x2bc5c00:

lock: 0x2c29840:

lock: 0x2dc7876960:

lock: 0x2dc7845620:

lock: 0x2dc7a61630:

lock: 0x2c93ac0:

lock: 0x336c8a0:

lock: 0x2dc7806460:

lock: 0x2dc7872020:

lock: 0x2dc7852210:

lock: 0x2dc7821f40:

lock: 0x2b61d90:

lock: 0x2baf1a0:

lock: 0x33800d0:

lock: 0x2dc78a1b50:

lock: 0x2bdbcb0:

lock: 0x2c2dad0:

lock: 0x336c9c0:

lock: 0x2dc7803a90:

lock: 0x2b74b10:

lock: 0x2dc7866910:

lock: 0x2dc7cf6ec0:

lock: 0x2bdcb50:

lock: 0x32295a0:

lock: 0x2bd7560: read
read : 0x2dc7a81738 (1155197280:1); 0x2dc7afe558 (1153390944:1); 0x2dc7ac85a8 (1151383904:1);

lock: 0x2bd05a0: read
read : 0x2dc7a00b98 (1155197280:1); 0x2bd0478 (1153390944:1); 0x2dc7ac9aa8 (1151383904:1);


Key caches:
default
Buffer_size: 8388600
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 142
not flushed: 0
w_requests: 115068
writes: 0
r_requests: 157388
reads: 75707


handler status:
read_key: 21926665
read_next: 133012787
read_rnd 409064
read_first: 369486
write: 2493636
delete 0
update: 970152

Table status:
Opened tables: 1152
Open tables: 64
Open files: 6
Open streams: 0

Alarm status:
Active alarms: 59
Max used alarms: 64
Next alarm time: 28210

Thread database.table_name Locked/Waiting Lock_type

41 zabbix.items Locked - read Low priority read lock
41 zabbix.hosts Locked - read Low priority read lock

[Alexei]

Please give us "Required server performance" value from the Dashboard.

[ataylo13]

currently it is at 250.48 and the DB is keeping up. When I posted it was at ~390. The math I have done once everything is enabled will put it around 700. From what I have read on the forum, I figured I had enough hardware for that.

[ataylo13]

I was able to get my hands on another server for testing...

It is a 4 core 16G just like the other
OS: RHEL 5.2 and running MySQL-server-community-5.1.28-0.rhel5
The database is running on local disks and not the SAN on this server (for now)

Current Status:

Parameter Value Details
ZABBIX server is running Yes -
Number of hosts (monitored/not monitored/templates) 2051 1601 / 436 / 14
Number of items (monitored/disabled/not supported) 45783 38677 / 5570 / 1536
Number of triggers (enabled/disabled)[true/unknown/false] 18668 18478 / 190 [1361 / 1319 / 15797]
Number of users (online) 17 1
Required server performance, new values per second 480.0443 -

When I left last night the server was keeping up and the Queue was empty, but this morning I come in and everything is in the over 10mins column.



Results of mysqladmin -p debug
Status information:

Current dir: /mysql/data/
Running threads: 65 Stack size: 196608
Current locks:
lock: 0x2aade4024398:

lock: 0x2aade403d518:

lock: 0x2aadec00bc18:

lock: 0x315221a0:

lock: 0x31445f60:

lock: 0x2aadec0024e0:

lock: 0x2aade0006240:

lock: 0x2aade002df60:

lock: 0x2aade003d210:

lock: 0x2aade004e840:

lock: 0x2aade048b0c0:

lock: 0x2aadec00a3f0:

lock: 0x2aade004da80:

lock: 0x2aade04786f0:

lock: 0x2aade0016950:

lock: 0x2aade41b8020:

lock: 0x2aade41b84e0:

lock: 0x31420700:

lock: 0x2aade4035950:

lock: 0x2aade403ee90:

lock: 0x31455120:

lock: 0x2aade4003930:

lock: 0x2aade001edd0:

lock: 0x2aade80fc020:

lock: 0x2aade41b82b0:

lock: 0x2aade4234ea0:

lock: 0x2aade0014e60:

lock: 0x3141c8f0:

lock: 0x31464c60: read
read : 0x2aaded58b6b8 (62:1); 0x2aadec009af8 (16:1); 0x2aade4224ae8 (28:1);

lock: 0x314036c0: read
read : 0x2aaded589238 (62:1); 0x2aadec145838 (16:1); 0x2aade417cf78 (28:1);


Key caches:
default
Buffer_size: 8388600
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 236
not flushed: 0
w_requests: 4570349
writes: 0
r_requests: 6861950
reads: 0


handler status:
read_key: 785750982
read_next: 8358914124
read_rnd 11758119
read_first: 8507975
write: 70160749
delete 0
update: 25070676

Table status:
Opened tables: 5480
Open tables: 64
Open files: 6
Open streams: 0

Alarm status:
Active alarms: 61
Max used alarms: 67
Next alarm time: 28295

Thread database.table_name Locked/Waiting Lock_type

62 zabbix_1_6.items Locked - read Low priority read lock
62 zabbix_1_6.hosts Locked - read Low priority read lock


I am not seeing anything obvious in the zabbix_server logs.

[ataylo13]

Also the new server has the fresh zabbix database from 1.6 tgz and I only migrated over the hosts table.

[ataylo13]

I also noticed today that after the system has been running for 1-2 hours, something happens and the load on the database drops in half and the the queues start filling up.

I caught something in the logs about:
read_config_store open failure on /var/net-snmp/snmpapp.conf

Not sure what this was but it came up 6000+ times. I check the file and only root could read it so I made it to wher anyone can read it, and restarted everything.

[ataylo13]

Any ideas/recomendations?

[nibb]

Well I completelty undestand you. I have created a test VM with 5 servers and 400 items, 350 MB RAM and its swapping at 50%.

I have used the tuning mysql scripts which is greate but I cannot make the server dont write to the disk. You would say 350 MB is low but I have read here on the forums people running 1 GB servers whith thousands of items and hundreds of hosts. Thats not my intention but I dot think there is a problem with the mysql. The load is low, if you shutdown mysql is stops swapping.

If people run so many items with 1 GB and 2 GB I should be able to monitor a couple of things with only 350 MB. I plan to increase it but just like my test zabbix server is right now its not having a good performance at all. It doesnt matter if I put it 32 GB ram if with 350 MB it cannot monitor 4 servers + locally. I have tried to optimize everything and the tuning keeps telling its writing tmp tables to disk.

It seems some people like us have terrible performace on the database and others can monitor thousands of things on low hardware servers. There must be something that we are goind wrong for sure, I did not noticed anything wrong on your my.cnf and it seems fine for your specs. I also noticed on my case that the web interface is the one that is hitting the server so hard.

Its a x64 bit system, Cent OS and the processor is under 0 load most of the time but its the ram that seems so intensive. I increased it to 512 MB and it also did not stopped swapping so putting more ram is not the solution and i went back to 350 until i know what is wrong.

[Alexei]

Well I completelty undestand you. I have created a test VM with 5 servers and 400 items, 350 MB RAM and its swapping at 50%.

Well, tune Apache/MySQL parameters or increase amount of available memory. You wouldn't expect normal performance from any application if the system swapping most of the time!

[nibb]

Yes I did, I actually tuned apache and mysql for micro servers. They both arent over 200 Mb combined. I just wonder if this is just apache and mysql of the whole performance. Its not swapping anymore but I hear people monitoring over 100 servers and thousands of items with 1 to 3 GB ram and this is half RAM running only 5 servers. and less then 400 items now.