Galera data on Percona Cloud Tools (and other MySQL monitoring tools)

I was talking with a Percona Support customer earlier this week who was looking for Galera data on Percona Cloud Tools. (Percona Cloud Tools, now in free beta, is a hosted service providing access to query performance insights for all MySQL uses.)

The customer mentioned they were already keeping track of some Galera stats on Cacti, and given they were inclined to use Percona Cloud Tools more and more, they wanted to know if it was already supporting Percona XtraDB Cluster. My answer was: “No, not yet: you can install agents in each node (the regular way in the first node, then manually on the other nodes… and when prompted say “No” to create MySQL user and provide the one you’re using already) and monitor them as autonomous MySQL servers – but the concept of cluster and specially the “Galera bits” has yet to be implemented there.

Except I was wrong.

By “concept of cluster” I mean supporting the notion of group instances, which should allow a single cluster-wide view for metrics and query reports, such as the slow queries (which are recorded locally on the node where the query was run and thus observed in a detached way). This still needs to be implemented indeed, but it’s on the roadmap.

The “Galera bits” I mentioned above are the various “wsrep_” status variables. In fact, those refer to the Write Set REPlication patches (based in the wsrep API), a set of hooks applied to the InnoDB/XtraDB storage engine and other components of MySQL that modifies the way replication works (to put it in a very simplified way), which in turn are used by the Galera library to provide a “generic Synchronous Multi-Master replication plugin for transactional applications.” A patched version of Percona Server together with the Galera libray compose the base of Percona XtraDB Cluster.

As I found out only now, Percona Cloud Tools does collect data from the various wsrep_ variables and it is available for use – it’s just not shown by default. A user only needs to add a dashboard/chart manually on PCT to see these metrics:


Click on the picture to enlarge it

Now, I need to call that customer …

Monitoring the cluster

Since I’m touching this topic I thought it would be useful to include some additional information on monitoring a Galera (Percona XtraDB Cluster in particular) cluster, even though most of what I mention below has already been published in different posts here on the MySQL Performance Blog. There’s a succint documentation page bearing the same title of this section that indicates the main wsrep_ variables you should monitor to check the health status of the cluster and how well it’s coping with load along the time (performance). Remember you can get a grasp of the full set of variables at any time by issuing the following command from one (or each one) of the nodes:

mysql> SHOW GLOBAL STATUS LIKE "wsrep_%";

And for a broader and real time view of the wsrep_ status variables you can use Jay Janssen’s myq_gadgets toolkit, which he modified a couple of years ago to account for Galera.

There’s also a specific Galera-template available in our Percona Monitoring Plugins (PMP) package that you can use in your Cacti server. That would cover the “how well the cluster copes with load along the time,” providing historical graphing. And while there isn’t a Galera specific plugin for Nagios in PMP, Jay explains in another post how you can customize pmp-check-mysql-status to “check any status variable you like,” describing his approach to keep a cluster’s “health status” in check by setting alerts on specific stats, on a per node basis.

VividCortex recently added a set of templates for Galera in their product and you can also rely on Severalnines’ ClusterControl monitoring features to get that “global view” of your cluster that Percona Cloud Tools doesn’t yet provide. Even though ClusterControl is a complete solution for cluster deployment and management, focusing on the automation of the whole process, the monitoring part alone is particularly interesting as it encompasses cluster-wide information in a customized way, including the “Galera bits”. You may want to give it a try as the monitoring features are available in the Community version of the product (and if you’re a Percona customer with a Support contract covering Percona XtraDB Cluster, then you’re entitled to get support for it from us).

One thing I should note that differentiate the monitoring solutions from above is that while you can install Cacti, Nagios and ClusterControl as servers in your own infrastructure both Percona Cloud Tools and VividCortex are hosted, cloud-based services. Having said that, neither one nor the other upload sensitive data to the cloud and both provide options for query obfuscation.


Contrary to what I believed, Percona Cloud Tools does provide support for “Galera bits” (the wsrep_ status variables), even though it has yet to implement support for the notion of group instances, which will allow for cluster-wide view for metrics and query reports. You can also rely on the Galera template for Cacti provided by Percona Monitoring Plugins for historical graphing and some clever use of Nagios’ pmp-check-mysql-status for customized cluster alerts. VividCortex and ClusterControl also provide monitoring for Galera.

