[clue-tech] MySQL server reply packets delayed/lost under network congestion?

[Jim Ockers]

Hi Silas,

It is real time sensor data and not suitable for replication because it 
is somewhat time sensitive (and has one second data updates).

Thanks though,
Jim

Silas Martinez wrote:
> Hey there,
>
> I'm afraid I'm not going to be much help in directly solving the 
> problem, but perhaps there is another approach?
>
> If clients are mostly doing read activity, for example, perhaps 
> replication is a potential solution? Clients could do read activity 
> without interruption (the data feed to the local slave may be 
> interrupted, so the data may not be up-to-the-moment, but connections 
> wouldn't arbitrarily drop, either), and a connection would only be 
> required at the moment for write activity (to the remote master) - and 
> even that isn't a strict requirement, depending on the sensitivity of 
> the data, and the app itself. 
>
> Replication can be configured to be tolerant of losing connectivity to 
> the master with few issues:
> http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html#qandaitem-16-3-4-1-1
>
> Unfortunately, outside of traffic shaping, which you've already 
> considered, I'm not aware of a better fix. Perhaps someone will be 
> more mysql-clueful than I.
>
> Best of luck!
>
> Silas.
>
> On Fri, Nov 6, 2009 at 5:32 PM, Jim Ockers <ockers at ockers.net 
> <mailto:ockers at ockers.net>> wrote:
>
>     Hi CLUEbies,
>
>     We have a Windows MySQL client on one end of a 1Mbps serial PPP
>     connection and a Linux MySQL server on the other end.  Actually we
>     have thousands of these and they all behave the same.  Since it's
>     weird I'd appreciate any ideas or suggestions about what we could
>     change or do to fix this!
>
>     Whenever there is sustained high bandwidth network traffic over
>     the PPP link (such as downloading a large e-mail attachment, or
>     streaming video) the mysql connections all start to time out
>     because response packets from the server are not received at the
>     client.  I observed this by doing a simple "telnet server 3306"
>     and observed that the MySQL banner response with version number
>     was delayed by several seconds or until the bandwidth-hogging stopped.
>
>     What I don't understand is why I can still transfer data from
>     other servers on the Linux system, such as "net view \\server" and
>     "dir \\server\sharename" (using the Windows redirector to talk to
>     Samba on the Linux system).  Also the apache web server on the
>     Linux system responds normally, both from a web browser and also
>     in response to "telnet server 80" and then "GET / HTTP/1.0".
>
>     It seems that every network service on the linux server responds
>     normally and in a timely manner in the presence of a bandwidth
>     hog, EXCEPT for MySQL.  The link MTU is 1500 bytes on both ends,
>     and server interface txqueuelen is 100.  MySQL seems to be
>     somewhat better at responding when we decrease the MTU to 384, but
>     for a variety of reasons that is not practical and we are going to
>     have to stick with 1500.
>
>     Is this behavior expected?  Has anyone else seen this?  Is there
>     something we can change in the mysql server config which will make
>     it more aggressive about sending query response packets out to
>     clients?  By the way both 4.0.24 and 5.0.45 do this, although it
>     seems the older version is slightly more robust.  I am not a MySQL
>     expert so I don't know what the differences are or why this would
>     happen.
>
>     We can use tc traffic shaping to force it to work better, but
>     traffic shaping has its own set of issues and risks, and really
>     the only thing on the server that's not working OK in the presence
>     of a bandwidth hog is the mysql server.  Any ideas, anyone?
>
>     Thanks,
>     Jim
>
>     -- 
>     Jim Ockers, P.Eng. (ockers at ockers.net <mailto:ockers at ockers.net>)
>     Contact info: http://www.ockers.ca/pason.html
>
>         
>
>
>     _______________________________________________
>     clue-tech mailing list
>     clue-tech at cluedenver.org <mailto:clue-tech at cluedenver.org>
>     http://www.cluedenver.org/mailman/listinfo/clue-tech
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> clue-tech mailing list
> clue-tech at cluedenver.org
> http://www.cluedenver.org/mailman/listinfo/clue-tech

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cluedenver.org/pipermail/clue-tech/attachments/20091106/ee61a36e/attachment-0001.html