I have two MySQL servers in multi-master mode with HAproxy machines for simple load balancing/redundancy. When I connect directly to one of the servers and try to update approximately At 100,000 entries, it will be completed in about half a minute, including replication. When connecting through a proxy, it usually takes more than three minutes. Is this type of delay normal? Is there a problem with my proxy configuration (included below)? This makes me very frustrated because I think the proxy will do some kind of load balancing, or at least almost 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
#---------------- -------------------------------------------------- ---
global
# 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/s ysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local2
# chroot /var /lib/haproxy
# pidfile /var/run/haproxy.pid
maxconn 4096
user haproxy
group haproxy
daemon
#debug
#quiet
# 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
#- -------------------------------------------------- ------------------
defaults
mode tcp
log global
#option tcplog
option dontlognull
option tcp-smart-accept
option tcp-smart-connect
#option http-server-close
#option forwardfor ex cept 127.0.0.0/8
#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 0.0.0.0: 3306
mode tcp
balance roundrobin
option tcpka
option httpchk
server db01 192.168.15.118:3306 weight 1 inter 1s rise 1 fall 1
server db02 192.168.15.119:3306 weight 1 inter 1s rise 1 fall 1
The fact that the connection is updated once means that each connection has a little overhead. Many updates add many connections. If you can, try batch updates. Or, because its master/master simply selects a node and pushes the update there.
I have two MySQL servers in multi-master mode with HAproxy machines for simple load balancing/redundancy. When I connect directly to one of the servers and try to update approximately At 100,000 entries, it will be completed in about half a minute, including replication. When connecting through a proxy, it usually takes more than three minutes. Is this type of delay normal? Is there a problem with my proxy configuration (included below)? This makes me very frustrated because I think the proxy will do some kind of load balancing, or at least almost 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
#---------------- -------------------------------------------------- ---
global
# 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/sysc onfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local2
# chroot /var /lib/haproxy
# pidfile /var/run/haproxy.pid
maxconn 4096
user haproxy
group haproxy
daemon
#debug
#quiet
# 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
#- -------------------------------------------------- ------------------
defaults
mode tcp
log global
#option tcplog
option dontlognull
option tcp-smart-accept
option tcp-smart-connect
#option http-server-close
#option forwardfor excep t 127.0.0.0/8
#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 0.0.0.0: 3306
mode tcp
balance roundrobin
option tcpka
option httpchk
server db01 192.168.15.118:3306 weight 1 inter 1s rise 1 fall 1
server db02 192.168.15.119:3306 weight 1 inter 1s rise 1 fall 1
The fact that you connect once for each update means that each connection There is a little overhead. Many updates add many connections. If you can, try batch updates. Or, because its master/master only needs to select a node and push the update there.