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

Improve Innodb Tablespaces Data Collection

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: MySQLd_Exporter
    • Labels:

      Description

      This improvement would be great to contribute to upsteam

       

      We currently have data such as:

       

      mysql_info_schema_innodb_tablespace_allocated_size_bytes{agent_id="/agent_id/df9cfd20-8c2c-46ff-a53b-6e6f308fddab",agent_type="mysqld_exporter",az="myaz",environment="versions",instance="/agent_id/df9cfd20-8c2c-46ff-a53b-6e6f308fddab",job="mysqld_exporter_agent_id_df9cfd20-8c2c-46ff-a53b-6e6f308fddab_lr-1m0s",machine_id="/machine_id/612892249edc4cf7b40cdfd1534feded",node_id="/node_id/5389b202-d9ad-4857-978b-de49cfc811e2",node_model="linode1024",node_name="ps57-2-n-1-tpcc-tb1-s10-th1x",node_type="generic",region="datacenter4",service_id="/service_id/dd4ea72b-9431-4dde-a72e-5971697acd9e",service_name="ps57-2-n-1-tpcc-tb1-s10-th1x-mysql",service_type="mysql",tablespace_name="mysql/engine_cost"}

       

      As well as some other metrics 

       

      We need to improve it in 2 ways

       

      1) we currently report  tablespace_name  combining  Schema and Table in one Label which does not allow to conveniently join this data or group by Schema.  For tablespaces where possible (have schema and table name separated by /" we should also extract schema_name and table_name labels

       

      2) There are additional tablespaces which exist in MySQL 8 which are not named this way.  For these tablespaces we will have tablespace_name. We also should be able to differentiate them by space_type which also should be extracted as label

       

      mysql> select name, space_type, state,file_size, allocated_size, encryption from information_schema.INNODB_TABLESPACES where space_type!='single';
      -------------------------------------------------------------------+

      name space_type state file_size allocated_size encryption

      -------------------------------------------------------------------+

      mysql General normal 25165824 25169920 N
      innodb_system System normal 0 0 N
      innodb_temporary System normal 0 0 N
      innodb_undo_001 Undo active 12582912 12582912 N
      innodb_undo_002 Undo active 12582912 12582912 N

      -------------------------------------------------------------------+
      5 rows in set (0.00 sec)

       

      Note table structure will be slightly different in MySQL 8 and MySQL 5.7 so we need to ensure collector does select * and figures out which columns are available

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              pz Peter Zaitsev
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: