Why SQL Queries Are 10× Slower Over a 100M Line – TCP Send/Receive Buffer Insights

Introduction

An application accessed Alibaba Cloud services over a 100 M dedicated line (20 ms latency). A 22 MB SQL query took about 25 seconds, far slower than the 1–2 seconds observed when accessing the service from within the cloud or using HTTP/SCP, indicating a TCP performance issue.

Goal

The article aims to clarify the relationship between socket.setSendBufferSize, sysctl parameters ( rmem / wmem), and the TCP send/receive windows, and how they influence TCP transmission performance.

Key Parameters

net.core.rmem_default = 212992
net.core.rmem_max = 212992
net.core.wmem_default = 212992
net.core.wmem_max = 212992
net.ipv4.tcp_adv_win_scale = 1
net.ipv4.tcp_moderate_rcvbuf = 1
net.ipv4.tcp_rmem = 4096    87380   6291456
net.ipv4.tcp_wmem = 4096    16384   4194304
net.ipv4.udp_rmem_min = 4096
net.ipv4.udp_wmem_min = 4096
vm.lowmem_reserve_ratio = 256   256 32

Problem Observation

Wireshark captured the TCP flow: the 22 MB transfer spanned ~25 seconds. Zooming into a 50 ms window revealed that packets were sent quickly but then stalled for ~20 ms, waiting for acknowledgments.

Send Buffer Analysis

The default send buffer is 16 KB ( socket.setSendBufferSize(16*1024)). When the buffer is full, the kernel blocks in sk_stream_wait_memory, as shown by the following SystemTap script:

#!/usr/bin/stap
# Detect when a process waits for more socket send buffer memory
probe kernel.function("sk_stream_wait_memory") {
    printf("%u: %s(%d) blocked on full send buffer
", gettimeofday_us(), execname(), pid())
}
probe kernel.function("sk_stream_wait_memory").return {
    printf("%u: %s(%d) recovered from full send buffer
", gettimeofday_us(), execname(), pid())
}

Because the RTT is 20 ms, after sending 16 KB the sender must wait for ACKs before refilling the buffer, causing a 20 ms pause each window.

Optimization of Send Buffer

Increasing the socket send buffer to 256 KB reduced the query time to ~4 seconds. However, net.core.wmem_max limited the effective size to 130 KB. Raising net.core.wmem_max allowed the transfer to complete in ~2 seconds, matching the 100 M bandwidth.

Bandwidth‑Delay Product (BDP)

BDP = RTT × (bandwidth/8) = 0.02 s × (100 Mbit/s / 8) = 250 Kb. A 256 KB send buffer is sufficient to fully utilize the link; larger buffers provide no additional benefit.

Receive Buffer and Window

When the receive buffer (SO_RCVBUF) is small and RTT is large, performance degrades. A batch insert of 5532 rows (376 KB) over a 40 ms RTT took 5–6 seconds because the receive window could only hold ~3 KB per RTT.

Zero‑Window and Application Read

If the application does not read data, the receive buffer fills, the TCP stack advertises a zero window, and the sender stalls. The article shows a case where the server waited ~6 seconds for the client to read data, causing a long pause before the connection resumed.

Key Takeaways

Never manually set SO_SNDBUF or SO_RCVBUF; let the kernel adjust them.

The send buffer must be large enough to cover the BDP; otherwise, the sender waits for ACKs.

Receive window size depends on SO_RCVBUF and kernel parameters; a small receive buffer with high RTT severely impacts performance.

UDP does not suffer from these buffer‑related stalls because it lacks reliability mechanisms.