Steps to Reproduce
- Customer or alert identifies instance performance degradation or inaccessibility.
- Support identifies that the instance is very slow for users because the semaphores are all used up by requests to xmlhttp.do.
- The xmlhttp.do is causing requests to back up because they are running hundreds of queries, such as the following:
SELECT live_message0.`sys_id` FROM live_message live_message0 WHERE ((live_message0.`reply_to` IS NULL AND live_message0.`group` = '7cf072f40fb721009799e64be1050ef2') OR live_message0.`sys_id` IN (SELECT live_message0.`reply_to` FROM live_message live_message0 WHERE live_message0.`group` = '7cf072f40fb721009799e64be1050ef2')) AND live_message0.`last_activity` > '2014-10-21 09:22:36' limit 0,1
These queries take several seconds to finish and have no supporting indexes. Adding indexes on live_message (sys_created_on) and on live_message (last_activity) resolves the issue.
To resolve the issue, add indexes on live_message (sys_created_on) and on live_message (last_activity).
Related Problem: PRB610812