I'm trying to understand a discrepancy in the file size on disk of my tables and the size of the information stored in them.
I'm using MySQL 8 hosted on RDS (InnoDB, one file per table), so getting direct access to the disk is unfortunately impossible.
One table in particular demonstrates the problem well:
mysql> describe prod_insti.workItemContentVersion;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| parentId | int(11) | NO | PRI | NULL | |
| serialNumber | int(11) | NO | PRI | NULL | |
| previousVersionSerialNumber | int(11) | YES | | NULL | |
| authorPrincipalName | varchar(256) | YES | | NULL | |
| textAnswer | longtext | YES | | NULL | |
| textAnswerContentType | varchar(10) | YES | | NULL | |
| blobAnswer | longblob | YES | | NULL | |
| contentSavedOn | datetime | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
This answer indicates that the correct way to get the size of an InnoDB table is like so:
mysql> SELECT
-> table_name AS `Table`,
-> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
-> FROM information_schema.TABLES
-> WHERE table_schema = 'prod_insti'
-> AND table_name = 'workItemContentVersion';
+------------------------+------------+
| Table | Size in MB |
+------------------------+------------+
| workItemContentVersion | 1398809.50 |
+------------------------+------------+
However, my actual disk usage reported through RDS is way higher than this. I went trawling through the information_schema tables and eventually found the following information, which aligns much more closely with the observed disk usage:
mysql> select name, row_format, round(file_size / 1024 / 1024) as 'File size in MB', round(allocated_size / 1024 / 1024) as 'Allocated size in MB' from information_schema.INNODB_TABLESPACES where name = 'prod_insti/workItemContentVersion';
+-----------------------------------+------------+-----------------+----------------------+
| name | row_format | File size in MB | Allocated size in MB |
+-----------------------------------+------------+-----------------+----------------------+
| prod_insti/workItemContentVersion | Compressed | 2779672 | 2779677 |
+-----------------------------------+------------+-----------------+----------------------+
Scrutinizing the reference documentation here, it seems like the DATA_LENGTH attribute is actually the rounded-to-the-nearest-page length of the clustered index for an InnoDB table.
Now, it further seems (blog article, official docs) that when the ROW_FORMAT of an InnoDB table is DYNAMIC or COMPRESSED, BLOB and LONGTEXT columns are mostly not stored in the clustered index, but rather in their own 'overflow' section (or something? I'm not sure exactly what this really means).
So, my hypothesis is that DATA_LENGTH simply doesn't report the space used by the BLOB or TEXT columns in this table.
My question:
I'm looking for confirmation that this makes sense; and if so, can I sensibly assume that the disk size used by BLOB/TEXT columns in this table is equal to allocated_size from information_schema.INNODB_TABLESPACES minus (data_length + index_length) from information_schema.TABLES? Or... are there other things that could be using this space?
If so, I should be able to get a lot of space back by replacing the blobs with pointers to files in S3.
I've also considered that the discrepancy might be due to unallocated space, e.g. if there were many deletes in the past. However, I think that would show up as DATA_FREE here, and it looks like it's negligible to me:
mysql> SELECT table_name AS `Table`, round(data_free / 1024 / 1024) as 'Data free in MB', round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = 'prod_insti' AND table_name = 'workItemContentVersion';
+------------------------+-----------------+------------+
| Table | Data free in MB | Size in MB |
+------------------------+-----------------+------------+
| workItemContentVersion | 2 | 1398809.50 |
+------------------------+-----------------+------------+
This makes sense to me as it's a rapidly-growing table and I wasn't aware of any deletes happening. Having a look for high DATA_FREE values in other tables I haven't found anything in the same order of magnitude as the DATA_LENGTH values, so I don't think this is my problem.
I also thought that maybe I need to run OPTIMIZE_TABLE on this table, but... I can't figure out what information would indicate that that would be necessary or helpful. Is an unusually high DATA_FREE count the indicator that you need to run OPTIMIZE_TABLE?