Not the answer you need?
Register and ask your own question!

QAN not picking up change to long_query_time

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

Comments

  • kamil-dziedzickamil-dziedzic Entrant Inactive User Role Beginner
    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
    
  • chrishatcher1138chrishatcher1138 Entrant Current User Role Beginner


    LOG_OUTPUT
    FILE


    LOG_QUERIES_NOT_USING_INDEXES
    OFF


    LOG_SLOW_ADMIN_STATEMENTS
    OFF


    LOG_SLOW_FILTER
    admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk


    LOG_SLOW_RATE_LIMIT
    1


    LOG_SLOW_SLAVE_STATEMENTS
    OFF


    LOG_SLOW_VERBOSITY
    query_plan


    LONG_QUERY_TIME
    1.000000


    MIN_EXAMINED_ROW_LIMIT
    0


    PERFORMANCE_SCHEMA
    ON


    PERFORMANCE_SCHEMA_DIGESTS_SIZE
    5000


    PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH
    1024


    SLOW_QUERY_LOG
    ON


    SLOW_QUERY_LOG_FILE
    /var/log/mysql/mariadb-slow.log




    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)
  • MykolaMykola Percona Percona Staff Role
    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?
  • chrishatcher1138chrishatcher1138 Entrant Current User Role Beginner
    Once I restarted the agent, it seems to be working correctly. Thanks for the help.
  • MykolaMykola Percona Percona Staff Role
    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...
  • chrishatcher1138chrishatcher1138 Entrant Current User Role Beginner
    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
  • MykolaMykola Percona Percona Staff Role
    chrishatcher1138 , thank you, I created bug report https://jira.percona.com/browse/PMM-1267
    feel free to add any additional information into it
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.