Home / server / Questions / 409973
In Process
quanta
Asked: 2012-07-19 22:06:52 +0800 CST

Load balancing MySQL using HAProxy: Got an error reading communication packets?

  • 772

I've set up load balancing MySQL slaves using HAProxy via a xinetd. 2 load balancers shared a virtual IP that is managed by Pacemaker:

crm configure show:

node SVR120-27148.localdomain
node SVR255-53192.localdomain
primitive failover-ip ocf:heartbeat:IPaddr2 \
    params ip="192.168.5.9" cidr_netmask="32" \
    op monitor interval="5s" \
    meta is-managed="true"
primitive haproxy ocf:heartbeat:haproxy \
    params conffile="/etc/haproxy/haproxy.cfg" \
    op monitor interval="30s" \
    meta is-managed="true"
colocation haproxy-with-failover-ip inf: haproxy failover-ip
order haproxy-after-failover-ip inf: failover-ip haproxy
property $id="cib-bootstrap-options" \
    dc-version="1.0.12-unknown" \
    cluster-infrastructure="openais" \
    no-quorum-policy="ignore" \
    expected-quorum-votes="2" \
    stonith-enabled="false" \
    last-lrm-refresh="1342783084"

/etc/haproxy/haproxy.cfg:

global
    log 127.0.0.1 local1 debug
    maxconn 4096
    pidfile /var/run/haproxy.pid
    daemon

defaults
    log global
    mode tcp
    option dontlognull 
    retries 3 
    option redispatch
    maxconn 2000
    contimeout 5000
    clitimeout 50000
    srvtimeout 50000

frontend FE_mysql
    bind 192.168.5.9:3307
    default_backend BE_mysql

backend BE_mysql
    mode tcp
    balance roundrobin
    option tcpka
    option httpchk
    #server mysql1 192.168.6.47:3306 weight 1 check port 9199 inter 12000 rise 3 fall 3
    server mysql2 192.168.6.248:3306 weight 1 check port 9199 inter 12000 rise 3 fall 3
    server mysql3 192.168.6.129:3306 weight 1 check port 9199 inter 12000 rise 3 fall 3

My problem is most of time connecting via virtual IP, /var/log/mysqld.log keeps flooding with:

120719 12:59:46 [Warning] Aborted connection 17237 to db: 'db' user: 'user' host: '192.168.5.192' (Got an error 
reading communication packets) 
120719 12:59:49 [Warning] Aborted connection 17242 to db: 'db' user: 'user' host: '192.168.5.192' (Got an error 
reading communication packets) 
120719 12:59:52 [Warning] Aborted connection 17248 to db: 'db' user: 'user' host: '192.168.5.192' (Got an error 
reading communication packets)

(connection still established)

192.168.5.192 is the HAProxy's IP address.

mysql> show global status like 'Aborted%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 53626 |
| Aborted_connects | 400   |
+------------------+-------+

I don't think 128M is not enough for max_allowed_packet:

max_connections = 300
max_allowed_packet = 128M

_timeout variables:

mysql> show global variables like '%timeout';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 60       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 3600     |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 600      |
+----------------------------+----------+

Is there anything that can cause this? Does it relate to HAProxy?

Any thoughts?

mysql

4 Answers

  1. These are the reasons given in MySQL docs:

    The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld. See Section C.5.2.10, “Packet too large”.

    Use of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file using FTP between the client and server machines. If a transfer goes in burst-pause-burst-pause mode, you are experiencing a Linux duplex syndrome. Switch the duplex mode for both your network card and hub/switch to either full duplex or to half duplex and test the results to determine the best setting.

    A problem with the thread library that causes interrupts on reads.

    Badly configured TCP/IP.

    Faulty Ethernets, hubs, switches, cables, and so forth. This can be diagnosed properly only by replacing hardware.

    And, this explains better:

    Although they could be a symptom of a larger problem, they can be caused from normal (i.e. unpreventable) network issues.

    Even if they're on the same LAN, for a variety of reasons, communication errors may occur between your application server and the database. In the cases of corrupt communications or time-outs, the applications and/or MySQL most likely retries and works and the problem never surfaces or makes itself apparent.

    In my experience, the most common sources of these types of messages are from the application (server) flaking out, the application not terminating connections properly, or from latencies in off-site replication.

    Quite likely they were happening before you enabled error logging on the MySQL server.

    • 2

  2. I found that increasing the timeout settings in the haproxy.cfg file solved this error for me. I spent a lot of time checking the my.cnf wait_timeout etc and realised the bottleneck was actually HAProxy.

    • 1

  3. check haproxy mannul

    tune.idletimer

    Sets the duration after which haproxy will consider that an empty buffer is probably associated with an idle stream. This is used to optimally adjust some packet sizes while forwarding large and small data alternatively. The decision to use splice() or to send large buffers in SSL is modulated by this parameter. The value is in milliseconds between 0 and 65535. A value of zero means that haproxy will not try to detect idle streams. The default is 1000, which seems to correctly detect end user pauses (eg: read a page before clicking). There should be not reason for changing this value. Please check tune.ssl.maxrecord below.

    I set tune.idletimer=60000 and restart haproxy service. and the problem happpen again. I meet the problem in haproxy 1.8.14

    the old haproxy 1.5.4 is OK.

    • 0

  4. For me the major setting I had to use/change was:

    timeout connect 6s
timeout client  600s
timeout server  600s

    I suspect some clients for performance purposes maintain socket connections to mysql -- in particular PHP so the only workaround is to allow them to maintain such connections for longer. I found that 600s disappeared the errors and settled with that value.

    • 0