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

Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size)

    XMLWordPrintable

Details

    Description

      I have a InnoDB table with the following columns:

      `BIGINT` -> 8 bytes
      `BIGINT` -> 8 bytes
      `ENUM(0,1)` -> 1 byte
      `MEDIUMINT` -> 3 bytes
      `INT` -> 4 bytes

      = 24 bytes per row

      It contains 10454004 rows (based on COUNT), so I calculate the Data size as *250MB*.

      This table's `PRIMARY` key is `BIGINT`, `BIGINT`, `ENUM`.
      And has an `INDEX` on the `INT`.

      Based on `information_schema`,
      `DATA_LENGTH` = *530.8MiB*
      `INDEX_LENGTH` = 272.0MiB
      `DATA_FREE` = 6.0MiB
      = 808.8MiB = *848 MB*

      Also, `AVG_ROW_LENGTH` = 87 instead of 24

      And then I look at the actual `.ibd` file size, which is reported as:
      1451229184 bytes = *1451.23 MB*

      I understand there is fragmentation and sparse files in question (the records on this table are regularly expired, but never had a bigger retention time than currently is).
      But I'm not understanding why do I see 3 different sizes, with such big gap each.

      Goes from 250MB of real calculable data, to 530MiB of Data.
      And `DATA_FREE` is only 6MiB.

      And 848 MB reported by `information_schema` (which per my understanding includes "free space" as well), much lower than the actual `.ibd` file.

      *What am I missing or not considering?*

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              nunop Nuno
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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