performance of db_out adapter
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.
Andrzej Kucharczyk replied
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.