ZABBIX Forums  
  #1  
Old 18-07-2017, 17:53
HarryKalahan HarryKalahan is offline
Member
 
Join Date: Jan 2014
Location: Spain
Posts: 34
Default Slow Query problem in Zabbix 3.2.6

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:

Quote:
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!
Attached Files
File Type: txt zabbix_server.txt (16.2 KB, 132 views)
File Type: txt zabbix_my.txt (1.6 KB, 129 views)
Reply With Quote
  #2  
Old 21-07-2017, 12:05
HarryKalahan HarryKalahan is offline
Member
 
Join Date: Jan 2014
Location: Spain
Posts: 34
Default 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:

Quote:
optimizer_switch = 'index_condition_pushdown=off'
Best regards!
Reply With Quote
  #3  
Old 05-12-2017, 13:47
Rodney Rodney is offline
Junior Member
 
Join Date: Jun 2008
Posts: 14
Default

Hi, Harry!

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

Did you solve it changing the parameter "optimizer_switch"?

Thank you.
Reply With Quote
  #4  
Old 05-12-2017, 13:58
HarryKalahan HarryKalahan is offline
Member
 
Join Date: Jan 2014
Location: Spain
Posts: 34
Default

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!
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +2. The time now is 07:56.