USER COUNT 1500 concurrent
We are in the very beginning of the process to upgrade our hardware,
our storage solution. Our storage and SA guy/gal say that NetAPP AFF 300 all flash array is the way to go. ?
The other day I was trying to get the fastest possible response out of a SQL broker for OE. My plan is to use this as an indication of whether the database was online or not.
In addition to making a SQL92 database connection, I wanted a query response to be returned from the database broker. The fastest end-to-end response I could get out of OE was unimpressive (~150 ms) and I only attained this when the client code was running in a very tight loop. For my query I used "SELECT * FROM sysprogress.syscalctable". The vast majority of that time was consumed by a *CPU* bottleneck (in "_sqlsrv2" on HP-UX).
I think this could be a reasonable example of how the application software can become a massive bottleneck in an operation that might otherwise be a matter of sending a few network packets back and forth. I suspect that it was less than 1 ms that was actually spent waiting on network resources, and all the remaining delay was because of a "storage" engine bottleneck. (Granted no actual disk was ever used - that would have added even *more* overhead that was not network related.)
After my previous post, I tried a faster way to get that SELECT statement response back from the database and now my responses come in under 10 ms, which seems much more reasonable. It kept bothering me that my 150 ms duration was so extremely long - that was taking even longer than round-trips that use classic state-free appserver.
I discovered the reason for the delay: the ADO.Net method I was using ("FillSchema" on a data adapter) was doing quite a lot more work on the OE server than when I used another simple method ("ExecuteScalar").
Either way, the network latency appears to be a small proportion of the overhead. It is obvious that a lot of CPU is being used for these round-trips -- both on the server and on the client.
Even without a network to traverse a SQL-92 query is going to go through an awful lot of overhead and through multiple context switches to get executed.
Did you also connect and disconnect in your measurement?
Either way I'm kind of surprised it was only 150ms. (HPUX isn't exactly a fast platform these days.)
Software is, indeed, a huge part of the latency. When someone has a storage device external to their server there are many, many layers of software that handle the data. If there were not then one nanosecond per foot would probably be a decent measure of latency. But instead the data moves a few inches, gets processed within the the drive, moves a few more inches, gets processed by an adapter inside the storage array, moves a few more inches, gets processed by the fancy logic within the array that supposedly magically makes everything fast, then gets passed to a network adapter... travels for a few feet and runs into a switch which then processes it some more, repeat until we get to the server, then we go through a network adapter and on through to the CPU that asked for that data an eon or two ago. Oh, most of those layers also probably involve a queue and if your system is busy you probably get to wait in line a lot. And if you are virtualized you can add a few more layers.
> On Feb 15, 2018, at 2:14 PM, Tim Kuehn wrote:
> for the author to assert network assets are measured in nanoseconds contradicts the laws of physics
not at all. he did not say how many nanoseconds ! obviously a marketroid.
“less is my favorite editor. too bad it can’t actually edit files.”
if you want to tell if the sql server is online, send it the following:
select * from table_that_does_not_exist;
that’s the most efficient way.