This is a static archive of our old Q&A Site. Please post any new questions and answers at ask.wireshark.org.

Strange SQL traffic over WAN (link to csv file)

0

I'd like some opinions on a wireshark capture between a web server and SQL database server that takes place over our WAN. This is east coast to west coast. We are trying to troubleshoot issues with slow performance on this web application. It was developed in house. We already know we have some issues with our WAN connection to this location from time to time and also looking into that. I haven't found in issues on the LAN yet. Also, there are other dependencies but this seems to be the main bottleneck since it's the only one cross-country.

I'm not a packet guru, I do know the fundamentals of TCP. I'm seeing a lot of ACK's with no SYN's and multiple ACK's in a row. Is this a bad capture? Also, it's saturated with reassembled PDUs. The average MS for this connection is 90-100, which I know might be out of the range of tolerance for SQL and could be causing the problem. Can anyone take look at this capture and shed some light on what they're seeing?

https://drive.google.com/file/d/0By8V5doQZvoWR1k1VUN4Q0tJRG8/view?usp=sharing

web-srvr==10.10.10.200, db-srvr==10.0.0.50

asked 30 Aug '15, 13:44

Vontech615's gravatar image

Vontech615
11115
accept rate: 0%

edited 30 Aug '15, 13:45

If you want to look behind the Reassembled PDUs, then you could do the following:

On the Packet pane Select the tcp Header then click right mouse button.
Context Menu -> Protocol preferences -> Deselect "Allow subdissectors to reasemble..." 
(30 Aug '15, 14:46) Christian_R

One Answer:

2

We are trying to troubleshoot issues with slow performance on this web application. It was developed in house.

To me it looks like your application is not well designed to work over a WAN. Take a look at TCP stream 9 (the only one with some data to transmit). The client sends a SELECT and gets the answer pretty fast. But then it waits for ~ 2.5 seconds before it sends the next SELECT. These seconds wait time add up to a lot of "dead wait time" with the result, that the whole communication takes longer than it should.

Example:

frame #1462, 50.174: SELECT 
frame #1469, 50.461: last ACK for that request

~2.2 seconds wait time

frame #1480, 52.693: SELECT frame #1487, 52.977: last ACK for that request

~2.6 seconds wait time

frame #1488, 55.562: SELECT frame #1495, 55.821: last ACK for that request

As you can see. Between the SELECT and the last ACK for that request, there is a time delta of a few 100 ms, which is O.K. for a WAN link, especially as the SELECT statement is pretty large (~ 6KB).

However between the last ACK and the next SELECT, there is a pause of ~2.5 seconds!!

You can see that in the TCP Stream graph as well.

Please ask your developers why there is a pause of n seconds between the SELECT statements, as that’s causing the whole problem!

++ UPDATE ++
BTW: A database application that uses a lot of SELECT statements might work well on a local network (or on localhost of the developers laptop) as there is almost no latency, but it won’t work well on a WAN link with high latency (RTT > 50ms). I’ve seen this particular problem quite often, because developers usually run everything on their laptop or on the local lan (client, web server, database server) and don’t realize, that latency might be a problem while they write and test the code. So, what a good programmer usually does to solve this kind of problem:

  • have an application server next to the database server (same lan) and send a request to the application server to retrieve the whole data in one step (JSON, XML, whatever)
  • write a stored procedure on the SQL server and call that, instead of sending 20-30 SELECT statements over a low latency network

;-))

alt text

Regards
Kurt

answered 30 Aug ‘15, 14:09

Kurt%20Knochner's gravatar image

Kurt Knochner ♦
24.8k1039237
accept rate: 15%

edited 30 Aug ‘15, 14:25

Thanks Kurt, that makes sense. As I said there are some other dependencies so it’s possible, the web server is contacting those in these pauses and that’s adding time. I’m not a developer, just a network guy, but this analysis helps. The full capture file actually has the traffic to those other dependencies as well and they are on the same lan as the web server. This DB server is the only one across the WAN.

(30 Aug ‘15, 14:17) Vontech615

see my updates above how to solve the problem.

(30 Aug ‘15, 14:24) Kurt Knochner ♦

Awesome, that gives me some ammunition when I meet with these people tomorrow.

(30 Aug ‘15, 14:32) Vontech615
1

Awesome, that gives me some ammunition when I meet with these people tomorrow.

Good luck!

Maybe there are more problems to find in the whole capture file, but looking at that is beyond the scope of this site. I suggest to hire a network troubleshooting professional to analyze your application performance problems, in case the problem mentioned above is not the only one ;-)

I dare to bet that there are other problems as well, because as I said: developers often forget about latency when they develop and test applications on the local network and/or in virtual machines running on their laptop!

They get bitten in the a.. only when it’s to late and the release date is due next monday :-))

(30 Aug ‘15, 14:41) Kurt Knochner ♦

1 last question… Why so many ACK’s without a SYN or 3 way handshake? When I reveal what’s underneath the reassembled PDU’s it’s mostly uknown packets (not last buffer). I’m reading that can be normal if WS doesn’t understand the DB language?

(30 Aug ‘15, 15:02) Vontech615

Why so many ACK’s without a SYN or 3 way handshake?

because you’ve (apparently) captured TCP streams that were already established when you started Wireshark.

Why so many ACK’s

You can spot frames that are larger than ~1500 bytes in the capture file. That’s because you’ve captured on the web server which has TCP offloading enabled. Those large TCP segements will be split into several smaller IP frames, according to the MTU size, by the NIC driver. As a result, you will see a lot more ACKs than you’ve expected.

When I reveal what’s underneath the reassembled PDU’s it’s mostly uknown packets (not last buffer).

without the full pcap file (not anonymized), it’s hard to tell what you are actually seeing….

(30 Aug ‘15, 15:30) Kurt Knochner ♦
showing 5 of 6 show 1 more comments