Have you ever used such architecture?
There are local Broker & IS and remote database. When a JDBC adapter hosts on the local IS performance of adapter notifications working with the remote database is inappropriate 2-4 records in a second. The same adapter notification working with a local database loads about 30 records/ second.
I was recommended to set up Adapter Run Time (ART) near the remote database. As I understood the ART is a truncated version of IS 6.1 (it is because ART is embedded into IS). This ART would host the JDBC adapter and publish documents (containing lists of record I suppose) on our local broker.
Have you ever used such architecture? Is it really good for performance? Is it worth spending money for an additional license for ART?
Yevgen,
The network performance doesn’t sound correct. There should not be that kind of latency in a normal switched network(latency should be less than 10ms). How remote is the remote database? Are we talking WAN?
If your latency is that bad then putting the jdbc adapter closer is not going to help because you still have to get the message back to the IS server over that same poor performing network. The JDBC adapter is normally very fast, it selects all records at once and then writes them individually to the broker and then deletes them one at the time from the buffer table as they are persisted to the broker. Is your sql optimized?
I personally would not spend the money on an additional license for this particular problem without doing a whole lot of performance testing and tuning first.
Yep, we are talking about WAN. The distance doe not make difference but if you are interested WM server is in UK but SQL server is in Russia and we cannot make anything with this. The latency is really 90 - 120 ms.
I was talking about Adapter notification. From my experience it retrieves records from a temporary table one by one. Even if 10 000 records are inserted.
Here are the elements I had in my tests.
SQl table -> wm Temporary table -> publishable document (automatically created by the adapter notification) -> a trigger -> an empty flow service
‘SQl table -> wm Temporary table’ <1 sec
‘wm Temporary table -> publishable document’ <took all the time
I would insert 1000 records into the source table and watched how WM removes records from the temporary table. The speed was 2-4 records in a second. That is all.
You see there cannot be any problem with SQL. It just runs WM trigger and it takes it < 1 second. The problem is on WM side. WM processes recordes from the temporary table one by one.
I wonder if it possible to make WM process records in a batch?
When I would create an adapter notification WM Developer 6.1 created an appropriate publishable document automatically. Such a document contained just a list of fields but not a list of documents. I wonder if it is possible to make WM create a list? Is it possible?
Thanks in advance for your answer.
Okay, serious distance here. I think your first idea is better. Put the adapter at site and have the brokers communicate. SQL across WAN not so good. I would put an IS server and broker on the remote site and connect the brokers via a gateway. Better reliability and performance.
As far as the way the JDBC notification works - It selects all available records in the buffer table at one time but does not delete them. It then has them in memory in the IS server and writes them one at the time to the broker. After the single record is written to the broker, the broker sends an ack back the IS server which in turn deletes the individual record from the buffer table. It then processes the next record. It works that way to eliminate the possibility of duplicate records on an IS or broker failure(although it is still technically possible but small chance).
If you want to decrease the volume of records it selects at one time, you can try and set the poller to a smaller number. If you can set the poller rate to match the data insertion rate on the buffer table, you might get better throughput. This will bring back less traffic and possibly speed up your retrieval time.