PMM2 and mariaDB, missing metrics

Percona Monitoring and Management (PMM) PMM 2.x
1

Description:

Im missing some metrics when monitoring mariadb server.
Specifically i want to see seconds behind master in the mysql replication summary, but there are more parameters missing like IO thread running, sql thread running, replication error etc.
Im suspecting this is because mariadb and mysql have different output on show slave status, and maybe mariadb is not supported by pmm?
When i googled about this i found posts about pmm having mariadb dashboards etc and did not see anything about it not being supported. But it is not listed as one of the supported versions.

Steps to Reproduce:

This is a clean install of PMM.

Version:

Server:

MariaDB [(none)]> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 10.11.4-MariaDB-1~deb12u1 |
+---------------------------+

root@xxxx:~# pmm-admin status
Agent ID : /agent_id/79d048af-bb63-4712-9be7-05f79b51483c
Node ID  : /node_id/e9ff015a-3ea4-4716-81e2-84a496c67961
Node name: xxxx

PMM Server:
	URL    : https://x.x.x.x:443/
	Version: 2.41.1

PMM Client:
	Connected        : true
	Time drift       : 655.721µs
	Latency          : 529.593µs
	Connection uptime: 100
	pmm-admin version: 2.41.1
	pmm-agent version: 2.41.1
Agents:
	/agent_id/0f4a4666-ee65-41f8-90ca-d5f45f0d083b mysql_perfschema_agent Running 0
	/agent_id/aa7332fe-5c29-4fd9-a783-3e611dcce0bf vmagent Running 42000
	/agent_id/db086bcd-4444-4435-b3a7-8ec446b1665e mysqld_exporter Running 42003
	/agent_id/ed4ae7e8-c1f0-4d74-9daf-ae1978bba01a node_exporter Running 42001

Logs:

[If applicable, include any relevant log files or error messages]

Expected Result:

I expect to see all metrics that are common for both mysql and mariadb, regardless of server being mariadb or mysql

Actual Result:

The fields show “no data”

Additional Information:

[Include any additional information that could be helpful to diagnose the issue, such as browser or device information]

2

@Patrick_Winnem, please verify that the mariadb user you used to init pmm-agent can successfully log into the local mariadb server and can execute ‘SHOW SLAVE STATUS’. This user must have ‘REPLICATION CLIENT’ privileges. You can also look at the PMM agent logs on the host for any missing privileges.

3

There seems to have been something amiss regarding the pmm user, i followed the guide and created it with 127.0.0.1 as allowed host, but when i tried to log into mariadb manually as you suggested, it wouldnt let me.
I again ran create user, but with localhost, and now i can log in. Will wait a bit and see if i get more data in the dashboard.

4

‘127.0.0.1’ and ‘localhost’ are not the same thing, in terms of MySQL users. Yes, in “networking” they are the same, but not in “MySQL”. localhost refers to connections over a local unix socket, where 127.0.0.1 are connections over local tcp/ip.

5

Yeah i understand, i dont know how pmm logs in, if its through tcp/ip from 127.0.0.1 or through socket. I recon its the last one since thats whats in the installation docs.
I havent been able to test more yet, im doing a larger job on the replica atm and its not, well, replicating.
But i did check the logs and i couldnt really see anything about it not being able to connect to the database.
Hopefully things have calmed down a bit tomorrow and i can do some more troubleshooting on it. I will get back here with more info once ive done so.

6

By default pmm-admin add uses TCP connection regardless of using localhost or 127.0.0.1 for --address, if you want to use a unix socket you have to pass in --socket=/path/to/socket which you can find with mysql -u root -p -e "select @@socket".

7

OK i finally have replication running again and can look at this.

So i see in syslog:
pmm-agent[1953497]: time="2024-03-06T12:10:18.312+01:00" level=error msg="failed to query events_statements_history: dial tcp 127.0.0.1:3306: connect: connection refused" agentID=/agent_id/0f4a4666-ee65-41f8-90ca-d5f45f0d083b component=agent-builtin type=qan_mysql_perfschema_agent

I checked netstat to see if it was listening to everything:

netstat -tulpn | grep mariadb
tcp        0      0 0.0.0.0:8385            0.0.0.0:*               LISTEN      4001041/mariadbd    
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      4001041/mariadbd

When i try with telnet:

telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
c
5.5.5-10.11.4-MariaDB-1~deb12u1a/9rU2:k.��!A;E)=68U%SW9mysql_native_passwordConnection closed by foreign host.

Not sure why it gets connection refused then, i have the following user created, with login accepted both for localhost (socket) and 127.0.0.1 (tcp/ip):

mysql -e "select * from mysql.user where user = 'pmm'\G"
*************************** 1. row ***************************
                  Host: 127.0.0.1
                  User: pmm
              Password: *snip*
           Select_priv: Y
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: Y
         Shutdown_priv: N
          Process_priv: Y
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: Y
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
   Delete_history_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *snip*
      password_expired: N
               is_role: N
          default_role: 
    max_statement_time: 0.000000
