Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
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
- relates to
-
MDEV-32663 mariadb filesystem size increasing for wp_options.ibd files
- Needs Feedback
-
MDEV-13013 InnoDB unnecessarily extends data files
- Closed
-
MDEV-26782 InnoDB temporary tablespace: reclaiming of free space does not work
- Closed