Extending Percona Monitoring and Management for MySQL InnoDB Cluster with Custom Queries
A few days ago, a customer got in touch asking how they could use Percona Monitoring and Management (PMM) to monitor the roles played by each node in an InnoDB cluster. More specifically, they wanted to check when one of the nodes changed its role from Primary to Secondary, or vice-versa. PMM allows for a high level of flexibility and customization through its support for custom queries, and we just have to be creative on how we approach the problem. In this post, we present the steps we did to test our solution, including the deployment of a 3-node InnoDB Cluster hosted in the same server (for testing) and a PMM 2 server, and connecting them together. Even though this has already been covered in other blog posts and in the online documentation, we hope that providing the full procedure will be helpful if you want to create a similar test environment and try your hand at creating custom dashboards for InnoDB Cluster on PMM.
Deploying Our Test Cluster
Starting with a fresh new CentOS 7.6 test server, we disabled SELinux (to make things simpler in this test as we will be using a set of non-standard ports in this environment) by setting SELINUX=disabled in /etc/selinux/config and restarting the server. Once this was done, we configured the Percona YUM repository in order to install the latest version of Percona Server for MySQL available at the time of this draft.
$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm $ sudo percona-release setup ps80
Starting With a Regular Percona Server for MySQL
$ sudo yum install percona-server-server
Note: if you are strictly looking at deploying a test InnoDB Cluster using local sandboxes, MySQL Shell includes a built-in functionality for this, dba.deploySandboxInstance(). The procedure we use below is less straight-forward but can be easily adjusted to deploy a regular cluster by having each instance installed in a dedicated server instead.
A fresh installation of Percona Server 8 on CentOS will leave us with an empty datadir, we need to start the server in order to initialize it. But before we do it, let’s take advantage that the server was not yet started and add some extra settings required for InnoDB cluster to work by editing its main configuration file, /etc/my.cnf. Here’s how it looks like now:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid relay-log=relay-bin binlog-checksum=NONE gtid-mode=on enforce_gtid_consistency=on super-read-only=OFF port=3306 server-id=1 report-host=node-1 report-port=3306 mysqlx-port=33060 mysqlx-socket=/tmp/mysqlx-33060.sock
The last two settings are used to configure access to the MySQL Shell, which we will use to set up our InnoDB Cluster. We can now start the server:
$ sudo systemctl start mysqld
To access MySQL for the first time, we need to get hold of the temporary password from the log file:
$ sudo grep password /var/log/mysqld.log
We can then connect to the server:
$ mysql -uroot -p
And set a new password:
mysql> set password = '5JYz!Bkcr';
In order to manage an InnoDB Cluster, we need a privileged user that can connect from any of the nodes (i.e., from other than localhost). Since our instances will all be hosted in the same server, we created a privileged account restricted to the server’s IP address, using the same password as above:
mysql> create user 'root'@'192.168.80.12' identified by '5JYz!Bkcr'; mysql> grant all privileges on *.* to 'root'@'192.168.80.12' with grant option;
The Group Replication plugin itself is not loaded by default so we have to do it manually (or otherwise make use of plugin_load_add for this):
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
One last requirement to fulfill; the report-host defined in the MySQL configuration file needs to be resolved internally; we do this by editing the server’s /etc/hosts file and linking it to the server’s private IP address:
192.168.80.12 node-1
Creating an InnoDB Cluster
Now that the server is up and properly configured we can deploy our test cluster from it. This is done through the MySQL Shell, so let’s install the respective package:
$ sudo yum install percona-mysql-shell
and access it:
$ mysqlsh
The Shell is an advanced MySQL client that provides JavaScript scripting capabilities by default. From it, we can validate that the user we created is “suitable for cluster usage”:
MySQL JS > dba.configureLocalInstance('root@192.168.80.12:3306')
Providing all goes well, we can connect to the server in the Shell:
MySQL JS > \c root@192.168.80.12:3306
and proceed to create our test cluster. This being JavaScript, after all, the cluster itself is instantiated as cluster (or whatever term you prefer to use):
MySQL 192.168.80.12:3306 ssl JS > cluster = dba.createCluster('test_cluster')
We can verify the status of our cluster with:
MySQL 192.168.80.12:3306 ssl JS > cluster.status()
Adding a Second Node
So far we have a 1-node test cluster up and running. Let’s add a couple of extra nodes to it. We start by editing the /etc/my.cnf configuration file of our server and adding a new mysqld option group below the existing [mysqld] one in order to define the new instance that will become our second node:
[mysqld@replica01] datadir=/var/lib/mysql01 socket=/var/lib/mysql01/mysql.sock log-error=/var/log/mysqld01.log pid-file=/var/run/mysqld/mysqld01.pid relay-log=relay-bin binlog-checksum=NONE gtid-mode=on enforce_gtid_consistency=on super-read-only=OFF port=3307 server-id=2 report-host=node-2 report-port=3307 mysqlx-port=33070 mysqlx-socket=/tmp/mysqlx-33070.sock
As you can see above, the definition of this new mysqld instance is very similar to the main one; we highlighted the differences between them in bold in the table above.
Before initializing this new instance, we had to create its own log file since the mysql user does not have write permission under /var/log:
$ sudo install -omysql -gmysql -m0640 /dev/null /var/log/mysqld01.log
Until not long ago, we would rely on mysqld_multi to manage multiple MySQL instances running on the same server but this tool is not necessary for systemd-managed distributions; we just need to indicate which of the option groups defined in /etc/my.cnf we want the command to affect:
$ sudo systemctl start mysqld@replica01
After we initialize the new instance, we need to do exactly as we did with the main we configured as our first node, starting by finding what is the temporary password attributed to it:
$ sudo grep password /var/log/mysqld01.log
Then connecting to the instance to update the password, create the new account, and installing the Group Replication plugin:
$ mysql -uroot -p -S /var/lib/mysql01/mysql.sock mysql> set password = '5JYz!Bkcr'; mysql> create user 'root'@'192.168.80.12' identified by '5JYz!Bkcr'; mysql> grant all privileges on *.* to 'root'@'192.168.80.12' with grant option; mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
This second node reports itself as node-2 so we need to adjust /etc/hosts to support the name-resolving:
192.168.80.12 node-1 node-2
We can now go back to our MySQL Shell session and add the node to the existing cluster:
192.168.80.12:3306 ssl JS > cluster.addInstance('root@192.168.80.12:3307')
During this process, the system will identify in this new node “errant GTIDs that do not exist in the cluster“; chose [C]lone to “completely overwriting the state of node-2:3307 with a physical snapshot from an existing cluster member“; the seed instance, in this case, will be the only other node in the cluster, node-1.
Running cluster.status() again will now show a 2-node cluster:
{
"clusterName": "test_cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node-1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"node-1:3306": {
"address": "node-1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"node-2:3307": {
"address": "node-2:3307",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node-1:3306"
}
Adding a Third Node
There are other ways we could deploy a new mysqld instance other than initializing it from scratch. Adding a new group option to /etc/my.cnf remains indispensable though:
[mysqld@replica02] datadir=/var/lib/mysql02 socket=/var/lib/mysql02/mysql.sock log-error=/var/log/mysqld02.log pid-file=/var/run/mysqld/mysqld02.pid relay-log=relay-bin binlog-checksum=NONE gtid-mode=on enforce_gtid_consistency=on super-read-only=OFF port=3308 server-id=3 report-host=node-3 report-port=3308 mysqlx-port=33080 mysqlx-socket=/tmp/mysqlx-33080.sock
For this third instance, instead of initializing the server and then proceeding to adjust its password, create the new account, and install the plugin, let’s make a cold copy of the second instance’s datadir and start from there (a binary backup with Percona XtraBackup would also work):
$ sudo systemctl stop mysqld@replica01 $ sudo cp -r -p=mode,ownership /var/lib/mysql01 /var/lib/mysql02 $ sudo systemctl start mysqld@replica01
Under the datadir, the auto.cnf file will contain the source server’s UUID while the mysqld-auto.cnf file will contain that server’s group replication settings. Let’s remove them to allow a fresh start for this new instance:
$ sudo rm /var/lib/mysql02/auto.cnf $ sudo rm /var/lib/mysql02/mysqld-auto.cnf $ sudo systemctl start mysqld@replica02
One last edit to /etc/hosts:
192.168.80.12 node-1 node-2 node-3
And we can add the new instance to the cluster from the Shell session:
MySQL 192.168.80.12:3306 ssl JS > cluster.addInstance('root@192.168.80.12:3308')
This completes our 3-node InnoDB test cluster:
MySQL 192.168.80.12:3306 ssl JS > cluster.status()
{
"clusterName": "test_cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node-1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"node-1:3306": {
"address": "node-1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"node-2:3307": {
"address": "node-2:3307",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"node-3:3308": {
"address": "node-3:3308",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node-1:3306"
}
Note that at any time you can connect to the cluster by instantiating the cluster object on MySQL Shell once you are connected to any of the nodes that compose it:
$ mysqlsh MySQL JS > \c root@192.168.80.12:3307 MySQL 192.168.80.12:3307 ssl JS > cluster = dba.getCluster('test_cluster')
Deploying a PMM Server
Deploying a Percona Monitoring and Management (PMM) version 2 server with Docker remains as simple as it has been since its early days:
- Make sure to have the latest Docker image of PMM2:
$ docker pull percona/pmm-server:2
- Create the data container:
$ docker create -v /srv --name pmm-data percona/pmm-server:2 /bin/true
- Create the server container:
$ docker run -d --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:2
That’s it! Now, it is just a matter of finding out which IP address has been assigned to pmm-server in order to access its web interface:
$ docker inspect -f '' pmm-server 172.17.0.3
Monitoring the InnoDB Cluster on PMM
We start by installing the PMM 2 client package on our test server. Since the Percona Repository is already configured there, it is as simple as executing:
$ sudo yum install pmm2-client
Our test server is a Vagrant machine with two network interfaces, eth0, and eth1. The latter, not the former, is the one that can access the PMM server we just created so when configuring the PMM client we need to indicate that communication should be established through its IP address, otherwise the main network interface would be used by default:
$ sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@172.17.0.3:443 192.168.80.12
Since we have 3 mysqld instances running on the server, we will have to set up 3 distinct agents (or exporters) to retrieve data from each of them. To differentiate the instances on PMM, we will name them node1, node2, and node3. And to make things simpler, we will use the existing main root user to connect to the instances:
$ sudo pmm-admin add mysql --query-source=perfschema --username=root --password='5JYz!Bkcr' --port=3306 --host=localhost node1 $ sudo pmm-admin add mysql --query-source=perfschema --username=root --password='5JYz!Bkcr' --port=3307 --host=localhost node2 $ sudo pmm-admin add mysql --query-source=perfschema --username=root --password='5JYz!Bkcr' --port=3308 --host=localhost node3
A final check to make sure everything is in order:
$ sudo pmm-admin status
Agent ID: /agent_id/24651474-7f70-4bbc-bf86-67b1f3825bb6
Node ID : /node_id/3ec368d6-0b68-4db0-99e8-f9d0f06f26b6
PMM Server:
URL : https://172.17.0.3:443/
Version: 2.7.0
PMM Client:
Connected : true
Time drift : 7.080757ms
Latency : 10.285062ms
pmm-admin version: 2.7.0
pmm-agent version: 2.7.0
Agents:
/agent_id/3214e903-4907-4526-b4e2-a32df809bd14 mysqld_exporter Running
/agent_id/5f5704f5-3706-4be7-b87a-35b4aba9b791 mysql_perfschema_agent Running
/agent_id/636ee6bb-f1c2-4d37-8478-3c0d29643f97 node_exporter Running
/agent_id/8a89402e-c3b2-420d-b533-ebb206bd5cbe mysqld_exporter Running
/agent_id/af4a7932-c581-4ee2-b457-39f4ea7353b2 mysql_perfschema_agent Running
/agent_id/c26e60fb-b3d8-4283-91d5-7d8def4dd37a mysqld_exporter Running
/agent_id/f9142f47-b4e0-41bb-89bb-7025163cf8e6 mysql_perfschema_agent Running
Note: the mysql_perfschema_agent agents are used for Query Analytics (QAN).
Creating a Custom Query and Dashboard
Now that the test cluster is in place and monitored by a PMM server, we can finally get to focus on the main task at hand:
How can we monitor the roles played by each node in an InnoDB cluster on PMM?
We can find the current role played by each node in the cluster by querying the PERFORMANCE_SCHEMA:
mysql> SELECT MEMBER_HOST, MEMBER_ROLE AS ROLE FROM performance_schema.replication_group_members; +-------------+-----------+ | MEMBER_HOST | ROLE | +-------------+-----------+ | node-2 | SECONDARY | | node-3 | SECONDARY | | node-1 | PRIMARY | +-------------+-----------+ 3 rows in set (0.02 sec)
It is not possible to plot this information on a graph and track changes between PRIMARY and SECONDARY roles as is. But we can be a little creative here and convert these values to numbers, say 1 to PRIMARY and 2 otherwise;
mysql> SELECT IF (MEMBER_ROLE='PRIMARY',1,2) AS ROLE FROM performance_schema.replication_group_members; +------+ | ROLE | +------+ | 2 | | 2 | | 1 | +------+ 3 rows in set (0.19 sec)
Even though this data is not currently being collected by the MySQL exporter we can configure it to start doing so by creating a custom query:
Starting from the version 1.15.0, PMM provides user the ability to take a SQL SELECT statement and turn the result set into metric series in PMM.
On PMM 2, custom queries are stored under the /usr/local/percona/pmm2/collectors/custom-queries directory. You choose how often you want the data to be retrieved by configuring it in the respective resolution level sub-directory. For this experiment, let’s configure it to run often by editing /usr/local/percona/pmm2/collectors/custom-queries/mysql/high-resolution/queries-mysqld.yml as follows:
mysql_performance_schema_group_replication_member:
query: "SELECT IF (MEMBER_ROLE='PRIMARY',1,2) AS ROLE FROM performance_schema.replication_group_members WHERE MEMBER_HOST=@@report_host"
metrics:
- ROLE:
usage: "GAUGE"
description: "The current role of the group member."
Note we modified the above query adding a WHERE clause so it returns only the value for the role of the node being queried.
To create a graph to display this metric, click on the + (“plus”) icon in the menu occupying the left side of the PMM web interface home page, then Create, Dashboard, and Add Query. In the Query section, on the bottom of that screen, enter the following under Metrics: mysql_performance_schema_group_replication_member_ROLE – that is the custom query name suffixed by the metric name, ROLE. Below it, in the Legend field, enter: – that’s because we’ve created all three MySQL instances on the same server, each being covered by a separate agent and thus considered a “service”. In a production environment, you would probably use instead, or maybe .:
Creating a Dashboard for our custom query
Finally, click on the little “disk” icon on the top of the screen, Save dashboard, and choose a name for it; we named ours “InnoDB Cluster node role”:
The custom dashboard showing the exact moment node2 is promoted to PRIMARY
Note there are other dashboards available on PMM2. Instead of the traditional Graph used for data visualization we can choose a Discrete panel and map the members’ roles back to their original naming:
Using a Discrete panel to display the nodes’ roles
There you have it: two dashboards to monitor InnoDB Cluster node roles powered by a custom query. What other metrics do you want to monitor?
by Fernando Laudares Camargos via Percona Database Performance Blog
Comments
Post a Comment