Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

pmm metrics 1.17 issue

Description

Hi,

in pmm-client-1.17 those two queries are issued:

 

SELECT t.table_schema, t.table_name, column_name, `auto_increment`,SELECT t.table_schema, t.table_name, column_name, `auto_increment`,   pow(2, case data_type     when 'tinyint'   then 7     when 'smallint'  then 15     when 'mediumint' then 23     when 'int'       then 31     when 'bigint'    then 63     end+(column_type like '% unsigned'))-1 as max_int   FROM information_schema.tables t   JOIN information_schema.columns c     ON BINARY t.table_schema = c.table_schema AND BINARY t.table_name = c.table_name

 

and

 

SELECT     TABLE_SCHEMA,     TABLE_NAME,     TABLE_TYPE,     ifnull(ENGINE, 'NONE') as ENGINE,     ifnull(VERSION, '0') as VERSION,     ifnull(ROW_FORMAT, 'NONE') as ROW_FORMAT,     ifnull(TABLE_ROWS, '0') as TABLE_ROWS,     ifnull(DATA_LENGTH, '0') as DATA_LENGTH,     ifnull(INDEX_LENGTH, '0') as INDEX_LENGTH,     ifnull(DATA_FREE, '0') as DATA_FREE,     ifnull(CREATE_OPTIONS, 'NONE') as CREATE_OPTIONS   FROM information_schema.tables   WHERE TABLE_SCHEMA = 'lists'

 

Needless to say that doesn't play out well with databases that have hundreds of thousands of tables. I understand this is documented in the FAQ as : "Table statistics are disabled automatically if there are over 1 000 tables."; but somehow it doesn't seem to happen.

 

Addendum. Those two particular scrapes seem to be collect.auto_increment.columns and collect.info_schema.tables:

time="2019-02-22T10:32:21Z" level=error msg="Error scraping for collect.auto_increment.columns: context deadline exceeded" source="exporter.go:110"
time="2019-02-22T10:32:21Z" level=error msg="Error scraping for collect.info_schema.tables: context deadline exceeded" source="exporter.go:110"

 

It would be great to provide a way to disable them with pmm-client.

How to test

None

How to document

None

Smart Checklist

Activity

Lalit Choudhary February 25, 2019 at 10:09 AM

Thank you for the report.

you can use the following command to disable extra table stats, collectors.

Example:

 

# pmm-admin add mysql:metrics ps57 --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox21800.sock --disable-tablestats

 

OR

 

# pmm-admin add mysql:metrics ps57 --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox21800.sock -- --collect.auto_increment.columns=false --collect.info_schema.table=false

 

Test: PMM 1.17.1

1. Default options:

root 8095 8094 2 15:02 ? 00:00:01 /usr/local/percona/pmm-client/mysqld_exporter -web.listen-address=172.17.0.1:42002 -web.auth-file=/usr/local/percona/pmm-client/pmm.yml -web.ssl-key-file=/usr/local/percona/pmm-client/server.key -web.ssl-cert-file=/usr/local/percona/pmm-client/server.crt -collect.auto_increment.columns=true -collect.binlog_size=true -collect.global_status=true -collect.global_variables=true -collect.info_schema.innodb_metrics=true -collect.info_schema.innodb_cmp=true -collect.info_schema.innodb_cmpmem=true -collect.info_schema.processlist=true -collect.info_schema.query_response_time=true -collect.info_schema.tables=true -collect.info_schema.tablestats=true -collect.info_schema.userstats=true -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 -collect.slave_status=true

 

 2. loading 1100 tables, expectation, expectation it should disable 

sysbench /usr/share/sysbench/oltp_write_only.lua --mysql_storage_engine=innodb --table-size=100 --tables=1100 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox5725.sock --threads=3 --time=900 --report-interval=1 --events=0 --db-driver=mysql prepare

 # sudo ps -ef | grep mysqld_exporter

