This document contains the results of testing Data Import for MARC Bibliographic records at Sunflower release [non-ECS] - CSP1.
In scope is testing of 1K,5K,10K,25K,50K, 100K data import create and update.
Ticket CSP1: PERF-1194: [Sunflower] [non-ECS] [Data import] Update and Create MARC BIB RecordsClosedPreview

Ticket CSP2 PERF-1218: [Sunflower-CSP2] [non-ECS] [Data import] Update and Create MARC BIB RecordsClosedPreview

• All tests passed successfully in both CSP1 and CSP2

• CSP1:

  • Duration for 5k, 10k, 25k MARC BIB Create around the same;

  • Duration for 50k AND 100k MARC BIB Create 14% AND ~29% slower than Sunflower GA accordingly;

  • Duration for all PTF - Updates Success - 1 profile are faster;

  • With disabled mod-search duration for 50k AND 100k MARC BIB Create are faster compared to Ramsons with disabled mod-search;

  • Results for 50k AND 100k MARC BIB Create on another tenant (fs07000001) showed much faster results due to a smaller dataset.

• CSP2:

  • Durations are either the same or better than CSP1, and a little slower than Sunflower GA

  • CSP2 Updates are faster than CSP 1 likely due to a mod-search query that was running in CSP 1 (which created locking waits in the DB) not running in CSP 2.

  • CSP2’s Create imports MSK CPU utilizations are much lower compared to CSP 1 Create imports.

min

Results of additional DI PTF - Create 2 profile test runs for 50k AND 100k:

Memory utilization showed stable trend during DI creates and updates tests in CSP1 and CSP2

CSP 1	CSP 2

CSP 1	CSP 2
Open	Creates Open
	Updates Open

CPU utilization is stable for all modules during all tests in both CSP 1 and CSP 2. CPU utilization in CSP 2 of all modules are slightly higher than in CSP 1. It’s notable that mod-tlr and mod-requests-mediated were active during DI jobs (see graphs and chart below). In the case of mod-tlr, the activities were mostly Kafka related, setting offsets. More investigations is needed to understand why mod-tlr and mod-requests-mediated ran during Data Imports.

CSP 1	CSP 2

CSP 1	CSP 2
Open	Creates Open
	Updates Open

CPU usage of DB is high (as usual during data import process).

CSP 1 (Creates and Updates)	CSP 2 (Creates top, Updates bottom)

CSP 1 (Creates and Updates)	CSP 2 (Creates top, Updates bottom)
Open	Open
	Open

Without test load is about 1380. When DI Create 1520, DI Update 1500 connections.

CSP1 Creates and Updates	CSP2 Creates and Updates

CSP1 Creates and Updates	CSP2 Creates and Updates
Open	Open

CSP 1 (Creates and Updates)	CSP Creates

CSP 1 (Creates and Updates)	CSP Creates
Open	Open
	Open

At first glance the DB load graphs of both CSP1 and CSP2 are pretty much the same in amplitudes, meaning that DI creates and update imports in both versions incurred about the same average number of active sessions, but upon a closer look we can see that in CSP1 updates there are lock waits, whereas there were no lock waits in CSP 2. When examining the Top SQL queries below, it’s clearer that CSP 1’s locking was due to a mod-search query (with cte…), whereas CSP 2 didn’t have this query running. Together these two facts explain why update imports in CSP 2 were a little bit faster.

Top SQL

CSP 1 Create and Updates	CSP 2 (Creates top, Updates bottom)

CSP 1 Create and Updates	CSP 2 (Creates top, Updates bottom)
Open	Open
	Open

Deadlocks for CSP 1 and CSP 2

Further evidence of deadlocking happening in CSP 1 updates but not in CSP 2

CSP 1 (Updates)	CSP 2 (Creates and Updates)

CSP 1 (Updates)	CSP 2 (Creates and Updates)
Open	Open

No deadlocks detected in all the DI jobs, Create and Updates.

It’s remarkable that CSP 2’s create imports are did not use much MSK CPU compared to CSP1 (the left group of spikes in the graph below). For updates both versions used the same level of CPU.

CSP1	CSP2

CSP1	CSP2
Open	Open

PTF -environment secp1