Percona Cloud Tools, now in free beta, is a hosted service providing access to query performance insights for all MySQL uses. After a brief setup, unlock new information about your database and how to improve your applications. Sign up to request access to the beta today.  

The post Galera data on Percona Cloud Tools (and other MySQL monitoring tools) appeared first on MySQL Performance Blog.


How to add an existing Percona XtraDB Cluster to Percona ClusterControl

In my last blog post I explained how to use Percona ClusterControl to create a new Percona XtraDB Cluster from scratch. That’s a good option when you want to create a testing environment in just some mouse clicks. In this case I’m going to show you how to add your existing cluster to Percona ClusterControl so you can manage and monitor it on the web interface.

The environment will be pretty similar, we will have UI, CMON and 3 XtraDB Cluster nodes. The cluster should be already running and Percona ClusterControl also installed.

Adding an existing Cluster

The ClusterControl web interface is empty, there are no clusters on it. To add an existing one we need to click on “Add existing Galera Cluster.” (Click on the image for an enlarged view).



Add Existing Cluster
A new form will be shown pretty similar to the one we saw last time when we were creating a new cluster. We can divide the form in two parts. First we need to give information about our Cluster. The info requires is the Linux distribution and version, IP of PXC nodes and MySQL root passwords. Pretty easy:





In the second part we have the SSH configuration. There is one pre-requisite, the UI server should be able to connect to all servers using a SSH key. Therefore, our first step is to create a SSH key pair in our UI server and copy the public one to all other servers.



It is also necessary to add the private key in the web interface. You can do it using the form shown after clicking on “Add Key Pair”:

Add key pair

Once the key is added, we can verify the access:

Access Check

As we can see here, everything works as expected and all servers are reachable by SSH. The parameter “Create shared SSH key” also needs to be enabled. That option will make ClusterControl to create a new SSH key pair on CMON node so this one can also connect to PXC nodes with passwordless SSH.

Now everything is prepared. We can proceed with the deployment. Just click on “Add cluster” and the installation process will start. While the installation is in progress you will see this notification:



Deployment notification


Clicking on it we can see the progress of the deployment:


After some minutes our PXC is shown in the Percona ClusterControl UI:


Now we can monitor it, get alerts, clone, run backups and everything from the web interface. You can also add multiple clusters and create new ones.

The post How to add an existing Percona XtraDB Cluster to Percona ClusterControl appeared first on MySQL Performance Blog.


How to create a new Percona XtraDB Cluster with ClusterControl

We recently announced Percona ClusterControl supplied in partnership with Severalnines. This enterprise-grade cluster management and monitoring software is available with a Percona Support contract covering Percona XtraDB Cluster, MySQL Cluster, or MySQL Galera. In this blog post I am going to show you how to use Percona ClusterControl to to create a new 3-node cluster with Percona XtraDB Cluster.

Percona ClusterControl architecture

The software has three main parts:

  • Percona ClusterControl UI: This is the web interface from where we can create new clusters and monitor and manage existing ones.

In this example hostname cc and IP

  • Controller (or cmon): We need one controller for each Percona XtraDB Cluster. The controller is placed between ClusterControl UI and the cluster itself. It connects to the cluster to gather information or run commands and send back info to the ClusterControl UI.

In this example hostname cmon and IP

  • Percona XtraDB Cluster: This is the cluster itself. We can create a new one or manage an existing one.

In this example hostnames xtradb1, xtradb2, xtradb3 and IPs

Installation of Percona ClusterControl UI

First we need to install the Percona ClusterControl UI. We provide a link to the installation script in Percona’s customer portal. We just need to download the script and run it as root in the server designed for Percona ClusterControl UI.

root@cc:~# ./

The installer will do everything for us. It will download Percona ClusterControl UI and all the dependencies needed from distribution’s repositories. It will also install MySQL, PHP5 and Apache. When the installation process finishes it will show this message:

Initial setup complete. To finalize the installation open your web browser
and go to this URL, to finalize the installation.
Public/external IP => http://x.x.x.x/install
NOTE: Detected more than one IP:

Now we can access the Percona ClusterControl UI from the web interface and continue the installation process there. The installer will check if the server meets all the pre-requisites and it will ask some extra details to finish the installation:

ClusterControl pre-requisites

  • MySQL username and password: This is the MySQL server the installed for us and where the Percona ClusterControl UI will store its own information.
  • Your name, logging e-mail and password: This is important, because this is the admin account for the Percona ClusterControl UI. The e-mail is also very important, because it is where we will receive new customised package installations created by the tool as we will see later on.

