We are currently using basic notifications, but struggling to get more than 2 records a second out of a DB in the UK. From a similar DB in RSA, we are getting 100 or more records a second with the same config/settings. We suspect network North/South is impacting the performance, but we can’t see where or why the notification/JDBC adapter is throttling itself so much because of assumed network latency.
It seems that this component has a mind of its own with regards to managing the number of records it processes and appears to be a bit of a black box. Any suggestions as to how we can peak inside and understand why it is behaving so poorly, when normal SQL select and update queries on those tables isn’t actually that slow?
Latency is between 100ms to 400ms depending on time of day, but we don’t see any variation in the processing speed either up or down.
Went through the JDBC adapter documentation, and the only thing we can see that we have deviated from, is that we are using the actual table rather than a separate buffer. We are changing this as we suspect this is part of the issue (i.e. 500k records in select, versus the 50-180k new records which were actually added). However, there is no performance difference between a total 100k table vs the 500k table, still 2 a second.
We also were informed that the UK DB is SQL Server 2005, where as the local server is 2014. This could also be an issue, as our SQL guru says triggers on 2005 where many performance issues reported. This trigger (not ours) is added on the update, and we don’t know what else it is doing, so it is possible this could be causing issues with the update/and thus the NDT publishing.
We have contents of UK DB copied to RSA Server, and will be running tests now to see if the version and location change make any noticeable performance improvements.
We will then implement the buffer table for the basic notif as suggest by the documentation, and then retest to see if that improves. We need that fix regardless.
Thanks for your updates Robert. Also try to check the performance by making use of Oracle DB instead of SQL server which may give more than what you expecting.
Not sure what you mean by “abobe”?, presume we are at latest Fix level
SQL 2005 in UK, 2014 in RSA.
@MR - SQL Server is default for client, so Oracle test won’t really.
Update:
After a full copy of 2005 DB made to local site, we ran a test and performance improvement of 2000% was seen from a throughput perspective. However, when we ran another test from the same server but just a different DB name, performance degraded back down to 2-4 records a second.
So location of server and network latency is not the issue based on the above, and there must be some setting we are not implementing correctly to improve the throughput.
We tried adding the buffer table the correct way as per the documentation, but this too did not see any improvement in performance, even with small record sets of 1000 to 5000 inserts.
We thought lack of Index and Primary key could be an issue, but after these were added, performance remained the same - no improvement. Record set in table was reduced, and also no improvement.
This leads me to believe, that webMethods JDBC Basic Notification is improsing a limiti on itself somewhere. We have upped available threads, increased connection pool size, increased trigger capacity to 1000 and concurrency setting to 100, and still only 2 records a second.
We have confirmed there are no issues with the queues and downstream processing of messages (i.e. able to clear queues more rapidly than we can fill them). Is there anybody who has been able to extract 100 records a second or more using Basic Notifications, and if so, can you share your settings and related config parameters?
Just a quick check is it like your buffer table created have say 500 records and webMethods notification trigger service is publishing only 2 records per seconds?
Can you please adapter notification "Maximum Row " property once.By default it have value set as “0” to pick all the records available in buffer table.
You are correct. If we load 100 records, 1000 records or 10000 records into the buffer table, we see the same throughput of 2 records per sec.
We have seen it behave differently only once or twice, picking up more records and processing more quickly, but it then resorts to the slow 2 records per second (i.e. when we copied the UK DB to RSA it ran quickly, but using the existing DB on the same SQL server - just a different logical DB name, and it ran again quite slowly, 2 records per second per IS server in the cluster = total 3-4 records per second).
Setting for “Max Row” is currently set unbounded on the notification adapter (aka 0), and if we set it to 1000 or smaller number, it still won’t process any faster (i.e. doesn’t seem to make any difference).
Are there any extended settings we should set, or priority settings which would re-prioritize this interface and force the adapter to process more quickly?
@RMG - 5 stars on beating support team. We are using sqljdbc4.jar, however we found that the Fix10 patch worked due to the following from Fix1 release notes:
JDB-1247 (JDBC_9.0_Fix1)
A performance related issue was discovered in JDBC. adapter services during internal performance testing.
This patch was already on DEV, but due to the fact that that box is overloaded already, it made no difference. However, after applying this to our clustered QA environment, performance increased from 2 records per sec upto 125 per sec (55 per sec on a 50 plus field notification) which is a 6250% increase
We still need to see this more repeatable in PROD as we have only tested a couple of times in QA. But considering we couldn’t get the adapter to even modify it’s behaviour at all, the result of apply the fix has been very welcome, even if not completely sustainable at those levels should volumes increase and performance drops off slightly.
@RMG - Thanks again. We had assumed patch levels were same as DEV but was obviously overlooked by the ops team.
