hi and thanks for the responses.
grigory: obviously local connections will be faster...but a query time
difference of 2 seconds locally compared to 5 minutes for a machine on the
same network (that can scp/ftp a 1 meg file in a few seconds) is a little
strange.
and at the risk of going offtopic, i'm attempting to track down what the
cause of this is and how it could be improved...
downloaded and installed tcpdump. as a disclaimer, IANANG. (network guy)
ran it: [root@joker sbin]$ tcpdump -n host 10.10.10.31
no significant traffic between the boxes under regular circumstances.
for smaller queries: select * from big_table limit 100, or 1000, or even
10000, i receive a solid stream of lines like this, the amount obviously
depending on the number of rows returned.
12:15:14.209326 10.10.10.12.4289 > 10.10.10.31.3306: . ack 617866 win 27512
<nop,nop,timestamp 925063024 440770145> (DF)
12:15:14.209427 10.10.10.31.3306 > 10.10.10.12.4289: P 617866:619314(1448)
ack 538 win 32120 <nop,nop,timestamp 440770145 925063024> (DF)
when i do a select * from big_table that returns the 53000 rows, i get
around 13 lines of output, followed by a pause of almost exactly 30 seconds,
then another 13-ish lines. this continues for a loooong time. this time,
although the query was executed from a different box (still close to the
database server) i waited about 15 minutes for the query to complete, but
finally killed the query process in mysql before it did.
the other limit-ed queries executed in appropriate times...2 seconds, ~8
seconds, ~20 seconds respectively.
what's up with that 30 second pause?
does this look like a network problem to you guys? if it was, why would it
not affect the 'limit 10000' query as well?
is there some sort of resource that mysql needs to process the 53000 rows
that it might not have that would cause this?
maybe different tcpdump options are in order for more pertinent information?
again, thanks in advance for any ideas you may have.
-ravi.
-----Original Message-----
From: mike cullerton [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 02, 2001 8:41 PM
To: Ravi Raman; [EMAIL PROTECTED]
Subject: Re: State: "Writing to net"
being a network guy at heart, i'd like to see what tcpdump says is going
over the ethernet card (if anything). does there appear to be traffic moving
between the two machines, or is it caught in a buffer somewhere and hasn't
even made it to the cable.
since ftp works well, i'm guessing it isn't the physical network or anything
at the ip layer. possibly getting data into the transport (tcp/udp) layer or
above that.
on 8/2/01 10:19 AM, Ravi Raman at [EMAIL PROTECTED] wrote:
> hi.
>
> quick question...
> i have a simple query that returns ~53000 rows.
>
> when connected to the mysql server from localhost, this query takes about
2
> seconds.
> when connected to the mysql server from another box, this query takes
about
> 3-4 minutes. 'show processlist' shows the query state as "Writing to net"
> for the majority of the query time.
> these boxes are right next to each other (1 hop) and ftp and scp transfers
> between them are quite fast.
>
> is this a network problem?
> the load average on both machines is quite low....they're running linux
and
> relatively recent versions of mysql.
> what's going on here?
> what does the "writing to net" state mean (besides the obvious)? couldn't
> find any relevant docs.
>
> any input is appreciated.
>
> -ravi
-- mike cullerton
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
