Slow Query problem in Zabbix 3.2.6

[HarryKalahan]

Hi all,

We've recently migrate an installation with Zabbix 2.0.10 and MySQL 5.6.13 to Zabbix 3.2.6 and MySQL 5.7.18. We didn't have problems in a small enviroment, but in large we are experiencing problems with too many slow queries everytime:

23231:20170718:161916.830 slow query: 10.811605 sec, "select clock,ns,value from history_uint where itemid=227901 and clock<=1500387351 and clock>1500383751 order by clock desc limit 1"
23231:20170718:161923.804 slow query: 6.943898 sec, "select clock,ns,value from history_uint where itemid=216364 and clock<=1500387351 and clock>1500383751 order by clock desc limit 1"
23231:20170718:161931.567 slow query: 7.598017 sec, "select clock,ns,value from history_uint where itemid=217767 and clock<=1500387351 and clock>1500383751 order by clock desc limit 1"

I've been changing a lot of parameters in my.cnf without success. We receive more than 1000 new values per second.

I trying to keep the configuration of the previous installation as possible, but it seems we need to allocate more memory or something we are doing bad.

I attach here my.cnf and zabbix_server.conf parameters so that you can help me. This config worked well in Zabbix 2.0.

Any help would be apreciated.

Best regards!

[HarryKalahan]

Slow Query problem in Zabbix 3.2.6

Hi all,

I answer myself. There's known bug of MySQL for versions 5.6 and 5.7 with queries ORDER BY LIMIT: https://bugs.mysql.com/bug.php?id=74602

Experts of Zabbix comment this case here: https://support.zabbix.com/browse/ZBX-10652

The provided solution is disabling this parameter over the optimizer switch in my.cnf:

optimizer_switch = 'index_condition_pushdown=off'

Best regards!

[eangly]

hi bro,
when i insert this command then my zabbix can't start DB

[Rodney]

Hi, Harry!

I think that I´m having a problem exactly like yours.

Did you solve it changing the parameter "optimizer_switch"?

Thank you.

[HarryKalahan]

Hi Rodney!

Yes, that solves the problem. We use that configuration in producction enviroments and works very well.

In spite of the fact that, try to look for the best configuration for your MySQL Server, sizing the InnoDB buffer pool, large-pages and other parameters in base of the server resources. That's really important for the best performance.

Good luck!