The requested behavior will not be possible. but we can look into the issue why CMDBOutput step throughput is very slow.
please check if you have followed the below recommendation if now can you create a support ticket so we can have look.
Things to take when running AI for large volumes of Data where Source Data is > 100K.
Bare Minimum Hardware:
RAM: 8 GB
JVM: 64 bit.
It is strongly recommended that 32 bit systems are not used for running large integrations (>250K CIs). The best practices to run integrations are on a separate box with 64 bit hardware and operating system as specified above.
Configuration changes on the Plugin:
1> Initial loads where we are inserting data for the first time in CMDB, we can optimize performance by selecting options:a> Insert Always (Provided data in source is Unique) b> Select Cache Required option.
Cache size default is 1M that needs to be changed to 4M if the volume of data is about 2-4M.
2> For incremental loads one has to disable Insert Always option so that the modified records are updated appropriately.
3> Number of threads on the CMDBOutput plugin should not exceed 10. For smaller sized machines, use a lower number.
4> Number of threads on CMDBLookUp plugin should always be set to 1.
5> Carte server should be run with JVM options of -Xmx and -Xms set to 4096m.
6> If run from Spoon always run the Transformatin/Job with Logging mode set to Minimal. In Carte, it would be good to have logging disabled???? what is the default?
7> For Relationships as an best practise we can always turn of Updates.
Thanks & Regards
Hi Velan, I have used this without problems.
yes I agree if you use AROutput with associated remedy join form it will work no issue in that but if you want to create a relation ship for this data then it will not be possible with AR Steps.
Thanks & Regards
Why is not possible creating relationships with AROuput?
Relationship is just another remedy join form right?
Why can't we lookup source and dest instanceIDs and use those in the ARInput.
Any Input Step will not pass the previous Step data to next one.it only pass the current data which it fetched based on the qualification.
Lookup - step will only check for the existence of CI in specific class depending on the keys mentioned in the look up values if the record with specific key values are found it will then return the attributes
Thanks & Regards
You can provide the CI names of source and destination CI into the ARInput and do the lookup for the instance IDs as the next step in your transformation.
With this approach overall I don't think the performance will improve . My suggestion use the same behavior as it is with CMDBOutput I am sure that give much more performance if you follow the best practice which I have suggested.
I can tell you the DB Tuning part also. because we have seen a good performance with CMDBOutput.
using ARInput and AROutput on the class level you may see a better performance but at the relationship side i don't think so.
Thanks & Regards
The problem with CMDBOutput is, all the calls from CMDBOutput are going through the RPC:390696.
And this queue is working as single threaded.(no matter what my server settings are)
Even if I setup multiple threads for RPC 390696(min 3 max 10) in Admin Console Ports and Queues and do multiple restarts, still only single thread is initiated for this queue.
I tried with multiple CMDBOutput copies as well.
All CMDBOutput calls are seems to be serialized in a single thread, hence is causing the latency.
I tried in multiple environments and I see the same behavior where all CMDB calls are single threaded.
Where as AROuput is using multiple list and fast threads and we are seeing better throughput.
So we are preferring to use AROuput to create CIs and relationships.
We do few lookups to get the instanceIDs of source and destination and pass them to AROutput step to create relationships.
Here is the thread for more details where I discussed those details.
No need to set the Min and Max for this RPC 390696. this is the CMDB RPC and it internally use the AR Fast or List Thread based on the call like Create Instance or GetListInstance.
If you can get me the AR API and SQL Log in single file then I can Look into that and provide you the input.
I think it some other process is using the fast thread and due to that this RPC call is get only one thread.
because I tried setting the AR Fast Thread to Min 2 and Max 12 and I saw it was picking 5 thread as below.
CI is the Create Instance call.it did total 61 create Instance.
CI 0000003984 13 13 0.0150 8618 1.1770 8188 0.1503 1.9550 0000004960 10 10 0.0160 8842 1.0340 8290 0.1484 1.4840 0000004892 12 1 13 0.0010 10401 1.0320 8316 0.1173 1.5260 0000005144 13 13 0.0160 8790 1.0270 8238 0.1146 1.4900 0000005196 13 13 0.0150 8816 1.0200 8264 0.1570 2.0420 Total CI 61 1 62 8.4970
Have you tried creating queue 390698 with multiple threads and configure the connection so that it uses this queue?
I checked this on my system the step is using this queue.
have you tried the above recommendation to improve the CMDBOutput Plug-in whether that is resolved your issue are still you are seeing the same throughput 6 CI per sec. if yes can you create a support ticket so we can have a look.
Thanks & Regards
Thanks Velan for your support.
We are running some tests to compare against AROutput and CMDBOuput by changing threads,copies etc...
Will certainly update the thread in couple of days with the results and will create ticket if necessary.
Just curious to know what is your outcome of the test to compare or changing the thread copies of CMDBOutput?
Thanks & Regards
We ran few tests and found that the earlier reason for very less through put was due to lookup step (CMDBLookup) not due to CMDBOutput.
We changed it from CMDBLookup to DBLookup and also increased the number of copies on both lookup and output step. That resulted in much higher throughput.
Earlier we were just increasing the number of copies on CMDBOutput only and that did not help much.
So the bottleneck was really the lookup not the output.
The only outstanding issue with CMDBOutput was that the throughput is slowing down significantly after certain time.
a) We created two simple transformations for an apple to apple comparison between CMDBOuput Vs AROuput.
Transformation-1, Table Input with 20,000 rows and CMDBOutput
Transformation-2, Table Input with 20,000 rows and AROutput
With both options we saw throughput started with as high as 100 rows/sec.
Not much difference between AROuput and CMDBOuput.
Throughput of both AROutput and CMDBOuput were keep on fluctuating and then stayed relatively constant after certain time.
For ex: From first record to 10,000 rows, we saw a lot of fluctuation (increasing and decreasing) on throughput, after that it was relatively constant.
CMDBOutput started with for ex: 2600 rows/min throughput and after 10,000 rows it slowed down to 650 rows/min.
AROuput started with for ex: 2600 rows/min throughput and after 10,000 rows it slowed down to only 1800 rows/min.
So seems like AROuput is performing better on bigger datasets.
Not sure though what caused the CMDBOutput to slowed down a lot compared to AROuput. We saw this consistently.
b)Increasing bulk commit size:
We varied the CMDBOuput bulk commit size to 100,200,500 etc... and we did not see much use.
Ex: The 500 row commit transaction took around 4.5 times of what 100 commit size took.
So very minimal gain.
c)Increasing the Threads in ARServer:
We kept number of CMDBOutput copies constant, and increased the number of threads in ARServer Ports and Queues config.
We did see more threads being used by CMDBOutput in the SQL logs, however the throughput did not increase much.
May be a bottleneck at the database side.
We identified a weird issue with AROuput so we could not replace CMDBOuput with AROutput.
We transformations using AROuput on both regular remedy forms and CMDB forms(ComputerSystem etc...) hoping to replace CMDBOuput with AROuput.
The option we chose was to Upsert.(Update the row if same key exists in CMDB, Create new row if the key did not match)
Some how the AROuput is not handling the Update part correctly for the data loads into CMDB form.
It was always creating new CIs even though same CI already exists within the same dataset.
It was able to handle updates properly with data in the regular form.
It was able to identify a matching row and able update instead of creating new one in a regular form.
We double verified all the options selected and the query qual etc in AROOuput step.
We felt weird why would AROutput would behave differently between regular remedy forms Vs CMDB forms.