MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster
How do I… Monitor the status & the configuration of my cluster?
Short answer
Use:
status()
Long answer…
Assuming you already have a MySQL InnoDB Cluster up and running. If not, please RTFM 🙂
Additionally you can read this tutorial and this article from my colleague lefred or this one on Windows Platform from my colleague Ivan.
I’m using MySQL 8.0.15
MySQL localhost:33060+ JS> session.sql('SELECT VERSION()')
+-----------+
| VERSION() |
+-----------+
| 8.0.15 |
+-----------+Let’s connect to my cluster
$ mysqlsh root@localhost --cluster
Please provide the password for 'root@localhost': ****
MySQL Shell 8.0.15
Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1520 (X protocol)
Server version: 8.0.15 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
You are connected to a member of cluster 'pocCluster'.
Variable 'cluster' is set.
Use cluster.status() in scripting mode to get status of this cluster or cluster.help() for more commands.The “– – cluster” argument enables cluster management by setting the global variable.
This variable is a reference to the MySQL InnoDB Cluster object session. It will give you access (among others) to the status() method that allows you to check and monitor the cluster.
Ask for help
The built-in help is simply awesome!
MySQL localhost:33060+ JS> cluster.help('status')
NAME
status - Describe the status of the cluster.
SYNTAX
<Cluster>.status([options])
WHERE
options: Dictionary with options.
RETURNS
A JSON object describing the status of the cluster.
DESCRIPTION
This function describes the status of the cluster including its
ReplicaSets and Instances. The following options may be given to control
the amount of information gathered and returned.
- extended: if true, includes information about transactions processed by
connection and applier, as well as groupName and memberId values.
- queryMembers: if true, connect to each Instance of the ReplicaSets to
query for more detailed stats about the replication machinery.
EXCEPTIONS
MetadataError in the following scenarios:
- If the Metadata is inaccessible.
- If the Metadata update operation failed.Cluster status
So let’s discover the status of our cluster
MySQL localhost:33060+ JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "172.19.0.11:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"172.19.0.11:3306": {
"address": "172.19.0.11:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"172.19.0.12:3306": {
"address": "172.19.0.12:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"172.19.0.13:3306": {
"address": "172.19.0.13:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "172.19.0.11:3306"
}Note:
The instance’s state in the cluster directly influences the information provided in the status report. Therefore ensure the instance you are connected to has a status of ONLINE.
As you can see, by default status() gives you a lot of relevant information.
Thus it could be used to monitor your cluster although the best tool available to monitor your MySQL InnoDB Cluster (but also MySQL Replication, MySQL NDB Cluster and obviously your standalone MySQL servers) is MySQL Enterprise Monitor.
More details with “A Guide to MySQL Enterprise Monitor“.
Extended cluster status
MySQL Group Replication provides several metrics and detailed information about the underlying cluster in MySQL InnoDB clusters.
These metrics which are used for monitoring are based on these Performance Schema tables.
Some of these information are available through MySQL Shell. You can control the amount of information gathered and returned with 2 options: extended & queryMembers.
extended
if enabled, includes information about groupName and memberID for each member; and general statistics about the number of transactions checked, proposed, rejected by members…
MySQL localhost:33060+ JS> cluster.status({extended:true})
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"groupName": "72568575-561c-11e9-914c-0242ac13000b",
"name": "default",
"primary": "172.19.0.11:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"172.19.0.11:3306": {
"address": "172.19.0.11:3306",
"memberId": "4a85f6c4-561c-11e9-8401-0242ac13000b",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"transactions": {
"appliedCount": 2,
"checkedCount": 53,
"committedAllMembers": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,
72568575-561c-11e9-914c-0242ac13000b:1-51",
"conflictsDetectedCount": 0,
"inApplierQueueCount": 0,
"inQueueCount": 0,
"lastConflictFree": "72568575-561c-11e9-914c-0242ac13000b:56",
"proposedCount": 53,
"rollbackCount": 0
}
},
"172.19.0.12:3306": {
"address": "172.19.0.12:3306",
"memberId": "4ad75450-561c-11e9-baa8-0242ac13000c",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"transactions": {
"appliedCount": 44,
"checkedCount": 43,
"committedAllMembers": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,
72568575-561c-11e9-914c-0242ac13000b:1-41",
"conflictsDetectedCount": 0,
"inApplierQueueCount": 0,
"inQueueCount": 0,
"lastConflictFree": "72568575-561c-11e9-914c-0242ac13000b:52",
"proposedCount": 0,
"rollbackCount": 0
}
},
"172.19.0.13:3306": {
"address": "172.19.0.13:3306",
"memberId": "4b77c1ec-561c-11e9-9cc1-0242ac13000d",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"transactions": {
"appliedCount": 42,
"checkedCount": 42,
"committedAllMembers": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,
72568575-561c-11e9-914c-0242ac13000b:1-41",
"conflictsDetectedCount": 0,
"inApplierQueueCount": 0,
"inQueueCount": 0,
"lastConflictFree": "72568575-561c-11e9-914c-0242ac13000b:53",
"proposedCount": 0,
"rollbackCount": 0
}
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "172.19.0.11:3306"
}queryMembers
if enabled, includes information about recovery and regular transaction I/O, applier worker thread statistic and any lags; applier coordinator statistic…
MySQL localhost:33060+ JS> cluster.status({queryMembers:true})
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "172.19.0.11:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"172.19.0.11:3306": {
"address": "172.19.0.11:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"transactions": {
"connection": {
"lastHeartbeatTimestamp": "",
"lastQueued": {
"endTimestamp": "2019-04-03 14:26:33.394755",
"immediateCommitTimestamp": "",
"immediateCommitToEndTime": null,
"originalCommitTimestamp": "",
"originalCommitToEndTime": null,
"queueTime": 0.000077,
"startTimestamp": "2019-04-03 14:26:33.394678",
"transaction": "72568575-561c-11e9-914c-0242ac13000b:13"
},
"receivedHeartbeats": 0,
"receivedTransactionSet": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,
72568575-561c-11e9-914c-0242ac13000b:1-65",
"threadId": null
},
"workers": [
{
"lastApplied": {
"applyTime": 0.022927,
"endTimestamp": "2019-04-03 14:26:33.417643",
"immediateCommitTimestamp": "",
"immediateCommitToEndTime": null,
"originalCommitTimestamp": "",
"originalCommitToEndTime": null,
"retries": 0,
"startTimestamp": "2019-04-03 14:26:33.394716",
"transaction": "72568575-561c-11e9-914c-0242ac13000b:13"
},
"threadId": 58
}
]
}
},
"172.19.0.12:3306": {
"address": "172.19.0.12:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"transactions": {
"connection": {
"lastHeartbeatTimestamp": "",
"lastQueued": {
"endTimestamp": "2019-04-03 15:42:30.855989",
"immediateCommitTimestamp": "",
"immediateCommitToEndTime": null,
"originalCommitTimestamp": "2019-04-03 15:42:30.854594",
"originalCommitToEndTime": 0.001395,
"queueTime": 0.000476,
"startTimestamp": "2019-04-03 15:42:30.855513",
"transaction": "72568575-561c-11e9-914c-0242ac13000b:65"
},
"receivedHeartbeats": 0,
"receivedTransactionSet": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,
72568575-561c-11e9-914c-0242ac13000b:1-65",
"threadId": null
},
"workers": [
{
"lastApplied": {
"applyTime": 0.024685,
"endTimestamp": "2019-04-03 15:42:30.880361",
"immediateCommitTimestamp": "",
"immediateCommitToEndTime": null,
"originalCommitTimestamp": "2019-04-03 15:42:30.854594",
"originalCommitToEndTime": 0.025767,
"retries": 0,
"startTimestamp": "2019-04-03 15:42:30.855676",
"transaction": "72568575-561c-11e9-914c-0242ac13000b:65"
},
"threadId": 54
}
]
}
},
"172.19.0.13:3306": {
"address": "172.19.0.13:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"transactions": {
"connection": {
"lastHeartbeatTimestamp": "",
"lastQueued": {
"endTimestamp": "2019-04-03 15:42:30.855678",
"immediateCommitTimestamp": "",
"immediateCommitToEndTime": null,
"originalCommitTimestamp": "2019-04-03 15:42:30.854594",
"originalCommitToEndTime": 0.001084,
"queueTime": 0.000171,
"startTimestamp": "2019-04-03 15:42:30.855507",
"transaction": "72568575-561c-11e9-914c-0242ac13000b:65"
},
"receivedHeartbeats": 0,
"receivedTransactionSet": "4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,
72568575-561c-11e9-914c-0242ac13000b:1-65",
"threadId": null
},
"workers": [
{
"lastApplied": {
"applyTime": 0.021354,
"endTimestamp": "2019-04-03 15:42:30.877398",
"immediateCommitTimestamp": "",
"immediateCommitToEndTime": null,
"originalCommitTimestamp": "2019-04-03 15:42:30.854594",
"originalCommitToEndTime": 0.022804,
"retries": 0,
"startTimestamp": "2019-04-03 15:42:30.856044",
"transaction": "72568575-561c-11e9-914c-0242ac13000b:65"
},
"threadId": 54
}
]
}
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "172.19.0.11:3306"
}To summarize
Q: How do I monitor the status & the configuration of my cluster?
A: Use status() or status({extended:true}) or status({queryMembers:true})
References
- status() method
- Working with InnoDB Cluster
- Checking a cluster’s Status with Cluster.status()
- MySQL InnoDB Cluster – Extended cluster information and rescan
- Performance Schema Replication Tables
- Tutoriel – Déployer MySQL 8.0 InnoDB Cluster (09-2019)
- MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster
- MySQL InnoDB Cluster – HowTo #2 – Validate an instance
Thanks for using MySQL!
Watch my videos on my YouTube channel and subscribe.
Thanks for using HeatWave & MySQL!
Cloud Solutions Architect at Oracle
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
or both at the same time 😉
cluster.status({extended:true,queryMembers:true})
Indeed!
Thanks for your feedback buddy 🙂
running cluster.status({extended:true,queryMembers:true})
mysqlsh Ver 8.0.15-commercial for Linux on x86_64 – for MySQL 8.0.15 (MySQL Enterprise Server – Commercial)
output : Error
cluster.status({queryMembers:true})
Cluster.status: get_uint(25): field value out of the allowed range (LogicError)
Thanks for your comment!
Probably related to https://bugs.mysql.com/bug.php?id=95191
We are working on a fix.
Thanks
[…] MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster […]