Mysql Server Load since Zabbix 2.x

[stalks]

Ever since we upgraded to Zabbix 2.2 the MySQL back-end for Zabbix load increased 10x. We upgraded from 1.8 -> 2.2.

I install from source using "./configure --enable-server --with-mysql --with-net-snmp --with-libcurl --with-jabber"

If I enable MySQL logging I see Zabbix is constantly accessing SQL with no stop in sight, sample log here: http://pastebin.com/raw.php?i=CcH0ErR0

I don't even use escalations.

Our zabbix box is ver modest and only has an "new value per second" of 4.09. See status information here: http://i.imgur.com/byQW0gN.png

The MySQL is a dedicated machine just for Zabbix with a i3-3220 CPU @ 3.30GHz and 4GB of RAM. I have done the usual tweaks such as: "innodb_file_per_table = 1", "innodb_buffer_pool_size = 3G".

It is running on SSDs but the ib_logfile data files had to be put on a spindle disk as it was writing close to 30GB of data a day. We are a small outfit, havign to explain to my boss we needed a new machine just for Zabbix MySQL raised questions.

I understand some major changes were made such as removing the last() field from each item, meaning an extra lookup was required but I have found the SQL requirements for Zabbix are astronomical in comparison to a few months ago.

I have tried dumping all data, starting fresh and only copying templates over. I have tried using MariaDB instead of MySQL with no effect.

MySQL queries per second from 1.8 -> 2.2 went from an average of <15 to >70.

Has anyone else got this issue? Some pointers? Config options I am perhaps missing?

Thanks.

What can I do to tame Zabbix?

[dogbert831]

You might try increasing mysql max_connections. I've got mine set at 200 for a server with "new values per second" of 14. I was having issues when it was set to a much lower value. Also this thread gives some good info:

https://www.zabbix.com/forum/showthread.php?t=43839&highlight=mysql+max_connections

[stalks]

Thanks dogbert831 I got some info out of that link.

My max connections is set to 100, and according to mysqltuner my maximum connection usage is 19 with 3 days uptime.

However the link you sent showed how useful the internal zabbix template is. I've only been monitoring the queue and not using the template.

Well with the template enabled I see that "escalation processes" is pegged at 88%, re-affirming what I saw in MySQL usage logs.



My actions have no excalations other than recovery messages. I use Step 1 to 1, with a default step duration of 3600. I only use Actions to send emails.

What can cause this escalation issue?

PS. The process also shows:

/usr/local/sbin/zabbix_server: escalator [processed 2 escalations in 70.235948 sec, processing escalations]

Edit:
I adjusted the cache values to:

CacheSize=32M
HistoryCacheSize=512M
TrendCacheSize=128M
HistoryTextCacheSize=128M
ValueCacheSize=256M

After 6 hours the Zabbix internal graph is:

[stalks]

I think I have this figured out.

After some investigation I looked at the escalations table:

"select count(*) from escalations;"
+----------+
| count(*) |
+----------+
|     1097 |
+----------+

I took a backup, stopped Zabbix and TRUNCATE'd the table.

I've brought Zabbix back up and the MySQL Queries per second has gone from 150 to 25. The escalations process busy % from 80-90% to 0%.

I shall monitor the escalations table to see if it is still filling up.

Edit: a few hours later and the change is dramatic. If the system remains similar over the next month I shall be re-integrating MySQL back to the Zabbix server and freeing up a server.