PMM Gravana does not show all data

:

Screens from grafana and prometheus

But it is Linux metrics, it always exist for bad and good servers.

Some other news: we have 3 graphs, all different - bad server have no some metrics, other have all metrics, other have gaps in some metrics:

oh, can you check prometheus graph for mysql_global_variables_max_connections ?

Bad server is missing. And i have feeling like we walk by the circle.

How many tables do you have?

I think we have very long or wrong response from mysqld_exporter.

can you measure response time with the following command?

wget https://192.168.200.206:42002/metrics-lr --no-check-certificate

wget output should show start time in the first line and end time in the last

After some internal discussion, my colleague created feature request for mysqld_exporter - [URL=“Gather statistics on mysqld_exporter collection performance · Issue #188 · prometheus/mysqld_exporter · GitHub”]https://github.com/prometheus/mysqld...ter/issues/188[/URL]
feel free to +1 it

also we created two internal tickets
[URL][PMM-663] scrape_duration_seconds is broken with current prometheus configuration file - Percona JIRA
[URL][PMM-664] Gather statistics on mysqld_exporter collection performance - Percona JIRA

Done :slight_smile:

And ones more. I checked memory usage by mysqld_exporter with top.
Bad server:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18853 root 20 0 2441360 2,054g 4680 S 280,4 13,2 19:02.85 mysqld_exporter

Good servers:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26945 root 20 0 766688 117948 3560 S 7,0 0,7 2550:20 mysqld_exporter

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15257 root 20 0 316880 144584 3168 S 23,6 7,7 2103:36 mysqld_exporter

On bad server RES=2,054g (10 min after pmm-admin restart)
On good servers RES=117948(144584) (few days after restart)

How many tables do you have?

can you show output of the following command?


wget https://192.168.200.206:42002/metrics-lr --no-check-certificate 

Tables count in bad db = 3639, good db`s = 1736 each
About output - command downloaded file 250M size. If you want i can share it somewhere.

OUTPUT FROM BAD SERVER:

–2017-03-16 17:04:10-- https://bad_ip:42002/metrics-lr
Подключение к bad_ip:42002… соединение установлено.
ПРЕДУПРЕЖДЕНИЕ: невозможно проверить сертификат bad_ip, выпущенный «O=PMM Client»:
Невозможно локально проверить подлинность запрашивающего.
HTTP-запрос отправлен. Ожидание ответа… 200 OK
Длина: 261433316 (249M) [text/plain]
Сохранение в: «metrics-lr»

metrics-lr 100%[================================================== ==========================================>] 249,32M 5,15MB/s in 57s

2017-03-16 17:06:04 (4,34 MB/s) - «metrics-lr» сохранён [261433316/261433316]

OUTPUT FROM GOOD SERVER:

–2017-03-16 17:48:43-- https://good_ip:42002/metrics-lr
Подключение к good_ip:42002… соединение установлено.
ПРЕДУПРЕЖДЕНИЕ: невозможно проверить сертификат good_ip, выпущенный «O=PMM Client»:
Невозможно локально проверить подлинность запрашивающего.
HTTP-запрос отправлен. Ожидание ответа… 200 OK
Длина: 8876739 (8,5M) [text/plain]
Сохранение в: «metrics-lr.1»

metrics-lr.1 100%[============================================================================================>] 8,46M 3,30MB/s in 2,6s

2017-03-16 17:48:50 (3,30 MB/s) - «metrics-lr.1» сохранён [8876739/8876739]

wow!
can you share this archive??

[url]DepositFiles
or
[url]http://rg.to/file/79f11a1fb036e734bfe207a525dd46ec/metrics-lr.tar.gz.html[/url]

I found that table_statistics is the biggest part of all output (235Mb of 249M).

grep mysql_info_schema_table_statistics_rows_ metrics-lr | wc -c
246603314

can you disable it?

pmm-admin remove mysql:metrics
pmm-admin add mysql:metrics --disable-tablestats

unfortunately, two graphs on “MySQL Table Statistics” dashboard became empty for bad host - “Top Tables by Rows Read” “Top Tables by Rows Read”

finally, after this change everything should work fine :slight_smile:

MySQL Table Statistics show no data on all graphics with --disable-tablestats

May be i can use --disable-tablestats-limit with some count for bad server to collect as more stats as possible?

this option works only on during command run.
pmm-admin looking into mysql database and fetch table count, if current count is bigger than disable-tablestats-limit, tablestats queries become disabled.

Questions:

  1. You told, that with “pmm-admin add mysql:metrics --disable-tablestats”, two graphs on “MySQL Table Statistics” dashboard became empty for bad host - “Top Tables by Rows Read” “Top Tables by Rows Read”. ({#59|https://www.percona.com/forums/questions-discussions/percona-monitoring-and-management/47254-pmm-gravana-does-not-show-all-data?p=47685#post47685}). But all graphics on “MySQL Table Statistics” empty for bad server. Can you clarify, is it OK with --disable-tablestats, or it is error and must be fixed?
  2. “MySQL General” → “Process States” show “No datapoints” for bad server, for other servers there is data.
  3. All graphs on “MySQL Performance Schema” show “No datapoints” for bad server, for other servers there is data.
  4. All graphs on “MySQL Query Responce Time” show “No datapoints” for all servers.
  1. we have issue in mysqld_exporter option collect.info_schema.tablestats, but when you run pmm-admin with --disable-tablestats, it also disables mysqld_export collect.info_schema.tables option.
    so, all graphs on “MySQL Table Statistics” dashboard became empty. you can manually modify mysqld_exporter startup file but it is not recommended.

  2. can you check share output of following commands:

curl https://BAD-SERVER-IP:42002/metrics-mr --insecure | grep mysql_info_schema_threads
mysql -e "SELECT COALESCE(command,''),COALESCE(state,''),count(*),sum(time) FROM information_schema.processlist WHERE ID != connection_id() GROUP BY command,state ORDER BY null;"
  1. what mysql vendor and version do you use?

  2. 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.

2. can you check share output of following commands:

aleksey@aleksey-filippov:~$ curl https://bad_server:42002/metrics-mr --insecure | grep mysql_info_schema_threads

% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 117k 0 1058 0 0 349 0 0:05:43 0:00:03 0:05:40 349# HELP mysql_info_schema_threads The number of threads (connections) split by current state.
# TYPE mysql_info_schema_threads gauge
mysql_info_schema_threads{state="after create"} 0
mysql_info_schema_threads{state="altering table"} 0
mysql_info_schema_threads{state="analyzing"} 0
mysql_info_schema_threads{state="checking permissions"} 0
mysql_info_schema_threads{state="checking table"} 0
mysql_info_schema_threads{state="cleaning up"} 0
mysql_info_schema_threads{state="closing tables"} 0
mysql_info_schema_threads{state="converting heap to myisam"} 0
mysql_info_schema_threads{state="copying to tmp table"} 0
mysql_info_schema_threads{state="creating sort index"} 0
mysql_info_schema_threads{state="creating table"} 0
mysql_info_schema_threads{state="creating tmp table"} 0
mysql_info_schema_threads{state="deleting"} 0
mysql_info_schema_threads{state="end"} 0
mysql_info_schema_threads{state="executing"} 0
mysql_info_schema_threads{state="execution of init_command"} 0
mysql_info_schema_threads{state="flushing tables"} 0
mysql_info_schema_threads{state="freeing items"} 0
mysql_info_schema_threads{state="fulltext initialization"} 0
mysql_info_schema_threads{state="idle"} 62
mysql_info_schema_threads{state="init"} 0
mysql_info_schema_threads{state="killed"} 0
mysql_info_schema_threads{state="logging slow query"} 0
mysql_info_schema_threads{state="login"} 0
mysql_info_schema_threads{state="manage keys"} 0
mysql_info_schema_threads{state="opening tables"} 0
mysql_info_schema_threads{state="optimizing"} 0
mysql_info_schema_threads{state="other"} 0
mysql_info_schema_threads{state="preparing"} 0
mysql_info_schema_threads{state="reading from net"} 0
mysql_info_schema_threads{state="removing duplicates"} 0
mysql_info_schema_threads{state="removing tmp table"} 0
mysql_info_schema_threads{state="reopen tables"} 0
mysql_info_schema_threads{state="repair by sorting"} 0
mysql_info_schema_threads{state="repair done"} 0
mysql_info_schema_threads{state="repair with keycache"} 0
mysql_info_schema_threads{state="replication master"} 0
mysql_info_schema_threads{state="rolling back"} 0
mysql_info_schema_threads{state="searching rows for update"} 0
mysql_info_schema_threads{state="sending data"} 1
mysql_info_schema_threads{state="sorting for group"} 0
mysql_info_schema_threads{state="sorting for order"} 0
mysql_info_schema_threads{state="sorting index"} 0
mysql_info_schema_threads{state="sorting result"} 0
mysql_info_schema_threads{state="statistics"} 0
mysql_info_schema_threads{state="updating"} 1
mysql_info_schema_threads{state="waiting for lock"} 0
mysql_info_schema_threads{state="waiting for table flush"} 0
mysql_info_schema_threads{state="waiting for tables"} 0
mysql_info_schema_threads{state="waiting on cond"} 0
mysql_info_schema_threads{state="writing to net"} 0
# HELP mysql_info_schema_threads_seconds The number of seconds threads (connections) have used split by current state.
# TYPE mysql_info_schema_threads_seconds gauge
mysql_info_schema_threads_seconds{state="after create"} 0
mysql_info_schema_threads_seconds{state="altering table"} 0
mysql_info_schema_threads_seconds{state="analyzing"} 0
mysql_info_schema_threads_seconds{state="checking permissions"} 0
mysql_info_schema_threads_seconds{state="checking table"} 0
mysql_info_schema_threads_seconds{state="cleaning up"} 0
mysql_info_schema_threads_seconds{state="closing tables"} 0
mysql_info_schema_threads_seconds{state="converting heap to myisam"} 0
mysql_info_schema_threads_seconds{state="copying to tmp table"} 0
mysql_info_schema_threads_seconds{state="creating sort index"} 0
mysql_info_schema_threads_seconds{state="creating table"} 0
mysql_info_schema_threads_seconds{state="creating tmp table"} 0
mysql_info_schema_threads_seconds{state="deleting"} 0
mysql_info_schema_threads_seconds{state="end"} 0
mysql_info_schema_threads_seconds{state="executing"} 0
mysql_info_schema_threads_seconds{state="execution of init_command"} 0
mysql_info_schema_threads_seconds{state="flushing tables"} 0
mysql_info_schema_threads_seconds{state="freeing items"} 0
mysql_info_schema_threads_seconds{state="fulltext initialization"} 0
mysql_info_schema_threads_seconds{state="idle"} 4067
mysql_info_schema_threads_seconds{state="init"} 0
mysql_info_schema_threads_seconds{state="killed"} 0
mysql_info_schema_threads_seconds{state="logging slow query"} 0
mysql_info_schema_threads_seconds{state="login"} 0
mysql_info_schema_threads_seconds{state="manage keys"} 0
mysql_info_schema_threads_seconds{state="opening tables"} 0
mysql_info_schema_threads_seconds{state="optimizing"} 0
mysql_info_schema_threads_seconds{state="other"} 0
mysql_info_schema_threads_seconds{state="preparing"} 0
mysql_info_schema_threads_seconds{state="reading from net"} 0
mysql_info_schema_threads_seconds{state="removing duplicates"} 0
mysql_info_schema_threads_seconds{state="removing tmp table"} 0
mysql_info_schema_threads_seconds{state="reopen tables"} 0
mysql_info_schema_threads_seconds{state="repair by sorting"} 0
mysql_info_schema_threads_seconds{state="repair done"} 0
mysql_info_schema_threads_seconds{state="repair with keycache"} 0
mysql_info_schema_threads_seconds{state="replication master"} 0
mysql_info_schema_threads_seconds{state="rolling back"} 0
mysql_info_schema_threads_seconds{state="searching rows for update"} 0
mysql_info_schema_threads_seconds{state="sending data"} 1
mysql_info_schema_threads_seconds{state="sorting for group"} 0
mysql_info_schema_threads_seconds{state="sorting for order"} 0
mysql_info_schema_threads_seconds{state="sorting index"} 0
mysql_info_schema_threads_seconds{state="sorting result"} 0
mysql_info_schema_threads_seconds{state="statistics"} 0
mysql_info_schema_threads_seconds{state="updating"} 1
mysql_info_schema_threads_seconds{state="waiting for lock"} 0
mysql_info_schema_threads_seconds{state="waiting for table flush"} 0
mysql_info_schema_threads_seconds{state="waiting for tables"} 0
mysql_info_schema_threads_seconds{state="waiting on cond"} 0
mysql_info_schema_threads_seconds{state="writing to net"} 0
100 117k 100 117k 0 0 38924 0 0:00:03 0:00:03 --:--:-- 38937

BAD_SERVER:
mysql -e "SELECT COALESCE(command,‘’),COALESCE(state,‘’),count(),sum(time) FROM information_schema.processlist WHERE ID != connection_id() GROUP BY command,state ORDER BY null;"
±---------------------±-------------------±---------±----------+
| COALESCE(command,‘’) | COALESCE(state,‘’) | count(
) | sum(time) |
±---------------------±-------------------±---------±----------+
| Sleep | | 65 | 3631 |
| Query | Sending data | 1 | 1 |
| Query | update | 1 | 0 |
±---------------------±-------------------±---------±----------+

GOOD_SERVER:
mysql -e "SELECT COALESCE(command,‘’),COALESCE(state,‘’),count(),sum(time) FROM information_schema.processlist WHERE ID != connection_id() GROUP BY command,state ORDER BY null;"
±---------------------±-------------------±---------±----------+
| COALESCE(command,‘’) | COALESCE(state,‘’) | count(
) | sum(time) |
±---------------------±-------------------±---------±----------+
| Sleep | | 4 | 3132 |
±---------------------±-------------------±---------±----------+

3. what mysql vendor and version do you use?

Percona-Server-client-57-5.7.17-11.1.el7.x86_64
Percona-Server-server-57-5.7.17-11.1.el7.x86_64
Percona-Server-shared-57-5.7.17-11.1.el7.x86_64
Percona-Server-shared-compat-57-5.7.17-11.1.el7.x86_64

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:

[root@mysql ~]# 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

root@mysql ~]# mysql -e “show variables like ‘%query_response_time_stats%’”
±--------------------------±------+
| Variable_name | Value |
±--------------------------±------+
| query_response_time_stats | ON |
±--------------------------±------+

I created separate threads for each new topic, lets don’t mix
2. [url]https://www.percona.com/forums/questions-discussions/percona-monitoring-and-management/47831-mysql-general-process-states-show-no-datapoints[/url]
3. [url]https://www.percona.com/forums/questions-discussions/percona-monitoring-and-management/47836-mysql-performance-schema-show-no-datapoints[/url]
4. [url]https://www.percona.com/forums/questions-discussions/percona-monitoring-and-management/47839-mysql-query-responce-time-show-no-datapoints[/url]