zabbix mysql problems

[ataylo13]

I am at my wits end on trying to trouble shoot zabbix/mysql issues. I can't narrow down where the problem is...
1. Do i have mysql configured properly?
2. Do I have zabbix configured properly?
3. how the heck do I determine the difference?

I have tried so many things and zabbix will just stop writing to the database. I would prefer to use zabbix proxies, but they stop writing to db after a few hours. The nodes selectively send data, and no trends or latest data.

I know a few of you have large installations, so thoughts/suggestions would be welcomed.

~2000 hosts
~ 41k items
~ 20k triggers
~550 inserts per second


Latest log in DB:
16922:20090210:160334 [Z3005] Query failed: [1205] Lock wait timeout exceeded; try restarting transaction [delete from node_cksum where nodeid=3 and cksumtype=1]
16945:20090210:160335 [Z3005] Query failed: [1205] Lock wait timeout exceeded; try restarting transaction [update node_cksum set cksumtype=0,cksum='',sync='2' where nodeid=4 and tablename='triggers' and recordid=400400000015633 and cksumtype=0]

[ataylo13]

And it is the lack of responses on this forum that are the most frustrating part of zabbix. I am willing to look past and work through issues that arise with the tool, but the lack of community is what will keep this application from exploding.

For small sites Zabbix rocks but when you start monitoring larger environments.... you are on your own.

*steps down from soap box*

[simix]

The lack of information you provide may be the reason for the lack of response. That said, a little bit guessing is all someone can do.

So, did you try increasing 'innodb_lock_wait_timeout' to see if it improves your situation? I don't consider it a fix but it's a step to find out where the problem lies.

[bbrendon]

There are also plenty of paid support options available.

Without a lot of details, troubleshooting your issue would be difficult.

[ataylo13]

I agree that there is a lack of detail. This is the third time I have posted on the subject and this is the first time anyone has suggested anything. As stated in the initial post, I know there are plenty of possibilities on where the issues can lie, but I would like suggestions on what I should be looking for.

I have been tweaking my.cnf from other posts on this forum, but have not seen any major changes.

I have seen posts of people with several proxy servers and multiple nodes, yet when I add a proxy server with ~400 hosts the setup fails after 12hours - 1 day. When I check the logs everything is acting as if all is well, but when I check the data from the web interface everything is blank. That led me to believe that I was over running the database, so that is where I have been focusing.

My current database server has 8 cores, 32G of RAM, SAN attached storage, RHEL 5.2 64bit, MySQL 5.1.30. I have been running the mysqltuner.pl every week or so to tune the configuration. My current zabbix instance has 534 new values per second. When I turn on the proxy server that number climbs to ~690 new events per second.

Thoughts/opinions.

[ataylo13]

Current my.cnf

@simix my current innodb_lock_wait_timeout is 120. Should I go higher?

[mysqld]
datadir=/mysql/data
socket=/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

#from my-innodb-heavy-4G
back_log = 50
max_connections = 400
max_allowed_packet = 512M
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 512M
sort_buffer_size = 8M
join_buffer_size = 1024M
thread_cache_size = 8
thread_concurrency = 8
table_cache = 2048
query_cache_size = 1536M
query_cache_limit = 512MM
#query_cache_size = 64M
#query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
#transaction_isolation = REPEATABLE-READ
transaction-isolation = READ-COMMITTED
tmp_table_size = 512M
long_query_time = 2
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 20G
innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
innodb_log_buffer_size = 20M
innodb_log_file_size = 256M
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

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[simix]

Your innodb_lock_wait_timeout is already quite high so I don't think you have to increase it more. Maybe I'm wrong.

What I'm wondering is when you say it stops working after 12 hours or so, what is the state of the database? Can you still query it or is it also hanging somehow?

And I'm not sure but, why don't you run the MySQL which is shipped with RHEL5.2? Version 5.0.45 shipped seems to work quite fine and 5.1.x is know to have some issues. Maybe you are running into a problem which only exists in the 5.1.x version while 5.0.x is much better tested by now.

[ataylo13]

I was running with the version that came out of the box with RHEL 5 and figured it was a db performance issue.

Do you know how to determine that I am overrunning the mysql server?

What do you think i should drop the lock_wait time to?

[richlv]

My current zabbix instance has 534 new values per second. When I turn on the proxy server that number climbs to ~690 new events per second.

interesting. do you mean you move same checks to proxy or enable additional checks with the proxy ?

so zabbix_server keeps on running, but it stops inserting new data, and you get that lock related error messages last in the log ?

have you reproduced the problem with debug level set to 4 for zabbix_server ?

my itemcount is 6-7 times smaller, so it's not quite comparable, but i'd hate to stumble upon this problem at some point later, so figuring this out could be helpful

[ataylo13]

I have zabbix in debug mode now and I will see what I can capture the next time the db dies.

[krisjturn]

Did you find a solution to your problem? I have having the same issue with Zabbix and MySQL. At least I think I am, my issues sound a lot like yours and the logs I think point that direction.

[ataylo13]

No I haven't. I am still tuning/troubleshooting the problem.

[krisjturn]

Thanks,

Will keep my eye on this thread... my problem just started today and still trying to trouble shoot. However, I do think I have it narrowed down to a MySQL issue now after looking at the logs and reading a few postings. Too bad there isn't a solution!!!

Have a good night!

[ataylo13]

Well zabbix and the database are on the ropes again... If there is anyone out there that can help me figure out this issue i would be very happy. I feel like a rat running in a wheel, because there are so many variables that "could" help the problem, even though I am not 100% where the problem is.

[richlv]

so did you get any interesting messages in the debug mode ?