2

I use haproxy as banlancer for mariadb galera cluster, it can connect and do some operation normally,but after serval seconds I want to do some search again but a got lost connection during query error.see the error below image.

lost connection img

here is my haproxy.cfg

 defaults
        log                     global
        mode                    tcp
        option                  tcplog
        option                  dontlognull
        option http-server-close
        option                  redispatch
        retries                 3
        timeout http-request    10s
        timeout queue           1m
        timeout connect         10s
        timeout client          50000ms
        timeout server          50000ms
        timeout http-keep-alive 10s
        timeout check           10s
        maxconn                 3000

and the balance for mariadb cluster in haproxy.cfg 

listen mariadb_cluster_writes 0.0.0.0:50613    
    ## A failover pool for writes to ensure writes only hit one node at a time.
        mode tcp
        #option httpchk
        option tcpka
        option mysql-check user haproxy
        server node65 172.27.12.65:3306 check weight 2
        server node64 172.27.12.64:3306 check weight 1
        server node67 172.27.12.67:3306 check weight 1

    listen mariadb_cluster_reads 0.0.0.0:50614
    ## A load-balanced pool for reads to utilize all nodes for reads.
        mode tcp
        balance leastconn
        #option httpchk
        option tcpka
        option mysql-check user haproxy
        server node65 172.27.12.65:3306 check weight 1
        server node64 172.27.12.64:3306 check weight 1
        server node67 172.27.12.67:3306 check weight 1

Does anyone have idea?

尹雪松
  • 71
  • 1
  • 6
  • This could happen because of a number of reasons. Check the error log of all the nodes. – Nirbhay Choubey May 24 '16 at 13:07
  • Can you change to MaxScale from MariaDB as DB proxy. It is designed for Galera and works very nice. Easy to install and setup : https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/ – Bernd Buffen Jun 25 '16 at 16:28

1 Answers1

5

I think i find the reason.Haproxy itself has a timeout for server and client,I set the sever timeout and client timeout as same as mysql timeout which is 8 hour.Now it seems like this:

defaults
        log                     global
        mode                    tcp
        option                  tcplog
        option                  dontlognull
        option http-server-close
        option                  redispatch
        retries                 3
        timeout http-request    10s
        timeout queue           1m
        timeout connect         10s
        timeout client          480m
        timeout server          480m
        timeout http-keep-alive 10s
        timeout check           10s
        maxconn                 3000

Hope can help others.

however i use mariadb cluster,it is master and mater architecture,all of node can insert,so I changed the haproxy.cfg,now it seems like this:

# Global settings
global
    log         127.0.0.1 local2
    maxconn     4000
    daemon

defaults
    log                     global
    mode                    tcp
    #option                  tcplog

    option                  dontlognull
    option                  tcp-smart-accept
    option                  tcp-smart-connect
    option                  redispatch
    retries                 3
    timeout connect         5s
    timeout client          480m
    timeout server          480m

listen admin_stats 
        bind 0.0.0.0:50613
        mode http                       
        maxconn 10
        stats refresh 30s              
        stats uri /stats               
        stats realm XingCloud\ Haproxy  
        stats auth admin:admin          


listen galera_back
        bind :50614
        balance leastconn
        server node65 172.27.12.65:3306 check weight 1
        server node64 172.27.12.64:3306 check weight 1
        server node67 172.27.12.67:3306 check weight 1
尹雪松
  • 71
  • 1
  • 6