List:General Discussion« Previous MessageNext Message »
From:Philip Brown Date:October 6 2001 8:42am
Subject:RE: Bizarre query performance
View as plain text  
> > This rules out mysql as the cause for the delay.
>
> I agree.

It rules out query execution time as the problem, but not necessarily
a transmission problem. I'm not actually pointing the finger at mySQL,
just trying to understand where the delays are coming in.

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

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.

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

Thankyou for you help, suggestions and information on Nagle. The thing is,
that to eliminate as much as possible, I wrote a test stub that used the
mySQL C API directly. Therefore, ODBC does not enter the equation. Nagle
might, however when I run a Win32 mySQL server response times from all
clients
are fine.

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?

Thanks again, your help and thoughts are appreciated.

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