Balanceamento de carga MySQL usando HAProxy: Obteve um erro ao ler os pacotes de comunicação?

Question

Configurei balanceamento de carga do MySQL escravos usando HAProxy via xinetd . Dois balanceadores de carga compartilharam um IP virtual gerenciado pelo 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

Meu problema é a maior parte do tempo conectando via IP virtual, /var/log/mysqld.log continua inundando com:

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) 

(conexão ainda estabelecida)

192.168.5.192 é o endereço IP do HAProxy.

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

Eu não acho que 128M não seja suficiente para 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      |
+----------------------------+----------+

Existe alguma coisa que possa causar isso? Isso se relaciona com o HAProxy?

Alguma opinião?

    

Answer 1

Estas são as razões dadas em docs do MySQL:

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.

E, this explica melhor:

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.

    

Answer 2

Descobri que aumentar as configurações de tempo limite no arquivo haproxy.cfg resolveu esse erro para mim. Passei muito tempo checando o wait.ctime do my.cnf etc e percebi que o gargalo era realmente HAProxy.