Difference between revisions of "Pandora: Documentation en: Percona Cluster"
Line 10: | Line 10: | ||
We will have 3 nodes, which all have Percona Cluster installed, and they are all connected. Two of the nodes will have HAProxy and Keepalived installed, otherwise when node 1 fails, the whole cluster will fail. The first node has to be started with bootstrap. | We will have 3 nodes, which all have Percona Cluster installed, and they are all connected. Two of the nodes will have HAProxy and Keepalived installed, otherwise when node 1 fails, the whole cluster will fail. The first node has to be started with bootstrap. | ||
+ | <center> | ||
[[File:node123.jpg]] | [[File:node123.jpg]] | ||
+ | </center> | ||
node #1 | node #1 | ||
Line 27: | Line 29: | ||
The picture below shows that the system uses two HAProxy servers and keepalived with Virtual IP-addresses. Keepalived is needed for load balancing and high availability. This system ensures that when the first node fails, the others still keep working. | The picture below shows that the system uses two HAProxy servers and keepalived with Virtual IP-addresses. Keepalived is needed for load balancing and high availability. This system ensures that when the first node fails, the others still keep working. | ||
+ | <center> | ||
[[File:proxyvip.jpg]] | [[File:proxyvip.jpg]] | ||
+ | </center> | ||
The top part of the architecture is meant for applications, in this case Pandora FMS. We will have a Pandora FMS server, console and agents set up to work with the Percona Cluster. | The top part of the architecture is meant for applications, in this case Pandora FMS. We will have a Pandora FMS server, console and agents set up to work with the Percona Cluster. | ||
+ | <center> | ||
[[File:pandoralogo.png]] | [[File:pandoralogo.png]] | ||
− | + | </center> | |
=== Percona introduction === | === Percona introduction === | ||
Line 43: | Line 48: | ||
The Cluster consists of Nodes. Recommended configuration is to have at least 3 nodes, but you can make it running with 2 nodes as well. | The Cluster consists of Nodes. Recommended configuration is to have at least 3 nodes, but you can make it running with 2 nodes as well. | ||
+ | <center> | ||
[[File:node123.jpg]] | [[File:node123.jpg]] | ||
+ | </center> | ||
Each Node is regular MySQL / Percona Server setup. The point is that you can convert your existing MySQL / Percona Server into Node and roll Cluster using it as a base. Or otherwise – you can detach Node from Cluster and use it as just a regular server. | Each Node is regular MySQL / Percona Server setup. The point is that you can convert your existing MySQL / Percona Server into Node and roll Cluster using it as a base. Or otherwise – you can detach Node from Cluster and use it as just a regular server. | ||
Line 49: | Line 56: | ||
Each Node contains the full copy of data. That defines XtraDB Cluster behavior in many ways. And obviously there are benefits and drawbacks. | Each Node contains the full copy of data. That defines XtraDB Cluster behavior in many ways. And obviously there are benefits and drawbacks. | ||
+ | <center> | ||
[[File:cluster.png]] | [[File:cluster.png]] | ||
+ | </center> | ||
Percona XtraDB Cluster provides: | Percona XtraDB Cluster provides: | ||
Line 399: | Line 408: | ||
HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. High availability is required to keep the cluster running even if the bootstrapping node fails. Load balancing makes sure that workloads are distributed equally. | HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. High availability is required to keep the cluster running even if the bootstrapping node fails. Load balancing makes sure that workloads are distributed equally. | ||
+ | <center> | ||
[[File:Haproxy.jpg]] | [[File:Haproxy.jpg]] | ||
+ | </center> | ||
The picture above shows how HAProxy communicates with the nodes | The picture above shows how HAProxy communicates with the nodes | ||
Line 566: | Line 577: | ||
Now you should have an architecture like in the picture below: | Now you should have an architecture like in the picture below: | ||
+ | <center> | ||
[[File:Architecture.jpg]] | [[File:Architecture.jpg]] | ||
+ | </center> | ||
== Avoid a problem of 'too many open files' == | == Avoid a problem of 'too many open files' == |
Revision as of 14:57, 10 July 2017
Contents
1 Introduction
This guide’s purpose is to have a working system with an architecture similar to the picture below:
We will have 3 nodes, which all have Percona Cluster installed, and they are all connected. Two of the nodes will have HAProxy and Keepalived installed, otherwise when node 1 fails, the whole cluster will fail. The first node has to be started with bootstrap.
node #1 hostname: percona1 IP: 192.168.70.71
node #2 hostname: percona2 IP: 192.168.70.72
node #3 hostname: percona3 IP: 192.168.70.73
The picture below shows that the system uses two HAProxy servers and keepalived with Virtual IP-addresses. Keepalived is needed for load balancing and high availability. This system ensures that when the first node fails, the others still keep working.
The top part of the architecture is meant for applications, in this case Pandora FMS. We will have a Pandora FMS server, console and agents set up to work with the Percona Cluster.
1.1 Percona introduction
Percona XtraDB Cluster is open-source, free MySQL High Availability software. It integrates Percona Server and Percona XtraBackup with the Galera library of MySQL high availability solutions in a single package which enables you to create a cost-effective MySQL high availability cluster.
The main benefit of using Percona cluster would be the performance gains. Although these gains are not linear, for example 3-node cluster would not result in 3 times higher performance. One reason being that in a cluster one node must also distribute tasks.
The Cluster consists of Nodes. Recommended configuration is to have at least 3 nodes, but you can make it running with 2 nodes as well.
Each Node is regular MySQL / Percona Server setup. The point is that you can convert your existing MySQL / Percona Server into Node and roll Cluster using it as a base. Or otherwise – you can detach Node from Cluster and use it as just a regular server.
Each Node contains the full copy of data. That defines XtraDB Cluster behavior in many ways. And obviously there are benefits and drawbacks.
Percona XtraDB Cluster provides:
- We have successfully performed rolling restarts and upgrades of our production cluster without stopping production traffic.
- Synchronous replication. Transaction either committed on all nodes or none.
- Multi-master replication. You can write to any node.
- Parallel applying events on slave. Real “parallel replication”.
- Automatic node provisioning.
- Data consistency. No more unsynchronized slaves.
Percona XtraDB Cluster drawbacks:
- Overhead of joining new node. The new node has to copy full dataset from one of existing nodes. If it is 100GB, it copies 100GB.
- This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes.
- You have several duplicates of data, for 3 nodes – 3 duplicates.
2 Percona installation
Prerequisites:
- CentOS installation
- Firewall has been set up to allow connecting to ports 3306, 4444, 4567 and 4568 (or disabled)
- Delete any existing mysql installations
- SELinux is disabled - you can use command
echo 0 >/selinux/enforce
or check status with command
sestatus
Check the IP of your nodes with:
ifconfig
Here are the examples:
node #1 hostname: percona1 IP: 192.168.70.71
node #2 hostname: percona2 IP: 192.168.70.72
node #3 hostname: percona3 IP: 192.168.70.73
2.1 Installing the required packages
Firstly we need to install the required packages (Yum, Percona RPM and Percona XtraDB Cluster).
Insert command for installing repository with yum package:
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
Enter Yes whenever asked. Install XtraDB Cluster package:
yum install Percona-XtraDB-Cluster-56
Insert command to enable the repository:
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
2.2 Setting up the nodes
Now we are going to set up the configuration file. Individual nodes should be configured to be able to bootstrap the cluster.
2.2.1 Node #1
Open up terminal and open my.cnf file:
nano /etc/my.cnf
Set up my.cnf with this configuration (with your own IP’s) in node1 then save and exit.
Configuration should look like this:
[mysqld] datadir=/var/lib/mysql user=mysql # Path to Galera library wsrep_provider=/usr/lib64/libgalera_smm.so # Cluster connection URL contains the IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73 # In order for Galera to work correctly binlog format should be ROW binlog_format=ROW # MyISAM storage engine has only experimental support default_storage_engine=InnoDB # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Node #1 address wsrep_node_address=192.168.70.71 # SST method wsrep_sst_method=xtrabackup-v2 # Cluster name wsrep_cluster_name=my_centos_cluster # Authentication for SST method wsrep_sst_auth="sstuser:s3cret"
After setting up my.cnf, enter this command:
mysql_install_db --datadir=/var/lib/mysql --user=mysql
Now start node1 with this command:
/etc/init.d/mysql bootstrap-pxc
or
service mysql bootstrap-pxc
This command will start the cluster with initial wsrep_cluster_address set to gcomm://. This way the cluster will be bootstrapped and in case the node or MySQL have to be restarted later, there would be no need to change the configuration file.
The output should look like this:
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster). SUCCESS!
To check mysql status, first enter:
mysql -u root
then enter this command:
show status like 'wsrep%';
The output should look something like this:
+----------------------------+--------------------------------------+ | Variable_name | Value +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec ... | wsrep_local_state | 4 | wsrep_local_state_comment| Synced ... | wsrep_cluster_size | 1 | wsrep_cluster_status | Primary | wsrep_connected | ON ... | wsrep_ready | ON +----------------------------+--------------------------------------+ 40 rows in set (0.01 sec)
(Optional) To add password for root, use this command:
UPDATE mysql.user SET password=PASSWORD("yourpassword") where user='root';
then:
FLUSH PRIVILEGES;
In order to perform successful State Snapshot Transfer using XtraBackup new user needs to be set up with proper privileges with these commands:
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret';
then:
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
and also:
FLUSH PRIVILEGES;
Exit mysql with:
exit
When the first node is finished, you will need to configure the other nodes.
2.2.2 Node #2
Now we need to stop node1 with:
service mysql stop
Next step is to open node2 terminal and modify my.cnf with this command:
nano /etc/my.cnf
Paste this configuration to node2 my.cnf (with your own IP’s) then save and exit.
Configuration should look like this:
[mysqld] datadir=/var/lib/mysql user=mysql # Path to Galera library wsrep_provider=/usr/lib64/libgalera_smm.so # Cluster connection URL contains IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73 # In order for Galera to work correctly binlog format should be ROW binlog_format=ROW # MyISAM storage engine has only experimental support default_storage_engine=InnoDB # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Node #2 address wsrep_node_address=192.168.70.72 # Cluster name wsrep_cluster_name=my_centos_cluster # SST method wsrep_sst_method=xtrabackup-v2 #Authentication for SST method wsrep_sst_auth="sstuser:s3cret"
After setting up node2 my.cnf, enter this command:
mysql_install_db --datadir=/var/lib/mysql --user=mysql
If node2 is configured correctly, then we need to start Mysql.
Start mysql with this command:
service mysql start
The output should be like this:
Starting MySQL (Percona XtraDB Cluster)... [ OK ]
This means that everything is correct and working and we can continue to configure node3.
2.2.3 Node #3
Now we are going to configure node3. Stop node2 mysql with:
service mysql stop
Open node3 terminal and open my.cnf with:
nano /etc/my.cnf
Paste this configuration to node3 my.cnf (with your own IP’s) then save and exit.
Configuration should look like this:
[mysqld] datadir=/var/lib/mysql user=mysql # Path to Galera library wsrep_provider=/usr/lib64/libgalera_smm.so # Cluster connection URL contains IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73 # In order for Galera to work correctly binlog format should be ROW binlog_format=ROW # MyISAM storage engine has only experimental support default_storage_engine=InnoDB # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Node #3 address wsrep_node_address=192.168.70.73 # Cluster name wsrep_cluster_name=my_centos_cluster # SST method wsrep_sst_method=xtrabackup-v2 #Authentication for SST method wsrep_sst_auth="sstuser:s3cret"
Start node3 mysql with this command
service mysql start
The output should look like this
Starting MySQL (Percona XtraDB Cluster)... [ OK ]
If the output looks like that, then you can continue with the steps. But first stop node3 mysql with this command:
service mysql stop
2.3 Starting the Server
If all the nodes work, then we need to stop them all and start them in the correct order. For this, follow these steps:
Now start node1 with this command:
/etc/init.d/mysql bootstrap-pxc
After bootstrapping node1, start node2 and node3 with this command:
service mysql start
After the server has been started, it should receive the state snapshot transfer automatically on nodes 2 and 3.
Now check status in mysql (node1, node2 or node3):
mysql -u root
or if you set up a password then:
mysql -u root -p
then check status:
show status like 'wsrep%';
Look for this line to see how many nodes are connected to cluster:
| wsrep_cluster_size | 3 |
To test the cluster, follow these steps:
Create database on node2 mysql ( connect with mysql -u root or if you set up a password then mysql -u root -p ). Command for database:
CREATE DATABASE percona;
Output: Query OK, 1 row affected (x sec) Create example table on node3 ( connect with mysql -u root or if you set up a password then mysql -u root -p ). First command for example table:
USE percona;
Output: Database changed
Second command:
CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Output: Query OK, 0 rows affected (x sec) Inserting records on node1 ( connect with mysql -u root or if you set up a password then mysql -u root -p ). Command for inserting records:
INSERT INTO percona.example VALUES (1, 'percona1');
Output: Query OK, 1 row affected (x sec) Retrieving all the rows from that table on node2: Command:
SELECT * FROM percona.example;
Output:
+-----------+------------------+ | node_id | node_name | +-----------+------------------+ | 1 | percona1 | +-----------+------------------+ 1 row in set (0.00 sec)
This example shows that all nodes in the cluster are synchronized and working as intended.
3 Setting up Pandora Console
Now that you have all the nodes configured, you should start setting up the Pandora Console. Follow these steps:
Install Pandora FMS server and console using this guide: http://wiki.pandorafms.com/index.php?title=Pandora:Documentation_en:Installing#Installation_in_Red_Hat_Enterprise_Linux_.2F_Fedora_.2F_CentOS Open Terminal, log into Pandora server using command
ssh [email protected]<pandora_server_ip>
Connect to MySQL using command
mysql -u root -p
Enter this command to grant all privileges:
grant all privileges on pandoraname.* ‘pandora’@<node #1 ip> identified by ‘pandora’;
Run <Pandora FMS server IP>/pandora_console/install.php Use guide: http://wiki.pandorafms.com/index.php?title=Pandora:Documentation_en:Installing#Console_Initial_Configuration
Please note that apache may be under different names, in our case it was httpd. Few examples: apache, apached, http, httpd, apache2
Now we need to configure the config.php and pandora_server.conf files to work with the nodes:
Configure config.php with
nano /var/www/html/pandora_console/include/config.php
Change dbuser to root, dbpass to pandora (default) and dbhost to node #1 ip. Configure pandora_server.conf with:
nano /etc/pandora/pandora_server.conf
Change dbuser to root, dbpass to pandora (default) and dbhost to node #1 ip. Start all nodes from 1 to 3, bootstraping first, then restart pandora_server with command:
service pandora_server restart
4 Setting up HAProxy
HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. High availability is required to keep the cluster running even if the bootstrapping node fails. Load balancing makes sure that workloads are distributed equally.
The picture above shows how HAProxy communicates with the nodes
Prerequisites:
At least 2 nodes with:
- SELinux's enforcing “permissive”
To do that you need to modify your SELinux config with
nano /etc/selinux/config
and change SELINUX=disabled to SELINUX=permissive and reboot
- iptables disabled
To disable iptables, use command
service iptables stop
- ntpd ON
To enable ntpd, use command
service ntpd start
4.1 Install the HAProxy software
Execute this command on all individual nodes to install HAProxy software:
yum -y install haproxy
4.2 Configure HAProxy
In this part we will configure the HAproxy configuration files to work with the nodes. Simply follow these steps:
Make a backup of the configuration by executing this command
mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.orig
Now make a new configuration file
nano /etc/haproxy/haproxy.cfg
Paste the lines below and set your own parameters (hostname and IP’s of Node1, Node2 and Node3 )
global log 127.0.0.1 local0 log 127.0.0.1 local1 notice maxconn 4096 uid 188 gid 188 daemon #debug #quiet defaults log global mode http option tcplog option dontlognull retries 3 option redispatch maxconn 2000 contimeout 5000 clitimeout 50000 srvtimeout 50000 listen mysql-cluster 0.0.0.0:33306 mode tcp balance roundrobin option httpchk server percona1 192.168.70.71:3306 check port 9200 inter 12000 rise 3 fall 3 server percona2 192.168.70.72:3306 check port 9200 inter 12000 rise 3 fall 3 server percona3 192.168.70.73:3306 check port 9200 inter 12000 rise 3 fall 3 listen mysql-cluster-failover :43306 mode tcp balance leastconn option httpchk server percona1 192.168.70.71:3306 check port 9200 server percona2 192.168.70.72:3306 check port 9200 backup server percona3 192.168.70.73:3306 check port 9200 backup
With this configuration HAProxy will load balance between three nodes.
Save and exit the configuration file
Execute these two commands to start server
service haproxy start
and
chkconfig haproxy on
5 Make the two HAProxy nodes redundant with Keepalived
5.1 Installing the latest version of Keepalived
The latest version of keepalived will be installed on your system with the use of this command:
yum install -y keepalived
5.2 Configuring Keepalived
On both two nodes create the configuration file by following these steps:
Rename original configuration file with a command (for backup):
mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.backup
Create a new file with a command:
nano /etc/keepalived/keepalived.conf
Insert this configuration to created file, replace the Virtual IP address, the email address, the SMTP's IP address and set the OTHER nodes IP address with your values:
global_defs { notification_email { [email protected] } notification_email_from [email protected] smtp_server 192.168.70.1 smtp_connect_timeout 30 router_id LVS_DEVEL } vrrp_script chk_haproxy { script "killall -0 haproxy" interval 1 } vrrp_instance VI_1 { interface eth0 state MASTER smtp_alert virtual_router_id 51 priority 101 # on the nodes considered slaves, change 101 -> 100 unicast_peer { 192.168.70.72 # this is the other node's IP address } advert_int 1 authentication { auth_type PASS auth_pass 1111 } # check every second # add 2 points of prio if OK # 101 on master, 100 on slaves virtual_ipaddress { 192.168.70.54 } } track_script { chk_haproxy }
In order to be able to bind on a IP which is not yet defined on the system, we need to enable non local binding at the kernel level:
Open sysctl.conf with command:
nano /etc/sysctl.conf
Add a line to the bottom:
net.ipv4.ip_nonlocal_bind = 1
To enable it, use this command:
sysctl -p
Now start keepalive using these commands:
service keepalived start
and
chkconfig keepalived on
You can use this command to check which node the Virtual IP is associated with:
ip addr sh eth0
If done correctly the output should look similar to this:
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 44:37:e6:dd:fa:27 brd ff:ff:ff:ff:ff:ff inet 192.168.70.71/24 brd 192.168.70.255 scope global eth0 inet 192.168.70.54/32 scope global eth0 inet6 fe80::4637:e6ff:fedd:fa27/64 scope link valid_lft forever preferred_lft forever
Now you should have an architecture like in the picture below:
6 Avoid a problem of 'too many open files'
To avoid this error, open the limits configuration file
nano /etc/security/limits.conf
Add these two lines below at the end then save and exit
* soft nofile 60000 * hard nofile 60000
Open sysctl.conf
nano /etc/sysctl.conf
then add the following line:
fs.file-max = 900000
After that enter command to enable it:
sysctl -p
7 Optimization
This part is optional, but recommended. For optimal cluster work you should optimize your configuration files, we added these lines to our /etc/my.cnf file:
innodb_io_capacity = 100 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT key_buffer = 300M innodb_buffer_pool_size = 600M innodb_log_buffer_size =32M max_allowed_packet = 64M bind-address = 0.0.0.0 innodb_lock_wait_timeout = 30 query_cache_size = 128M query_cache_limit = 2M join_buffer_size = 16M log_warnings back_log=100 max_connections=500 innodb_file_per_table
The result were as follows:
Prior to optimization:
-> Current rate: 0.82 modules/sec -> Current rate: 0.69 modules/sec -> Current rate: 0.60 modules/sec -> Current rate: 0.80 modules/sec -> Current rate: 0.81 modules/sec -> Current rate: 0.56 modules/sec
After optimization:
-> Current rate: 62.44 modules/sec -> Current rate: 61.49 modules/sec -> Current rate: 74.81 modules/sec -> Current rate: 73.90 modules/sec -> Current rate: 73.22 modules/sec -> Current rate: 77.00 modules/sec -> Current rate: 72.77 modules/sec -> Current rate: 77.10 modules/sec -> Current rate: 72.44 modules/sec -> Current rate: 69.99 modules/sec -> Current rate: 70.81 modules/sec
Using this configuration should improve performance noticeably although considering you might be running on different hardware, improvements might not be as big as ours. Tinkering with these setting might result in even higher performance gains.