Unusual CPU high usage in Myrocks

[xvinh]

Our myrocks server run well for ages, but some days ago, CPU high usage occurred, it made our system stalled in some minutes, after that period, the system become well as normal.

Attached images show CPU usage and Top SQL Queries. You can see that each time the our second SQL run, it also make some temp tables, the top 2 queries accounted for nearly 100% CPU usage of the whole system.

We also gathered a slow SQL queries and rerun it again, but the SQL run very fast, and there is no unusual thing happen.

Did you see this before? Please advise what happened in our system and let me know how to solve it?

We are using Ubuntu 18.04.4 LTS, 20 vCores CPU, 10Gb memory, 20Gb Swap, Percona server 8.0.19-10-1.bionic, Percona Monitoring and Management.

[Ivan_Groenewold]

Hi, it is difficult to troubleshoot an issue like this without a full investigation on the system. Regarding the top queries did you notice any change or anything unusual in the query plan?

I suggest you consider the possibility of doing a health audit https://www.percona.com/store/percona-mysql-health-audit

[xvinh]

Slow log is

Time: 2020-12-03T03:26:26.069747Z

User@Host: ihcm[ihcm] @ [172.16.20.214] Id: 2173

Query_time: 33.722727 Lock_time: 0.000743 Rows_sent: 0 Rows_examined: 3399940 Thread_id: 2173 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 10720 Read_first: 0 Read_last: 0 Read_key: 34 Read_next: 3399940 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2020-12-03T03:25:52.347020Z End: 2020-12-03T03:26:26.069747Z Schema: ihcm Rows_affected: 0

Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

Filesort: No Filesort_on_disk: No Merge_passes: 0

No InnoDB statistics available for this query

SET timestamp=1606965952;

SELECT a.ID, a.ORG_ID, a.COM_ID, a.LIBRARY_ID, a.TEMPLATE_TYPE, a.EMP_ID, a.WORKING_PLAN_ID, a.GOAL_ID, a.PROJECT_ID, a.NAME, a.DESCRIPTION, a.STATUS, a.COMPLETE_PERCENT, a.PROGRESS_METHOD, a.STATUS_PROCESS, a.STATUS_GROUP, a.IMPORTANT_LEVEL, a.HOURS, a.START_DATE, a.DUE_DATE, a.COMPLETE_DATE, a.LINK_FROM, a.UPDATE_TOTAL, a.COM_WORK, a.LABEL_ID, a.GOAL_KEY_RESULT_ID, a.LABEL_OBJECT, a.WORKING_CUSTOM_STATUS, a.WORKING_CUSTOM_STATUS_ID, a.TOTAL_FEEDBACK, a.CHECKLIST, a.CUSTOM_SHARING, a.VIEW_DETAIL_EMP_ID, a.STATUS_CANCEL, a.STATUS_CLOSED, a.GOAL_STATUS_CANCEL, a.MILESTONE, a.CREATED_BY, a.CREATED_DATE, a.MODIFIED_BY, a.MODIFIED_DATE, a.STRING000, a.STRING001, a.STRING002, a.STRING003, a.STRING004, a.STRING005, a.STRING006, a.STRING007, a.STRING008, a.STRING009, a.STRING010, a.STRING011, a.STRING012, a.STRING013, a.STRING014, a.STRING015, a.STRING016, a.STRING017, a.STRING018, a.STRING019, a.LONG000, a.LONG001, a.LONG002, a.LONG003, a.LONG004, a.LONG005, a.LONG006, a.LONG007, a.LONG008, a.LONG009, a.INTEGER000, a.INTEGER001, a.INTEGER002, a.INTEGER003, a.INTEGER004, a.INTEGER005, a.INTEGER006, a.INTEGER007, a.INTEGER008, a.INTEGER009, a.BOOLEAN000, a.BOOLEAN001, a.BOOLEAN002, a.BOOLEAN003, a.BOOLEAN004, a.BOOLEAN005, a.BOOLEAN006, a.BOOLEAN007, a.BOOLEAN008, a.BOOLEAN009, a.DATE000, a.DATE001, a.DATE002, a.DATE003, a.DATE004, a.DATE005, a.DATE006, a.DATE007, a.DATE008, a.DATE009, a.FLOAT000, a.FLOAT001, a.FLOAT002, a.FLOAT003, a.FLOAT004, a.FLOAT005, a.FLOAT006, a.FLOAT007, a.FLOAT008, a.FLOAT009, a.DATETIME000, a.DATETIME001, a.DATETIME002, a.DATETIME003, a.DATETIME004, a.DATETIME005, a.DATETIME006, a.DATETIME007, a.DATETIME008, a.DATETIME009, a.TEXT000, a.TEXT001, a.TEXT002, a.TEXT003, a.TEXT004, a.TEXT005, a.TEXT006, a.TEXT007, a.TEXT008, a.TEXT009, a.TEXT010, a.TEXT011, a.TEXT012, a.TEXT013, a.TEXT014, a.TEXT015, a.TEXT016, a.TEXT017, a.TEXT018, a.TEXT019, a.LIST000, a.LIST001, a.LIST002, a.LIST003, a.LIST004, a.LIST005, a.LIST006, a.LIST007, a.LIST008, a.LIST009, a.MULTI000, a.MULTI001, a.MULTI002, a.MULTI003, a.MULTI004, a.MULTI005, a.MULTI006, a.MULTI007, a.MULTI008, a.MULTI009, d.NAME AS KEY_RESULT_NAME, b.NAME AS GOAL_NAME, e.NAME AS KEY_RESULT_PARENT_NAME FROM TBL_GOAL_WORKING a INNER JOIN TBL_GOAL b ON (b.ID = a.GOAL_ID) LEFT JOIN TBL_GOAL_KEY_RESULT d ON (d.ID = a.GOAL_KEY_RESULT_ID) LEFT JOIN TBL_GOAL_KEY_RESULT e ON (d.PARENT_ID = e.ID) WHERE a.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND b.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND (b.PLAN_ID IN (‘94Yv3OcivZHLoXjDoA01c03F’, ‘8TEY2aq7h5GZoXjDoA01c03F’, ‘7vY47HoJV7OCoXjDoA01c03F’, ‘7X1XaNrJPniVoXjDoA01c03F’, ‘7aS06DcjpEmSoXjDoA01c03F’, ‘8W31VMYh7hv9oXjDoA01c03F’, ‘8TGaNWSmleMJoXjDoA01c03F’) AND a.STATUS = 2 AND a.GOAL_ID = ‘7aRs8nFx4TWKoXjDoA01903F’ AND a.ID IN ((SELECT DISTINCT r.DESTINATION_ID FROM HFW_OBJECT_RELATION r WHERE r.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND (r.TYPE = 21 AND r.DESTINATION_OBJ_ID = null AND r.RELATION_ID IN (‘7X1XZlQ2zQmzoXjDoA03403F’, ‘7hMm8gY2hMT6oXjDoA03403F’, ‘7X1XZlVvRTiRoXjDoA03403F’, ‘7hW6d8RxcJuxoXjDoA03403F’, ‘7XUPXouoz3neoXjDoA03403F’, ‘8CcA6YwQaDnjoXjDoA03403F’)) )));

