Mysql – How to measure amount of MySQL 8 INNODB table used to store blobs

amazon-rdsdisk-spaceinnodbMySQLmysql-8.0

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?

Best Answer

 SHOW TABLE STATUS LIKE 'workItemContentVersion';

will probably give you the 1.4TB that you got from the I_S query. But give it a try.

What is the value of innodb_page_size ? Normally, it is 16384, but between COMPRESSED and your two numbers differing by almost exactly a factor of 2, I wonder.

"Data_free" give only one of several "free" space in a table. So, alas, there is no decent metric to decide when to OPTIMIZE TABLE. Only in rare case, such as lots of deletes that won't be replaced, is OPTIMIZE worth doing. And your 1.4TB (or 2.8TB) table will take a long time.

What is the setting of innodb_file_per_table? It had better be 1, else the optimize will just make things worse. Even so, you will need a few TB to run the query.

2MB for Data_free is actually abnormally small. In virtually all tables I see, the the number is 4M-7M (or more).

The data and the PRIMARY KEY coexist in one BTree that is ordered by that PK. Each secondary index lives in a separate BTree, where the leaf 'rows' contain the PK.

Big blobs (and big texts, and sometimes big varchars) do not live in the above-mentioned BTrees; they are off in some other blocks. Some call this "off-record storage".

COMPRESSED is typically benchmarked at only 2x shrinkage. (Unlike most compress routines that shrink text/xml/code/etc by 3x.)

OPTIMIZE TABLE essentially copies the table over and rebuilds the indexes. For the data BTree, this is relatively straightforward, and it squeezes out most unused space from that BTree. The indexes may or may not shrink; I think they are rebuilt, not copied over.

If you need to do any maintenance on the table (add columns, change datatypes, etc), I suggest you simply do the ALTER. In some situations, it does the optimize work. Better, yet, use ALGORITHM=COPY would force it to do so.

I would hope that Data_length includes the blobs, but I don't have proof.

Deletes do not necessarily show up in Data_free. That metric only counts blocks. A BTree is a bunch of blocks. InnoDB makes some attempt to combine adjacent blocks after deletes. But, in the best of worlds, each BTree block is between 50% and 100% full, for an average of 69%. This is not reflected in Data_free.

So, for example, if you delete every third row in a table, Data_length and Data_free are not likely to change. Deleting 2 out of each 3 is likely to move some blocks from used to free.

Compression is at the block level. So, the buffer_pool tends to have two copies of many blocks -- both a compressed and an uncompressed copy. That is, disk space is saved, but the buffer_pool is impacted. I prefer to compress in the client rather than burdening the server. This offloads the CPU and provides 3x in more cases.

For rows with short text/blob, that data will be stored on-record. That is, the off-record storage is reserved for big columns. (This varies with the ROW_FORMAT, which I assume is COMPRESSED for your table.)

Related Solutions

MySQL table locks solution -> InnoDb / Partitions

key_buffer_size ? RAM size?

On huge tables, indexes, especially if they are not "appended to", cause disk hits -- whether MyISAM or InnoDB.

var0, being an AUTO_INCREMENT, will be "appended to" the end. Essentially no disk hits for it. I can't tell about the other 4 indexes. Let's say they are all very random. This implies that each row inserted will need 4 disk hits. On normal drives that means about 25 rows inserted per second.

Solutions...

RAID striping (0,5,6,10). That will give you a factor of improvement.

SSDs. $$$

PARTITIONing. This may help. It may also help with some SELECTs. To discuss further, please reveal some of the semantics of the indexed fields. (dates? md5s? names?) Also provide some of the SELECTs, so we can optimize them at the same time. The "partition key" must be part of any UNIQUE key. This is not a problem with the AUTO_INCREMENT, but it may be with the other UNIQUE key.

Nothing (yet) says whether the Engine will make any difference.

MySQL InnoDB Optimization – Dynamic Table Optimization Without ‘File Per Table’

Before answering the question, please click here to see the InnoDB Infrastructure Map

Based on innodb_file_per_table being disabled, let's go through your questions:

Q 1: How much fragmentation is allowed before it affects performance?

The system tablespace can grow to the limit of the disk volume.

