List:General Discussion« Previous MessageNext Message »
From:Boyd Lynn Gerber Date:October 5 2001 10:16pm
Subject:Re: Bizarre query performance
View as plain text  
On Fri, 5 Oct 2001, Dan Nelson wrote:
> This rules out mysql as the cause for the delay.

I agree.

> > > 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.

Great.  That is exactly what I was trying to point out in my last post.
But done really poorly.

tt,

--
Boyd Gerber <gerberb@stripped>
ZENEZ	3748 Valley Forge Road, Magna Utah  84044
Office 801-250-0795 FAX 801-250-7975

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