Query causing CPU Spike to 100% after MYSQL upgrade
Greetings!
We recently upgraded the kernel from Ubuntu 4.4.0-92-generic to 4.4.0-103-generic. Together with that, mysql got an upgrade from mysql "5.7.19" to "5.7.20".
Current setup:
mysql Ver 14.14 Distrib 5.7.20
Server:
x86_64, Ubuntu 4.4.0-103-generic
# of CPUs -> 2
Issue:
The query below no longer works (worked OK before the upgrade) and causes the CPU to spike to 100%:
mysql>
mysql> select distinct cast(`subtype_values`.`language_override` as char(500) charset latin1) AS `doc_type`,cast(`cffir`.`value` as char(400) charset latin1) AS `doc_type_code` from (((((`col_xxxyyyzzztracking` `cct` join `col_flexicontent_items_tmp` `cfit`) join `col_flexicontent_fields_item_relations` `cffir`) join `col_flexicontent_fields` `cff`) join `col_flexicontent_types` `cft`) join `aabb_ws_doc_subtype_mapping` `subtype_values`) where ((1 = 1) and (`cfit`.`type_id` = `cft`.`id`) and (`cffir`.`item_id` = `cct`.`item_id`) and (`cffir`.`field_id` = `cff`.`id`) and (convert(`cffir`.`value` using utf8mb4) = `subtype_values`.`subtype_value`) and (`cft`.`name` = ('Document - Product, Processing and Application')));
After the upgrade, this query doesn't complete,and the CPU spikes to 100%.
Temporary fix we have is by replacing the "= ('Document - Product, Processing and Application'" with "like ('Document%Product, Processing and Application'", as below:
mysql>
mysql> select distinct cast(`subtype_values`.`language_override` as char(500) charset latin1) AS `doc_type`,cast(`cffir`.`value` as char(400) charset latin1) AS `doc_type_code` from (((((`col_xxxyyyzzztracking` `cct` join `col_flexicontent_items_tmp` `cfit`) join `col_flexicontent_fields_item_relations` `cffir`) join `col_flexicontent_fields` `cff`) join `col_flexicontent_types` `cft`) join `aabb_ws_doc_subtype_mapping` `subtype_values`) where ((1 = 1) and (`cfit`.`type_id` = `cft`.`id`) and (`cffir`.`item_id` = `cct`.`item_id`) and (`cffir`.`field_id` = `cff`.`id`) and (convert(`cffir`.`value` using utf8mb4) = `subtype_values`.`subtype_value`) and (`cft`.`name` like ('Document%Product, Processing and Application')));
+-----------------------------------+-----------------+
| doc_type | doc_type_code |
+-----------------------------------+-----------------+
| General Information | ProductDocType2 |
| Product Brochure | ProductDocType5 |
| Published Posters | ProductDocType6 |
| Product Process Parameters | ProductDocType3 |
| Published White Papers / Articles | ProductDocType7 |
| Product Application Data | ProductDocType1 |
| Product Process Parameters | ProductDocType4 |
+-----------------------------------+-----------------+
7 rows in set (2.70 sec)
mysql>
Any ideas as to why the "="/ "equal" operator fails to work as before, while the "like" operator works?
Subject
Views
Written By
Posted
Query causing CPU Spike to 100% after MYSQL upgrade
1541
December 11, 2017 02:16PM
599
December 11, 2017 09:21PM
564
December 12, 2017 09:13AM
491
December 12, 2017 02:52PM
524
December 13, 2017 12:29PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.