EXAMPLE: I just answered a question about what do to when the system tablespace reaches the limit of an ext3 disk : How to solve "The table ... is full" with "innodb_file_per_table"?

There may still be some wiggle room inside the system tablespace. However, when the wiggle room dwindles to the point that all 1023 undo logs inside the system tablespace are completely filled and can no longer extend, then you must add a new system tablespace file.

Please note that when I say wiggle room, I am referring to the free space within the system tablespace that must accommodate the following:

Data Dictionary
Double Write Buffer (can be disabled but not recommended)
Insert Buffer (Cached Index Changes in System Tablespace instead of the OS)
Rollback Segments (1023 slots)
Undo Logs (referenced from the Rollback Segments)
Please refer Back to the InnoDB Infrastructure Map

Q 2: Should InnoDB tables even be optimized (some say yes others say no)?

If you run OPTIMIZE TABLE, you basically make the data and index pages contiguous in the system tablespace. This defragments the table and accesses all data and indexes quicker until the fragmentation reappears over time in production use. This can introduce new areas of fragmentation. Again, all that fragmentation can fill up with data and indexes. This will endanger the wiggle room I mentioned before.

Q 3: How do you test for InnoDB fragmentation if the server does not use the “file per table” option?

Back on Aug 27, 2012, I answered this post : How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I explained there how to get the fragmentation. In essence, you do this:

Goto the OS and run

cd /var/lib/mysql
ls -l ibdata1 | awk '{print $5}'

This gets you the size of ibdata1 in bytes

SELECT (data_length+index_length) InnoDBDataIndexBytes
FROM information_schema.tables WHERE engine='InnoDB';

This gets you the sum total of data and index pages in bytes

Subtract the sum total from ibdata1's total bytes. The difference represents the wiggle room. This space causes fragmentation, but is constantly in use until ibdata1 gets filled.

CAVEAT : When innodb_file_per_table is enabled, I explain how to get the fragmentation of an individual table: Innodb table with many deletes and inserts - is there any disk space wasted?

Q 4: Is fragmentation the only reason to run “optimize table”?

Yes. It is far more beneficial for MyISAM and for InnoDB tables (innodb_file_per_table being enabled). Do this with innodb_file_per_table off and you will just make the system tablespace grow faster. See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

Q 5: If I do need to run “optimize table” on an InnoDB table should I run ALTER TABLE mydb.mytable ENGINE=InnoDB; and not ANALYZE TABLE

Running ALTER TABLE mydb.mytable ENGINE=InnoDB; would indeed shrink when innodb_file_per_table is enabled. Again, it is not worth when innodb_file_per_table is disabled.

Q 6: Can you selectively tell which innodb tables needs optimizing if the server does not use the "file per table" option?

No you cannot. Why? The INFORMATION_SCHEMA becomes totally useless because all the tables are inside one file. I wrote a script to find to uptime_time (last time an InnoDB table was written) of all InnoDB tables. Is there a way to find the least recently used tables in a schema? script only works for innodb_file_per_table. This shows that you cannot ascentain that fragmentation with ease. You could resort to more aggressive techniques like dumping the tablespace map and located segments with unused space : See this blog post : http://www.markleith.co.uk/2009/01/19/innodb-table-and-tablespace-monitors/. This is way too much firepower to deal with. You could just run the OPTIMIZE TABLE to eliminate segment fragmentation, but this brings us back full circle to getting everything out of ibdata1.

SUGGESTION

If you want to remove all data and index pages from ibdata1 and shrink ibdata1 permanently, please read my Oct 29, 2010 StackOverflow post Howto: Clean a mysql InnoDB storage engine?

As you can see, this subject is not new to me

Jan 13, 2013 : How to check growth of database in mysql?
Sep 23, 2012 : MySQL / InnoDB: Does daily optimization improve performance?
Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB

EPILOGUE

Running OPTIMIZE TABLE is not the biggest reason that ibdata1 grows quickly. Please see this post from mysqlperformanceblog and learn about the other contributing factors.

Please remember that most who run OPTIMIZE TABLE do so sequentially. You could probably script many of them in parallel. Of course, you need to convert to innodb_file_per_table like I mentioned before.