146 views

Description

Live Feed is routing some requests for non-conversation data to the conversations API. The parameters provided to the conversations API are invalid and the result is not relevant to the requestor. Further, the conversation API does not properly validate inputs and can generate unbounded queries against live_group_profile. The subsequent processing of such an unbounded query can take minutes to complete if the result set is large enough (1000s of rows) and/or cause high memory consumption on the application node running the transaction.

Steps to Reproduce

The steps to reproduce are dependent on the number of conversations a user is a part of (which will produce the large result set returned by the "/api/now/live_feed/conversations" transaction).

Symptoms

1. Slow "/api/now/live_feed/conversations" request:

0:C724A10813A626C404FCD9722244B010 #23244957 /api/now/live_feed/conversations (Default-thread-10) (2:26:51.889)

This is caused by a long running query against live_group_profile:

2016-11-22 21:35:24 (971) Default-thread-12 B994440113132200F7EBBC122244B0EF Time: 0:01:00.721 id: database_1 [glide.30] for: /* node014, gs:B994440113132200F7EBBC122244B0EF, tx:8e73788113df2200f7ebbc122244b010 */ SELECT live_group_profile0.`sys_id` FROM live_group_profile live_group_profile0 WHERE live_group_profile0.`type` != 'team' AND live_group_profile0.`document_group` = 1 AND live_group_profile0.`sys_id` IN (SELECT live_group_member0.`group` FROM live_group_member live_group_member0 WHERE (live_group_member0.`state` IN ('admin' , 'active') AND live_group_member0.`member` = 'd476d89df8b5254084b3a5f027c9d60a')) AND (live_group_profile0.`visible_group` = 1 OR live_group_profile0.`sys_id` IN (SELECT live_group_member0.`group` FROM live_group_member live_group_member0 WHERE (live_group_member0.`state` IN ('active' , 'admin' , 'invited' , 'request') AND live_group_member0.`member` IN ('49af5b5813596200c5e476022244b0e1' , 'd476d89df8b5254084b3a5f027c9d60a'))))

2.  Out of memory on node processing the long running "/api/now/live_feed/conversations" transaction. This leads to slow performance for all users on the affected node until either the transaction is cancelled or the node is restarted.

In the localhost logs you will see the messages, "Compacting large row block"  and "Expanding large row block," while processing the results of the above query against live_group_profile:

2016-10-13 17:03:10 (014) http-22 New transaction C724A10813A626C404FCD9722244B010 #23244957 /api/now/live_feed/conversations
2016-10-13 17:03:10 (034) Default-thread-10 C724A10813A626C404FCD9722244B010 #23244957 /api/now/live_feed/conversations Parameters -------------------------
api=api
sysparm_auto_request=true
cd=1476403389944
2016-10-13 17:03:10 (042) http-47 New transaction C724A10813A626C404FCD9722244B010 #23244958 /livefeed.do
2016-10-13 17:03:10 (086) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.028] Compacting large row block
2016-10-13 17:03:10 (128) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.030] Compacting large row block
2016-10-13 17:03:10 (176) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.032] Compacting large row block
2016-10-13 17:03:10 (221) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.033] Compacting large row block
2016-10-13 17:03:10 (268) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.034] Compacting large row block
2016-10-13 17:03:10 (315) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.031] Compacting large row block
2016-10-13 17:03:10 (356) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.029] Compacting large row block
2016-10-13 17:03:10 (423) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.055] Compacting large row block
2016-10-13 17:03:10 (462) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.028] Compacting large row block
2016-10-13 17:03:10 (504) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.031] Compacting large row block
2016-10-13 17:03:10 (543) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.027] Compacting large row block
2016-10-13 17:03:10 (583) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.028] Compacting large row block
2016-10-13 17:03:10 (624) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.031] Compacting large row block
2016-10-13 17:03:10 (666) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.031] Compacting large row block
2016-10-13 17:03:10 (674) Default-thread-10 C724A10813A626C404FCD9722244B010 WARNING *** WARNING *** Large Table: Table handling an extremely large result set: 145637 >>>> This is the query against live_group_profile
2016-10-13 17:03:10 (690) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.016] Compacting large row block
2016-10-13 17:03:10 (695) Default-thread-10 C724A10813A626C404FCD9722244B010 [0:00:00.005] Expanding large row block

 

Note: PRB670678 also addresses performance issues for "/api/now/live_feed/conversations" transactions. However, their root cause and fix is different. If you do not see the above symptoms, refer toPRB670678.

 

Workaround

Temporary relief

Add a before query Business Rule on live_group_profile to fix the parameters of the affected query (see attached Business Rule from development team).

  • Download the Business Rule attached to this PRB (sys_script_bf3e2fab374321009a80a0ffbe41f1cb.xml).
  • Navigate to System Definition > Business Rules, right-click on the list header, and select Import XML. Then import the "sys_script_bf3e2fab374321009a80a0ffbe41f1cb.xml" XML file.

Permanent relief

If you are able to upgrade, check the Fixed In field below to determine the versions that have a permanent fix.


Related Problem: PRB718120

Seen In

Geneva Patch 7
Geneva Patch 9
Geneva Patch 9 Hot Fix 1

Fixed In

Jakarta

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-05-11 03:38:41
Published:2017-02-22