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

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

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

Comments

  • weberweber Advisor Inactive User Role Beginner
    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.
  • mmajaJokermmajaJoker Entrant Current User Role Beginner
    weber wrote: »
    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.

    [email protected]_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
  • cduffincduffin Entrant Current User Role Beginner
    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 |
    +----------+
    
  • weberweber Advisor Inactive User Role Beginner
    The same thing - If per table stats queries produce any db performance issues, you can disable them using --disable-tablestats flag.
This discussion has been closed.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.