From: Dan Nelson
Date: October 7 2001 12:32am
Subject: Re: Bizarre query performance
List-Archive: http://lists.mysql.com/mysql/87292
Message-Id: <20011006193257.A75578@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Oct 06), Philip Brown said:
> Except that the query that has a longer bytecount and 50 times as much
> data in the response, can always be executed and received by the client
> in 10ms. The shorter query (on the primary key) that returns a single
> record takes 200 ms (180ms for mysql_store_result). Which eliminates a
> "simple" network bytecount rationale.

.. 
 
> > On Oct 5, Dan Nelson wrote:
> > > Nagle's algorithm applies to all TCP sessions unless explicitly
> > > disabled.  It buffers outgoing data less than your MSS for up to 200ms
> > > if there is unacknowleged data already on the wire.  This is usually
> > > triggered by inefficient code on the sending end that does multiple
> > > writes(); the first write() gets sent immediately.  Any subsequent
> > > writes() get buffered up by Nagle until 200ms or the ACK for the first
> > > block of data from the receiving machine.  The standard fix is to
> 
> I still assert that the behaviour I am seeing is not explicable by a
> simple network transfer layer problem. Why would a complex query that
> returns a lot more data ALWAYS work fast, yet a simple query that
> returns little data (one small row) ALWAYS work slow?

Because Nagle only kicks in if you get a write() followed immediately
by a write() that is less than one network packet in size.  Nagle's
algorithm causes that small packet to get buffered at the sending end
in the hopes that another small packet will follow that the system can
concatenate and send as one network packet.

If you have a short resultset, it will fit in one packet and Nagle might
delay it.  If you return a lot of records, it will have to be split
into multiple packets and Nagle won't kick in.

The only way to really verify this is to dump packets on the wire. 
It's almost impossible to get tcpdump running on SCO OpenServer, so
I suggest downloading Ethereal for windows, and viewing the connection
from there.  If you see 

0.000 client -> server: query (150 bytes)
0.010 server -> client: result header (32 bytes)
0.210 server -> client: result records (100 bytes)
0.211 client -> server: ACK 

, that's Nagle.  The server actually sent the header and records right
after each other, but since they were done with two write() calls, the
system decided to delay the 2nd one in the hopes that a couple more
100-byte writes were to follow that it could coalesce.  A query that
returned a lot of data would send more than one 1500 bytes of data in
the 2nd write, forcing the system to send the data immediately.

If, instead, you see

0.000 client -> server: query (150 bytes)
0.001 server -> client: ACK
0.210 server -> client: result data+records (132 bytes)
0.211 client -> server: ACK 

That means the server actually took 200ms to process the query.

-- 
	Dan Nelson
	dnelson@stripped