List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 5 2001 10:03pm
Subject:Re: Bizarre query performance
View as plain text  
In the last episode (Oct 05), Philip Brown said:
> > What are your timings if you run your client on the SCO box? mysql
> simply reports a query time of 10ms or less (0.01s). Of course, this
> doesn't have any network overhead.

This rules out mysql as the cause for the delay.  
 
> > I'd say start dumping packets on the network.
> 
> I'd agree, but I'm confused as to why a different query (that
> requests more data; 33 rows vs 1) can reliably execute and fetch in
> 10ms on all machines? The behaviour is completely reproducible:
> SELECT (1 record) ON PRIMARY KEY = slow (200ms), SELECT (lots of
> records on indexed field) = fast (10ms)

The fact that your two queries take different times to process has
nothing to do with indices, and more to do with the bytecount of the
query and the response.
 
> >  200ms sounds a lot like Nagle's Algorithm kicking in (which
> > shouldn't happen assuming the mysql libs are written right).
> 
> Indeed, I wouldn't have thought they'd have included that! Isn't
> Nagle restricted to telnet? But anyway, not all queries perform
> equally badly.

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
rewrite the sending code to send all its data in a single write(), but
the simple fix (which ends up wasting bandwidth by sending many small
packets) is to

int var=1;
setsockopt(socket, IPPROTO_TCP, TCP_NODELAY, &var, sizeof(var));

Chances are your two windows machines have differnt myodbc versions, or
different TCP settings in the registry, that make Nagle kick in at
different times.

For more reading:

http://www.faqs.org/rfcs/rfc896.html
http://www.openldap.org/lists/openldap-devel/199907/msg00082.html

-- 
	Dan Nelson
	dnelson@stripped
Thread
Bizarre query performancePhilip Brown5 Oct
  • Re: Bizarre query performanceRussell Miller5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • Re: Bizarre query performanceRussell Miller5 Oct
        • RE: Bizarre query performancePhilip Brown5 Oct
          • Re: Bizarre query performanceRussell Miller6 Oct
            • RE: Bizarre query performancePhilip Brown6 Oct
  • Re: Bizarre query performanceBoyd Lynn Gerber5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • RE: Bizarre query performanceBoyd Lynn Gerber5 Oct
  • Re: Bizarre query performanceDan Nelson5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • Re: Bizarre query performanceDan Nelson5 Oct
        • Re: Bizarre query performanceBoyd Lynn Gerber6 Oct
          • RE: Bizarre query performancePhilip Brown6 Oct
            • Re: Bizarre query performanceDan Nelson7 Oct
              • Preventing Nagle with mysql (was: Bizarre query performance)Philip Brown8 Oct
                • Re: Preventing Nagle with mysql (was: Bizarre query performance)Dan Nelson10 Oct
                  • Re: Preventing Nagle with mysql (was: Bizarre query performance)Sinisa Milivojevic10 Oct