Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-7227

Table stats metrics not being collected in instances with millions of tables

    XMLWordPrintable

    Details

    • Story Points:
      1
    • Sprint:
      02 - Core
    • Needs Review:
      Yes
    • Needs QA:
      Yes
    • Needs Doc:
      Yes
    • Platform Team:
      C/S Core

      Description

      User story:

      • As a PMM user, I need to have my Table metrics not being collected when the number of tables in DB is big so that the system will not be overloaded with metrics collection

      UI/UX:

      `disable-tablestats` flag used as before but includes one additional collector to be excluded "info_schema.innodb_tablespaces"

      Acceptance criteria:

      • For the system with more than 1000 (as current default) tables when we disable some collectors in mysqld exporter we must exclude info_schema.innodb_tablespaces metrics collection too.
      • Documentation Update 
        1. Have in documentation list of collectors we disable by flag disable-tablestats
        2. Add information that it's now include info_schema.innodb_tablespaces. 
        3. Describe the behavior of already monitored systems where the flag is used after the update to the new version (client  or server ? )

      Out of scope:

      • N/A

      Suggested implementation:

      1. Remove collect.info_schema.innodb_tablespaces from here: https://github.com/percona/pmm-managed/blob/PMM-2.0/services/agents/mysql.go#L42:L42

      2. Add collect.info_schema.innodb_tablespaces here: https://github.com/percona/pmm-managed/blob/PMM-2.0/services/agents/mysql.go#L79:L79

      3. Remove collect.info_schema.innodb_tablespaces here: https://github.com/percona/pmm-managed/blob/1b79357f4ef7037243e532c07d3cb8f6ceee7ee4/services/victoriametrics/scrape_configs.go#L324

      4. Add collect.info_schema.innodb_tablespaces here: https://github.com/percona/pmm-managed/blob/1b79357f4ef7037243e532c07d3cb8f6ceee7ee4/services/victoriametrics/scrape_configs.go#L332

      5. Check if all tests works, otherwise fix them.

       

       

      How to test:

      Run this in mysql to create 1 500 tables in selected database:

      CREATE PROCEDURE `createTableProcTest`()
      BEGIN
          DECLARE count INT Default 0;
            simple_loop: LOOP
               SET @a := count + 1;
               SET @statement = CONCAT('Create table Table',@a,' ( name VARCHAR(70), age int );');
               PREPARE stmt FROM @statement;
                       EXECUTE stmt;
                       DEALLOCATE PREPARE stmt;
                       SET count = count + 1;
               IF count=1500 THEN
                  LEAVE simple_loop;
               END IF;
      END LOOP simple_loop;
      END

      Then:

      Call createTableProcTest(); 

      Tables should be created now. Then continue with these steps (1st comment here): https://jira.percona.com/browse/PMM-7227?focusedCommentId=275234&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#/comment-275234

       

       

      Details:
       
      ------------

      Hello PMM team

      we have a customer with huge amount of servers and each server have massive numbers  of tables as well

      We disabled the next params  

      --disable-queryexamples
      --disable-tablestats
      --skip-connection-check

      but the next queries still pilling up, and some times these queries are killing the server until it's unreachable

       

      | 81163726 | percona_pmm | 127.0.0.1:56940 | NULL | Query | 179 | executing | SELECT
      SPACE,
      NAME,
      ifnull(FILE_FORMAT, 'NONE') as FILE_FORMAT,
      ifnull(ROW_FORMA | 0 | 0 |
      | 81163798 | percona_pmm | 127.0.0.1:56998 | NULL | Query | 119 | executing | SELECT
      SPACE,
      NAME,
      ifnull(FILE_FORMAT, 'NONE') as FILE_FORMAT,
      ifnull(ROW_FORMA | 0 | 0 |
      | 81163890 | percona_pmm | 127.0.0.1:57068 | NULL | Query | 59 | executing | SELECT
      SPACE,
      NAME,
      ifnull(FILE_FORMAT, 'NONE') as FILE_FORMAT,
      ifnull(ROW_FORMA | 0 | 0 |
      

      This is the number of tables for one server

      XXXXXXXX-chi (none)> select count(*) from information_schema.tables where table_schema not in ('mysql', 'percona', 'information_schema');
      +----------+
      | count(*) |
      +----------+
      | 7830912 |
      +----------+
      1 row in set (21.40 sec)

      is there another option to enable PMM client without killing the server? maybe another param to disable?

      Please let us know any comments.

      Thanks

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              walter.garcia Walter Garcia (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 2 hours
                  2h

                    Smart Checklist