Is there a way to improve the Assets performance of the last step?

[Stefan Stadler]

Hi team,

I hope you are doing fine.

I have a problem with importing a larger number of objects.

On some large object types, I got importing time, which is taking an hour or even longer for about 50000 objects (some with references to even larger object types).

Most of the time (more than 95% of the overall time) is spent within the last step to create the references.

Are you aware of any ways to improve the performance of this step?

Thanks!

[Dan C]

Hello - We are having very similar problems to you.

Any import that involves objects that have references to other objects tends to take an unacceptable amount of time, and sends our SQL server up to 100% CPU usage for the entirety of the mapping references part of the import.

In production, this sometimes runs for hours, and has a roll-on effect of stopping the entire rest of the application from working properly. We get major automation rule delays (rules running up to 30 mins later than they should).

We have a fairly powerful setup, with 3 nodes, and 1 node dedicated 100% to Assets and imports. The problem still rolls over to the other nodes, because it hits the single database so hard, so the other nodes can't make their requests in a reasonable amount of time.

We have done a LOT of performance testing, and now have a testbed set up with a dedicated instance, and DB server to test against. We see the same exact problem even on a completely un-used instance (ie. no users, no automation rules running, no other imports running etc)

We have tried the following:

- Major increase in JVM memory (and testing at various levels) - currently at 96GB, and we have tested between 12GB and 224GB.

- Adjustments to the virtual hardware - adding and removing CPU cores to both the app server and the DB server (MS SQL)

- Reducing contention on the VMWare host, so that we have less competition for CPU time.

- Adjusting the number of Assets parallelism threads everywhere from 2, to 100% of cores (26) and above (150% - 39).

- We have full JMX -based monitoring set up using prometheus and grafana

Findings:

- None of the above changes helped with larger import times.

- For some assets functions, the Jira app server shoots up to 100%, and DB is low.

- For others, SQL shoots to 100% and app server stays low.

- The Atlassian DB latency test tool, says everything is fine (https://confluence.atlassian.com/jirakb/test-database-performance-for-jira-server-54362302.html) but it has no tests for Assets specifically

- We have found a strong correlation between setting an attribute as "unique" on an objectType - causing imports to be multiple times slower.

- If we DON'T set an attribute to unique, then other bugs in Assets often allow duplicates to show up (for example, the bug that allows imports to run while indexing is still running during Jira startup) Then we have a lot of work to do to clean it all up.

We have more findings, but I'll stop here, and see if anyone has any more feedback.

We are using Jira 9.12.13 (SM 5.12.13) for this testing. We have the same issue with 9.4.x.

Reducing the data we are importing is not an option. The product needs to provide the ability to do the things it advertises. There are no documented restrictions on object counts, or import sizes as far as I'm aware.

We're trying to get to the bottom of:

1. Is this a hardware issue we can resolve by throwing more power at it

or

2. Is this a restriction in the way the application is written, that simply can't be overcome.

[Carla Ann Rowland]

on the import, with that amount of data then you can remove the fields that do not be imported. Also you might  write a filter to have it only import data that meets a specific criteria.

[Carla Ann Rowland]

data center and cloud process is same for the Asset tables just the layout of the import screens are slightly different

[Carla Ann Rowland]

the import process is same for data center and cloud

[Carla Ann Rowland]

The only way to do that is so save the configuration so it can be reused. then you can review the mapping instead of recreating the mapping

[Stefan Stadler]

Hi Carla,

can you please explain this in a little more detail?

And can you let me know, what exactly is causing this mapping to be that expensive? Maybe there is some way to improve the performance by some hardware tuning.

[Carla Ann Rowland]

when you import items into asset tables: you set up the import and then you map the fields from your csv to your asset fields this is saved. Here is a link: https://support.atlassian.com/jira-service-management-cloud/docs/create-objects-from-data-using-object-type-mapping/

[Stefan Stadler]

Hi Carla,

thanks for that. I noticed this document is for Cloud only. And it basically describes how to link objects to other objects. That is what I am doing, which is why the last step (Updating references) is executed at all.

We are running on a Server instance, so the article does not apply completely, but if I did not misunderstood this, We are already doing this. This is the configuration we are running. The object type LotID is referenced by another object type. Unfortunately LotID holds more than 600k objects. And also the new object type is holding about 30k objects.

I am a little concerned about the amount of references to be updated. It seems the import updates all objects available even if almost nothing has changed. Is this the expected behaviour? Or did I miss anything in configuration, which is forcing this to happen?

Thanks for your help!

[Carla Ann Rowland]

Sorry took so long to get back to you, "thinking out loud"; if swr  is unique and you are gathering those unique as criteria 1 and then you are looking for Lot that is also unique. so the combination of swr and lot are unique to one record. if you put check mark next to lot and turn off the case sensitivity that might make it run faster because it knows the combo of swr and lot together are a unique id instead of doing pass 1 swr and then lot. (fingers crossed)