I have two MySQL servers in multi-master mode, with an HAproxy machine for simple load balancing/redundancy. When I am connected to one of the servers directly and try to update about 100,000 entries, it is completed including replication in about half a minute. When connecting through the proxy it takes usually over three whole minutes. Is it normal to have that type of latency? Is something amiss with my proxy configuration (included below)? This is getting really frustrating as I assumed the proxy would do some sort of load balancing, or at least have little to no overhead.

# Example configuration for a possible web application.  See the
# full configuration options online.
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt

# Global settings
# to have these messages end up in /var/log/haproxy.log you will
# need to:
# 1) configure syslog to accept network log events.  This is done
#    by adding the '-r' option to the SYSLOGD_OPTIONS in
#    /etc/sysconfig/syslog
# 2) configure local2 events to go to the /var/log/haproxy.log
#   file. A line like the following can be added to
#   /etc/sysconfig/syslog
#    local2.*                       /var/log/haproxy.log
log local2

#    chroot      /var/lib/haproxy
#    pidfile     /var/run/haproxy.pid
maxconn     4096
user        haproxy
group       haproxy

# turn on stats unix socket
stats socket /var/lib/haproxy/stats

# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
mode                    tcp
log                     global
#option                  tcplog
option                  dontlognull
option                  tcp-smart-accept
option                  tcp-smart-connect
#option http-server-close
#option forwardfor       except
#option                  redispatch
retries                 3
#timeout http-request    10s
#timeout queue           1m
timeout connect         400
timeout client          500
timeout server          300
#timeout http-keep-alive 10s
#timeout check           10s
maxconn                 2000

listen mysql-cluster
    mode tcp
    balance roundrobin
    option tcpka
    option httpchk

server db01 weight 1 inter 1s rise 1 fall 1
server db02 weight 1 inter 1s rise 1 fall 1
  • 1
    How are you doing the update? Is it connecting and then doing an update query that updates all of the records? Is it a single connection for each row updated? – MichaelB Jun 26 '12 at 0:22
  • It would be a single connection for each row, as that's what the code monkeys are telling me their application ends up doing. – Joe Gibson Jun 26 '12 at 12:58
  • I've been testing options 1 by 1 from the HAproxy documentation (haproxy.1wt.eu/download/1.4/doc/configuration.txt) to see if I can get any improvement, no luck so far. – Joe Gibson Jun 26 '12 at 14:32
  • It seems as part of the issue was connecting through windows CLI instead of just telling mysql to connect to the proxy server from my CentOS box. I'm still getting about 8-9 seconds of overhead. Not as good as I'd like but more reasonable. – Joe Gibson Jun 26 '12 at 16:26

The fact that you are connecting once for each update means that each connection has a little overhead. That many updates adds up over a lot of connections. Try to batch the updates if you can. Or, since it's master/master just pick one node and push the updates there.

|improve this answer|||||
  • Problem is even if I route it to one node the times are still drastically increased when I go through the Proxy. If I connect directly to one of the nodes from another machine and run the update, it goes about twice as fast as running the same through the proxy. My understanding was that using a proxy in front of our servers would increase the overall speed (using a different server for multiple connections). – Joe Gibson Jun 29 '12 at 16:16
  • 1
    Everything is a trade off. What you lose in connection overhead with your configuration will likely be made up for in gains in concurrency, mysql server load, and lock contention. – longneck Jun 30 '12 at 11:36
  • That's understandable. I guess I just had some misunderstandings about how things are supposed to operate with a proxy. Thanks. – Joe Gibson Jul 3 '12 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.