:
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 ?
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
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
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:
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.
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;"
what mysql vendor and version do you use?
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]
Unanswered | Unsolved | Solved
MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.