Long running Import Set Deleter job causing performance issues on the affected node

[Ankit K]

The Import Set Deleter job cleans records in the import tables. This is a default job which cleans the data of sys_import_set_row and its child tables as these tables tend to grow big very often. Sometimes there is a possibility, although rare, that orphan records are left in sys_import_set_row table and they are not cleaned in future scheduling of the job. This is a problem that was fixed in Fuji addressed out of memory exceptions that arise from MultipleDelete, which the Import Set Deleter job utilizes. However, the fixes did not completely prevent memory concerns for the particular job.

You could be affected by the issue if you are on certain early patches of Geneva up to Geneva Patch 9. It is also seen in Helsinki, up to Helsinki Patch 5. The severity may depend on the number of child tables that the import set row tables have for an instance.

Here are a few scenarios that may help determine if you may be affected by the issue:

1. Long-running import set delete job
2. JAVA in use memory goes up
3. Localhost logs statements

All 3 scenarios need to be experienced in order for this issue to apply.

Long running import set deleter job

In checking the stats page(/stats.do), you see that the Import Set Deleter was the longest running job on that particular node. Snippet from the stats.do glide.scheduler.worker.5 Current job: Import Set Deleter Job started: Tue Sep 06 18:00:02 PDT 2016 Job duration: 1:11:24.846 Total jobs: 7612 Mean duration: 0:00:01.276 The same can be checked via the sys_trigger table to understand which node this job has been running on. url: /sys_trigger_list.do?

JAVA HEAP in use memory is high / free memory low
Check for memory usage patterns for the affected node. If you notice that this is the only transaction running for long and the in use memory is on an uptick, make a note of that and save the information. Snippet from the stats.do Servlet Memory Max memory: 2022.0 Allocated: 2022.0 In use: 1926.0 Free percentage: 5.0

Local Host Logs statements

Download the node files and verify the logs for information/statements such as the ones below: Snippet from local host log files 2016-09-29 00:00:01 (609) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning import set ISET6612326 2016-09-29 00:00:01 (633) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=cf96b7b0db46aa00e93ff2e9af9619e6 2016-09-29 00:00:01 (637) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (639) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612283 2016-09-29 00:00:01 (641) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=55e8f7b4db8a2e4090b0ff1aaf96195c 2016-09-29 00:00:01 (645) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (647) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612284 2016-09-29 00:00:01 (649) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=f32b3738db8a2e4090b0ff1aaf961938 2016-09-29 00:00:01 (652) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (654) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612285 2016-09-29 00:00:01 (656) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=5a7d33b8db8a2e4090b0ff1aaf9619b0 2016-09-29 00:00:01 (659) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (662) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612286 2016-09-29 00:00:01 (664) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=81cf3f34db46aa00e93ff2e9af961975 2016-09-29 00:00:01 (667) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (669) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612287 2016-09-29 00:00:01 (671) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=5f02484ddb8a2e4090b0ff1aaf96193d 2016-09-29 00:00:01 (674) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (676) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612288 2016-09-29 00:00:01 (678) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=7d5484cddb8a2e4090b0ff1aaf961900 2016-09-29 00:00:01 (681) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (684) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612289 2016-09-29 00:00:01 (686) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=6ca60885db46aa00e93ff2e9af96194b 2016-09-29 00:00:01 (689) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (692) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612290 2016-09-29 00:00:01 (693) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=fee88c81dbca2e4090b0ff1aaf9619a6 2016-09-29 00:00:01 (696) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (699) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612291 2016-09-29 00:00:01 (701) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=d53bc8c5db46aa00e93ff2e9af96193e 2016-09-29 00:00:01 (705) worker.5 worker.5 *** Script: Import Set Cleaner:: .. 0 rows removed from ldap_import 2016-09-29 00:00:01 (707) worker.5 worker.5 *** Script: Import Set Cleaner:: Removing data from import set table ldap_import where import set=ISET6612292 2016-09-29 00:00:01 (709) worker.5 worker.5 *** Script: Import Set Cleaner:: Cleaning table ldap_import query:sys_import_set=088d0409db46aa00e93ff2e9af9619e9 .. .. ** Script: Import Set Cleaner:: Deleting orphaned import set row records if any by querying for import set sys_id being empty for table: ldap_import

Workaround long-running Import Set Deleter job causing performance issues on the affected node:

1. Navigate to Script Includes > ImportSetCleaner.

2. Change the line:
   this.cleanedImportSetRowTables.push(gr.table_name);
   to
   //this.cleanedImportSetRowTables.push(gr.table_name);

Once you change the ImportSetCleaner line, you will notice that the line is now commented out.

Releases this issue is currently fixed in:

• Geneva Patch 10
• Helsinki Patch 6
• Istanbul

We recommend that you upgrade to one of the releases, mentioned in ServiceNow KB: Import Set Deleter job causing instances to run low or out of memory, causing perform..., where the fix is found. Upgrading to one of these releases will prevent or improve performance caused by this particular issue.