A Network Administrator’s View:
When digging into some packet dumps to try to solve some issues I was seeing with our Broadcom network cards, something else caught my eye. When looking in Wireshark to see if there were TCP retransmits I didn’t see any in my capture but I did see a very large amount of TCP zero window messages between our web tier and our Database tier.
To follow this you need to be familiar with TCP flow control, so I will briefly cover this. Since TCP is full duplex, each side of the connection is both a sender and a receiver. However, you will often have one side doing more of one then the other. In this case with our sites it is the SQL server backend that plays the role of the sender and the web tier is the receiver. The reason for this is that the web tier just sends a database query which will be short, and the database server will send back the results of that query which will generally be larger than the query itself.
The rate of data transfer is controlled by the receiver telling the sender how much data it can receive. The amount of data that can be received is called the TCP Window. This window shrinks as the network buffers fill up. If the window fills up faster than the application retrieves the data from the network buffers then eventually the receiver will let the sender know that is can’t receive any more data for the time being. TCP informs the sender that it can’t receive more data by sending a TCP packet where the window size is zero — this is our zero window message. What this means in our case is that the sender (SQL server) is sending data to the receiver (the web servers) faster than they can process it.
So as a network administrator, if I don’t want to just blame the application, I look to what I can fix on the network side. One cause of this would be that if there is a lot a latency between the web tier and the database tier than the window might be too small. To check this the simplest way was to send pings up to the size of the MTU with the don’t fragment bit set and make them as rapid as possible. I did this but only saw peaks of 1-2 MS latency. Even if we take a view that the performance is worse than measured, the bandwidth delay product for this latency is ( (RWIN in Bytes)/(Latency) * 8 = (Max Throughput in Bits) ):
(65535/0.003) * 8 = 174,760,000
So this didn’t really seem to be the issue here since the bandwidth is lower than 174 mbit/s. Also, in Windows Server 2008 R2 there isn’t much you can do to enlarge the default window by using window scaling because Windows automatically controls this.
The other theory I had is that maybe somehow the network stack or network driver is not letting the application know that there is data to be retrieved fast enough. CPU usage is moderate so I figured that it was not a lack of processing power the web servers. The way the network stack will inform the application that there is data in the buffers is by sending an interrupt. Because at gigabit speeds interrupts can start to take up a lot of CPU power there are several tuning options for this. One option is to dedicate these interrupts to a certain core or group of cores. Another option that the NICs have to keep the interrupt CPU load low is interrupt moderation, this dampens the rate of interrupts by batching them. I tried tunning these various options to make the interrupts more frequent but I still saw a high rate of zero window messages.
My skills as a network administrator pretty much hit a wall at this point and I didn’t get any network level answers on Server Fault that solved this issue for me. Next I turned to Stack Overflow to see if there was maybe a way to have .NET tell Windows to increase the size of the TCP window. My theory was that if the TCP window was bigger than it might stop bottoming out as it does in this graph of the average window size over time during my capture:
A Developer’s View:
When I asked about Speeding up the rate that IIS/.NET/LINQ retrieves data from the network buffers on Stack Overflow the pieces started to fall together with Remus’ answer. I wasn’t sure if what he was saying was the case, but I now had something to run with to try to get more information. With this information, I put on my admittedly somewhat shabby DBA hat.
A DBA’s View:
To verify that this might be the case I used the queries I had learned from Professional SQL Server 2008 Internals and Troubleshooting to view the SQL DMV of top wait times. One of the top wait times was async_network_io. This SQL server wait type means that SQL server has to wait because the client is not ready to receive all of the data it is sending. The problem with this DMV view is that it only shows total times since SQL server was restarted, and I needed to see which particular queries were causing the waits. So I turned to dba.stackexchange.com to try to find out how I could find the queries causing network waits safely in a production environment. The answers provided me with queries that I could run to take snapshots of queries. There were queries that frequently showed up with async_network_io wait times. I saw one query over and over again with 200-800MS of network wait. The query with “SELECT TOP 3000” and a whole wall of fields after it raised my eyebrows as that sounded like a lot of data to be sending back to the web server.
Not being that much of a DBA (at least, yet) this was about the end of the road for me. So I sent the top offenders I found to the developers and Brent Ozar.
Back to the Developer’s View:
Remus’ original answer had two theories for what might be going on:
- The client (web tier) was requesting more data than it should
- There was waiting going on while processing the data before fetching it all
The Top 3000 query was clearly more data than was probably needed and was a query constructed by LINQ. A large query in some ways make sense at first because the results of this query were aggressively cached. Also, for a while now our web tier has had CPU power to spare so moving processing to the web tier appears to be a good thing to do. However, returning large data sets to the web tier usually won’t work well, at least as part of a user request, due to the high network penalty.
The second theory is that a DataReader is being used to read the data one record at at time, and something is performed on each row before fetching the next record causing wait time between each query. I am not aware of any instances of this for our large queries yet. If there are such queries the solution might be to use a DataSet which would fetch all off the rows before processing them.
So the solution was to move the query to a background thread so it won’t slow down user response time, and of course make the query more limited in the amount of data it returns.
Back to the Network View, Meta is Murder:
The most shocking thing is that after this query was adjusted the amount of data being sent from the database sever dropped about 20mbit/s (Notice the difference between Tuesday and Wednesday during peak hours):
So was this query really pulling this much data even though it isn’t running that frequently? The answer is both yes and no.
Since TCP/IP and Ethernet carries overhead for the headers part of the data going over the wire is just meta data added by the network. The minimal amount of TCP/IP and ethernet overhead is (See this page for more information):
(1500-40)/(38+1500) = 94.9285 % IPv4, minimal headers
So at an optimal window TCP window size each packet will have the maximum amount of user data of 1460 bytes (without jumbo frames/vlan tagging/etc). The 78 bytes of overhead in this case is about 5% of overhead (78.00/1538.00). When our network is not hitting zero window messages the window sizes were often around 200 bytes. I made a histogram of my capture to show just how often it was in the range of small windows (resolution isn’t there to show it, but most of it is around 200):
The window size of a TCP packet will be the size of payload data minus 8 bytes since the window size is everything beyond the acknowledgement number in a TCP packet (I might be off on this calculation, I could not find a direct reference to verify this). So with a window size of 200 bytes we are sending 194 bytes of user data in a packet. So with this we have 70 bytes of overhead and 194 bytes of user data which is about 27% of overhead.
So when transferring about 100 mbit/s of user data you would only see about 5 mbit/s of extra data in the SNMP octets counters with a good window size, so from the SNMP view the transfer rate would be 105 mbit/s. When transferring 100 mbit/s of user data with a window size around 200 bytes there is 27% overhead and you end up with about 27 megabits a second of overhead for 127 mbit/s of traffic from the SNMP view. This ignores any overhead provided by the application level protocol that the web tier uses to speak to SQL server.
This pattern of window sizes is referred to as “Silly Window Syndrome” since the meta data can start to overtake the actual amount of user data being transfered. This overhead explains the large drop in database traffic beyond just reducing the amount of query data returned.
I think the biggest lesson is that the full view of many problems is missed unless each person in the team has at least some understanding of what is going on from other team members views. Also, the communication between different specialists is needed to solve many issues. In this case what looked initially to me like a networking problem was actually a symptom. Trying to attack the problem solely as a network administrator was treating the symptom, not the disease.
From a technical standpoint the difference between having the web tier and SQL servers on the same box compared to having a network connection between them is important. Things may work well on a single server, but when the data needs to be moved over the network shifting the load to web servers might not always work.
We still have a good amount of zero window packets going back and forth, so although the worse offenders have been mitigated I believe there is still might be work to do.
Until a year ago I never really thought much about NIC vendors. I figured if you bought Intel or Broadcom you were safe, maybe if I was doing near Gigabit speeds constantly or iSCSI I would have to pay more attention, but other than that I figured I was good.
Man, was I ever wrong.
This all started almost a year ago in our Oregon data center. In fact it was one the first things that was handed back to me when I started. On our web servers after upgrading to Windows Server 2008 R2 after about a week or two we would lose connectivity on a web server. This was happening to all the web servers, just not at the same time. After losing connectivity when you went to reboot the server it wouldn’t. You had to wait about 10 minutes for the BSOD to come and then the server would reboot (More at Windows Server 2008 R2 network adapter stops working, requires hard reboot).
So we raised a case with Microsoft and after a month of back and forth and some kernel patches we still had the problem. So we tried some Intel NICs and the problem went away.
Now in our NY data center (Dell hardware instead of IBM, but still Broadcom NICs) I saw some packets being lost and various network timeouts recently. So I updated the firmware on a test server and a couple days later updated the rest of our web servers. I saw no improvements so I started to dig deeper into some tcpdump data with Wireshark and I see the following sorts of ARP requests coming from the servers:
17:03:41.187682 ARP, Request who-has 18.104.22.168 (00:21:9b:a2:c9:be) tell 22.214.171.124, length 46 17:03:41.187684 ARP, Request who-has 126.96.36.199 (00:21:9b:a2:c9:be) tell 188.8.131.52, length 46 17:03:41.187686 ARP, Request who-has 184.108.40.206 (00:21:9b:a2:c9:be) tell 220.127.116.11, length 46ARP is used to find the MAC address of servers within the same network. In fact it is at the foundation of the network stack that much of the Internet is built on. There should never be ARP requests for IPs outside of your network (More at Windows 2008 R2 Servers Sending Arp Requests for IPs outside Subnet). This disappeared when I disabled failover teaming and came back when I enabled it again.
Now about every week I am getting corrupted arp tables. Deleting the table with arp -d usually results in the table not being rebuilt, or if it does it shortly fails again after. The solution is to reboot the server. So I called Dell and they can’t help me because it is a software issue. So now we are right back where we were with our NICs in Oregon.
We are replacing our Broadcom NICs with Intel on our primary production boxes. We replaced one of the NICs with an Intel NIC a couple weeks ago and have not seen either of these problems in that server so we are going to do this with the rest of our servers.
I don’t ever want to touch a Broadcom NIC again. Intel is a company that makes more sense to me anyways as their engineers are more frequently part of what I think of as the fabric of the Internet. Last issue I had with an Intel product I posted on their free mailing list and got a response from an Intel engineer within hours. The best thing to do at this point I think is to take these Broadcoms out to the field: