load balancing mysql with haproxy - slides
TRANSCRIPT
![Page 2: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/2.jpg)
Copyright Severalnines AB
Webinar Housekeeping
!This webinar is being recorded
!A link to the recording & slides will be posted on severalnines.com
!We welcome questions: enter questions into the chat box and we will respond at the end of the presentation
!Think of something later? ! Email Severalnines at [email protected]
2
![Page 3: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/3.jpg)
Copyright Severalnines AB
Agenda
!What Is HAProxy?
!SQL Load Balancing with HAProxy
! Failure detection with MySQL
!Fault Tolerance with HAProxy
! Active/Passive setup
!Deployment Scenarios
! MySQL Cluster, Galera Cluster and MySQL Replication
!Load Balancing Alternatives
3
![Page 4: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/4.jpg)
Copyright Severalnines AB
Why Load Balance?
!Optimising server utilisation ! Queuing and throttling
!Maximise availability ! No SPOF
!Maximise throughput ! Distribute workload across a set of servers
!Scalability ! Site traffic grows ! Add DB servers to provide more throughput
4
LB
DB DB DB …
![Page 5: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/5.jpg)
!Reliable High Performance TCP/HTTP Load Balancer ! v1.5 stable released June 2014
! Native SSL support, IPv6, full HTTP keepalive, HTTP Compression … ! v1.4 most deployed and stable version, released Feb 2010
! client-side keep alive, TCP speedups, source base stickiness … ! Single Process Event-Driven. Can easily saturate 10Gb link with a single instance
! 108 000 session/s, http://www.haproxy.org/10g.html (2009) !Reverse Proxy
! load balancing, scaling out, failover (health checks) !High traffic sites like
! reddit, DISQUS, GitHub, Imgur, StackOverflow/Server Fault, Instagram …
Copyright Severalnines AB
HAProxy?
5
![Page 6: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/6.jpg)
Copyright Severalnines AB
SQL Load Balancing for MySQL
!Configuration file ! Sections; global, defaults, frontend, backend, listen ! /etc/haproxy/haproxy.cfg
!Global (process wide)
6
MySQL[WSREP]
Galera Replication (Synchronous)
LB
MySQL[WSREP]
MySQL[WSREP]
R/W R/W R/W
Client Client Client
global log 127.0.0.1 local0 pidfile /var/run/haproxy.pid daemon user nobody group nobody stats socket /tmp/haproxy.socket … node HAProxy_1 description HAProxy 1 maxconn 40000 spread-checks 3 quiet
![Page 7: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/7.jpg)
Copyright Severalnines AB
SQL Load Balancing for MySQL (cont.)
!defaults
7
MySQL[WSREP]
Galera Replication (Synchronous)
LB
MySQL[WSREP]
MySQL[WSREP]
R/W R/W R/W
Client Client Client
defaults log global mode tcp maxconn 40000 option dontlognull option tcp-smart-accept option tcp-smart-connect option redispatch retries 3 timeout queue 3500ms timeout connect 3500ms timeout client 120s timeout server 120s
![Page 8: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/8.jpg)
Copyright Severalnines AB
SQL Load Balancing for MySQL (cont.)
! listen (frontend + backend)
8
listen s9s1_33306_defaul_LB mode tcp bind *:33306 timeout client 60s timeout server 60s balance leastconn option httpchk default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 256 maxqueue 128 weight 100 server db1 10.0.3.70:3306 check server db2 10.0.3.60:3306 check server db3 10.0.3.50:3306 check
![Page 9: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/9.jpg)
Copyright Severalnines AB
SQL Load Balancing for MySQL (cont.)
! listen (stats ui)
9
userlist STATSUSERS group admin users admin user admin insecure-password admin
listen admin_page 0.0.0.0:9600 mode http stats enable stats refresh 60s stats uri / acl AuthOkay_ReadOnly http_auth(STATSUSERS) acl AuthOkay_Admin http_auth_group(STATSUSERS) admin stats http-request auth realm admin_page unless AuthOkay_ReadOnly
![Page 10: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/10.jpg)
Copyright Severalnines AB
10
!Session rate ! number of new connections per second
!Sessions ! current number of sessions/connections
!LastChk ! health check
!Wght ! server weight
![Page 11: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/11.jpg)
Copyright Severalnines AB
MySQL Health Checks
11
!Built-in MySQL health check ! option mysql-check user <username> ! Sends two MySQL packages
! Authentication and Quit ! No errors unless MySQL server is down
!Complete HTTP Request ! option httpchk ! Response: HTTP/1.1 200 OK ! Check Host and/or MySQL server state
! Galera Node State
MySQL[WSREP]
Galera Replication (Synchronous)
LB
MySQL[WSREP]
MySQL[WSREP]
R/W R/W R/W
Client Client Client
![Page 12: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/12.jpg)
Copyright Severalnines AB
MySQL Health Checks (cont.)
12
!Galera Node ! Check 1: Get node state
! SYNCED or DONOR ! Check 2: If Node is a “DONOR”
! Get the SST method ! xtrabackup
! Return “200 OK” ! rsync or mysqldump
! Return “503 Service Unavailable” Galera Node State
![Page 13: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/13.jpg)
Copyright Severalnines AB
MySQL Health Checks (cont.)
13
!xinetd service ! mysqlchk.sh (shell script) ! spams syslog by default
! log_on_success =
#/etc/xinetd.d/mysqlchk # default: on # description: mysqlchk service mysqlchk { flags = REUSE socket_type = stream port = 9200 wait = no user = nobody server = /usr/local/bin/mysqlchk.sh log_on_failure += USERID disable = no #only_from = 0.0.0.0/0 per_source = UNLIMITED }
![Page 14: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/14.jpg)
Copyright Severalnines AB
Network Tuning
!TCP tuning
14
#/etc/sysctl.conf net.ipv4.ip_nonlocal_bind=1 net.ipv4.tcp_tw_reuse=1 # reuse TIME-WAIT sockets net.ipv4.ip_local_port_range=1024 65023 # increase max num of ports net.ipv4.tcp_max_syn_backlog=40000 #Increase the number of outstanding syn requests net.ipv4.tcp_max_tw_buckets=400000 # Maximal number of timewait sockets net.ipv4.tcp_max_orphans=60000 net.ipv4.tcp_max_syn_backlog=40000 # TCP SYN Flood Protection net.ipv4.tcp_synack_retries=3 # TCP SYN Flood Protection net.core.somaxconn=40000 # burst connection rate net.ipv4.tcp_fin_timeout=5 # how long to keep sockets in FIN-WAIT-2
![Page 15: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/15.jpg)
Copyright Severalnines AB
Fault Tolerance for HAProxy
15
Client
HAProxy 1
MySQL MySQL MySQL
IP: 10.10.10.20
![Page 16: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/16.jpg)
Copyright Severalnines AB
Fault Tolerance for HAProxy (cont.)
!Active/Passive IP failover ! keepalived ! VRRP protocol provides router/IP failover
!VIP, “floating IP”: 10.10.0.10 ! net.ipv4.ip_nonlocal_bind=1
!Master - HAProxy 1 ! IP: 10.10.10.20
!Backup - HAProxy 2 ! IP: 10.10.10.30
16
![Page 17: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/17.jpg)
Copyright Severalnines AB
Fault Tolerance for HAProxy (cont.)
!keepalived configuration file
17
#/etc/keepalived/keepalived.conf vrrp_script chk_haproxy { script "killall -0 haproxy" # verify the pid existence interval 2 # check every 2 seconds weight 2 # adjust priority by this weight } vrrp_instance VI_1 { interface eth0 state MASTER virtual_router_id 51 # ID for this route priority 101 # 101 on master # 100 on backup virtual_ipaddress { 10.10.0.10 # the virtual IP } track_script { chk_haproxy } }
![Page 18: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/18.jpg)
Copyright Severalnines AB
ClusterControl HAProxy + Keepalived (cont.)
!Deployment and configuration automated from ClusterControl
18
![Page 19: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/19.jpg)
Copyright Severalnines AB
ClusterControl HAProxy + Keepalived (cont.)
19
![Page 20: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/20.jpg)
Copyright Severalnines AB
Deployment Scenarios
20
!MySQL Cluster / NDB Engine ! Active/Active, Shared nothing architecture ! SQL, Data and MGMT Nodes ! HAProxy co-located with MGMT Nodes
! MGMT is a light weight process ! Re-use “idle” servers ! Connections originate from HAProxy server
! Additional latency ! App Server <-> HAProxy <-> SQL Node <-> Data Node
NDB Data Node
NDB Data Node
(MGMT)
HAProxy
(MGMT)
HAProxy
SQL Node MySQL Server
SQL Node MySQL Server
![Page 21: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/21.jpg)
Copyright Severalnines AB
Deployment Scenarios (cont.)
21
NDB Data Node
NDB Data Node
!MySQL Cluster / NDB Engine ! Active/Active, Shared nothing architecture ! SQL, Data and MGMT Nodes ! HAProxy co-located with App servers
! No extra latency ! Extra load on app servers ! Health check loads on SQL nodes from all LBs
! Alt: MySQL Connector/J’s JDBC driver ! jdbc:mysql:loadbalance://host-1,host-2,…host-n/database?
loadBalanceBlacklistTimeout=5000 ! Config changes propagate to all app servers
(App)
HAProxy
(App)
HAProxy
SQL Node MySQL Server
SQL Node MySQL Server
![Page 22: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/22.jpg)
Copyright Severalnines AB
Deployment Scenarios (cont.)
22
MySQL SlaveMySQL Master
!MySQL Replication Master/Slave ! 1 Master for Writes ! N Slaves for Reads ! Separate front and backends configs for Master and Slaves
!ex: backend configuration for SlavesHAProxy
MySQL Slave
MySQL Slave
Writes
Readsfrontend front_reads mode tcp bind 192.168.100.110:3316 default_backend cluster_reads backend cluster_reads mode tcp balance roundrobin server Slave1 10.10.10.30:3306 check server Slave2 10.10.10.40:3306 check
![Page 23: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/23.jpg)
Copyright Severalnines AB
Deployment Scenarios (cont.)
23
!Galera Cluster ! Synchronous Replication, Read & Write to any Node ! Cluster wide optimistic locking
! Higher probability for “deadlocks” ! Pessimistic locking on the Node ! Ex: sequence table, “hotspot” tables
! Write to one node, Read/Write split ! Application: Retry deadlock errors
MySQL[WSREP]
Galera Replication (Synchronous)
LB
MySQL[WSREP]
MySQL[WSREP]
R/W R/W R/W
Client Client Client
![Page 24: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/24.jpg)
Copyright Severalnines AB
Alternatives!MySQL’s Connector/J JDBC driver
!jdbc:mysql:loadbalance://host-1,host-2,…
!PHP mysqlnd
! http://www.php.net/manual/en/intro.mysqlnd-ms.php
!Oracle’s MySQL Proxy 0.84
! http://dev.mysql.com/downloads/mysql-proxy/
!SkySQL’s MaxScale 1.0.0. beta
! https://github.com/skysql/MaxScale
!ProxySQL
! https://github.com/renecannao/proxysql
!Pen 0.24.0
! http://morestuff.siag.nu/category/pen/
24
![Page 25: Load Balancing MySQL with HAProxy - Slides](https://reader035.vdocuments.us/reader035/viewer/2022082209/55a68d161a28abb27d8b46a5/html5/thumbnails/25.jpg)
Copyright Severalnines AB
Thank You!
!Tutorial – MySQL Load Balancing with HAProxy ! http://www.severalnines.com/resources/clustercontrol-mysql-haproxy-load-
balancing-tutorial
!Severalnines Blog and Webinars ! www.severalnines.com/blog ! http://www.severalnines.com/resources/webinars
!More Questions? Contact us at: ! [email protected]
25