Understanding Informix self tuning
Informix automatically tunes itself in several areas.
AUTO_CKPTS enables the use of automatic
checkpoints, and it is enabled by default.
The database server automatically adjusts checkpoint frequency to avoid
transaction blocking. Recall from earlier in this tutorial that the checkpoints
triggered by physical and logical use can block processing. With
automatic checkpoints in effect, the server monitors physical and
logical log consumption, along with information about past checkpoint
performance. The server triggers checkpoints more frequently to avoid
transaction blocking. Informix added the following two tables to the
sysmaster database to keep the checkpoint data.
- The syscheckpoint table keeps history on the last 20 checkpoints.
- The sysckptinfo table tracks checkpoint activity.
Because the database server does not block transactions during checkpoint processing, LRU flushing should be relaxed. If the server is not able to complete checkpoint processing before the physical log is consumed (which causes transaction blocking), and if you cannot increase the size of the physical log, you can configure the server for more aggressive LRU flushing.
The increase in LRU flushing impacts transaction performance, but it should reduce transaction blocking. If you do not configure the server for more aggressive flushing, the server will automatically adjust LRU flushing to be more aggressive only when the server is unable to find a low priority buffer for page replacement.
You can disable automatic checkpoint tuning by running
onmode -wf AUTO_CKPTS=0 or by setting the
AUTO_CKPTS configuration parameter to
The LRU settings for flushing each buffer pool with interval checkpoints are not critical to checkpoint performance in V11. The LRU settings are necessary only for maintaining enough clean pages for page replacement, which makes LRU flushing less aggressive.
Starting in V11, the database server automatically tunes the LRU flushing whenever a
page replacement occurs. The new configuration parameter for this is
AUTO_LRU_TUNING configuration parameter
specifies whether automatic LRU tuning is enabled or disabled when the
AUTO_LRU_TUNING is enabled, after a checkpoint, if a page-replacement foreground write occurred
during the previous checkpoint interval, the database server decreases
the LRU settings by five percent. The server continues to decrease the LRU
flushing at each subsequent checkpoint until page-replacement
foreground writes stop or the
for a given buffer pool falls below 10 percent.
For example, if a page-replacement foreground write occurs and the LRU
settings for a buffer pool are 80 and 90, the database server adjusts
these 5 percent to 76 and 85.5.
LRU flushing is tuned more aggressively whenever a page fault replaces a hot page (high priority buffers) and non-high priority buffers are on the modified LRU queue. Automatic LRU adjustments only make LRU flushing more aggressive; they do not decrease LRU flushing.
LRU flushing is reset to the values contained in the
ONCONFIG file on which the database server
Automatic LRU tuning affects all buffer pools and adjusts
LRU_MAX_DIRTY values in the
BUFFERPOOL configuration parameter.
AUTO_LRU_TUNING is enabled by default in
the configuration file. You can dynamically enable or disable
automatic LRU tuning by using
onmode -wm or
Informix V11 introduces the configuration parameter
RTO_SERVER_RESTART, which configures the number of
seconds that the database server will try to finish recovery and come
back to online or quiescent mode. This parameter is useful when the
server needs to be back online within a given time after a crash.
Manual estimation of the recovery time is difficult, because the user
does not know the total I/O needed for recovery.
Recovery starts with reading data page before-images from the physical log into the buffer pool. Logical logs are then used to roll forward committed transactions and roll back uncommitted transactions. To fulfill the recovery time goal, Informix must manage the amount of I/O that will occur during recovery.
RTO_SERVER_RESTART is configured, the
server maintains current information on the physical and logical log
space used since the previous checkpoint plus the I/O speed available
to access these logs during fast recovery. The time to reboot the
server is also known. Using these numbers, the server determines the
checkpoint interval needed to maintain the restart policy.
RTO_SERVER_RESTART needs to be turned on
while the engine is online so that pages can be marked to be populated
in the buffer pool in fast recovery. Only after recycling, will the
benefit be realized.
RTO_SERVER_RESTART is enabled, the
database server does the following:
- Ensures that automatic checkpoints do not run out of critical resources (such as log space) and block by triggering more frequent checkpoints
- Ignores the configuration parameter
CKPTINTVLand sets a calculated checkpoint interval
- Automatically adjusts the number of AIO virtual processors and cleaner threads
- Automatically tunes LRU flushing to accommodate the increased number of checkpoints
Note that AIO virtual processors, cleaners, and LRU flushing are
automatically adjusted when
RTO_SERVER_RESTART is enabled, regardless
AUTO_LRU_TUNING is enabled.
Following are the disadvantages to enabling
- Storing pages for logical recovery increases physical log activity, which might impact transaction performance
- Increased checkpoint frequency, although the size of the physical log can be increased to alleviate this
At minimum, total log space needs to be only large enough to contain
all the transactions for two checkpoint cycles. In the situation where
RTO_SERVER_RESTART is enabled and the
server has a combined buffer pool size of less than 4GB, the total log
space recommended is 110% of the combined buffer pool sizes.
RAS_LLOG_SPEED, store the rate at which the
physical and logical log can be recovered during fast recovery and are
used for calculating the checkpoint interval for RTO policy.
RAS_PLOG_SPEED is initially set when the
physical log is initialized.
RAS_LLOG_SPEED is initialized to 1/8 of
Each time a fast recovery occurs, these values are updated to reflect what the real recovery speed is. The units are pages per second.
RAS_LLOG_SPEED in the
ONCONFIG file to enable DBAs who embed
Informix into their applications to provide pre-computed values so no
tuning is required to get optimal performance. DBAs should not need to
change these values unless directed by IBM Technical Support.
RTO_SERVER_RESTART is disabled.
The ranges of values allowed to configure this configuration parameter
are as follows:
RTO_SERVER_RESTARTis off, the value range is 0.
RTO_SERVER_RESTARTis on, the value range is 60-1800 pages per second.
You can change the
configuration parameter by modifying the configuration file manually
onmode -wf RTO_SERVER_RESTART.
The new configuration value of the
RTO_SERVER_RESTART takes effect when the
database server is stopped and restarted.
With Informix V11, fuzzy checkpoints have been deprecated. As a result,
the new checkpoint algorithm requires more physical log activity.
Applications configured for Informix V7 should notice little or no
change in the rate of physical logging activity. Applications that ran
on V9 might experience an increase in physical logging. Enabling
RTO_SERVER_RESTART also causes an increase
in physical logging activity.
A large physical log will not impact performance. A typical physical log size of 1GB to 4GB is usually sufficient.
This is a new feature in Informix 11.70. The database server on a
multiprocessor machine automatically adds CPU VPs if
SINGLE_CPU_VP is set to 0 and the
auto_tune_cpu_vps task in the ph_task table of sysadmin database is
enabled. The server adds up to half the number of physical processors
to a maximum (configured plus automatic) of eight. If you need more
than eight CPU VPs, they will have to be configured. The addition is
made at startup, and a message regarding dynamic CPU VP addition is
printed to the online.log, as shown in Listing 34.
Listing 34. Message about dynamic CPU VP addition
15:45:27 Dynamically added 3 cpu VPs
Starting with IBM Informix V11, you can use the
AUTO_AIOVPS configuration parameter to
enable automatic adjusting of the number of AIO VPS and flusher
threads when the server detects that AIO VPs are not keeping up with
the I/O workload.
AUTO_AIOVPS is enabled by default in the
configuration file. You can dynamically enable or disable the
automatic increase of AIO VPs and flusher threads by using
onmode -wm or