11.8.3 Setting suppression of LRU management of binary data accessed by UAPs
When you execute a UAP that accesses a great deal of large-sized binary data, caching the binary data in the global buffer pushes out of memory whatever was most recently cached in the global buffer, so performance might decline temporarily. If the binary data is not accessed frequently, performance declines can be avoided at this time by suppressing LRU management of the branch row page of the binary data.
Note that this setting is valid for BINARY type binary data. It is not valid for BLOB type binary data.
- Reference note
- LRU management can only be suppressed for accesses from UAPs. Accesses from commands and utilities are managed by LRU. However, the following commands suppress LRU management.
- Commands provided by plug-ins
- pddbst
You can suppress LRU management by specifying the -b option.
- Note also that for the following commands, LRU management cannot be suppressed, but you can avoid pushing from memory the most recently cached data in the global buffer.
- pdload, pdrorg, pdrbal
Pushing of base row data out of the global buffer can be avoided by specifying the -n option and using a local buffer.
- pdpgbfon
When the -b option is omitted, the branch row page of the binary data is not accessed.
- Organization of this subsection
- (1) Application criteria
- (2) Effect of application
- (3) Specification method
- (4) Notes
(1) Application criteria
We recommend using suppression of LRU management of binary data accessed by UAPs when both of the following conditions are met.
- There are tables that include large binary data such as BINARY type, abstract data types that include BINARY type attributes, or XML type.
- Access of binary data is rare.
- Note
- Do not apply this setting if you access binary data frequently.
(2) Effect of application
The branch row that stores binary data is cached in the global buffer as the least recently used page regardless of access frequency. For this reason, the branch row page is pushed from memory before the base row page, meaning that data in the base row page is not pushed from memory.
(3) Specification method
Specify N for the system common definition operand pd_dbbuff_binary_data_lru.
(4) Notes
- When LRU management is suppressed, branch row pages of binary data that UAPs access are pushed from memory when the buffer runs short, regardless of access frequency. For this reason, UAPs that access binary data branch row pages might experience degraded response performance as well due to the increase in the number of I/Os that results from a lower buffer hit rate.
- UAPs secure one to four sectors of buffer simultaneously. For this reason, one to four of the pages cached in the global buffer might be pushed out of the cache for each UAP, even when suppression of LRU management has been specified.
- When LRU management is suppressed, there are numerous writes to databases and log output events when UAPs are executed that update binary data branch row pages. For this reason, the amount of log output will be greater than if LRU management is not suppressed. Do the following to make sure that capacity does not run short.
- Re-calculate the size of the system log file.
- If you are executing in no-log mode, specify NO for client environment definition PDDBLOG.
The log size when LRU management is suppressed can be determined using the following formula. If 1024 is specified as the pd_log_rec_leng operand, the log output size when LRU management is suppressed will be minimized.
number of update GETs of UAPs that access binary data# x pd_log_rec_leng operand value |
- #
- The number of update GETs can be confirmed using the DIDUC value in the UAP statistical report or the DIDUC value in the statistical information pertaining to UAPs.
- When a rollback occurs, LRU management might be used even if the UAP suppresses LRU management. This differs depending on the timing of the rollback, as shown below.
Rollback timing | Is LRU management used? |
---|
Timing is defined by SQL | There is a rollback to the commit-time immediately prior to execution of a specified control SQL ROLLBACK statement. | LRU management is suppressed. |
Rollback is executed automatically by HiRDB | Processing cannot continue at the time SQL is executed, and an implicit rollback to the immediately prior commit-time is executed by HiRDB. | LRU management is suppressed. |
The UAP terminates abnormally and HiRDB rolls back to the immediately prior commit-time. | LRU management is used. |
Rollback is executed by a delayed rerun. | LRU management is used. |
- When this facility is applied, MIX is unconditionally assumed for the pd_dbbuff_lru_option operand. For this reason, the reference and update buffers cannot be managed by independent LRUs.