Note
- For V4.3.x, this parameter is available starting with V4.3.2.
- For V4.2.x, this parameter is available starting with V4.2.3.
Description
lob_enable_block_cache_threshold specifies the threshold for caching LOB query results in OUTROW storage.
Privilege requirements
Query the parameter
You can use the
SHOW PARAMETERSstatement or theGV$OB_PARAMETERSview to query this parameter in thesystenant and all user tenants.Modify the parameter
You can modify this parameter in the
systenant and user tenants.
Attributes
| Attribute | Description |
|---|---|
| Type | CAP |
| Default value | 256K |
| Value range | [0B, 512M] |
| Modifiable | Yes. You can use the ALTER SYSTEM SET statement to modify the parameter. |
| Effective upon OBServer node restart | No. The setting takes effect immediately. |
Considerations
When reading LOB data stored in OUTROW storage, if the length of the current LOB data is less than or equal to lob_enable_block_cache_threshold, the microblock containing the LOB data will be cached during the query. This way, no disk I/O is triggered in the next query, thereby improving the query performance of hot data. This performance improvement is particularly significant for JSON and spatial data.
For example, the initial data is stored in the t table.
obclient> create table t(pk int primary key, c1 text);
obclient> insert into t values(1, repeat('1', 9000);
When reading the LOB data, if the lob_enable_block_cache_threshold parameter is set to 8096B, and the length of the LOB data to be read is 9000B, which is greater than the value of lob_enable_block_cache_threshold, the cache logic will not be triggered for this read. If the value is set to 9000B, the cache logic will be triggered during the next read. If the cached data is not evicted, no disk I/O will be triggered. The setting method is as follows:
obclient> ALTER SYSTEM SET lob_enable_block_cache_threshold='9000b';
Additionally, if the next query is for other LOB data in the same microblock, no disk I/O will be triggered, thereby improving the query performance of LOB data stored in OUTROW storage.
Examples
Set the length of the LOB query cache for OUTROW storage to 4K.
obclient> ALTER SYSTEM SET lob_enable_block_cache_threshold='4K';