7

I have a server that runs HAProxy to load balance our mysql servers. Some of the server may go down when we have low average load for a extensive period of time, but, in the future, if the load becomes high again, those servers goes up automatically. The problem is when an instance goes down, HAProxy never looks to it again, so when the instance is up again, it is ignored. To fix this we reboot when needed.

Here is our configuration file:

global
    log 127.0.0.1 local0 notice
    user haproxy
    group haproxy

defaults
    log global
    retries 2
    timeout connect 3000
    timeout server 5000
    timeout client 5000

listen mysql-cluster
        bind 0.0.0.0:3306
        mode tcp
        option mysql-check user haproxy_check
        balance leastconn
        server mysql-1 ********:3306 check
        server mysql-2 ********:3306 check

Maybe if I change the retries from 2 to a big number it could solve our problem?

EDIT As requested, here is my HAProxy version:

$ haproxy -v
HA-Proxy version 1.4.24 2013/06/17
Copyright 2000-2013 Willy Tarreau <w@1wt.eu>

Thanks

Maurício Giordano
  • 3,116
  • 4
  • 32
  • 60
  • Changing `retries` will not change anything. That parameter refers to retries when establishing a backend connection *attempt* fails on a backend that HAProxy thought was healthy, even though it isn't, which is rare. You're saying no attempts are made, so that isn't applicable. When you say you have to reboot... you have to reboot *what*? Also, is `********:3306` an IP address or a hostname? – Michael - sqlbot Jun 04 '17 at 04:08
  • I need to reboot HAProxy, so it loads the config file again and tries to establish a connection to both servers. `********:3306` is a hostname. – Maurício Giordano Jun 04 '17 at 04:47
  • I think I know what the issue will be. Please mention your HAProxy version. (`haproxy -v`) – Michael - sqlbot Jun 04 '17 at 15:13
  • I edited my answer. – Maurício Giordano Jun 04 '17 at 17:58
  • Have a look https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial – Naruto Jun 10 '17 at 05:42
  • I've got a working configuration equal to yours, haproxy version is 1.5.4. When a node goes down or up it gets immediately reflected in the log files. What's in your logs? – Oleg Kuralenko Jun 11 '17 at 21:13
  • @ffeast Actually, the problem is that when an instance goes down, Amazon RDS removes their DNS (mysql1-taiqurqwsa.rds.amazon.com) and it points to nowhere. I think that maybe when HAProxy cannot resolve the proxy anymore, it starts to ignore it. – Maurício Giordano Jun 11 '17 at 21:42
  • @MaurícioGiordano, dns queries may fail because of a dozen of reasons. I think it would be odd for a proxy/balancer to give up attempts to resolve a hostname in such cases. Have you tried to reproduce such behavior locally? And still, what's in your log files? – Oleg Kuralenko Jun 12 '17 at 12:47

1 Answers1

2

We had similar situation in our AWS infrastructure: HAProxy on every instance for RDS replicas access (we have 3 replicas, but application able to work only with one hostname). We solve this issue globally by replacing HAProxy with Route53 internal using with multiple records with same name ( for example db.example.internal) and same weight (Weighted Route53 policy). Also we create Route53 health check for every replica (TCP 3306 port check). For us this solution works great and if we need to add/delete RDS replica - the only place where we need to change - Route53 record and health check (we don't need to maintain every instance HAProxy status/conf)

Tim Bikbaev
  • 86
  • 1
  • 8
  • Your solution is fine, but have you figured out what has been the cause of the problem? Is it a weird behavior of haproxy in case of failing dns queries as @Mauricio suggested or something else? – Oleg Kuralenko Jun 12 '17 at 12:42
  • @Tim Bikbaev, in this case, the decision to which instance to use is dealt by Route53 and HAProxy only redirects to Route53? If that's the case, we don't need the HAProxy, right? Is it possible to use Route53 as a load balancer for our instances? – Maurício Giordano Jun 12 '17 at 20:30
  • @MaurícioGiordano in this case we will not use HAProxy at all - its main benefit!!) all load balancing will be performed on Route53 level+health checks – Tim Bikbaev Jun 14 '17 at 07:36