Slow Transactions with Large Amount of sys_user_visibility Records


Description

We are experiencing extreme slowness connecting to instance with specific users on transactions

Cause

2019-11-11 08:04:54 (641) REST API (deprecated -- see REST_API_INT-thread-178 D1673B651B49089034850D4ACD4BCBB9 txid=26f458b91bc9 WARNING *** WARNING *** Large Table: Table handling an extremely large result set: 864515
Query that got us here is: TABLENAME = sys_user_visibility ENCODED_QUERY = user=d72bf0642b725100cd0e26e405da152d
com.glide.db.QueryWarning.warn(QueryWarning.java:44)
com.glide.db.DBQuery.loadResultSet(DBQuery.java:3169)
com.glide.db.DBQuery.executeAndReturnTable(DBQuery.java:323)
com.glide.db.DBAction.executeNormal(DBAction.java:236)
com.glide.db.DBAction.executeAndReturnException(DBAction.java:190)
com.glide.db.RDBMSQueryContext.executeQuery(RDBMSQueryContext.java:42)
com.glide.db.DBQuery.execute(DBQuery.java:2636)
com.glide.db.meta.Table.queryBasic(Table.java:315)
com.glide.db.meta.Table.query(Table.java:201)
com.glide.script.GlideRecordITable.query(GlideRecordITable.java:101)
com.glide.script.GlideRecord.query0(GlideRecord.java:3257)
com.glide.script.GlideRecord.query(GlideRecord.java:2982)
com.glide.script.GlideRecord.queryNoDomain(GlideRecord.java:2960)
com.glide.db.domain.AbstractDomainProvider.buildSessionUserVisibilityList(AbstractDomainProvider.java:305)
com.glide.db.domain.AbstractDomainProvider.getCurrentUserVisibleDomains(AbstractDomainProvider.java:277)
com.glide.db.domain.AbstractDomainProvider.getVisibleDomains(AbstractDomainProvider.java:239)
com.glide.db.domain.AbstractDomainProvider.getDomainSetForQuery(AbstractDomainProvider.java:385)
com.glide.db.domain.AbstractDomainProvider.query(AbstractDomainProvider.java:177)
com.glide.db.domain.DomainSupport.query(DomainSupport.java:878)
com.glide.db.DBQuery.separate(DBQuery.java:2690)
com.glide.db.DBQuery.execute(DBQuery.java:2631)
com.glide.db.meta.Table.queryBasic(Table.java:315)
com.glide.db.meta.Table.query(Table.java:201)
com.glide.script.GlideRecordITable.query(GlideRecordITable.java:101)
com.glide.script.GlideRecord.query0(GlideRecord.java:3257)
com.glide.script.GlideRecord.query(GlideRecord.java:2982)
com.glide.connect.conversation.ConversationDataStore.getDetailsMap(ConversationDataStore.java:219)
com.glide.connect.conversation.ConversationDataStore.mapProfilesToProfileIDs(ConversationDataStore.java:107)
com.glide.connect.conversation.ConversationDataStore.fillGroupMembers(ConversationDataStore.java:178)
com.glide.connect.conversation.ConversationDataStore.fromGroupIDs(ConversationDataStore.java:42)
com.glide.connect.conversation.ConversationGRLoader.fromMemberships(ConversationGRLoader.java:77)
com.glide.connect.conversation.ConversationGRLoader.loadAll(ConversationGRLoader.java:54)
com.glide.connect.conversation.ConversationService.getAllConversations(ConversationService.java:66)
sun.reflect.GeneratedMethodAccessor3351.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
com.glide.rest.handler.impl.ServiceHandlerImpl.invokeService(ServiceHandlerImpl.java:43)
com.glide.rest.processors.RESTAPIProcessor.process(RESTAPIProcessor.java:286)
com.glide.processors.AProcessor.runProcessor(AProcessor.java:532)
com.glide.processors.AProcessor.processTransaction(AProcessor.java:230)
com.glide.processors.ProcessorRegistry.process0(ProcessorRegistry.java:178)
com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:167)
com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:31)
com.glide.sys.Transaction.run(Transaction.java:2091)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:748)

Resolution

Most Probable Cause: The user created over 800,000 sys_user_visibility records for one user /sys_user_visibility_list.do?.
When any page is attempted to be loaded these records are reviewed to check visibility and it takes minutes to query and loop through all these records.

There is normally just a handful of sys_user_visibility per users. We would recommend removing this user from your dev instance or recloning based on your testing cycle.

Table Cleaner is the safest method to remove these records, it could take a few days but limits the impact.
https://support.servicenow.com/kb_view.do?sysparm_article=KB0717791