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

"MySQL Query Responce Time" show "No datapoints"

4. All graphs on "MySQL Query Responce Time" show "No datapoints" for all servers.

Comments

  • MykolaMykola Percona Percona Staff Role
    4. These graphs are available only for Percona Server and MariaDB. It requires query response time plugin installed and query_response_time_stats variable turned on.
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    4. These graphs are available only for Percona Server and MariaDB. It requires query response time plugin installed and query_response_time_stats variable turned on.

    BAD_SERVER:

    [[email protected] ~]# mysql -e "SHOW PLUGINS" | grep QUERY
    QUERY_RESPONSE_TIME ACTIVE INFORMATION SCHEMA query_response_time.so GPL
    QUERY_RESPONSE_TIME_AUDIT ACTIVE AUDIT query_response_time.so GPL
    QUERY_RESPONSE_TIME_READ ACTIVE INFORMATION SCHEMA query_response_time.so GPL
    QUERY_RESPONSE_TIME_WRITE ACTIVE INFORMATION SCHEMA query_response_time.so GPL

    [email protected] ~]# mysql -e "show variables like '%query_response_time_stats%'"
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | query_response_time_stats | ON |
    +
    +
    +
  • MykolaMykola Percona Percona Staff Role
    can you share output for the following command?
    mysql -e 'SELECT @@query_response_time_stats'
    
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    [[email protected] ~]# mysql -e 'SELECT @@query_response_time_stats'
    +
    +
    | @@query_response_time_stats |
    +
    +
    | 1 |
    +
    +
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    Sorry, but on good servers this setting was 0. After i enable it data start to showed. But on bad server this setting was 1, and All graphs on "MySQL Query Responce Time" show "No datapoints".
  • MykolaMykola Percona Percona Staff Role
    can you share output of the following command for bad and good host?
    curl https://pmm-client-ip:42002/metrics-mr --insecure | grep mysql_info_schema_read_query_response_time_seconds_sum
    
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    curl https://bad-server:42002/metrics-mr --insecure | grep mysql_info_schema_read_query_response_time_seconds_sum
    % Total % Received % Xferd Average Speed Time Time Time Current
    Dload Upload Total Spent Left Speed
    0 117k 0 1058 0 0 99 0 0:20:11 0:00:10 0:20:01 241mysql_info_schema_read_query_response_time_seconds_sum 2.8901127170120003e+06
    100 117k 100 117k 0 0 11269 0 0:00:10 0:00:10 --:--:-- 35050

    curl https://good-server:42002/metrics-mr --insecure | grep mysql_info_schema_read_query_response_time_seconds_sum
    % Total % Received % Xferd Average Speed Time Time Time Current
    Dload Upload Total Spent Left Speed
    0 4168k 0 1057 0 0 1057 0 1:07:18 --:--:-- 1:07:18 1057mysql_info_schema_read_query_response_time_seconds_sum 6846.318266
    100 4168k 100 4168k 0 0 2509k 0 0:00:01 0:00:01 --:--:-- 2509k
  • MykolaMykola Percona Percona Staff Role
    Looks like /metrics-mr response time it is very long,
    it is needed to understand why :(

    can you in one terminal run the following command
    curl https://bad-server:42002/metrics-hr --insecure
    

    and in second terminal try to catch ~10 seconds query, by running the following command several times in parallel to curl command?
    mysql -e 'SHOW FULL PROCESSLIST;'
    
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    "curl https://bad-server:42002/metrics-hr --insecure" works to fast, I have no chance to do something in parallel. Here the output: http://depositfiles.com/files/xlmdl8525 But "curl https://bad-server:42002/metrics-mr --insecure" works slow. And I run "mysql -e 'SHOW FULL PROCESSLIST;'" few times. Output: http://depositfiles.com/files/un9c4vg27
  • MykolaMykola Percona Percona Staff Role
    can you share PROCESSLIST?
    http://depositfiles.com/files/un9c4vg27 - is metrics-mr content..
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    I cant, because of there is all time exist 1 and more processes that restore data in some DBs, so "mysql -e 'SHOW FULL PROCESSLIST;'" is huge. May be i can give info in other form?
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    And more - i update pmm and percona server to latest versions(1.1.2 and 5.7.17-13). Nothing changed.
    I can't even chose bad server from "Host" dropdown list - it missed there
  • MykolaMykola Percona Percona Staff Role
    if we cannot catch long query, we can try to disable /metrics-mr collectors.

    first of all, try to disable processlist collector on bad node
    pmm-admin remove mysql:metrics
    pmm-admin add mysql:metrics --disable-processlist
    
    wait two minutes and refresh targets page

    if bad-server/metrics-mr target still red,
    it is needed to disable all /metrics-mr collectors, bad-server/metrics-mr target should become green, after that we can enable collectors one-by-one.

    it is needed to disable all the following collectors in file on bad server.
    collect.slave_status
    collect.info_schema.processlist
    collect.perf_schema.eventswaits
    collect.perf_schema.file_events
    collect.perf_schema.tablelocks
    collect.info_schema.query_response_time
    collect.engine_innodb_status
    
    all params should exists in to false

    after that restart service
    systemctl daemon-reload
    systemctl restart pmm-mysql-metrics-42002
    
    wait two minutes, refresh targets page

    if bad-server/metrics-mr target become green - try to enable collectors one-by-one (with restart and targets check after each of them)
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    Ok, i checked all of this settings:

    Good settings(no red target):
    collect.slave_status=true
    collect.info_schema.processlist=true(false by default)
    collect.info_schema.query_response_time=true
    collect.engine_innodb_status=true(no such parameter in file by default)

    Bad settings(metrics-mr down instance="mysql.db" 1.07s ago context deadline exceeded), For now set to false:
    collect.perf_schema.eventswaits
    collect.perf_schema.file_events
    collect.perf_schema.tablelocks(was false by default)

    I see some graphs with problems start to work :) Full info about what is good and what is bad i will give later
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    "MySQL Query Responce Time" show all data (resolved)
    "MySQL General" -> "Process States" show data (resolved)
    "MySQL Performance Schema" show "No datapoints"
  • Michael CoburnMichael Coburn Principal Architect, Percona Percona Staff Role
    Hi aleksey.filippov , you possibly don't have the PERFORMANCE_SCHEMA enabled, you may check with:
    $ sandboxes/multi_cmsb_5_7_17-mariadb-10_1_21-mariadb-10_2_4/node1/use -e "show global variables like 'performance_schema'"
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | performance_schema | ON |
    +
    +
    +
    If that reports OFF you need to set in your my.cnf and restart. Then you should start seeing datapoints. Let us know how you progress.
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    [[email protected] ~]# mysql -e "show variables like '%performance_schema%'"
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | performance_schema | ON |
    | performance_schema_accounts_size | -1 |
    | performance_schema_digests_size | 10000 |
    | performance_schema_events_stages_history_long_size | 10000 |
    | performance_schema_events_stages_history_size | 10 |
    | performance_schema_events_statements_history_long_size | 10000 |
    | performance_schema_events_statements_history_size | 10 |
    | performance_schema_events_transactions_history_long_size | 10000 |
    | performance_schema_events_transactions_history_size | 10 |
    | performance_schema_events_waits_history_long_size | 10000 |
    | performance_schema_events_waits_history_size | 10 |
    | performance_schema_hosts_size | -1 |
    | performance_schema_max_cond_classes | 80 |
    | performance_schema_max_cond_instances | -1 |
    | performance_schema_max_digest_length | 1024 |
    | performance_schema_max_file_classes | 80 |
    | performance_schema_max_file_handles | 32768 |
    | performance_schema_max_file_instances | -1 |
    | performance_schema_max_index_stat | -1 |
    | performance_schema_max_memory_classes | 400 |
    | performance_schema_max_metadata_locks | -1 |
    | performance_schema_max_mutex_classes | 200 |
    | performance_schema_max_mutex_instances | -1 |
    | performance_schema_max_prepared_statements_instances | -1 |
    | performance_schema_max_program_instances | -1 |
    | performance_schema_max_rwlock_classes | 50 |
    | performance_schema_max_rwlock_instances | -1 |
    | performance_schema_max_socket_classes | 10 |
    | performance_schema_max_socket_instances | -1 |
    | performance_schema_max_sql_text_length | 1024 |
    | performance_schema_max_stage_classes | 150 |
    | performance_schema_max_statement_classes | 203 |
    | performance_schema_max_statement_stack | 10 |
    | performance_schema_max_table_handles | -1 |
    | performance_schema_max_table_instances | -1 |
    | performance_schema_max_table_lock_stat | -1 |
    | performance_schema_max_thread_classes | 50 |
    | performance_schema_max_thread_instances | -1 |
    | performance_schema_session_connect_attrs_size | 512 |
    | performance_schema_setup_actors_size | -1 |
    | performance_schema_setup_objects_size | -1 |
    | performance_schema_users_size | -1 |
    +
    +
    +
  • Michael CoburnMichael Coburn Principal Architect, Percona Percona Staff Role
    HA! My apologies aleksey.filippov , when I clicked through to your post it was the only one on the page so I assumed it was post #1, not... post #1 of page #2!!

    First off I'm glad that the Response Time Distribution and Process States are again working. :thumbsup:

    in terms of :
    -collect.perf_schema.eventswaits=true
    -collect.perf_schema.file_events=true
    -collect.perf_schema.indexiowaits=true
    -collect.perf_schema.tableiowaits=true
    -collect.perf_schema.tablelocks=true
    
    Can you please review file on your Bad server and verify these are set to true, and that you restart the service:
    sudo systemctl restart pmm-mysql-metrics-42002.service
    
    Let us know if this helps,
  • MykolaMykola Percona Percona Staff Role
    Aleksey,

    "Performance Schema File IO (Events)", "Performance Schema File IO (Load)", "Performance Schema File IO (Bytes)" graphs use data from collector.
    "Performance Schema Waits (Events)" and "Performance Schema Waits (Load)" use
    "Index Access Operations (Load)" use
    "Table Access Operations (Load)" use

    we disabled
    so it is expected that "Performance Schema File" and "Performance Schema Waits" graphs are empty for bad server.
    but "Index Access Operations" and "Table Access Operations" should work fine if

    sorry for long response
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    Looks like all problems solved by starting docker pmm-server like this:

    docker run -d \
    -p 80:80 \
    --volumes-from pmm-data \
    --name pmm-server \
    --restart always \
    --env TZ="Europe/Kiev" \
    --env METRICS_RESOLUTION=5s \
    percona/pmm-server:1.1.3

    And client setup like this:

    pmm-admin uninstall
    pmm-admin repair
    pmm-admin config --server pmm-server --client-name mysql.db
    pmm-admin add linux:metrics
    pmm-admin add mysql:queries --query-source slowlog
    pmm-admin add mysql:metrics --disable-tablestats-limit 3000

    After that i have all graphics, including performance_schema, exception is table statistics for DBs with table count > 3000. I don't have to edit
  • MykolaMykola Percona Percona Staff Role
    it is not recommended way, because in this case, you lose details, with 5s resolution it is harder to debug database performance
  • aleksey.filippovaleksey.filippov Contributor Current User Role Beginner
    Ok, i do not think about. Thanks
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.