MySQL Group Replication Summary Dashboard is empty in PMM 2.15.0

Hi,

We registered the InnoDB cluster nodes with the PMM server using various options ( --cluster, --replication-set etc ) but the out-of-the-box group replication summary dashboard still doesn’t show any data at all.

More details have been provided in https://jira.percona.com/browse/PMM-7735.

Is this a bug with the PMM 2.15.0 ?

Regards,
Nikunj

Hi Nikunj,

Could you check metric mysql_up in Explore? Does parameter replication_set have any value?

e.g.

1 Like

Hi @adivinho ,

Thanks for your response. I checked the metric mysql_up in Explore and below are the results:

As you can see, the replication_set parameter has a valid value ( same value for the 3 nodes in InnoDB cluster ), but still for some reason the “Replication Set” field has just one value (“All”) in the actual Group Replication Summary Dashboard, as I mentioned in the JIRA ticket https://jira.percona.com/browse/PMM-7735 .

Thanks,
Nikunj

1 Like

Metric mysql_perf_schema_replication_group_member_info is based on table replication_group_members.

Could you check if your monitored mysql instance has got any data in the table?
select * from performance_schema.replication_group_members;

1 Like

Hi @adivinho ,

Sorry for the late response, yes I checked that as well some time back and we have data in this table for the monitored mysql instances:

And I actually wanted to get you up to speed with all the troubleshooting that we did at our end, for which I had several screenshots to share with you, but unfortunately the forum portal doesn’t let me add more than one image or even attach a single file that has several screenshots :frowning_face:

It would be great if I can work around this problem somehow.

Thanks,
Nikunj

1 Like

Ok if data is exist in DB then somehow exporter can’t fetch it.
Could you check pmm-agent logs?

journalctl -f -u pmm-agent

1 Like

Hi @adivinho ,

Sorry for the late response again. Actually the PMM agent in our setup is running via the same OS user as the MySQL service. So I enabled debugging for the agent and I am pasting below the lines that had the queries with the pattern “%group%” in the debug output. Also, please note that I started to record the debug o/p from Mar 26th, and the first occurrence of a query trying to gather the group replication details was surprisingly late on Apr 5th.

I have replaced the variable substitution with “##”, because the portal wouldn’t allow me to paste the logs as is, as new users can’t use “at the rate” symbol in a post more than two times :frowning_face:

====================================================

