Millions of flow executions & imported records without crashing?
Yes, we can!
Maik Skoddow, CCB1124
Import�4 Mio Records
Hundreds of millions �of operations & transformations
Instance is healthy & performant
Challenges
Speaker introduction
Name: Maik Skoddow
Title: Solutions Architect
Company: T-Systems International
Based in: Dresden, Germany
Experience: 22 years in the IT business in various roles
Expertise: 4.5 years with ServiceNow, CTA, 4x CIS, CAD
Interests: botanics, plant processing, outdoor activities
LinkedIn Connect
Agenda
Project & Initial Situation |
Classic Import Approach |
Implemented Solutions� - Import� - After Burner |
|
1
2
3
Project & Initial Situation
FSM implementation for "Best Mobile“, which is responsible for the maintenance of the mobile infrastructure at Germany
Existing software systems running in emergency mode
250 technicians must be dispatched to over 34,500 locations
Goal: automate scheduling of ca. 50,000 work orders per year
Numerous regulations & requirements from various interest groups
1
Classic Import Approach
2
Staging
3
Target
1
Data Source
Databases
Files�(CSV, Excel)
Inbound�Requests
Import Set�Table
Target Table(s)
Extract
Transform & Load
Extract, Transform, Load (ETL)
Additional Helper Tables
2
Classic Import Approach
Downsides & Problems
Too slow as the import is running in one thread on one node only
Concurrent import sets cannot be leveraged
Image by Gino Crescoli from Pixabay
“Shadow” records blow up the database without any added value
Transform Maps are not required/helpful in our scenario
3
Implemented Solution
2
Staging
3
Target
1
Data Source
5 Elastic Indexes�one for each �target table
Extract
Load
4
Transform
Target
sysevent
Table
Location�~ 170,000 records
Location Access�~ 50,000 records
Location Blocking�~ 65,000 records
Operating Site�~ 256,000 records�
Network Element�~ 3.6 Mio records
Extract, Load, Transform (ELT)
Index Documents
~ 1.5 GB text data
4
Implemented Solution
Data Source
Store, search & analyze huge volumes of data in near real-time
Elasticsearch: indexing, search & analytics engine on Apache Lucene
Data is stored in documents (instead of tables) & grouped in indexes
Server that can process JSON requests and give back JSON data
5
Implemented Solution
Data Source
selected index
single index document with same field names as in ServiceNow
number of documents inside the index
6
Implemented Solution
Extract
N batches a 10,000 documents
1
2
3
10000
1
2
3
10000
1
2
3
10000
1
N
2
Table sysevent with 3 custom queues
data loading via N REST requests
Queue 1
Queue 2
Queue 3
gs.eventQueue(…);
1
2
3
4
6
7
8
5
9
7
Implemented Solution
Load
Application Node 2
Processor 1
Processor 3
Processor 2
Application Node 1
Processor 1
Processor 3
Processor 2
Queue 1
Source Table sysevent
Queue 2
Queue 3
Target Tables
Reading Data
Writing�Data
8
Implemented Solution
Insights into the sysevent Table
Example of a processed�event
Target Table
Payload of the Index Document
Application Node
9
Implemented Solution
Insights into the sysevent Table
Example situation during the “Extract” phase: The sysevent table is pumped with events containing index documents (number of state “ready” is growing)
Simultaneously the events are claimed by the application nodes and the transfer to the target tables is performed (“Load” phase)
Waiting for assignment to an application node
Assigned to an application node and waiting for processing
Already processed events (data is loaded)
10
Implemented Solution
Transform: The Challenge
Network Element
Location Blocking
Location
Location Access
Operating Site
u_sto_id�(foreign key)
u_sto_id�(primary key)
Product Model
Company
Work Order�Tasks
u_location_id�(reference)
11
Implemented Solution
Transform: After Burner
Container Subflow
Table Name
var strPrefix = 'global.after_burner_';
var strSubFlow = strPrefix + strTableName;
var grRecords = new GlideRecord(strTableName);
if (strQuery.length > 0) {
grRecords.addEncodedQuery(strQuery);
}
grRecords.query();
while (grRecords.next()) {
sn_fd.FlowAPI
.getRunner()
.subflow(strSubFlow)
.inBackground()
.withInputs({table_record: grRecords})
.run();
}
Trigger the execution of a so-called „Container Subflow“ for all records (identified by strQuery)
Worker Subflows
<= more Subflows might be added here
12
Implemented Solution
Transform: Benefits of using (Sub)Flows
(Sub)Flows are precompiled Java codes �🡪 superfast execution times
Executions of background (Sub)Flows are also queued in the sysevent table and executed by all available application nodes in parallel�🡪 optimal load distribution & overall performance
(Sub)Flows are easier to build, to debug and to test than pure code�🡪 perfect for Citizen Developers
New transformations (Worker Subflows) can be added to an existing Container Subflow with a no-code approach �🡪 isolated testing possible & lower risk to break existing functionalities
Image by Marc Pascual from Pixabay
13
Key Takeaways
Import millions of records without any issues by staging them in the sysevent table
Distribute load over all application nodes & utilize unused resources
Leverage the power of (Sub)Flows for millions of operations & data transformations
Image by Stefan Schweihofer from Pixabay
14
Documentation
Using custom queues to process events: �https://docs.servicenow.com/csh?topicname=queues-create.html&version=latest
Register an Event: �https://docs.servicenow.com/csh?topicname=t_RegisterAnEvent.html&version=latest
Q&A
Thank you
Insights into the sysevent Table
Implemented Solution
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
1
2
3
4
5
6
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
2
3
4
5
6
1
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
3
4
5
6
1
2
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
4
5
6
1
2
3
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
5
6
1
2
3
4
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
6
1
2
3
4
5
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
1
2
3
4
5
6
The sysevent table is a rotated table, acting as a bracket over a series of numbered tables. One table from the configurable rotation plan is regularly deleted, typically the one holding the oldest data.
With the next time-based rotation a new table is created, the logical numbering is shifted and the last table in the previous chain is dropped. This allows ServiceNow to quickly dispose of very large amounts of data without blocking or slowing down the system.
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
1
2
3
4
5
6
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
2
3
4
5
6
1
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
3
4
5
6
1
2
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
4
5
6
1
2
3
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
5
6
1
2
3
4
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
6
1
2
3
4
5
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
1
2
3
4
5
6
sysevent0000
sysevent0001
sysevent0002
sysevent0003
sysevent0004
sysevent0005
sysevent0006
1
2
3
4
5
6