[ZBX-15513] upgrade zabbix from mysql 5.1 to 5.7, cpu load too high Created: 2019 Jan 25  Updated: 2019 Jan 31  Resolved: 2019 Jan 31

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 3.0.23
Fix Version/s: None

Type: Incident report Priority: Blocker
Reporter: Wang Jian Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

centos 6.5 /8core/16G mysql and zabbix server location different servert


 Description   

Steps to reproduce:

  1. Due to business requiremets, i upgrade zabbix mysql from 5.1 to mysql-community-server-5.7.17

Result:
mysql server cpu load too high, the i check zabbix server log, it have no errors. My Teams DBA found some SQL like blow take up a lot of CPU.

SELECT clock,ns,VALUE FROM history_uint WHERE itemid=1809973 AND clock>1548387847 AND clock<=1548388147 ORDER BY clock DESC LIMIT 1

But when we do not use "ORDER BY clock DESC LIMIT 1" , it's run fast.  Thanks for your support
Expected:
See screenshot....
See attached patch file...

 Comments   
Comment by Edgar Akhmetshin [ 2019 Jan 25 ]

Hello Wang,

Please, add this option and check:

mysql cli> SET optimizer_switch='index_condition_pushdown=off';

Also don't forget to add this option to the MySQL configuration file.

Regards,
Edgar

Comment by Aigars Kadikis [ 2019 Jan 30 ]

Hello Wang Jian,

To integrate this setting globally set into '/etc/my.cnf.d/server.cnf':

[mysqld]
optimizer_switch=index_condition_pushdown=off

Restart the database daemon. Please let us know how it goes.

Comment by Wang Jian [ 2019 Jan 31 ]

it's ok now, thank you for you strong support!

Generated at Thu Apr 25 16:35:23 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.