QAN not picking up change to long_query_time

chrishatcher1138 · July 25, 2017, 7:51am

I have decreased long_query_time from the default of 10 to 1 second. I have confirmed that queries > 1 second are in the slow log. QAN does not show queries between 1 and 10 seconds, but it will still display queries > 10 seconds. What do I need to do for QAN to pick up these queries?

I am using PMM 1.2.0. The database is MariaDB 10.1.21.

Thanks - Chris

kamil-dziedzic · July 25, 2017, 8:28am

Hi Chris,

Could you run:


SELECT * FROM information_schema.global_variables
WHERE VARIABLE_NAME LIKE "log_slow%"
OR VARIABLE_NAME IN(
"long_query_time",
"log_queries_not_using_indexes",
"min_examined_row_limit",
"log_throttle_queries_not_using_indexes",
"log_timestamps",
"slow_query_log",
"slow_query_log_file",
"log_output",
"performance_schema",
"performance_schema_digests_size",
"performance_schema_max_digest_length"
) ORDER BY VARIABLE_NAME;

and

$ sudo pmm-admin list

chrishatcher1138 · July 25, 2017, 11:53am

[TABLE]
[TR]
[TD]LOG_OUTPUT[/TD]
[TD]FILE[/TD]
[/TR]
[TR]
[TD]LOG_QUERIES_NOT_USING_INDEXES[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]LOG_SLOW_ADMIN_STATEMENTS[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]LOG_SLOW_FILTER[/TD]
[TD]admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk[/TD]
[/TR]
[TR]
[TD]LOG_SLOW_RATE_LIMIT[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]LOG_SLOW_SLAVE_STATEMENTS[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]LOG_SLOW_VERBOSITY[/TD]
[TD]query_plan[/TD]
[/TR]
[TR]
[TD]LONG_QUERY_TIME[/TD]
[TD]1.000000[/TD]
[/TR]
[TR]
[TD]MIN_EXAMINED_ROW_LIMIT[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PERFORMANCE_SCHEMA[/TD]
[TD]ON[/TD]
[/TR]
[TR]
[TD]PERFORMANCE_SCHEMA_DIGESTS_SIZE[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH[/TD]
[TD]1024[/TD]
[/TR]
[TR]
[TD]SLOW_QUERY_LOG[/TD]
[TD]ON[/TD]
[/TR]
[TR]
[TD]SLOW_QUERY_LOG_FILE[/TD]
[TD]/var/log/mysql/mariadb-slow.log[/TD]
[/TR]
[/TABLE]

pmm-admin 1.2.0

PMM Server | 172.31.27.92:8081
Client Name | ip-172-31-20-131
Client Address | XX.XX.XX.XX (172.31.20.131)
Service Manager | linux-upstart

SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS

mysql:queries ip-172-31-20-131 - YES pmm:@unix(/var/run/mysqld/mysqld.sock) query_source=slowlog, query_examples=true
linux:metrics ip-172-31-20-131 42000 YES -
mysql:metrics ip-172-31-20-131 42002 YES pmm:@unix(/var/run/mysqld/mysqld.sock)

Mykola · July 26, 2017, 8:46am

Hi chrishatcher1138

can you please also upload full content of file?
can you also restart qan-agent, wait for 2 hours and check if new queries shown on query analytics interface?

chrishatcher1138 · July 26, 2017, 5:27pm

Once I restarted the agent, it seems to be working correctly. Thanks for the help.

Mykola · July 27, 2017, 2:15am

Hi chrishatcher1138
anyway, can you please also upload full content of /var/log/pmm-mysql-queries-0.log file?
maybe we can find root of the issue…

chrishatcher1138 · July 27, 2017, 3:41pm

Here you go:

Version: percona-qan-agent 1.2.0

Basedir: /usr/local/percona/qan-agent

PID: 23257

API: 172.31.27.92:8081/qan-api

UUID: 10380f1923b644516cce462ff3bf75ab

2017/07/25 00:23:25.444672 main.go:163: Starting agent…
2017/07/25 00:23:25.449945 main.go:331: Agent is ready
2017/07/25 00:23:25.458813 main.go:204: API is ready
[mysql] 2017/07/25 00:26:34 packets.go:141: write unix @->/var/run/mysqld/mysqld.sock: write: broken pipe
2017/07/25 00:32:35.536298 WARNING mrms-monitor Listener not ready
2017/07/25 00:32:35.536406 WARNING mrms-monitor Listener not ready
2017/07/25 00:32:57.032812 ERROR agent Executable file not found in $PATH
2017/07/25 00:32:57.032915 WARNING agent Cmd fail: Reply[Cmd:GetServerSummary Error:‘Executable file not found in $PATH’ Id:9009810f-59$
2017/07/25 00:32:57.033500 ERROR agent Executable file not found in $PATH
2017/07/25 00:32:57.033577 WARNING agent Cmd fail: Reply[Cmd:GetMySQLSummary Error:‘Executable file not found in $PATH’ Id:d29966d1-668$
2017/07/25 06:56:47.000825 WARNING mrms-monitor Listener not ready
2017/07/25 06:56:47.000964 WARNING mrms-monitor Listener not ready
2017/07/25 12:18:28.167149 WARNING mrms-monitor Listener not ready
2017/07/25 12:18:28.167275 WARNING mrms-monitor Listener not ready
2017/07/25 12:47:38.294859 WARNING mrms-monitor Listener not ready
2017/07/25 12:47:38.295005 WARNING mrms-monitor Listener not ready
2017/07/25 12:49:18.315943 WARNING mrms-monitor Listener not ready
2017/07/25 12:49:18.316062 WARNING mrms-monitor Listener not ready
2017/07/25 12:51:48.336718 WARNING mrms-monitor Listener not ready
2017/07/25 12:51:48.336832 WARNING mrms-monitor Listener not ready
2017/07/25 12:56:15.737130 ERROR agent Executable file not found in $PATH
2017/07/25 12:56:15.737254 WARNING agent Cmd fail: Reply[Cmd:GetServerSummary Error:‘Executable file not found in $PATH’ Id:02cc9e65-eb$
2017/07/25 12:56:15.807089 ERROR agent Executable file not found in $PATH
2017/07/25 12:56:15.807186 WARNING agent Cmd fail: Reply[Cmd:GetMySQLSummary Error:‘Executable file not found in $PATH’ Id:241897eb-c3d$
2017/07/26 18:01:55.000910 WARNING mrms-monitor Listener not ready
2017/07/26 18:01:55.001039 WARNING mrms-monitor Listener not ready
2017/07/26 22:41:50.435607 main.go:359: Caught interrupt signal, shutting down
2017/07/26 22:41:50.435662 main.go:385: Stopping QAN…
2017/07/26 22:41:50.478587 main.go:392: Waiting 2 seconds to flush agent log to API…
2017/07/26 22:41:52.478873 main.go:167: Agent has stopped

Version: percona-qan-agent 1.2.0

Basedir: /usr/local/percona/qan-agent

PID: 7354

API: 172.31.27.92:8081/qan-api

UUID: 10380f1923b644516cce462ff3bf75ab

2017/07/26 22:41:52.634565 main.go:163: Starting agent…
2017/07/26 22:41:52.691738 main.go:331: Agent is ready
2017/07/26 22:41:52.705782 main.go:204: API is ready
2017/07/27 13:48:11.637632 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:48:24.638270 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:48:37.639057 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:49:14.636560 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:49:27.637602 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:49:40.638480 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:50:17.636479 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:50:30.637189 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:50:43.638066 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:51:20.636461 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:51:33.637100 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:51:46.637913 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:52:23.636566 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:52:36.637295 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:52:49.638500 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:53:26.636546 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:53:39.637176 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:53:52.638035 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:54:29.636563 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:54:42.637240 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:54:55.638088 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:55:32.636552 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:55:45.637200 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:55:58.638047 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:56:35.636666 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:56:48.637311 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:57:01.640109 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:57:38.636539 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:57:51.637281 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:58:04.638121 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:58:41.636502 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:58:54.637164 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:59:07.638039 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:59:44.636870 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 13:59:57.637552 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 14:00:10.638381 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 14:00:47.636539 WARNING data-sender Cannot connect to API: websocket.Dial ws://172.31.27.92:8081/qan-api/agents/10380f1923b$
2017/07/27 14:02:23.719845 WARNING agent ws error: read tcp 172.31.20.131:40717->172.31.27.92:8081: read: connection reset by peer
2017/07/27 14:02:23.720057 WARNING agent Lost connection to API, reconnecting

Mykola · August 2, 2017, 2:39am

chrishatcher1138 , thank you, I created bug report [url][PMM-1267] [qan agent] mysql restart accidentally not handled correctly - Percona JIRA
feel free to add any additional information into it

Topic		Replies	Views
PMM QAN Example doesn't show the whole query PMM 2.x	9	1010	April 20, 2022
Unable to get query in Query Analytics (MYSQL) taking time more than 5min Other MySQL® Questions mysql , percona	4	970	January 6, 2023
QAN is not match the time range what I want to see PMM 1.x	2	466	November 27, 2017
PMM QAN stopped to present information for about one hour and then resumed properly PMM 1.x	10	904	May 17, 2019
QAN: Slow Log Configuration + Bug in Logrotation PMM 1.x	7	2275	November 29, 2016

QAN not picking up change to long_query_time

Version: percona-qan-agent 1.2.0

Basedir: /usr/local/percona/qan-agent

PID: 23257

API: 172.31.27.92:8081/qan-api

UUID: 10380f1923b644516cce462ff3bf75ab

Version: percona-qan-agent 1.2.0

Basedir: /usr/local/percona/qan-agent

PID: 7354

API: 172.31.27.92:8081/qan-api

UUID: 10380f1923b644516cce462ff3bf75ab

Related topics