If everything is correct click on “Install” and it will show us the following notice:

Percona ClusterControl installed

After accepting the notice, Percona ClusterControl will show us the login screen.

ClusterControl Login

This is the main page still with no clusters monitored.

ClusterControl Installed

Creating our first Percona XtraDB Cluster

The next step is to install our cluster. It can be done from the web interface. We just need to click on “Create Database Cluster” button. The cluster creation wizard will start. First we need to choose which kind of configuration we want to deploy. We will choose “Galera Cluster for MySQL“:

ClusterControl Creation

The next step in the wizard is to provide the info about the kind of Galera Cluster we want and the network info about the nodes.

ClusterControl Form

It is divided in two different sections:

  • Settings: Here we have to make several decisions and provide info about our cluster nodes. By default Percona XtraDB Cluster will be deployed. We will need to provide extra info like the IPs, Linux distribution, hardware information and cluster configuration parameters.
  • Cluster Control Node: Here we need to configure the controller or cmon for this cluster. The controller node is the one that will connect to all our cluster nodes, install software and monitor them. Therefore, this control node needs to have access with SSH to all other nodes. That’s the reason we can upload an SSH private key. It is recommended that all 5 servers can connect to each other with passwordless authentication (root user) using an SSH key.

Now we have two options. Just deploy the cluster from the web interface or generate an installation package. We are going to use the second option so we can see what it does under the hood. When we click the “Generate Package” it will send us a tar.gz to our e-mail address.

Control Node and Cluster deployment:

We need to upload the package to the control node and run the installation script. It will check the connectivity, firewall ports and the SSH keys:

root@cmon:~/s9s-galera-percona-2.4.0/mysql/scripts/install# bash ./ 2>&1 |tee cc.log
Checking EC2 Keypair:
Key pair found
Firewalls must accept  4567 (galera), 4567+1 (galera IST),  22 (ssh),  (rsync), 3306 (mysql), ping (icmp).

Then it will connect to the three cluster nodes and start the installation process using apt-get or yum. Packages like xtrabackup, Percona XtraDB Cluster or netcat will be installed to ensure normal cluster operations. scp -P22 ../../repo/RPM-GPG-KEY-percona root@[ok] Executing 'gpg --import /root/s9s_tmp/RPM-GPG-KEY-percona' [ok] Executing '/bin/sh -c 'gpg -a --export CD2EFD2A | apt-key add -'' [ok] Executing '/bin/sh -c 'echo "deb wheezy main" >> /etc/apt/sources.list'' [ok] Executing '/bin/sh -c 'echo "deb-src wheezy main" >> /etc/apt/sources.list'' [ok] Executing 'apt-get -q -y update' [ok] Executing 'LC_ALL=en_US.utf8 DEBIAN_FRONTEND=noninteractive apt-get -o Dpkg::Options::='--force-confnew' -y -q install percona-xtradb-cluster-galera-2.x percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-server-5.5 percona-xtrabackup' [ok]

All the Percona XtraDB Cluster process is automatic. When the installation of nodes is finished it will start to install the Control Node package and its dependencies (apache, PHP and MySQL). After that, the process has finished:

galera : [  up]
galera : [  up]
galera : [  up]
Installation completed in 618 seconds
*                                                                             *
*                            CONGRATULATIONS                                  *
*                                                                             *
You have now installed one of the most advanced db infrastructures available.
Open your browser to
Logon with your email address and password.
This is your ClusterControl API token, xxxxxxxxxxxxxxxxxxxxxxxxxx, keep it for safekeeping if you need to (re)register your cluster again.

We need to open the URL and insert the API token there:

ClusterControl API

After that we need to “connect” the Control Node with the Percona ClusterControl UI:

Register the new CMON

And that’s all. We have Percona ClusterControl installed and a new Percona XtraDB Cluster in our infrastructure that we can monitor and manage from a web interface.

ClusterControl nodes


Percona ClusterControl is a really useful and user-friendly tool to manage and monitor our clusters. We can use it to create new clusters as we have seen here or to manage already existing clusters. I will write a second blog post explaining how to put our already in production Percona XtraDB Cluster under Percona ClusterControl management.

The post How to create a new Percona XtraDB Cluster with ClusterControl appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by