root 8095 8094 3 15:02 ? 00:00:24 /usr/local/percona/pmm-client/mysqld_exporter -web.listen-address=172.17.0.1:42002 -web.auth-file=/usr/local/percona/pmm-client/pmm.yml -web.ssl-key-file=/usr/local/percona/pmm-client/server.key -web.ssl-cert-file=/usr/local/percona/pmm-client/server.crt -collect.auto_increment.columns=true -collect.binlog_size=true -collect.global_status=true -collect.global_variables=true -collect.info_schema.innodb_metrics=true -collect.info_schema.innodb_cmp=true -collect.info_schema.innodb_cmpmem=true -collect.info_schema.processlist=true -collect.info_schema.query_response_time=true -collect.info_schema.tables=true -collect.info_schema.tablestats=true -collect.info_schema.userstats=true -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 -collect.slave_status=true
lalit 9615 11168 0 15:13 pts/9 00:00:00 grep --color=auto mysqld_exporter

  1. pmm-admin list

-------------- ----- ----------- -------- ------------------------------------------------ ---------------------------------------------

SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS
-------------- ----- ----------- -------- ------------------------------------------------ ---------------------------------------------
mysql:queries ps57 - YES msandbox:***@unix(/tmp/mysql_sandbox21800.sock) query_source=perfschema, query_examples=true
linux:metrics ps57 42000 YES -
mysql:metrics ps57 42002 YES msandbox:***@unix(/tmp/mysql_sandbox21800.sock)

As per PMM FAQ https://www.percona.com/doc/percona-monitoring-and-management/faq.html#what-are-common-performance-considerations

Table statistics are disabled automatically if there are over 1 000 tables.

But we can even after having 1000+ tables not disabing -collect.info_schema.tablestats=true

 

3. To solve this disable add mysql:metrics with --disable-tablestats option.

~# pmm-admin remove mysql:metrics ps57

~# pmm-admin add mysql:metrics ps57 --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox21800.sock --disable-tablestats

~# pmm-admin list

-------------- ----- ----------- -------- ------------------------------------------------ ---------------------------------------------
SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS
-------------- ----- ----------- -------- ------------------------------------------------ ---------------------------------------------
mysql:queries ps57 - YES msandbox:***@unix(/tmp/mysql_sandbox21800.sock) query_source=perfschema, query_examples=true
linux:metrics ps57 42000 YES -
mysql:metrics ps57 42002 YES msandbox:*@unix(/tmp/mysql_sandbox21800.sock) tablestats=OFF

 # sudo ps -ef | grep mysqld_exporter

root 12982 12981 2 15:33 ? 00:00:00 /usr/local/percona/pmm-client/mysqld_exporter -web.listen-address=172.17.0.1:42002 -web.auth-file=/usr/local/percona/pmm-client/pmm.yml -web.ssl-key-file=/usr/local/percona/pmm-client/server.key -web.ssl-cert-file=/usr/local/percona/pmm-client/server.crt -collect.auto_increment.columns=false -collect.binlog_size=true -collect.global_status=true -collect.global_variables=true -collect.info_schema.innodb_metrics=true -collect.info_schema.innodb_cmp=true -collect.info_schema.innodb_cmpmem=true -collect.info_schema.processlist=true -collect.info_schema.query_response_time=true -collect.info_schema.tables=false -collect.info_schema.tablestats=false -collect.info_schema.userstats=true -collect.perf_schema.eventswaits=true -collect.perf_schema.file_events=true -collect.perf_schema.indexiowaits=false -collect.perf_schema.tableiowaits=false -collect.perf_schema.tablelocks=false -collect.slave_status=true

 

Bug Here Is:  Table statistics are disabled automatically if there are over 1 000 tables, which is not happening.

Roma Novikov February 22, 2019 at 10:34 AM

Thanks for this report !  can you  check this and reproduce this. Thanks 

Details

Assignee

Reporter

Priority

Components

Labels

Needs QA

Yes

Needs Doc

Yes

Affects versions

Smart Checklist

Created February 22, 2019 at 10:28 AM
Updated March 27, 2024 at 2:59 PM

Flag notifications