Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-37083

Inconsistency between sys.innodb_buffer_stats_by_table and information_schema.innodb_buffer_page

    XMLWordPrintable

Details

    Description

      Hello

      I'll start out by saying I'm not 100 % sure of this, and I'm sorry if I'm wasting anyones time.

      When looking at sys.innodb_buffer_stats_by_table and sys.innodb_buffer_stats_by_schema two of the columns are, pages_hashed and pages_old

      Pulling up the CREATE VIEW for that view shows how each of those are gathered

      SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
             REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name,
             format_bytes(SUM(IF(ibp.compressed_size = 0, 16384, compressed_size))) AS allocated,
             format_bytes(SUM(ibp.data_size)) AS data,
             COUNT(ibp.page_number) AS pages,
             COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
             COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
             ROUND(SUM(ibp.number_records)/COUNT(DISTINCT ibp.index_name)) AS rows_cached 
        FROM information_schema.innodb_buffer_page ibp 
       WHERE table_name IS NOT NULL
       GROUP BY object_schema, object_name
       ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;
      

      They both reference information_schema.innodb_buffer_page
      pages_old checks if is_old is equal to 'YES', but the is_old is not a string but an int:
      https://github.com/MariaDB/server/blob/212fad1b7e26f80c65299179ed320b766570d251/storage/innobase/handler/i_s.cc#L118

      Running the following, seems to give a good indication that the "logic" has been flipped, in either INNODB_BUFFER_PAGE or the sys views

      SELECT IS_OLD, if(`IS_OLD` = 'YES',1,NULL) AS `pages_old` FROM `INNODB_BUFFER_PAGE`;
      

      And the same is true for IS_HASHED vs. pages_hashed

      Again, I'm not 100% sure of this, and I'm very sorry if this ends up wasting anyones time

      Attachments

        Issue Links

          Activity

            People

              mariadb-satishkumar Satish Kumar
              noir04 Lasse R. Brock
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.