*************************** 2. row ***************************
                  Host: localhost
                  User: pmm
              Password: *snip*
           Select_priv: Y
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: Y
         Shutdown_priv: N
          Process_priv: Y
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: Y
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
   Delete_history_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *snip*
      password_expired: N
               is_role: N
          default_role: 
    max_statement_time: 0.000000

Trying manually login, with socket then tcp:

mysql -u pmm -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1327389
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> Ctrl-C -- exit!
Aborted

mysql -h 127.0.0.1 -u pmm -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1334218
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

So that seems to work fine, if i did indeed force socket and tcp like i think i did… :slight_smile:
Not sure then…

8

Checked the logs again now and there has been no messages since i restarted the service some 6 hours ago. I also restarted mariadb at around the same time…

Replication summary on pmm server shows no data for all graphs except “Read Only” which is “Yes”.

image
image2438×1027 180 KB

Seems to detect OK in pmm tho:

image
image2577×198 29.4 KB

9

Did some more digging and testing and i have it working now.
mysql -h 127.0.0.1 does not seem to force tcp/ip, and i had to grant SLAVE MONITOR to be able to run show slave status.
This doesnt seem to be in accordance with documentation, as BINLOG MONITOR should work… But i dont want to spend more time on this…

And there were no log messages regarding this (or anything really, as per my previous post), so probably need to increase verbosity to show those errors…

snip:~# mysql -h 127.0.0.1 -u pmm -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13670053
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation
MariaDB [(none)]> exit
Bye
snip:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13692103
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT SLAVE MONITOR ON *.* TO 'pmm'@'127.0.0.1';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> exit
Bye
snip:~# mysql -h 127.0.0.1 -u pmm -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13713890
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation
MariaDB [(none)]> exit
Bye
snip:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13716333
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GRANTS FOR 'pmm'@'127.0.0.1';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pmm@127.0.0.1                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, PROCESS, BINLOG MONITOR, SLAVE MONITOR ON *.* TO `pmm`@`127.0.0.1` IDENTIFIED BY PASSWORD '*snip' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'pmm'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pmm@localhost                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, PROCESS, BINLOG MONITOR ON *.* TO `pmm`@`localhost` IDENTIFIED BY PASSWORD '*snip' |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> exit
Bye
snip:~# mysql -u pmm -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13766455
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status \G
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation
MariaDB [(none)]> show master status;
Empty set (0.000 sec)

snip:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13770715
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT SLAVE MONITOR ON *.* TO 'pmm'@'localhost';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'pmm'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pmm@localhost                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, PROCESS, BINLOG MONITOR, SLAVE MONITOR ON *.* TO `pmm`@`localhost` IDENTIFIED BY PASSWORD '*snip' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> exit
Bye
snip:~# mysql -u pmm -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13778754
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status;
+----------------------------------+---------------+-------------+-------------+---------------+-----------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+----------------------+-----------------+---------------------+--------------------+--------------------------+-------------------------+-------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+------------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+------------------+--------------------------------+----------------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Rewrite_DB | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table   | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table   | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_SSL_Crl | Master_SSL_Crlpath | Using_Gtid | Gtid_IO_Pos      | Replicate_Do_Domain_Ids | Replicate_Ignore_Domain_Ids | Parallel_Mode | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Slave_DDL_Groups | Slave_Non_Transactional_Groups | Slave_Transactional_Groups |
+----------------------------------+---------------+-------------+-------------+---------------+-----------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+----------------------+-----------------+---------------------+--------------------+--------------------------+-------------------------+-------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+------------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+------------------+--------------------------------+----------------------------+
| Waiting for master to send event | x.x.x.x | repl        |        3306 |            60 | bin-log.020273  |           698815045 | snip.001294 |     173307857 | bin-log.020198        | Yes              | Yes               |                      |                 |                     |                    | snip |                         | snip |          0 |            |            0 |           173307560 |     81234600014 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                 27151 | No                            |             0 |               |              0 |                |                             |                9 |                |                    | Slave_Pos  | 0-9-163171366587 |                         |                             | optimistic    |         0 |                NULL | Updating                |          1190783 |                        1980454 |                  877356317 |
+----------------------------------+---------------+-------------+-------------+---------------+-----------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+----------------------+-----------------+---------------------+--------------------+--------------------------+-------------------------+-------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+------------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+------------------+--------------------------------+----------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> exit
Bye

image
image2276×388 41.3 KB

10

Hey @Patrick_Winnem,
Our documentation states you need REPLICATION CLIENT privilege to see replication status.

BINLOG_MONITOR is a privilege which is unique to MariaDB. According to Maria’s documentation, BINLOG_MONITOR does not grant the ability to see replication status. REPLICA_MONITOR (another unique Maria grant) does give replication status by simply granting REPLICATION CLIENT (it’s just an alias for native MySQL grants)

11

I see this note:

but as you demonstrated, and as the other docs indicate, that is incorrect.