In my previous post, I tried to fix the haproxy flaws by swapping it with Zen Load Balancer. Here's the thing though: Zen Load Balancer introduces a flaw of its own; It uses pen for TCP load balancing, which can increase the CPU load to stupid levels. On a relatively mild benchmark that I performed on my MySQL cluster, I witnessed pen's process shoot to 60% CPU usage. Not good.
So what do we do? Well, if we use the best open-source solution for MySQL clustering, Percona XtraDB cluster, the answer is pretty simple: we keep Zen Load Balancer and all its goodies but just for our MySQL farm, we revert to haproxy and some nifty tools provided to us by Percona and we're set! Best of both worlds!
Here's what we do:
Log in to your MySQL console and create a user "clustercheckuser" with the following credentials:
Now, Percona gives us two tools to work with: clustercheck and pyclustercheck (they do exactly the same thing, but pyclustercheck is written in python and does not require the use of xinetd). What it does is it sends an HTTP 200 response in case the cluster is up and running and an HTTP 503 in case there is something wrong with it. Goodbye haproxy's buggy mysql-check! Nice to see you again tried, tested and great httpchk!
Let's go ahead and configure everything required to run clustercheck. Change the 192.168.108.0/24 to the needs of your network:
Change port 9200 in /etc/services:
Now, let's go ahead and install xinetd:
Check that it's up and working:
As we can see, there is definitely a server listening on port 9200. Great, time to check our iptables rules:
Right, these are the ports needed for Percona XtraDB cluster to work and SSH. Everything else is rejected. What I need to do is add a rule before my "reject all" rule. So, let connections from my network to port 9200, as the 8th rule in the INPUT chain:
Check that everything is working (obviously substitute 192.168.108.20 with your node's IP address):
Nice! Time to head over to our Zen Load Balancer and configure haproxy on it. First, remember to delete or at least stop your MySQL farm (if you have any). Install and configure haproxy:
So what do we do? Well, if we use the best open-source solution for MySQL clustering, Percona XtraDB cluster, the answer is pretty simple: we keep Zen Load Balancer and all its goodies but just for our MySQL farm, we revert to haproxy and some nifty tools provided to us by Percona and we're set! Best of both worlds!
Here's what we do:
Log in to your MySQL console and create a user "clustercheckuser" with the following credentials:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 156322
Server version: 5.6.15-56-log Percona XtraDB Cluster (GPL), Release 25.4, Revision 731, wsrep_25.4.r4043
Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant process on *.* to 'clustercheckuser'@'localhost' identified by 'clustercheckpassword!';
Query OK, 0 rows affected (0.01 sec)
mysql> grant process on *.* to 'clustercheckuser'@'127.0.0.1' identified by 'clustercheckpassword!';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Now, Percona gives us two tools to work with: clustercheck and pyclustercheck (they do exactly the same thing, but pyclustercheck is written in python and does not require the use of xinetd). What it does is it sends an HTTP 200 response in case the cluster is up and running and an HTTP 503 in case there is something wrong with it. Goodbye haproxy's buggy mysql-check! Nice to see you again tried, tested and great httpchk!
Let's go ahead and configure everything required to run clustercheck. Change the 192.168.108.0/24 to the needs of your network:
[root@mysql1 ~]# vi /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/bin/clustercheck
log_on_failure += USERID
only_from = 192.168.108.0/24
per_source = UNLIMITED
}
Change port 9200 in /etc/services:
[root@mysql1 ~]# vi /etc/services ....
sun-as-jpda 9191/udp # Sun AppSvr JPDA
mysqlchk 9200/tcp # Percona mysqlchk
#wap-wsp 9200/tcp # WAP connectionless session service
wap-wsp 9200/udp # WAP connectionless session service
....
Now, let's go ahead and install xinetd:
[root@mysql1 ~]# yum -y install xinetd.x86_64
[root@mysql1 ~]# chkconfig xinetd on
[root@mysql1 ~]# service xinetd startCheck that it's up and working:
[root@mysql1 ~]# netstat -ntlp Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 3842/master
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3704/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2637/sshd
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 3704/mysqld
tcp 0 0 ::1:25 :::* LISTEN 3842/master
tcp 0 0 :::9200 :::* LISTEN 20371/xinetd
tcp 0 0 :::22 :::* LISTEN 2637/sshd
As we can see, there is definitely a server listening on port 9200. Great, time to check our iptables rules:
[root@mysql1 ~]# iptables -L -v -n --line-numbers Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 318K 62M ACCEPT all -- * * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
2 2 120 ACCEPT all -- lo * 0.0.0.0/0 0.0.0.0/0
3 0 0 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:3306
4 0 0 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:4444
5 4 240 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:4567
6 0 0 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:4568
7 2 120 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 state NEW tcp dpt:22
8 0 0 REJECT all -- * * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 0 0 REJECT all -- * * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 271K 28M ACCEPT all -- * * 0.0.0.0/0 0.0.0.0/0
Right, these are the ports needed for Percona XtraDB cluster to work and SSH. Everything else is rejected. What I need to do is add a rule before my "reject all" rule. So, let connections from my network to port 9200, as the 8th rule in the INPUT chain:
[root@mysql1 ~]# iptables -I INPUT 8 -s 192.168.108.0/24 -p tcp -m tcp --dport 9200 -j ACCEPT
[root@mysql1 ~]# iptables -L -v -n --line-numbers Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 318K 62M ACCEPT all -- * * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
2 2 120 ACCEPT all -- lo * 0.0.0.0/0 0.0.0.0/0
3 0 0 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:3306
4 0 0 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:4444
5 4 240 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:4567
6 0 0 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:4568
7 2 120 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 state NEW tcp dpt:22
8 0 0 ACCEPT tcp -- * * 192.168.108.0/24 0.0.0.0/0 tcp dpt:9200
9 0 0 REJECT all -- * * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 0 0 REJECT all -- * * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT 0 packets, 0 bytes)
num pkts bytes target prot opt in out source destination
1 271K 28M ACCEPT all -- * * 0.0.0.0/0 0.0.0.0/0
[root@mysql1 ~]# iptables-save > /etc/sysconfig/iptables
Check that everything is working (obviously substitute 192.168.108.20 with your node's IP address):
[root@mysql1 ~]# nc 192.168.108.20 9200 HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
Nice! Time to head over to our Zen Load Balancer and configure haproxy on it. First, remember to delete or at least stop your MySQL farm (if you have any). Install and configure haproxy:
root@zen-lb:~# apt-get update
root@zen-lb:~# apt-get install haproxy
root@zen-lb:~# mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.orig
root@zen-lb:~# vi /etc/haproxy/haproxy.cfg global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
chroot /usr/share/haproxy
user haproxy
group haproxy
daemon
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 50000
timeout connect 3500ms
timeout client 50000ms
timeout server 50000ms
listen stats :445 #We set up our stats screen, remove block if not wanted or could be integrated below if mode was http. Now we can access the stats at http://LOAD_BALANCER_IP:445/haproxy using username: haproxy and password: haproxy
mode http
stats enable
#stats hide-version
stats realm Haproxy\ Statistics
stats uri /haproxy
stats auth haproxy:haproxy_password
listen Percona_xtradb_cluster_read 192.168.104.10:3306
balance roundrobin # Typical roundrobin method
mode tcp #In this mode, the service relays TCP connections as soon as they're established, towards one or several servers. No processing is done on the stream. Two other options are: http and health
option tcpka #Enable TCP keep-alives on both the client and server sides. This makes it possible to prevent long sessions from expiring on external layer 4 components such as firewalls and load-balancers.
option httpchk #When option httpchk is specified, a complete HTTP request is sent once the TCP connection is established, and responses 2xx and 3xx are considered valid, while all other ones indicate a server failure, including the lack of any response.
server MySQL1 192.168.108.20:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL2 192.168.108.30:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL3 192.168.108.40:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL4 192.168.108.50:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL5 192.168.108.60:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL6 192.168.108.70:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL7 192.168.108.80:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL8 192.168.108.90:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
server MySQL9 192.168.108.100:3306 check port 9200 inter 5000 downinter 30000 rise 5 fall 1
Here, I have configured the farm to listen to 192.168.1.104.10, port 3306 and my Percona XtraDB cluster has 9 nodes:192.168.108.20, 192.168.108.30, 192.168.108.40, 192.168.108.50, 192.168.108.60, 192.168.108.70, 192.168.108.80, 192.168.108.90, and 192.168.108.100. The maximum connections are 50,000. The connection timeout is 3.5s, while the response timeout is 50s. As before, you need to change these settings to the needs of your network. Set the response timeout too soon and you'll get false positives, resulting to servers getting shut off and connections between a client and a working server cut off, which results to unhappy clients; Set it too high and your load balancer will be late shutting off traffic to dead servers, resulting your service seeming unavailable to some clients, which results to unhappy clients. Finally, it sends probes to check whether a node is up or down every 5 secs if the node has been marked as 'up' (inter 5000), every 30 secs if it has marked as 'down' (downinter 30000), while it while mark a node that has been marked as 'down' only after 5 successful probes (rise 5) but will mark a node that has been marked as 'up' after a single unsuccessful probe (fall 1).
You will also want to change the stats screen variables. Some people remove the "listen stats" section altogether, but if you decide to keep it, you'll definitely want to change the password, which I have set to "haproxy_password", perhaps the user, which I have set to "haproxy" and maybe also the URI and the port (I have set it to http://192.168.104.10:445/haproxy).
We'll also change our haproxy memory usage settings. Change this according to your system's resources, I've set mine to use 2 gigs of RAM:
root@zen-lb:~# vi /etc/default/haproxy # Set ENABLED to 1 if you want the init script to start haproxy.
ENABLED=1
# Add extra flags here.
EXTRAOPTS="-de -m 2048"
Almost done. Time to create the necessary directories, make sure they have the correct permissions, start haproxy and arrange so that zen load balancer automatically starts and stops it in case of restarts, cluster failovers etc:
root@zen-lb:~# mkdir /usr/share/haproxy
root@zen-lb:~# chown haproxy:haproxy /usr/share/haproxy/
root@zen-lb:~# chown haproxy:haproxy /etc/haproxy/haproxy.cfg
root@zen-lb:~# chmod 640 /etc/haproxy/haproxy.cfg
root@zen-lb:~# service haproxy start
root@zen-lb:~# update-rc.d haproxy defaults
root@zen-lb:~# vi /usr/local/zenloadbalancer/config/zlb-start#make your own script in your favorite language, it will be called
#at the end of the procedure /etc/init.d/zenloadbalacer start
#and replicated to the other node if zen cluster is running.
service haproxy restart
root@zen-lb:~# vi /usr/local/zenloadbalancer/config/zlb-stop#make your own script in your favorite language, it will be called
#at the end of the procedure /etc/init.d/zenloadbalacer start
#and replicated to the other node if zen cluster is running.
service haproxy stop
Remember when I mentioned about my relatively mild benchmark and that I witnessed pen's process shoot to 60% CPU usage? Care to guess what haproxy's usage is now under the same conditions? 20%. Great stuff.
No comments:
Post a Comment