performance of db_out adapter

[Former Member]

Hi,

I need help on performance esp adapter db_out .  [ESP 5.1 SP08]

I have a problem statement like-

"I have one esp model that gets continuous data from ibm message queues.

these continuous messages are processed by esp dynamically  and results are written into 40 different output windows.

then esp model send the content of these 40 output windows to oracle 11g database using db_out continuously.

This whole thing works fine for limited time but when I keep pumping the data for longer time then these output windows

slow down while sending data to oracle 11g database.

for example initially these output windows/adapters sends data by say 500 records per second but after some time this reduces

to say 20-30 records per second.

I have referred the esp client monitors log and seen that their[output windows] queue is completely filled.[max 1024]

Means ESP is processing data faster then the output windows send data back to database.

because of this in my esp model their is logjam kind of condition.

esp model is not able to take any further data from ibm mq's till it sends its output windows data to oracle 11g database.

"

Now I need to improve this logjam kind of condition so that

Data can be sent to database quickly and esp should not be facing any logjam condition.

Please help me with your inputs and suggestions.

Regards

Adesh

[andrzej_kucharczyk · Accepted solution]

Hi Adesh,

db_out adapter performs best when it can consistently send large batches or rows. If your incoming data has frequent opcode changes (from insert to delete to update etc) new batch is started for everyone opcode change and data is likely transferred in small number of rows at a time. If you can control the order of your incoming data please arrange it in way that groups rows with same opcode together. For example first do all inserts, then any updates, then any deletes if at all possible without affecting the end result.

Another issue that often affects overall performance is that some publishers perform SDK commit operation too frequently. This causes entire project to block while all the queues are drained. That blocks all publishers that feed the data. Try doing commits less frequently. This will give adapter a chance to fully fill in their batches and transfer more data at a time.

Regards,

Andrzej.

[Former Member]

Hi Andrzej,

How can I make grouping in esp model file as per the operation types[update,inserts etc].

I have looked at the esp server log and find out that the order of execution/update or insert is pretty non linear . Like I am having almost 40 something output windows  which sends data to database.

I tried to make an order of execution but its like there are n numbers of db_out operations written to database at a time.

how to make grouping as per the operation type ??

thanks

regards

Adesh

[andrzej_kucharczyk]

Hi Adesh,

Actually what I had in mind is having the publishers that feed the model try to group their operations. That would be most efficient way since doing it in Java or C is faster than in CCL/Splash.

If that's not possible then another option would be to create a flex stream between window and the adapter. The flex stream would have some splash logic to for example collect updates in a vector (for lets say 2 minutes or for some maximum size) and only let inserts through while you collect updates. Then every 2 minutes or when your desired max size is reached let the updates through by iterating through your vector and then clearing it. Similarly for deletes. You would only have to do that for windows that get most traffic and where you see slow performance. Be aware that this approach will consume more memory. You might have to limit how long you collect your updates and vary it depending on traffic level you get in each window.

Also for each adapter make sure your batchLimit property is set to a large value. I would start with 10000. Then try increasing it to see if it makes a difference. Be aware that having large batch size might affect your latency. Same with collecting rows in flex stream.

Regards,

Andrzej.