XOG performance improvement for data migration

[venvzw]

We have two sets of Clarity instances which we are merging and as part of that we are migrating huge set of data using XOG. including Projects and all related dat, Ideas and all custom object data, OBS, partitions.

and the XOG is taking lot of times even when we divide and did it and getting timed a lot in the middle.

Just want to know if there is any recommendations or best practices at the server capacity level or database level or at config level to improve the XOG performance considerably to reduce the deployment time of data migration.

 

 

Any information is really appreciated

[Shoichi Sugawara]

Hi,

 

As you may know, design of XOG is changed after ppm13.3.

The XOG response is sent back to the client in chunks using pagination.

 

Please check the following manual.

 

XOG Governor Node Limit

https://docops.ca.com/ca-ppm/15-2/en/reference/xml-open-gateway-xog-development/xog-governor-node-limit 

 

Regards,

Shoichi

[Deactivated User]

There were presentations given in UK UG meetings on improving performance with large data sets.

The improvement was obtained with doing integration with spawned processes in order to reduce the time spend for just waiting. Another improvement was obtained with reducing writes to log.

[venvzw]

Thank you for your responses!

 

Could you please elaborate on "The improvement was obtained with doing integration with spawned processes in order to reduce the time spend for just waiting." what exactly needs to be done for this.

[turst07]

I have attached a copy of the presentation I made to the UK User Group back in 2015.

 

For my approach to work you will need to convert your loading scripts to run as GEL scripts in a process. These scripts will run on the source systems, build the XOG files, and then call XOG on the destination system. 

 

Also, the XOG data should be batched into multiple object instances in a single XOG file. E.g. 10/50/100 Projects/Resources/Custom Instances/etc per XOG file. Your mileage will vary so you will need to instrument your code and find the sweet-spot for your PPM system configuration.

 

I have also has some success with data loads where my GEL script ran outside of PPM/Clarity and used Threads within the script to send the XOG files. Throughput was impressive BUT getting all the Threads to synchonize properly to  a queue of available XOG files and catch errors was intermittent. I feel sure this approach would work if converted to Java but I have not had the time to investigate any further.

 

What all my experimentation has shown is that the XOG endpoint ('.../niku/xog') is capable of taking many simultaneous XOG files without choking as the limitation appears to be the raw processing speed of XOG and not any inherent limitations in the App code or it's interaction with the database.

Attachment(s)

Improving Large Volume Integration Performance_v02.pptx   320 KB 1 version

spawning-example_v002.gel.zip   6 KB 1 version

[venvzw]

Thank you very much!

[Roland Parrotte]

Additional points:

• Put in a database check to see if the data already exists and needs to be updated.  Do not include it again in the XOG if not required.  I found that just checking the data via XOG is a lot slower than a database read.  Though technically a record will just be set to the same values if XOGG'ed in twice, it will slow down the data migration significantly.  For example, we nightly export around 50,000 records from an external financial system which used to take around 3 hours to upload.  By checking to see if the record needs to be created/updated, this time has been reduced to around 2 mins due to the volume of data being presented which doesn't need to be updated again.
• Make sure the data being imported does not start a Clarity process - in one data migration I was involved in, the data imported than started off 'millions' of spawned process which then brought everything to a halt.  Just get the data migration scripts to do all the work, and put those processes on-hold when migrating the data.
• Though it is not supported, if migration is still taking too long, consider using XOG to create the shell of the record to be created, and then database update statement to update remaining attributes - for one client which had around 70 custom objects and very large volume of data to migrate, this approach had to be used as XOG was going to take several months, where as using database updates the whole data migration was able to be achieved in 3 days.
• Can't stress the importance of trying different batch sizes of XOG mentioned above by Stuart.  There is no magic formula that determines the batch size, though imagine it would be things like a Look-up attribute may require additional verification compared to a 'string' attribute.
• If you can get database-to-database access to the two environments, use it, so you can then write scripts that will check both environments to determine what data has been brought across and what remains to be brought across.
• Keep Look-Up Value ID's small in length if possible, otherwise it will increase the size of the XOG file required.  Make sure your functional team keep you updated when Look-ups are changed as the migration scripts will be using the Value ID's to migrate the data via XOG.
• Review the performance of the network, in particular, if you are undertaking it on SaaS where your server may be across the world.  Considering upload the XML/CSV file to the SaaS FTP server and getting the data migration processes to access it from there (ie FTP server and CA Background server will be at the same location).  Haven't undertaken any testing on this to see how much impact it does have on performance, but have used the FTP server in the last data migration projects worked on, just to be sure.

[venvzw]

Thank you very much for your reply!