The current plan is

explain SELECT a.ID, a.ORG_ID, a.COM_ID, a.LIBRARY_ID, a.TEMPLATE_TYPE, a.EMP_ID, a.WORKING_PLAN_ID, a.GOAL_ID, a.PROJECT_ID, a.NAME, a.DESCRIPTION, a.STATUS, a.COMPLETE_PERCENT, a.PROGRESS_METHOD, a.STATUS_PROCESS, a.STATUS_GROUP, a.IMPORTANT_LEVEL, a.HOURS, a.START_DATE, a.DUE_DATE, a.COMPLETE_DATE, a.LINK_FROM, a.UPDATE_TOTAL, a.COM_WORK, a.LABEL_ID, a.GOAL_KEY_RESULT_ID, a.LABEL_OBJECT, a.WORKING_CUSTOM_STATUS, a.WORKING_CUSTOM_STATUS_ID, a.TOTAL_FEEDBACK, a.CHECKLIST, a.CUSTOM_SHARING, a.VIEW_DETAIL_EMP_ID, a.STATUS_CANCEL, a.STATUS_CLOSED, a.GOAL_STATUS_CANCEL, a.MILESTONE, a.CREATED_BY, a.CREATED_DATE, a.MODIFIED_BY, a.MODIFIED_DATE, a.STRING000, a.STRING001, a.STRING002, a.STRING003, a.STRING004, a.STRING005, a.STRING006, a.STRING007, a.STRING008, a.STRING009, a.STRING010, a.STRING011, a.STRING012, a.STRING013, a.STRING014, a.STRING015, a.STRING016, a.STRING017, a.STRING018, a.STRING019, a.LONG000, a.LONG001, a.LONG002, a.LONG003, a.LONG004, a.LONG005, a.LONG006, a.LONG007, a.LONG008, a.LONG009, a.INTEGER000, a.INTEGER001, a.INTEGER002, a.INTEGER003, a.INTEGER004, a.INTEGER005, a.INTEGER006, a.INTEGER007, a.INTEGER008, a.INTEGER009, a.BOOLEAN000, a.BOOLEAN001, a.BOOLEAN002, a.BOOLEAN003, a.BOOLEAN004, a.BOOLEAN005, a.BOOLEAN006, a.BOOLEAN007, a.BOOLEAN008, a.BOOLEAN009, a.DATE000, a.DATE001, a.DATE002, a.DATE003, a.DATE004, a.DATE005, a.DATE006, a.DATE007, a.DATE008, a.DATE009, a.FLOAT000, a.FLOAT001, a.FLOAT002, a.FLOAT003, a.FLOAT004, a.FLOAT005, a.FLOAT006, a.FLOAT007, a.FLOAT008, a.FLOAT009, a.DATETIME000, a.DATETIME001, a.DATETIME002, a.DATETIME003, a.DATETIME004, a.DATETIME005, a.DATETIME006, a.DATETIME007, a.DATETIME008, a.DATETIME009, a.TEXT000, a.TEXT001, a.TEXT002, a.TEXT003, a.TEXT004, a.TEXT005, a.TEXT006, a.TEXT007, a.TEXT008, a.TEXT009, a.TEXT010, a.TEXT011, a.TEXT012, a.TEXT013, a.TEXT014, a.TEXT015, a.TEXT016, a.TEXT017, a.TEXT018, a.TEXT019, a.LIST000, a.LIST001, a.LIST002, a.LIST003, a.LIST004, a.LIST005, a.LIST006, a.LIST007, a.LIST008, a.LIST009, a.MULTI000, a.MULTI001, a.MULTI002, a.MULTI003, a.MULTI004, a.MULTI005, a.MULTI006, a.MULTI007, a.MULTI008, a.MULTI009, d.NAME AS KEY_RESULT_NAME, b.NAME AS GOAL_NAME, e.NAME AS KEY_RESULT_PARENT_NAME FROM TBL_GOAL_WORKING a INNER JOIN TBL_GOAL b ON (b.ID = a.GOAL_ID) LEFT JOIN TBL_GOAL_KEY_RESULT d ON (d.ID = a.GOAL_KEY_RESULT_ID) LEFT JOIN TBL_GOAL_KEY_RESULT e ON (d.PARENT_ID = e.ID) WHERE a.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND b.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND (b.PLAN_ID IN (‘94Yv3OcivZHLoXjDoA01c03F’, ‘8TEY2aq7h5GZoXjDoA01c03F’, ‘7vY47HoJV7OCoXjDoA01c03F’, ‘7X1XaNrJPniVoXjDoA01c03F’, ‘7aS06DcjpEmSoXjDoA01c03F’, ‘8W31VMYh7hv9oXjDoA01c03F’, ‘8TGaNWSmleMJoXjDoA01c03F’) AND a.STATUS = 2 AND a.GOAL_ID = ‘7aRs8nFx4TWKoXjDoA01903F’ AND a.ID IN ((SELECT DISTINCT r.DESTINATION_ID FROM HFW_OBJECT_RELATION r WHERE r.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND (r.TYPE = 21 AND r.DESTINATION_OBJ_ID = null AND r.RELATION_ID IN (‘7X1XZlQ2zQmzoXjDoA03403F’, ‘7hMm8gY2hMT6oXjDoA03403F’, ‘7X1XZlVvRTiRoXjDoA03403F’, ‘7hW6d8RxcJuxoXjDoA03403F’, ‘7XUPXouoz3neoXjDoA03403F’, ‘8CcA6YwQaDnjoXjDoA03403F’)) )));

