High Latency for SCD DSS Requests

[wing-utm-sharing-airspace]

Describe the bug
We've noticed timeouts on various DSS SCD API calls (exceeding 10s) in several of our DSS pools. Looking at the CRDB console for one instance of this issue, I'm seeing a lot of transaction restarts and very high tail latency for SCD Subscription queries in particular:

This looks somewhat similar to #1241.

To Reproduce
Unsure the best way to reproduce but will provide some context about the deployment and traffic levels where we've seen this issue:

Below screenshot shows QPS overlayed with latency spike:

This is a pooled deployment where 3 providers each have 3 CRDB nodes.
We're running DSS v0.20.0 and CRDB v24.1.3

Expected behavior
This is instance is receiving fairly spikey, high load compared with what we need to support our current operations but would appreciate investigation into the restarts and high latency to determine system limitations and available performance improvements.

Additional context
Sample of logs from around the time of the latency spike:
downloaded-logs-20251205-124443.json

[the-glu]

Hello,

What the approximate size of the scd_subscriptions table?

Do you use the command to clean the table? If I'm correct, in v0.20.0 it's via https://github.com/interuss/dss/tree/interuss/dss/v0.20.0/cmds/db-manager/cleanup and is not done automatically.

[the-glu]

Looking at the code, SELECT id FROM scd_subscriptions may come from the function that acquire a lock on relevant subscriptions, I'm wondering if there a possibility of a deadlock with the spike of concurrent operations, I will have a look.

Edit: Based on #1002 and it last comment, I would think that definitively worth investigating

[the-glu]

I created #1318 that should improve the lock issue, however I didn't find a good way to test & reproduce the situation.

Would it be possible to test with similar traffic pattern to verify if the fix is helping @wing-utm-sharing-airspace ? If needed we can build an image with the fix.

[wing-utm-sharing-airspace]

Sorry for the late response - we ran the cleanup tool a couple of weeks ago but I don't believe we run it super regularly so possible there's a ton of subscriptions building up in those tables. I'll look into that as well.

Is it possible to patch #1318 and the fixes from #1241 into a v0.20.x release? We can attempt to validate both with similar load to when we observed the issues.

Thanks for the quick turn around here!

[wing-utm-sharing-airspace]

Our subscriptions table had 7479 rows

[barroco]

@wing-utm-sharing-airspace may I kindly ask you to request a release using the following link ? Thank you

[gc-2412]

@wing-utm-sharing-airspace
Hi
We wanted to flag and correlate a recent DSS cancellation timeout observed in one of our production key-site environments, which appears consistent with the behavior being tracked here. We’re sharing this instance for correlation and to help validate frequency and conditions under which this occurs.

Summary of observation:

Event type: Operation cancellation
Observed behavior: HTTP timeout on DSS interaction
Timestamp: 2025-12-24 15:09:46.375 UTC
Operation ID: a21e2962-502b-4854-a2b1-3147086f22a0
Outcome: Cancellation ultimately completed successfully in the DSS after the timeout

[wing-utm-sharing-airspace]

I was able to write a script to send Upsert Intent requests at different QPS in an attempt to validate the proposed fixes and better reproduce the performance issues we've been observing.

Attached are results using both DSS v0.20.0 and v0.20.2. I also tested using only implicit subscriptions (which IIRC are roughly equivalent to the proposed intent volumes) and then additionally using a larger subscription encompassing all proposed intent volumes. Performance with the larger subscription saw a break down in performance around 15 QPS where with just implicit subscriptions, we saw intermittent issues up at around 25 QPS. Performance looked pretty similar to me between the two DSS releases across the board.

at > 20 QPS, we saw Unavailable Errors from the DSS even if requests were completing relatively quickly. Let me know if there's any additional data I can collect that would be helpful.

DSS Load Test - Sheet1 (1).pdf

[the-glu]

@wing-utm-sharing-airspace

Thanks for testing, would it be possible to share your test script? I tried to make my own and test locally but didn't managed to reproduce the issue, and I don't know if it dues to the different environment or test script.

Edit: Extra question: Do you still see transactions restarts on v0.20.2 when reaching the limit?

[wing-utm-sharing-airspace]

Here's the script - I modified some of it to just comments describing the logic where it was fairly specific to internal libraries or leveraged pieces of our USS implementation so please let me know if anything is unclear. The default flag values represent what I was using to run the script with the exception of updating the QPS value per run.
dss_load_test_script.rtf

Just re-ran the script with 20 QPS for v0.20.2 and am still seeing some transaction restarts though looks like a lot more transactions and less restarts than the original screenshot reporting this issue:

[the-glu]

Thanks, I will adapts my tests with similar load.

The fact that v0.20.2 have more transactions and less restart may be a good sign, maybe we did partially fixed the issue or we do have more that one issue and we fixed one of them. I hope I will be able to reproduce it locally for more investigations.