DEBU[2021-04-05T12:14:00.007+02:00] Normal query: SchemaName: <nil> (*string), Digest: `c58298686c4dc84fe485803d311c4b153a3e03f35ddaa73ec7e1053a89238c80` (*string), DigestText: "SELECT ##`group_replication_single_primary_mode` , `variable_value` AS `primary_uuid` FROM `performance_schema` . `global_status` WHERE `variable_name` = ?" (*string), CountStar: 30 (uint64), SumTimerWait: 7924040000 (uint64), MinTimerWait: 200268000 (uint64), AvgTimerWait: 264134000 (uint64), MaxTimerWait: 375455000 (uint64), SumLockTime: 1234000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 30 (uint64), SumRowsExamined: 30 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 0 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 0 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.007+02:00] Normal query: SchemaName: <nil> (*string), Digest: `32ca4854f751684a77513ef11c73a897514812fd69c6f84fed557b948a552417` (*string), DigestText: "SELECT CAST ( SUM ( IF ( `member_state` = ?, ... ) ) AS SIGNED ) AS `UNREACHABLE` , COUNT ( * ) AS `TOTAL` FROM `performance_schema` . `replication_group_members`" (*string), CountStar: 57 (uint64), SumTimerWait: 7832291000 (uint64), MinTimerWait: 87533000 (uint64), AvgTimerWait: 137408000 (uint64), MaxTimerWait: 430613000 (uint64), SumLockTime: 3165000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 57 (uint64), SumRowsExamined: 148 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 57 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 57 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.007+02:00] Normal query: SchemaName: <nil> (*string), Digest: `98190d0b478713ddd3c11be4077efdbdf1f1a758d4e3fe8324ace53d3623249e` (*string), DigestText: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `MEMBER_STATE` IS NOT NULL AND `MEMBER_STATE` != ?" (*string), CountStar: 59 (uint64), SumTimerWait: 14086579000 (uint64), MinTimerWait: 113059000 (uint64), AvgTimerWait: 238755000 (uint64), MaxTimerWait: 2185991000 (uint64), SumLockTime: 4980000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 59 (uint64), SumRowsExamined: 150 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 59 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 59 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `779b6eccf4f9856de82fc1ffa222eb9d2f28964f35f09dce4a5221d7e9b1bbf6` (*string), DigestText: "SELECT `MEMBER_STATE` FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ?" (*string), CountStar: 57 (uint64), SumTimerWait: 7481369000 (uint64), MinTimerWait: 88464000 (uint64), AvgTimerWait: 131252000 (uint64), MaxTimerWait: 254704000 (uint64), SumLockTime: 2427000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 57 (uint64), SumRowsExamined: 148 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 57 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 57 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `aed090d9bd4a304c8436848ab39bfa324e00ee944a74030e0dba1336dfb5e2a0` (*string), DigestText: "SELECT NOT ##`group_replication_single_primary_mode` OR ( SELECT `variable_value` FROM `performance_schema` . `global_status` WHERE `variable_name` = ? ) = ##`server_uuid`" (*string), CountStar: 11 (uint64), SumTimerWait: 6331400000 (uint64), MinTimerWait: 405726000 (uint64), AvgTimerWait: 575581000 (uint64), MaxTimerWait: 913894000 (uint64), SumLockTime: 1506000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 11 (uint64), SumRowsExamined: 11 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 0 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 0 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `30c72d215dc8df3be903e67e88ca99d14e51f36fe99f52bb70723b7ef00182bc` (*string), DigestText: "SELECT ##`group_replication_group_name`" (*string), CountStar: 14 (uint64), SumTimerWait: 746635000 (uint64), MinTimerWait: 38046000 (uint64), AvgTimerWait: 53331000 (uint64), MaxTimerWait: 99448000 (uint64), SumLockTime: 0 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 14 (uint64), SumRowsExamined: 0 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 0 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 0 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `1aee9574b869302fa5832c6c7bf770acd5eca6b781c34bb544ffac7733154611` (*string), DigestText: "SELECT * FROM `performance_schema` . `replication_group_member_stats`" (*string), CountStar: 15 (uint64), SumTimerWait: 3263769000 (uint64), MinTimerWait: 147144000 (uint64), AvgTimerWait: 217584000 (uint64), MaxTimerWait: 520411000 (uint64), SumLockTime: 1357000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 39 (uint64), SumRowsExamined: 39 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 15 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 15 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `b3e703ab7f1cb50188952c5f0e5e95f3c6c6766370ce4daeb194571957d0eb63` (*string), DigestText: "SELECT CAST ( SUM ( IF ( `member_state` = ?, ... ) ) AS SIGNED ) AS `UNRCH` , COUNT ( * ) AS `TOTAL` , ( SELECT `member_state` FROM `performance_schema` . `replication_group_members` WHERE `member_id` = ##`server_uuid` ) AS `my_state` FROM `performance_schema` . `replication_group_members`" (*string), CountStar: 26 (uint64), SumTimerWait: 6778984000 (uint64), MinTimerWait: 162153000 (uint64), AvgTimerWait: 260730000 (uint64), MaxTimerWait: 696864000 (uint64), SumLockTime: 3161000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 26 (uint64), SumRowsExamined: 134 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 52 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 26 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `73376b0b4bd8ba0e87e104959953309948c56bf714946a694b1232e0079361ad` (*string), DigestText: "SELECT `member_id` , `member_state` , `member_host` , `member_port` , `member_role` , `member_version` , ##`group_replication_single_primary_mode` FROM `performance_schema` . `replication_group_members`" (*string), CountStar: 16 (uint64), SumTimerWait: 2138617000 (uint64), MinTimerWait: 96183000 (uint64), AvgTimerWait: 133663000 (uint64), MaxTimerWait: 189664000 (uint64), SumLockTime: 762000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 42 (uint64), SumRowsExamined: 42 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 16 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 16 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
DEBU[2021-04-05T12:14:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `fe2ab0d753fd1550c4bf66eef9275a18846fe6e98c829ec9dc5c76a33b779094` (*string), DigestText: "SELECT `replicaset_name` , `topology_type` , `attributes` ->> ? AS `group_name` FROM `mysql_innodb_cluster_metadata` . `replicasets` WHERE `replicaset_id` = ?" (*string), CountStar: 14 (uint64), SumTimerWait: 3264398000 (uint64), MinTimerWait: 97425000 (uint64), AvgTimerWait: 233171000 (uint64), MaxTimerWait: 494066000 (uint64), SumLockTime: 2120000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 14 (uint64), SumRowsExamined: 14 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 0 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 0 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
      fingerprint: "SELECT ##`group_replication_single_primary_mode` , `variable_value` AS `primary_uuid` FROM `performance_schema` . `global_status` WHERE `variable_name` = ?"
      fingerprint: "SELECT CAST ( SUM ( IF ( `member_state` = ?, ... ) ) AS SIGNED ) AS `UNREACHABLE` , COUNT ( * ) AS `TOTAL` FROM `performance_schema` . `replication_group_members`"
      example: "SELECT CAST(SUM(IF(member_state = 'UNREACHABLE', 1, 0)) AS SIGNED) AS UNREACHABLE,  COUNT(*) AS TOTAL FROM performance_schema.replication_group_members"
      fingerprint: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `MEMBER_STATE` IS NOT NULL AND `MEMBER_STATE` != ?"
      example: "select count(*) from performance_schema.replication_group_members where MEMBER_ID = ##server_uuid AND MEMBER_STATE IS NOT NULL AND MEMBER_STATE <> 'OFFLINE'"
      fingerprint: "SELECT `MEMBER_STATE` FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ?"
      example: "SELECT MEMBER_STATE FROM performance_schema.replication_group_members WHERE MEMBER_ID = 'd5007267-a22f-11e9-8c81-fa163e51af36'"
      fingerprint: "SELECT NOT ##`group_replication_single_primary_mode` OR ( SELECT `variable_value` FROM `performance_schema` . `global_status` WHERE `variable_name` = ? ) = ##`server_uuid`"
      fingerprint: "SELECT ##`group_replication_group_name`"
      fingerprint: "SELECT * FROM `performance_schema` . `replication_group_member_stats`"
      example: "SELECT * FROM performance_schema.replication_group_member_stats"
      fingerprint: "SELECT CAST ( SUM ( IF ( `member_state` = ?, ... ) ) AS SIGNED ) AS `UNRCH` , COUNT ( * ) AS `TOTAL` , ( SELECT `member_state` FROM `performance_schema` . `replication_group_members` WHERE `member_id` = ##`server_uuid` ) AS `my_state` FROM `performance_schema` . `replication_group_members`"
      example: "SELECT   CAST(SUM(IF(member_state = 'UNREACHABLE', 1, 0)) AS SIGNED) AS UNRCH,  COUNT(*) AS TOTAL,  (SELECT member_state      FROM performance_schema.replication_group_members      WHERE member_id = ##server_uuid) AS my_state  FROM performance_schema.replication_group_members"
      fingerprint: "SELECT `member_id` , `member_state` , `member_host` , `member_port` , `member_role` , `member_version` , ##`group_replication_single_primary_mode` FROM `performance_schema` . `replication_group_members`"
      example: "SELECT member_id, member_state, member_host, member_port, member_role, member_version, ##group_replication_single_primary_mode FROM performance_schema.replication_group_members"
      example: "SELECT ##server_uuid, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'group_replication_primary_member'"
      fingerprint: "SELECT `replicaset_name` , `topology_type` , `attributes` ->> ? AS `group_name` FROM `mysql_innodb_cluster_metadata` . `replicasets` WHERE `replicaset_id` = ?"
      example: "SELECT COUNT(*) FROM performance_schema.threads WHERE NAME = 'thread/group_rpl/THD_delayed_initialization'"
DEBU[2021-04-05T12:17:00.007+02:00] Normal query: SchemaName: <nil> (*string), Digest: `98190d0b478713ddd3c11be4077efdbdf1f1a758d4e3fe8324ace53d3623249e` (*string), DigestText: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `MEMBER_STATE` IS NOT NULL AND `MEMBER_STATE` != ?" (*string), CountStar: 60 (uint64), SumTimerWait: 14206900000 (uint64), MinTimerWait: 113059000 (uint64), AvgTimerWait: 236781000 (uint64), MaxTimerWait: 2185991000 (uint64), SumLockTime: 4980000000 (uint64), SumErrors: 1 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 59 (uint64), SumRowsExamined: 150 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 59 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 59 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
      fingerprint: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `MEMBER_STATE` IS NOT NULL AND `MEMBER_STATE` != ?"
      example: "select count(*) from performance_schema.replication_group_members where MEMBER_ID = ##server_uuid AND MEMBER_STATE IS NOT NULL AND MEMBER_STATE <> 'OFFLINE'"
      example: "SELECT COUNT(*) FROM performance_schema.threads WHERE NAME = 'thread/group_rpl/THD_delayed_initialization'"
DEBU[2021-04-05T12:20:00.008+02:00] Normal query: SchemaName: <nil> (*string), Digest: `98190d0b478713ddd3c11be4077efdbdf1f1a758d4e3fe8324ace53d3623249e` (*string), DigestText: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `MEMBER_STATE` IS NOT NULL AND `MEMBER_STATE` != ?" (*string), CountStar: 61 (uint64), SumTimerWait: 14312729000 (uint64), MinTimerWait: 105829000 (uint64), AvgTimerWait: 234634000 (uint64), MaxTimerWait: 2185991000 (uint64), SumLockTime: 4980000000 (uint64), SumErrors: 2 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 59 (uint64), SumRowsExamined: 150 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 59 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 59 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
      example: "SELECT COUNT(*) FROM performance_schema.threads WHERE NAME = 'thread/group_rpl/THD_delayed_initialization'"
      fingerprint: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `MEMBER_STATE` IS NOT NULL AND `MEMBER_STATE` != ?"
      example: "select count(*) from performance_schema.replication_group_members where MEMBER_ID = ##server_uuid AND MEMBER_STATE IS NOT NULL AND MEMBER_STATE <> 'OFFLINE'"
DEBU[2021-04-05T12:47:00.006+02:00] New query: SchemaName: <nil> (*string), Digest: `5efefc668019baeb644d5ab18150b7bb43469897fa19b3ee0e12a123997949f5` (*string), DigestText: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `member_state` IS NOT NULL AND `MEMBER_STATE` != ?" (*string), CountStar: 1 (uint64), SumTimerWait: 430481000 (uint64), MinTimerWait: 430481000 (uint64), AvgTimerWait: 430481000 (uint64), MaxTimerWait: 430481000 (uint64), SumLockTime: 245000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 1 (uint64), SumRowsExamined: 3 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 1 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 1 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
      fingerprint: "SELECT COUNT ( * ) FROM `performance_schema` . `replication_group_members` WHERE `MEMBER_ID` = ##`server_uuid` AND `member_state` IS NOT NULL AND `MEMBER_STATE` != ?"
      example: "select count(*) from performance_schema.replication_group_members where MEMBER_ID=##`server_uuid` and member_state is not null and MEMBER_STATE !=\"?\""
DEBU[2021-04-05T12:48:00.007+02:00] Normal query: SchemaName: <nil> (*string), Digest: `9a3ab3f74fdefae224f0fc283828f12664f0fee1d4fa3a34aba9c3c099b7555a` (*string), DigestText: "SELECT * FROM `performance_schema` . `replication_group_members`" (*string), CountStar: 2 (uint64), SumTimerWait: 771042000 (uint64), MinTimerWait: 246041000 (uint64), AvgTimerWait: 385521000 (uint64), MaxTimerWait: 525001000 (uint64), SumLockTime: 379000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 6 (uint64), SumRowsExamined: 6 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 2 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 2 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
      fingerprint: "SELECT * FROM `performance_schema` . `replication_group_members`"
      example: "select * from performance_schema.replication_group_members"
DEBU[2021-04-05T12:49:00.007+02:00] Normal query: SchemaName: <nil> (*string), Digest: `30c72d215dc8df3be903e67e88ca99d14e51f36fe99f52bb70723b7ef00182bc` (*string), DigestText: "SELECT ##`group_replication_group_name`" (*string), CountStar: 15 (uint64), SumTimerWait: 871771000 (uint64), MinTimerWait: 38046000 (uint64), AvgTimerWait: 58118000 (uint64), MaxTimerWait: 125136000 (uint64), SumLockTime: 0 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 15 (uint64), SumRowsExamined: 0 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 0 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 0 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
      fingerprint: "SELECT ##`group_replication_group_name`"
      example: "select ##group_replication_group_name"
DEBU[2021-04-05T12:54:00.009+02:00] New query: SchemaName: <nil> (*string), Digest: `0ff3cc1a689b1a49ca9a08efb0eef3cdae5e1bdac9fb84ca30057f48c5282f8a` (*string), DigestText: "SELECT `replicaset_name` , `topology_type` , `attributes` AS `group_name` FROM `mysql_innodb_cluster_metadata` . `replicasets`" (*string), CountStar: 1 (uint64), SumTimerWait: 260760000 (uint64), MinTimerWait: 260760000 (uint64), AvgTimerWait: 260760000 (uint64), MaxTimerWait: 260760000 (uint64), SumLockTime: 121000000 (uint64), SumErrors: 0 (uint64), SumWarnings: 0 (uint64), SumRowsAffected: 0 (uint64), SumRowsSent: 1 (uint64), SumRowsExamined: 1 (uint64), SumCreatedTmpDiskTables: 0 (uint64), SumCreatedTmpTables: 0 (uint64), SumSelectFullJoin: 0 (uint64), SumSelectFullRangeJoin: 0 (uint64), SumSelectRange: 0 (uint64), SumSelectRangeCheck: 0 (uint64), SumSelectScan: 1 (uint64), SumSortMergePasses: 0 (uint64), SumSortRange: 0 (uint64), SumSortRows: 0 (uint64), SumSortScan: 0 (uint64), SumNoIndexUsed: 1 (uint64), SumNoGoodIndexUsed: 0 (uint64).  agentID=/agent_id/b754a00f-a620-4c3e-a9ac-d1c27830eef1 component=agent-builtin type=qan_mysql_perfschema_agent
      fingerprint: "SELECT `replicaset_name` , `topology_type` , `attributes` AS `group_name` FROM `mysql_innodb_cluster_metadata` . `replicasets`"
      example: "select replicaset_name,topology_type,attributes as group_name from mysql_innodb_cluster_metadata.replicasets"


====================================================

Are there any general guidelines that are to be followed while using the values for switches --replica-set and --cluster while registering the InnoDB cluster instances ? For now, I just provided the values that were more user friendly and environment related. Do these values have to be fetched from somewhere inside the cluster’s metadata ?

I have these values for some of the queries that the PMM agent ran for group replication:

 MySQL  ******:3306 ssl  SQL > select @@group_replication_group_name;
+--------------------------------------+
| @@group_replication_group_name       |
+--------------------------------------+
| 9dbba070-a231-11e9-abe1-fa163e2288c1 |
+--------------------------------------+
1 row in set (0.0003 sec)


 MySQL  **********:3306 ssl  SQL > select replicaset_name,topology_type,attributes as group_name from mysql_innodb_cluster_metadata.replicasets;
+-----------------+---------------+------------------------------------------------------------------------------------------+
| replicaset_name | topology_type | group_name                                                                               |
+-----------------+---------------+------------------------------------------------------------------------------------------+
| default         | pm            | {"adopted": "0", "group_replication_group_name": "9dbba070-a231-11e9-abe1-fa163e2288c1"} |
+-----------------+---------------+------------------------------------------------------------------------------------------+
1 row in set (0.0004 sec)

Thanks,
Nikunj

1 Like

Hi Nikunj,

Sad to say but provided logs doesn’t throw light on the issue.
The next query is used by exporter for generating mysql_perf_schema_replication_group_member_info metric

/*!80000 SELECT CHANNEL_NAME as channel_name, MEMBER_ID as member_id, MEMBER_HOST as member_host, MEMBER_PORT as member_port, MEMBER_STATE as member_state, MEMBER_ROLE as member_role, MEMBER_VERSION as member_version, CASE WHEN MEMBER_STATE = 'ONLINE' THEN 1 WHEN MEMBER_STATE = 'RECOVERING' THEN 2 WHEN MEMBER_STATE = 'OFFLINE' THEN 3 WHEN MEMBER_STATE = 'ERROR' THEN 4 WHEN MEMBER_STATE = 'UNREACHABLE' THEN 5 END as member_info FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid */

Could you execute it directly in MySQL by user that is used in pmm-agent?

1 Like

Hi @adivinho ,

Below is the o/p:

MySQL ********:3306 ssl SQL > \s
MySQL Shell version 8.0.16

Session type: Classic
Connection Id: 30289270
Current schema:
Current user: pmm@********
SSL: Cipher in use: DHE-RSA-AES128-GCM-SHA256 TLSv1.2
Using delimiter: ;
Server version: 8.0.16 Source distribution
Protocol version: classic 10
Client library: 8.0.16
Connection: ******** via TCP/IP
TCP port: 3306
Server characterset: utf8
Schema characterset: utf8
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Compression: Disabled
Uptime: 637 days 25 min 21.0000 sec

Threads: 8 Questions: 161612635 Slow queries: 0 Opens: 4019733 Flush tables: 857 Open tables: 256 Queries per second avg: 2.936

MySQL ******:3306 ssl SQL > SELECT CHANNEL_NAME as channel_name, MEMBER_ID as member_id, MEMBER_HOST as member_host, MEMBER_PORT as member_port, MEMBER_STATE as member_state, MEMBER_ROLE as member_role, MEMBER_VERSION as member_version, CASE WHEN MEMBER_STATE = ‘ONLINE’ THEN 1 WHEN MEMBER_STATE = ‘RECOVERING’ THEN 2 WHEN MEMBER_STATE = ‘OFFLINE’ THEN 3 WHEN MEMBER_STATE = ‘ERROR’ THEN 4 WHEN MEMBER_STATE = ‘UNREACHABLE’ THEN 5 END as member_info FROM performance_schema.replication_group_members WHERE MEMBER_ID=@@server_uuid\G
*************************** 1. row ***************************
channel_name: group_replication_applier
member_id: d5007267-a22f-11e9-8c81-fa163e51af36
member_host: ***********
member_port: 3306
member_state: ONLINE
member_role: PRIMARY
member_version: 8.0.16
member_info: 1
1 row in set (0.0005 sec)

Also, about the agent debug logs, I would’ve attached the complete log here in this case, but unfortunately the portal won’t allow me to do that :frowning_face:.

Thanks,
Nikunj

1 Like

Are there any errors related to group replication select queries?
BTW could you make sure that next file is present on your installation?
/usr/local/percona/pmm2/collectors/custom-queries/mysql/high-resolution/queries-mysqld-group-replication.yml

2 Likes

@adivinho ,

Thanks for the response, we didn’t have this yml file present in the installation. We created the necessary directories under /usr/local/percona/pmm2/collectors including this one and provided R/W permissions to the OS user which is running the PMM agent, but since we installed the PMM agent in a custom location, I think this file was missed.

After copying this file to the mentioned directory we can see the GR status on the GR dashboard.

Thank you very much for your prompt help and resolution of this issue.

Could you please tell me if there are any other necessary files that should be present in these custom directories so that we don’t run in a similar problem again?

Regards,
Nikunj

1 Like