MySQL Forums
Forum List  »  Performance

Query causing CPU Spike to 100% after MYSQL upgrade
Posted by: Duncan Muturi
Date: December 11, 2017 02:16PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Query causing CPU Spike to 100% after MYSQL upgrade
1034
December 11, 2017 02:16PM


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.