[MDEV-28038] Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size) Created: 2022-03-11  Updated: 2022-03-11

Status: Open
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Fix Version/s: None

Type: Task Priority: Major
Reporter: Nuno Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-13013 InnoDB unnecessarily extends data files Closed
relates to MDEV-26782 InnoDB temporary tablespace: reclaimi... Closed

 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?*



 Comments   
Comment by Marko Mäkelä [ 2022-03-11 ]

nunop, thank you for the report. This could be related to MDEV-13013 and MDEV-26782, which report that InnoDB is extending data files even though they already contain plenty of free pages.

Can you provide some SQL for reproducing this? It can be smaller, and I think that it can mostly be dummy data. What could be relevant is the number of pages, so it could be useful to set innodb_page_size=4k for reproducing this inconsistency while using less data.

You could use MDEV-26782 as a starting point. Yes, it is about temporary tables, and the temporary tablespace also contains undo log pages, so it is not an exact match of this report. I would recommend using innochecksum -S tablename.ibd for gathering statistics on the page allocation within the file.

Comment by Nuno [ 2022-03-11 ]

marko - thank you for the answer and references.

I said above that the records on this table are regularly expired. Every day, there are new records being inserted all the time, and then once a day, old records deleted.
Am I right to assume that the space "reclaimed" from the old records should be used for new records?
Note also that the PRIMARY KEY is not an autoincrement. It's based on two foreign IDs (e.g. User ID <-> Post ID)
I wonder if this could somehow be the reason for bigger fragmentation, since the records aren't added in a consistent order.

CREATE TABLE `mytable` (
 
  `uid` bigint(18) unsigned NOT NULL,
 
  `tid` bigint(18) unsigned NOT NULL,
 
  `fst` enum('0','1') COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `sn` mediumint(8) unsigned NOT NULL DEFAULT 1,
 
  `ls` int(10) unsigned NOT NULL,
 
  PRIMARY KEY (`uid`,`tid`,`fst`) USING BTREE,
 
  KEY `ls` (`ls`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

> Can you provide some SQL for reproducing this?

I'm not sure if I can easily provide some SQL that will visibly reproduce this.

Basically we have LOADS of INSERT queries every day, adding random: UID, TID, fst=1 or 2, ls=UNIX_TIMESTAMP()
Then a number of UPDATE queries incrementing "sn"

Once a day, DELETE from this table where ls < 5 months.

Hope this makes sense.

Thank you very much.

Generated at Thu Feb 08 09:57:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.