| Author |
Message
|
| Venkat10 |
 Posted: Mon Jan 30, 2017 2:35 pm Post subject: {Solved}HTTP - Database throughput issues |
 |
|
Novice
Joined: 10 Mar 2014
Posts: 19
|
We are running a WebService operation using HTTP input and reply node in IIB 9.2
This operation is selecting some columns from the database and sending back a response in XML. Request variable comes in a Query Sting.
If I ran this WebService from SOAP UI response times are between 20ms to 30ms but when there is a significant load running like more than 1200 transactions per minute we are seeing huge delays sometimes it is greater that 50 seconds and it's affecting the throughput.
Increased Additional instances, maxthreads, poolsize but still experiencing the same issue.
If anyone experienced the same can you please suggest if I am missing something which can increase the throughput for the database operations. This service is just doing selects with nolock on sql 2014 server.
Last edited by Venkat10 on Fri Feb 17, 2017 12:03 pm; edited 1 time in total |
|
| Back to top |
|
 |
| zpat |
| Posted: Tue Jan 31, 2017 12:15 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5849
Location: UK
|
You could re-design the flow to reduce the number of database calls by caching information in memory (either RYO or using the global cache).
Even a simple approach of checking whether you have just made an identical request and re-using the reply may help. External (shared) variables can be used to share information between flow invocations.
Unless these calls are updating the database, there will be a way to dramatically reduce the number of calls made. There is no point just running more instances etc, this is just flogging a bad design to death.
You could also tune the database to hold more of the table information in memory. The key is to avoid I/O operations wherever possible. Also check the database is indexed properly and use fast disks (ideally SSD).
_________________
Well, I don't think there is any question about it. It can only be attributable to human error. This sort of thing has cropped up before, and it has always been due to human error. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jan 31, 2017 12:37 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
The problem is likely to be in the DB itself
As has been suggested, it needs tuning.
I can't remember if SQLServer 2014 has the ability to lock tables in memory. If it does then get the DBA's to implement it. Unless it is really huge and would basically crash the DB server.
In any case go and get on very good terms with your DBA's.
Get them to tell you why their end is taking so long to answer queries and how it can be improved.
If nothing can be done then you know the performance limit of your solution already.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Tue Jan 31, 2017 2:14 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
Are these transactions read only ?
as already said, bottleneck can be in the db. If the query is reading only data from tables (not update/insert) then using an in-memory database is another option (if there is an very high volume of transactions) or memcached (or similar).
Changing to a noSql database is another option.
just my two cents
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
|
| Venkat10 |
| Posted: Fri Feb 17, 2017 11:56 am Post subject: |
|
|
Novice
Joined: 10 Mar 2014
Posts: 19
|
We were able to resolve this by adding more memory to SQL Server.
When we are performing the test SQL CPU was good but memory is between 80-90% once we increased the memory number of concurrent connections increased drastically and able to resolve this throughput issue. |
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Mon Feb 20, 2017 1:52 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
| Venkat10 wrote: |
We were able to resolve this by adding more memory to SQL Server.
When we are performing the test SQL CPU was good but memory is between 80-90% once we increased the memory number of concurrent connections increased drastically and able to resolve this throughput issue. |
I'm always scared about these solutions, from my experience:
1) You have an incorrect and really really bad sized environment for the usage expected (maybe nobody even knewn it would be so heavily used)
2) adding more RAM, disk, whatever hardware is more likely to add fuel to the fire 
Not saying that your solution is wrong but ...pay attention, probably there is a train over there coming in your direction 
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
|
| zpat |
| Posted: Mon Feb 20, 2017 2:54 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5849
Location: UK
|
Much better to reduce the number of SQL calls, than it is to add resources to the SQL server.
If you were driving your car with sticking brakes, the answer is not to increase the engine power....
_________________
Well, I don't think there is any question about it. It can only be attributable to human error. This sort of thing has cropped up before, and it has always been due to human error. |
|
| Back to top |
|
|
|
|
