My friend & colleague, Russ Stocker, recently retired from IBM. Before he did, he wrote up these two great posts based on investigation he did on LOB performance. I'm very happy to be able to share them here.
Understanding LOB inlining
First, a brief description of LOB handling in DB2. DB2 stores most row data in fixed size (4k, 8k, 16k or 32k) pages. This data is referenced by queries via bufferpool access, i.e. it is read from disk into bufferpool memory to be worked on.
LOB (large object) data is handled differently. A LOB (CLOB - character large object, BLOB – binary large object, or DBCLOB - double byte large object) is stored outside regular row based storage. A LOB can be up to 2GB in size (1GB for a logged LOB). The necessity of handling large amounts of data of potentially very disparate sizes efficiently is what drives the LOB storage mechanism.
There is a descriptor in the regular row data that points to the actual LOB data itself. The size of this descriptor varies from 60 to 312 bytes, depending on the size defined for the LOB in the definition of the table. The actual size of the LOB data is also included in the descriptor. If a row's LOB is defined with an inline size, and if the entire LOB will fit, then it will be inlined as part of the row on the regular data page. If the LOB data is larger than the inline size, then none of the LOB will be inlined – i.e., DB2 does not inline only a part of the LOB. If the actual LOB size is no larger than the descriptor size, it will be implicitly inlined, even if no inlining is specified for the column.
Two more things to note: first, inlining is specified at a column level, so different LOB columns within one table can have different inlining behaviors. Second, when inlining is specified, the decision to inline (or not) happens individually for each row. Some may be inlined, and others not. As well, if a LOB value which is already inlined is updated to be longer than the maximum inline length, it will be moved to non-inlined LOB storage – and vice-versa, if the LOB’s size is decreased below the inline length. This all happens transparently within DB2.
Accessing data in the bufferpool is typically much quicker than accessing LOB data stored outside the row. The reason is that each LOB that is not inlined is read directly from disk, on each reference. Some memory buffering can be achieved by ensuring that the LOB data is stored in a tablespace with file system caching turned ON. However this technique leaves LOB data management with the file system, which may not be ideal from the database perspective.
Under the right circumstances, using inlining can provide large performance benefits. Using inlining unwisely could also introduce performance problems. We need to take the following factors into account when deciding which columns to inline.
- What proportion of the LOB column data will actually be inlined? Because of DB2’s “all-or-nothing” inlining behavior, we have to understand the data distribution of the LOB column. Knowing the distribution (average length, maximum length, minimum length and standard deviation) allows you to estimate what proportion of data will be inlined for that specific column. This information can be determined quite easily using standard SQL functions.
select count(*) from T where length(<lob_col>)+4 < <max desired inline len>
- A good rule of thumb would be that a column may be a good candidate for inlining, if 90% or more of its data will be inlined. Note that scanning very large tables to find LOB length statistics can be expensive, so if needed, the results can be sampled with TABLESAMPLE and still usually give meaningful results. The ‘maximum desired inline size’ can be as much as almost a whole page (technically, the maximum row size that fits on the page you’re using), or perhaps only a fraction – e.g. ¼ or ½ of a page. If a better proportion of rows can be inlined with a larger page size, then increasing the page size is worth doing. However, this may necessitate an overall review of bufferpool tuning.
- To what degree is the LOB data referenced and re-referenced? Inlining LOB data will increase the amount of data stored on the base data pages of the table. This could reduce the bufferpool efficiency if the inlined data is not regularly referenced. The problem here is that by inlining data, we are increasing the data footprint. If the inlined LOB data is not typically referenced by queries accessing the other row columns, then these queries may need to cover more pages of data to get a result. If this is the case, inlining is likely to slow these queries down.
- Data that is inlined into the base table data will be compressed, if table compression is enabled. If the LOB data compresses well, this will go a long way towards mitigating any bloating of the base table data that could cause adverse effects by gratuitous references to LOB data (select *, for instance). Generally, data compresses better if it contains text. Binary data (i.e. BLOB) is not likely to be a good candidate for compression. Many applications do custom compression of data before storing into LOB columns. This can make the data more inlineable, but will render it unsuitable for further compression in DB2
- Be wary of inlining columns where other columns in the table are frequently updated, particularly columns with a varying length (e.g. varchar). Often the effect of inlining a column will be to fill up a page of data, i.e. allowing only one row on the page. Inlining may cause a table to become ‘disorganized’ (i.e., creating overflows to other pages) more frequently than would otherwise be the case, and this would imply the need for more frequent reorganization
- Also be wary of the extended_row_sz functionality, which allows a row to be defined that is larger than the page size. When a table is created with a maximum row size that exceeds the page size, some of the data is stored outside the row, i.e. in a (transparent) LOB. If a LOB column is defined in the table and has an inline size, and the LOB data size allows inlining to be done, then other column data may be pushed outside the base data row into a transparent LOB, via the extended row size behavior. This can defeat the object of using inlining in the first place. This extended row size behavior is enabled by default for new tables from V10.5