±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------------------------------+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |

±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

The current execution time is

SELECT a.ID, a.ORG_ID, a.COM_ID, a.LIBRARY_ID, a.TEMPLATE_TYPE, a.EMP_ID, a.WORKING_PLAN_ID, a.GOAL_ID, a.PROJECT_ID, a.NAME, a.DESCRIPTION, a.STATUS, a.COMPLETE_PERCENT, a.PROGRESS_METHOD, a.STATUS_PROCESS, a.STATUS_GROUP, a.IMPORTANT_LEVEL, a.HOURS, a.START_DATE, a.DUE_DATE, a.COMPLETE_DATE, a.LINK_FROM, a.UPDATE_TOTAL, a.COM_WORK, a.LABEL_ID, a.GOAL_KEY_RESULT_ID, a.LABEL_OBJECT, a.WORKING_CUSTOM_STATUS, a.WORKING_CUSTOM_STATUS_ID, a.TOTAL_FEEDBACK, a.CHECKLIST, a.CUSTOM_SHARING, a.VIEW_DETAIL_EMP_ID, a.STATUS_CANCEL, a.STATUS_CLOSED, a.GOAL_STATUS_CANCEL, a.MILESTONE, a.CREATED_BY, a.CREATED_DATE, a.MODIFIED_BY, a.MODIFIED_DATE, a.STRING000, a.STRING001, a.STRING002, a.STRING003, a.STRING004, a.STRING005, a.STRING006, a.STRING007, a.STRING008, a.STRING009, a.STRING010, a.STRING011, a.STRING012, a.STRING013, a.STRING014, a.STRING015, a.STRING016, a.STRING017, a.STRING018, a.STRING019, a.LONG000, a.LONG001, a.LONG002, a.LONG003, a.LONG004, a.LONG005, a.LONG006, a.LONG007, a.LONG008, a.LONG009, a.INTEGER000, a.INTEGER001, a.INTEGER002, a.INTEGER003, a.INTEGER004, a.INTEGER005, a.INTEGER006, a.INTEGER007, a.INTEGER008, a.INTEGER009, a.BOOLEAN000, a.BOOLEAN001, a.BOOLEAN002, a.BOOLEAN003, a.BOOLEAN004, a.BOOLEAN005, a.BOOLEAN006, a.BOOLEAN007, a.BOOLEAN008, a.BOOLEAN009, a.DATE000, a.DATE001, a.DATE002, a.DATE003, a.DATE004, a.DATE005, a.DATE006, a.DATE007, a.DATE008, a.DATE009, a.FLOAT000, a.FLOAT001, a.FLOAT002, a.FLOAT003, a.FLOAT004, a.FLOAT005, a.FLOAT006, a.FLOAT007, a.FLOAT008, a.FLOAT009, a.DATETIME000, a.DATETIME001, a.DATETIME002, a.DATETIME003, a.DATETIME004, a.DATETIME005, a.DATETIME006, a.DATETIME007, a.DATETIME008, a.DATETIME009, a.TEXT000, a.TEXT001, a.TEXT002, a.TEXT003, a.TEXT004, a.TEXT005, a.TEXT006, a.TEXT007, a.TEXT008, a.TEXT009, a.TEXT010, a.TEXT011, a.TEXT012, a.TEXT013, a.TEXT014, a.TEXT015, a.TEXT016, a.TEXT017, a.TEXT018, a.TEXT019, a.LIST000, a.LIST001, a.LIST002, a.LIST003, a.LIST004, a.LIST005, a.LIST006, a.LIST007, a.LIST008, a.LIST009, a.MULTI000, a.MULTI001, a.MULTI002, a.MULTI003, a.MULTI004, a.MULTI005, a.MULTI006, a.MULTI007, a.MULTI008, a.MULTI009, d.NAME AS KEY_RESULT_NAME, b.NAME AS GOAL_NAME, e.NAME AS KEY_RESULT_PARENT_NAME FROM TBL_GOAL_WORKING a INNER JOIN TBL_GOAL b ON (b.ID = a.GOAL_ID) LEFT JOIN TBL_GOAL_KEY_RESULT d ON (d.ID = a.GOAL_KEY_RESULT_ID) LEFT JOIN TBL_GOAL_KEY_RESULT e ON (d.PARENT_ID = e.ID) WHERE a.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND b.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND (b.PLAN_ID IN (‘94Yv3OcivZHLoXjDoA01c03F’, ‘8TEY2aq7h5GZoXjDoA01c03F’, ‘7vY47HoJV7OCoXjDoA01c03F’, ‘7X1XaNrJPniVoXjDoA01c03F’, ‘7aS06DcjpEmSoXjDoA01c03F’, ‘8W31VMYh7hv9oXjDoA01c03F’, ‘8TGaNWSmleMJoXjDoA01c03F’) AND a.STATUS = 2 AND a.GOAL_ID = ‘7aRs8nFx4TWKoXjDoA01903F’ AND a.ID IN ((SELECT DISTINCT r.DESTINATION_ID FROM HFW_OBJECT_RELATION r WHERE r.ORG_ID = ‘7X1XZIxGfeiUoXjDoA00003F’ AND (r.TYPE = 21 AND r.DESTINATION_OBJ_ID = null AND r.RELATION_ID IN (‘7X1XZlQ2zQmzoXjDoA03403F’, ‘7hMm8gY2hMT6oXjDoA03403F’, ‘7X1XZlVvRTiRoXjDoA03403F’, ‘7hW6d8RxcJuxoXjDoA03403F’, ‘7XUPXouoz3neoXjDoA03403F’, ‘8CcA6YwQaDnjoXjDoA03403F’)) )));

Empty set (0.01 sec)

So clearly I notice changes in the query plan