MySQL 8.0: New Storage Format for Compressed BLOBs

In this article, let us look at the new storage format of compressed large objects (compressed LOB or ZLOB).  In my previous article MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates, I have explained about the new storage format of uncompressed LOBs.  The approach is similar but the details are different.  But the reason for changing the storage format is the same – to provide the partial update feature for JSON documents.   And the ultimate goal for this feature was to improve the performance of JSON updates, by reducing the amount of redo log generated.  The partial update feature is currently available only for JSON documents, but it can be made available to other data types like TEXT, in a future release. (Note: It is a technical feasibility that I am talking about here rather than the product road map.)

Previous Storage Format – Single zlib Stream

Previously we stored a JSON document (or any other ZLOB) as a single zlib stream in a sequence of LOB pages.  The information about the first page of this sequence was stored in the LOB reference which is part of the clustered index record.  There were two types of LOB pages used namely FIL_PAGE_TYPE_ZBLOB and FIL_PAGE_TYPE_ZBLOB2.  The page type FIL_PAGE_TYPE_ZBLOB is used for the first page of the compressed BLOB and the page type FIL_PAGE_TYPE_ZBLOB2 is used for  subsequence pages of the compressed BLOB.  The following diagram illustrates this overall structure.

Compressed LOB – single zlib stream storage format

To do any operation on this compressed LOB the zlib stream must be fully uncompressed.  So this storage format is not suitable for doing partial updates on the data.  That is why there was a need to change this storage format to provide the partial update of JSON document feature.

Multiple zlib Stream Storage Format

In the new storage format, we store one LOB as a series of zlib streams.  Each uncompressed LOB is divided into chunks of size Z_CHUNK_SIZE (currently it is 128KB), and each of those chunks is separately compressed into an zlib stream.   As before, each zlib stream is stored in a sequence of database pages.  And information about the first page of each zlib stream is stored in a new structure called the ZLOB index (which is equivalent to the LOB index previously discussed.)  The LOB reference stored in the clustered index record now points to the beginning of this ZLOB index.

In the above figure, three zlib streams are shown in the box named “ZLOB Data Pages”.  Meta data about each of these three zlib streams are stored as index entries in the ZLOB index.  The ZLOB first page is a special type of page that contains both ZLOB index entries as well as ZLOB compressed data.  As shown in the diagram, the LOB reference points to this ZLOB first page number.

The New Page Types

The compressed BLOB is now stored in new page types.  They are listed below.

Page TypeDescription
FIL_PAGE_TYPE_ZLOB_FIRSTThe first page of the compressed BLOB. This is special because it can contain both ZLOB index entries as well as ZLOB zlib stream data. This page number will be stored in the LOB reference of the clustered index record.
FIL_PAGE_TYPE_ZLOB_DATAThese contain the compressed BLOB data.
FIL_PAGE_TYPE_ZLOB_INDEXThe ZLOB index pages. These pages contain a list of index entries.
FIL_PAGE_TYPE_ZLOB_FRAGFragment pages. They contain small chunks of compressed LOB from different zlib streams. This is just an optimization to save space.
FIL_PAGE_TYPE_ZLOB_FRAG_ENTRYContains list of fragment entries.

Fragment Pages

The ZLOB page types FIL_PAGE_TYPE_ZLOB_FIRST, FIL_PAGE_TYPE_ZLOB_DATA and FIL_PAGE_TYPE_ZLOB_INDEX are equivalent to their uncompressed LOB page type counterparts FIL_PAGE_TYPE_LOB_FIRST, FIL_PAGE_TYPE_LOB_DATA and FIL_PAGE_TYPE_LOB_INDEX.  But compressed LOBs have two more page types FIL_PAGE_TYPE_ZLOB_FRAG and FIL_PAGE_TYPE_ZLOB_FRAG_ENTRY.  Their purpose is explained in this section.

As mentioned earlier each BLOB is stored as multiple zlib streams.  Each zlib stream is stored in a series of pages of type FIL_PAGE_TYPE_ZLOB_DATA.   The last page in this series most probably will not be fully utilized.  The best case is that the compressed zlib stream utilizes the last page fully.  But the worst case is that the last page contains only one byte!  Since one BLOB is stored as multiple zlib streams, each zlib stream can waste lot of space in their respective last pages.  The fragment pages are introduced as a space saving mechanism.

A fragment page can contain small fragments from different zlib streams all belonging to the same compressed BLOB.

That is the high level use of the fragment pages.  It will need a separate article to discuss the details of these fragment pages.

Just like FIL_PAGE_TYPE_ZLOB_INDEX pages hold meta data information about FIL_PAGE_TYPE_ZLOB_DATA pages, we have FIL_PAGE_TYPE_ZLOB_FRAG_ENTRY pages containing meta data about FIL_PAGE_TYPE_ZLOB_FRAG pages.  This explains the need for 2 extra page types for compressed BLOBs.

ZLOB First Page

The first page of a compressed BLOB has type FIL_PAGE_TYPE_ZLOB_FIRST.  The LOB reference stored in the clustered index record contains the page number of this first page.  The first page is special in the sense that it stores all the three kinds of information:

  • some portion is used for storing compressed data (the kind of data stored in FIL_PAGE_TYPE_ZLOB_DATA pages),
  • some portion is used for storing index data (the kind of data stored in FIL_PAGE_TYPE_ZLOB_INDEX) and
  • some portion is used for storing meta data about fragment pages (the kind of data stored in FIL_PAGE_TYPE_ZLOB_FRAG_ENTRY).

You can refer to the class z_first_page_t to learn about the implementation details.

Partial Update Performance

The storage format of compressed BLOB was changed to provide the partial update of JSON document (stored with ROW_FORMAT=compressed tables) feature.  And the purpose of the partial update feature is to improve the performance of update operations.  The update performance of JSON documents are compared between MySQL 5.7 and 8.0 versions.  The results are presented here.

ThreadsMySQL 5.7 TPSMySQL 8.0 TPSPercentage Improvement
81522.8271566.9872.90%
161920.4632383.65324.12%
641702.0532668.87056.80%
1282004.4532719.74035.68%
5121997.3602647.35032.54%

The JSON document size used was 512KB.  The update operation was done in such a way that the partial update feature is exercised in MySQL 8.0.  The performance gain is because of this new feature of MySQL 8.0. The above tabular performance data  is graphically presented below. The black line is the update performance in MySQL 5.7 and the red line is the update performance (with partial update feature) in MySQL 8.0 version.

Conclusion

The purpose of this article is to give a high level overview of the storage format of the compressed LOB in InnoDB.  A short introduction to the previous format was presented followed by a more detailed introduction to the new format.  The new page types were listed and each of them were discussed to clarify their purpose.  There was also an explanation as to why compressed LOB has more page types than the uncompressed LOB.

Hope this high level picture will help you to dive into the details at source code level!  If you found this article useful, kindly leave a comment.

Thank you for using MySQL !

2 thoughts on “MySQL 8.0: New Storage Format for Compressed BLOBs

Leave a Reply