slow query in zabbix 2.2.0

[zdsec]

after upgrade zabbix from 2.0.5 to 2.2.0, many slow query in zabbix server log, just like below


26760:20131212:052612.808 slow query: 1452.724707 sec, "select clock,ns,value from history_uint where itemid=361230 and clock<=1383537720 order by clock desc,ns desc"

it need 1452 seconds, but if i run same query in mysql directly, it need 0.27 seconds only,
38417 rows in set (0.27 sec)


the slow query mostly on table history and history_uint, any idea? (we have mysql partition for talbe history and history_uint)

it similar as https://support.zabbix.com/browse/ZBX-5876

[jsosic]

I'm having the exact same problems, load on my machine is 10+, I/O is saturated 100%, and when I take a look at mytop or innotop, these are the queries that I see:

mysql> select from_unixtime("1383652988");
+-----------------------------+
| from_unixtime("1383652988") |
+-----------------------------+
| 2013-11-05 04:03:08         |
+-----------------------------+
1 row in set (3.31 sec)

Query     880  Sending data    zabbix  localhost      zabbix  08:06  select clock,ns,value from history where itemid=49158 and clock>1384348323 and clock<=1386940322

So it's been running for 8 minutes and still not finished.... And I have ~10 these queries...

[zdsec]

any solution you have found?

[jsosic]

Yeah, I have not succeed in lowering the time needed for this query, but I did found a way to eliminate the query from running repeatedly.

It seems that increasing the Zabbix Server parameter ValueCache - eliminates the need for zabbix_server to run this queries repeatedly. They are now run only once when Zabbix starts and are not run again later.... My load dropped significantly (from ~20 to under 1). I set it up to 64MB (had to also increase kernel.shmmax).

[jsosic]

Anyway, if this helps you too, please report back

[stalks]

I upgraded to 2.2.0 a few weeks ago and immediately noticed a massive jump in SQL load.

We run MySQL on a seperate box which usually is quite idle at ~0.2 load avg. Then immediately following the 2.2.0 upgrade load was 2-5+ and the MysqL queries per second had quadrupled.

I since reverted the upgrade from backup (2.0.9).

Is this what you were experiencing? If so I will schedule some time to test this fix on our system.

[jsosic]

Load is extremely high when server starts, but after a while (~30 min) it drops significanlty. But, please increase ValueCache to 64MB and report back if that's the case. If you keep the ValueCache small, load is indeed 3-4x higher then with 2.0.

[stalks]

It seems my issue was fixed in 2.2.1. I have installed 2.2.1 and the load issues I was experiencing in 2.2.0 seem to of gone.

I tried ValueCache at default 8M and 32M and didn't notice any difference.