pmm table status this query it's very slow, server has hang

Percona Monitoring and Management (PMM) PMM 1.x
1

SELECT table_schema, 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 USING (table_schema,table_name) WHERE c.extra = ‘auto_increment’ AND t.auto_increment IS NOT NULL

2

How many tables do you have? select count(*) from information_schema.tables;

To prevent such queries you need to disable tablestats. Normally, tablestats is automatically disabled on 1000+ tables (pmm-admin list will show this on OPTIONS column).

To disable tablestats explicitly, re-add “mysql:metrics” service using pmm-admin with --disabla-tablestats flag.

3

root@yrz_if 09:42:13>select count() from information_schema.tables;
±---------+
| count() |
±---------+
| 923 |
±---------+
1 row in set (0.01 sec)

ok i will disable tablestats, thank you

4

I have run into a similar issue with a slight variation in the query locking from PMM:

| 1685363 | root | localhost | NULL | Query | 1371 | Waiting for table metadata lock | 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 = 'squirrel'

In this case there were over 180 of this query waiting for a metadata lock.

We currently have 474 tables.

mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
| 474 |
+----------+
5

The same thing - If per table stats queries produce any db performance issues, you can disable them using --disable-tablestats flag.