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

[Jim Ockers]

Hi Dave,

David L. Anselmi wrote:
> Jim Ockers wrote:
>> Hi everyone,
>>
>> Replying to my own post. :)  We figured it out.  MySQL tries to set 
>> the IP TOS to a nonzero value.
> [...]
>> 1. Does anyone know how to make MySQL not do that, either a config 
>> file option or command line option?
>> 2. Is there some really excellent reason why MySQL would do that?
>
> This could be a kernel bug.  Can you talk to the MySQL people who 
> might know which kernel people to talk to?
>
> By any chance are you doing any queue management that might either be 
> buggy or not behave the way you think it should when presented with 
> these packets?
I think this problem is exacerbated by the fact that the MySQL client 
and server are connected with a serial connection and we use PPP.  One 
of our other engineers did some research into the kernel's queueing 
because we are in fact working on QoS as well.  He says: "The default 
queuing discipline is pfifo_fast.  It can not be configured and on our 
system it looks like this:

TOS     Bits  Means                    Linux Priority    Band
------------------------------------------------------------
0x0     0     Normal Service           0 Best Effort     1
0x2     1     Minimize Monetary Cost   1 Filler          2
0x4     2     Maximize Reliability     0 Best Effort     1
0x6     3     mmc+mr                   0 Best Effort     1
0x8     4     Maximize Throughput      2 Bulk            2 <--MySQL is 
"Bulk" traffic
0xa     5     mmc+mt                   2 Bulk            2
0xc     6     mr+mt                    2 Bulk            2
0xe     7     mmc+mr+mt                2 Bulk            2
0x10    8     Minimize Delay           6 Interactive     0
0x12    9     mmc+md                   6 Interactive     0
0x14    10    mr+md                    6 Interactive     0
0x16    11    mmc+mr+md                6 Interactive     0
0x18    12    mt+md                    4 Int. Bulk       1
0x1a    13    mmc+mt+md                4 Int. Bulk       1
0x1c    14    mr+mt+md                 4 Int. Bulk       1
0x1e    15    mmc+mr+mt+md             4 Int. Bulk       1
(source http://lartc.org/howto/lartc.qdisc.classless.html)

The problem is with this discipline if there are packets waiting in band 
0, band 1 won't be processed, and the same for band 1 vs 2, so it is not 
fair.  So basically everything gets processed before MySQL but there is 
no reason that MySQL shouldn't be allowed to set this bit."

I personally think that there should be a  
"--I-am-dumb-enough-to-try-to-use-MySQL-as-a-realtime-database" command 
line and config file option for mysql.  We don't see any way within 
MySQL to turn off the TOS=0x8 that MySQL sets on its packets.  We are 
using MySQL for a realtime application (well, "real enough time" we say) 
so there is no way that it's okay in realtime-land to have the 
equivalent of a Precedence: Bulk header for MySQL packets.
>
>> 3. Is there any other way (iptables mangle, for example) that we 
>> could fix this packet's TOS _BEFORE_ it hits the network 
>> stack/queues/buffers?  We tried with iptables mangle/OUTPUT and it 
>> was too late in the network processing to have any effect on the packet.
>
> I don't remember the tables well enough.  But you could probably do 
> something creative to send the MySQL traffic through a virtual 
> interface that routes to the PPP interface.  Then OUTPUT would be 
> early enough, right?
Ha ha very clever.  Yes we have some other clever options.  We could use 
netcat to handle the network socket over the PPP connection and loop it 
back through a localhost socket to MySQL or something like that, and we 
would tell netcat to set TOS=0x10 or something in the "interactive" band.

However since we control the choke point (NAT & Firewall) for all 
network access and the majority of the problem is caused by people 
watching youtube in HD, or downloading Windows updates etc., which the 
Linux kernel thinks is higher priority than our MySQL application 
because of the TOS bits, we are thinking about just mangling the TOS bit 
on all packets entering the system to make them lower priority than 
MySQL, and that will work around the problem.  Instead of trying to 
change MySQL or the TOS of MySQL packets, we could just change 
everything else and make it lower priority. :)  I am still thinking 
about what kind of stuff this might break, like VoIP, so there are some 
risks because maybe the relative priorities are important.

THE MORAL OF THE STORY: Don't ignore the TOS bits.  They mean something 
and you should know what they mean and how your applications and OSes 
use them!
THE OTHER MORAL OF THE STORY: As a colleague of mine says, "There are 
realtime databases and MySQL is not one of them."

Jim.

-- 
Jim Ockers, P.Eng. (ockers at ockers.net)
Contact info: http://www.ockers.ca/pason.html