{"id":2951,"date":"2019-04-11T09:28:16","date_gmt":"2019-04-11T08:28:16","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2951"},"modified":"2019-09-03T07:37:49","modified_gmt":"2019-09-03T06:37:49","slug":"mysql-innodb-cluster-howto-1-monitor-your-cluster","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2019\/04\/11\/mysql-innodb-cluster-howto-1-monitor-your-cluster\/","title":{"rendered":"MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster"},"content":{"rendered":"\n
\"Sakila<\/figure><\/div>\n\n\n\n

How do I\u2026 Monitor the status & the configuration of my cluster?<\/h2>\n\n\n\n

Short answer<\/h3>\n\n\n\n

Use:<\/p>\n\n\n\n

status<\/a><\/strong>()<\/p>\n\n\n\n\n\n

Long answer…<\/h3>\n\n\n\n

Assuming you already have a MySQL InnoDB Cluster<\/a> up and running. If not, please RTFM<\/a> \ud83d\ude42
Additionally you can read this tutorial<\/a> and this article<\/a> from my colleague lefred<\/a> or this one on Windows Platform<\/a> from my colleague Ivan<\/a>. <\/p>\n\n\n\n

I’m using MySQL<\/a> 8.0.15<\/strong><\/p>\n\n\n\n

MySQL localhost:33060+ JS> session.sql('SELECT VERSION()')\n+-----------+\n| VERSION() |\n+-----------+\n| 8.0.15    |\n+-----------+<\/code><\/pre>\n\n\n\n
Let’s connect to my cluster<\/p>\n\n\n\n
$ mysqlsh root@localhost --cluster\n\nPlease provide the password for 'root@localhost': ****\nMySQL Shell 8.0.15\n\nCopyright (c) 2016, 2019, Oracle and\/or its affiliates. All rights reserved.\nOracle is a registered trademark of Oracle Corporation and\/or its affiliates.\nOther names may be trademarks of their respective owners.\n\nType '\\help' or '\\?' for help; '\\quit' to exit.\nCreating a session to 'root@localhost'\nFetching schema names for autocompletion... Press ^C to stop.\nYour MySQL connection id is 1520 (X protocol)\nServer version: 8.0.15 MySQL Community Server - GPL\nNo default schema selected; type \\use <schema> to set one.\nYou are connected to a member of cluster 'pocCluster'.\nVariable 'cluster' is set.\nUse cluster.status() in scripting mode to get status of this cluster or cluster.help() for more commands.<\/code><\/pre>\n\n\n\n
The \u00ab\u00a0– – cluster<\/strong>\u00a0\u00bb 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.<\/p>\n\n\n\n\n\n
Ask for help<\/h3>\n\n\n\n
The built-in help is simply awesome!<\/p>\n\n\n\n
MySQL localhost:33060+ JS> cluster.help('status')\nNAME\n      status - Describe the status of the cluster.\n\nSYNTAX\n      <Cluster>.status([options])\n\nWHERE\n      options: Dictionary with options.\n\nRETURNS\n       A JSON object describing the status of the cluster.\n\nDESCRIPTION\n      This function describes the status of the cluster including its\n      ReplicaSets and Instances. The following options may be given to control\n      the amount of information gathered and returned.\n\n      - extended: if true, includes information about transactions processed by\n        connection and applier, as well as groupName and memberId values.\n      - queryMembers: if true, connect to each Instance of the ReplicaSets to\n        query for more detailed stats about the replication machinery.\n\nEXCEPTIONS\n      MetadataError in the following scenarios:\n\n      - If the Metadata is inaccessible.\n      - If the Metadata update operation failed.<\/code><\/pre>\n\n\n\n\n\n
Cluster status<\/h3>\n\n\n\n
So let’s discover the status of our cluster<\/p>\n\n\n\n
MySQL localhost:33060+ JS> cluster.status()\n{\n    \"clusterName\": \"pocCluster\", \n    \"defaultReplicaSet\": {\n        \"name\": \"default\", \n        \"primary\": \"172.19.0.11:3306\", \n        \"ssl\": \"REQUIRED\", \n        \"status\": \"OK\", \n        \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \n        \"topology\": {\n            \"172.19.0.11:3306\": {\n                \"address\": \"172.19.0.11:3306\", \n                \"mode\": \"R\/W\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\"\n            }, \n            \"172.19.0.12:3306\": {\n                \"address\": \"172.19.0.12:3306\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\"\n            }, \n            \"172.19.0.13:3306\": {\n                \"address\": \"172.19.0.13:3306\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\"\n            }\n        }, \n        \"topologyMode\": \"Single-Primary\"\n    }, \n    \"groupInformationSourceMember\": \"172.19.0.11:3306\"\n}<\/code><\/pre>\n\n\n\n
Note<\/u>:
 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<\/strong>.<\/p><\/blockquote>\n\n\n\n
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<\/a>, MySQL NDB Cluster<\/a> and obviously your standalone MySQL servers) is MySQL Enterprise Monitor<\/strong><\/a>. <\/p>\n\n\n\n
More details with \u00ab\u00a0A Guide to MySQL Enterprise Monitor<\/a>\u00ab\u00a0.<\/p>\n\n\n\n\n\n
Extended cluster status<\/h3>\n\n\n\n
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<\/a>.<\/p>\n\n\n\n
Some of these information are available through MySQL Shell<\/strong><\/a>. You can control the amount of information gathered and returned with 2 options: extended<\/em><\/strong> & queryMembers<\/em><\/strong>.<\/p>\n\n\n\n\n\n
extended<\/h4>\n\n\n\n
if enabled, includes information about groupName and memberID for each member; and general statistics about the number of transactions checked, proposed, rejected by members\u2026<\/p>\n\n\n\n
MySQL localhost:33060+ JS> cluster.status({extended:true})\n{\n    \"clusterName\": \"pocCluster\", \n    \"defaultReplicaSet\": {\n        \"groupName\": \"72568575-561c-11e9-914c-0242ac13000b\", \n        \"name\": \"default\", \n        \"primary\": \"172.19.0.11:3306\", \n        \"ssl\": \"REQUIRED\", \n        \"status\": \"OK\", \n        \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \n        \"topology\": {\n            \"172.19.0.11:3306\": {\n                \"address\": \"172.19.0.11:3306\", \n                \"memberId\": \"4a85f6c4-561c-11e9-8401-0242ac13000b\", \n                \"mode\": \"R\/W\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"transactions\": {\n                    \"appliedCount\": 2, \n                    \"checkedCount\": 53, \n                    \"committedAllMembers\": \"4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,\n72568575-561c-11e9-914c-0242ac13000b:1-51\", \n                    \"conflictsDetectedCount\": 0, \n                    \"inApplierQueueCount\": 0, \n                    \"inQueueCount\": 0, \n                    \"lastConflictFree\": \"72568575-561c-11e9-914c-0242ac13000b:56\", \n                    \"proposedCount\": 53, \n                    \"rollbackCount\": 0\n                }\n            }, \n            \"172.19.0.12:3306\": {\n                \"address\": \"172.19.0.12:3306\", \n                \"memberId\": \"4ad75450-561c-11e9-baa8-0242ac13000c\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"transactions\": {\n                    \"appliedCount\": 44, \n                    \"checkedCount\": 43, \n                    \"committedAllMembers\": \"4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,\n72568575-561c-11e9-914c-0242ac13000b:1-41\", \n                    \"conflictsDetectedCount\": 0, \n                    \"inApplierQueueCount\": 0, \n                    \"inQueueCount\": 0, \n                    \"lastConflictFree\": \"72568575-561c-11e9-914c-0242ac13000b:52\", \n                    \"proposedCount\": 0, \n                    \"rollbackCount\": 0\n                }\n            }, \n            \"172.19.0.13:3306\": {\n                \"address\": \"172.19.0.13:3306\", \n                \"memberId\": \"4b77c1ec-561c-11e9-9cc1-0242ac13000d\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"transactions\": {\n                    \"appliedCount\": 42, \n                    \"checkedCount\": 42, \n                    \"committedAllMembers\": \"4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,\n72568575-561c-11e9-914c-0242ac13000b:1-41\", \n                    \"conflictsDetectedCount\": 0, \n                    \"inApplierQueueCount\": 0, \n                    \"inQueueCount\": 0, \n                    \"lastConflictFree\": \"72568575-561c-11e9-914c-0242ac13000b:53\", \n                    \"proposedCount\": 0, \n                    \"rollbackCount\": 0\n                }\n            }\n        }, \n        \"topologyMode\": \"Single-Primary\"\n    }, \n    \"groupInformationSourceMember\": \"172.19.0.11:3306\"\n}<\/code><\/pre>\n\n\n\n\n\n
queryMembers<\/h4>\n\n\n\n
if enabled, includes information about recovery and regular transaction I\/O, applier worker thread statistic and any lags; applier coordinator statistic\u2026<\/p>\n\n\n\n
MySQL localhost:33060+ JS> cluster.status({queryMembers:true})\n{\n    \"clusterName\": \"pocCluster\", \n    \"defaultReplicaSet\": {\n        \"name\": \"default\", \n        \"primary\": \"172.19.0.11:3306\", \n        \"ssl\": \"REQUIRED\", \n        \"status\": \"OK\", \n        \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \n        \"topology\": {\n            \"172.19.0.11:3306\": {\n                \"address\": \"172.19.0.11:3306\", \n                \"mode\": \"R\/W\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"transactions\": {\n                    \"connection\": {\n                        \"lastHeartbeatTimestamp\": \"\", \n                        \"lastQueued\": {\n                            \"endTimestamp\": \"2019-04-03 14:26:33.394755\", \n                            \"immediateCommitTimestamp\": \"\", \n                            \"immediateCommitToEndTime\": null, \n                            \"originalCommitTimestamp\": \"\", \n                            \"originalCommitToEndTime\": null, \n                            \"queueTime\": 0.000077, \n                            \"startTimestamp\": \"2019-04-03 14:26:33.394678\", \n                            \"transaction\": \"72568575-561c-11e9-914c-0242ac13000b:13\"\n                        }, \n                        \"receivedHeartbeats\": 0, \n                        \"receivedTransactionSet\": \"4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,\n72568575-561c-11e9-914c-0242ac13000b:1-65\", \n                        \"threadId\": null\n                    }, \n                    \"workers\": [\n                        {\n                            \"lastApplied\": {\n                                \"applyTime\": 0.022927, \n                                \"endTimestamp\": \"2019-04-03 14:26:33.417643\", \n                                \"immediateCommitTimestamp\": \"\", \n                                \"immediateCommitToEndTime\": null, \n                                \"originalCommitTimestamp\": \"\", \n                                \"originalCommitToEndTime\": null, \n                                \"retries\": 0, \n                                \"startTimestamp\": \"2019-04-03 14:26:33.394716\", \n                                \"transaction\": \"72568575-561c-11e9-914c-0242ac13000b:13\"\n                            }, \n                            \"threadId\": 58\n                        }\n                    ]\n                }\n            }, \n            \"172.19.0.12:3306\": {\n                \"address\": \"172.19.0.12:3306\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"transactions\": {\n                    \"connection\": {\n                        \"lastHeartbeatTimestamp\": \"\", \n                        \"lastQueued\": {\n                            \"endTimestamp\": \"2019-04-03 15:42:30.855989\", \n                            \"immediateCommitTimestamp\": \"\", \n                            \"immediateCommitToEndTime\": null, \n                            \"originalCommitTimestamp\": \"2019-04-03 15:42:30.854594\", \n                            \"originalCommitToEndTime\": 0.001395, \n                            \"queueTime\": 0.000476, \n                            \"startTimestamp\": \"2019-04-03 15:42:30.855513\", \n                            \"transaction\": \"72568575-561c-11e9-914c-0242ac13000b:65\"\n                        }, \n                        \"receivedHeartbeats\": 0, \n                        \"receivedTransactionSet\": \"4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,\n72568575-561c-11e9-914c-0242ac13000b:1-65\", \n                        \"threadId\": null\n                    }, \n                    \"workers\": [\n                        {\n                            \"lastApplied\": {\n                                \"applyTime\": 0.024685, \n                                \"endTimestamp\": \"2019-04-03 15:42:30.880361\", \n                                \"immediateCommitTimestamp\": \"\", \n                                \"immediateCommitToEndTime\": null, \n                                \"originalCommitTimestamp\": \"2019-04-03 15:42:30.854594\", \n                                \"originalCommitToEndTime\": 0.025767, \n                                \"retries\": 0, \n                                \"startTimestamp\": \"2019-04-03 15:42:30.855676\", \n                                \"transaction\": \"72568575-561c-11e9-914c-0242ac13000b:65\"\n                            }, \n                            \"threadId\": 54\n                        }\n                    ]\n                }\n            }, \n            \"172.19.0.13:3306\": {\n                \"address\": \"172.19.0.13:3306\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"transactions\": {\n                    \"connection\": {\n                        \"lastHeartbeatTimestamp\": \"\", \n                        \"lastQueued\": {\n                            \"endTimestamp\": \"2019-04-03 15:42:30.855678\", \n                            \"immediateCommitTimestamp\": \"\", \n                            \"immediateCommitToEndTime\": null, \n                            \"originalCommitTimestamp\": \"2019-04-03 15:42:30.854594\", \n                            \"originalCommitToEndTime\": 0.001084, \n                            \"queueTime\": 0.000171, \n                            \"startTimestamp\": \"2019-04-03 15:42:30.855507\", \n                            \"transaction\": \"72568575-561c-11e9-914c-0242ac13000b:65\"\n                        }, \n                        \"receivedHeartbeats\": 0, \n                        \"receivedTransactionSet\": \"4a85f6c4-561c-11e9-8401-0242ac13000b:1-12,\n72568575-561c-11e9-914c-0242ac13000b:1-65\", \n                        \"threadId\": null\n                    }, \n                    \"workers\": [\n                        {\n                            \"lastApplied\": {\n                                \"applyTime\": 0.021354, \n                                \"endTimestamp\": \"2019-04-03 15:42:30.877398\", \n                                \"immediateCommitTimestamp\": \"\", \n                                \"immediateCommitToEndTime\": null, \n                                \"originalCommitTimestamp\": \"2019-04-03 15:42:30.854594\", \n                                \"originalCommitToEndTime\": 0.022804, \n                                \"retries\": 0, \n                                \"startTimestamp\": \"2019-04-03 15:42:30.856044\", \n                                \"transaction\": \"72568575-561c-11e9-914c-0242ac13000b:65\"\n                            }, \n                            \"threadId\": 54\n                        }\n                    ]\n                }\n            }\n        }, \n        \"topologyMode\": \"Single-Primary\"\n    }, \n    \"groupInformationSourceMember\": \"172.19.0.11:3306\"\n}<\/code><\/pre>\n\n\n\n\n\n
To summarize<\/h3>\n\n\n\n
Q: How do I monitor the status & the configuration of my cluster?

A: Use status()<\/strong><\/em> or status({extended:true})<\/em><\/strong> or status({queryMembers:true})<\/em><\/strong><\/p>\n\n\n\n\n\n
References<\/h3>\n\n\n\n