No Query Analytics

1

I’m getting no query analytics from PMM2. I get this in my messages log for the OS:

Sep 13 17:01:10 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:01:10.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:01:20 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:01:20.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:01:30 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:01:30.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:01:40 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:01:40.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:01:50 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:01:50.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:02:00 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:02:00.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:02:10 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:02:10.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:02:20 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:02:20.499-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:02:30 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:02:30.497-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:02:40 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:02:40.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent
Sep 13 17:02:50 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:02:50.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent

pmm-admin list results in:

Service type        Service name        Address and port        Service ID
MySQL               mysql-mysql         127.0.0.1:3306          /service_id/43a0654a-b00d-4545-90ab-30baa5ee26b2

Agent type                 Status           Metrics Mode        Agent ID                                              Service ID                                              Port
pmm_agent                  Connected                            /agent_id/4e4ff707-0ccb-494a-b760-c42697ea5da2                                                                0
node_exporter              Running          push                /agent_id/bc5dc28e-ccba-4010-b940-e89434627ce1                                                                42001
mysqld_exporter            Running          push                /agent_id/8aafb85e-e0e2-431a-b0d0-1f3912084ecd        /service_id/43a0654a-b00d-4545-90ab-30baa5ee26b2        42002
mysql_slowlog_agent        Running                              /agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03        /service_id/43a0654a-b00d-4545-90ab-30baa5ee26b2        0
vmagent                    Running          push                /agent_id/9b2185ec-dee2-4caf-87cc-05d7cd933540                                                                42000

and pmm-admin status results in:

Agent ID : /agent_id/4e4ff707-0ccb-494a-b760-c42697ea5da2
Node ID  : /node_id/2c576b0e-6b03-4283-849c-99cf61317691
Node name: mysql

PMM Server:
	URL    : https://192.168.11.22:443/
	Version: 2.30.0

PMM Client:
	Connected        : true
	Time drift       : 2.76267ms
	Latency          : 603.465µs
	Connection uptime: 100
	pmm-admin version: 2.30.0
	pmm-agent version: 2.30.0
Agents:
	/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 mysql_slowlog_agent Running 0
	/agent_id/8aafb85e-e0e2-431a-b0d0-1f3912084ecd mysqld_exporter Running 42002
	/agent_id/9b2185ec-dee2-4caf-87cc-05d7cd933540 vmagent Running 42000
	/agent_id/bc5dc28e-ccba-4010-b940-e89434627ce1 node_exporter Running 42001

Any ideas? I installed pmm-server via the easy install script.

MySQL server is running on Centos 7.9
MySQL server is Server version: 5.7.33-36-log Percona Server (GPL), Release 36, Revision 7e403c5

1 Like
2

Hello @dcmoody and welcome to the Percona forums!

Did you actually read the error message that you pasted above? It literally tells you why no query analytics are happening.

Sep 13 17:01:10 mysql pmm-agent: #033[33mWARN#033[0m[2022-09-13T17:01:10.498-04:00] @@slow_query_log is off: 0.                   #033[33magentID#033[0m=/agent_id/3a0f20fd-a81a-48f1-ac1f-fd015b880f03 #033[33mcomponent#033[0m=agent-builtin #033[33mtype#033[0m=qan_mysql_slowlog_agent

@@slow_query_log is off: 0

Your slow log is not enabled. Impossible for PMM to collect metrics from something that doesn’t exist. You need to turn on the slow query log and then set long_query_time to an appropriate value. Any queries that take longer than “long_query_time” to execute will get slow logged. We suggest 0 to capture all queries.

1 Like
3

Hi Matthew,

I did read the error message, and I saw that. The reason I posted is because I do not understand. My my.cnf (the pertinent section):

My server does create the slow query log it gets data every day. Do I have to configure PMM to look at this, or does it read my my.cnf file?

log-error                      = /sql/data/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /sql/data/mysql-slow.log
general-log		       = 1
general-log-file	       = /sql/data/mysql-general.log
#

#Percona Monitoring Suggestions
innodb_monitor_enable		= all
log-output 			= file
long-query-time			= 0
log-slow-rate-limit		= 100
log-slow-rate-type		= query
log-slow-verbosity		= full
log-slow-admin-statements	= ON
log-slow-slave-statements	= ON
slow-query-log-always-write-time= 1
slow-query-log-use-global-control= all
1 Like
4

Hi @dcmoody your configuration looks accurate on disk :+1: (In fact major props for long_query_time=0 !!)

Can you please confirm that the MySQL instance is also running with slow log enabled?

mysql -e "SHOW GLOBAL VARIABLES LIKE 'slow_query_log'"
1 Like
5

What in the world. That seems to be the issue:

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

So can I just set that global variable to ON and things will start working?

1 Like
6

That’s correct @dcmoody , it is a dynamic variable so will just kick on and start writing to disk. The agent will also see the results appearing in the slow log and will start to consume them.

Confirm just in case tho that you are using slow log as the data source, for example you can check like this:

pmm-admin list | grep slow

you’re looking for an agent running with slowlog .

1 Like
7

@Michael_Coburn that got it. Thank you very much!

2 Likes