IBM Informix Dynamic Server (IDS) uses memory (bufferpool) to cache application transaction updates (inserts/updates/deletes). This improves performance dramatically because the server does not need to flush transactions to stable storage (disk) upon commit. The updates are also logically recorded to stable storage (called the logical log or redo log) so that in the event of an unexpected outage, all transactions that have been committed can be restored. When an unexpected outage occurs, IDS accesses the redo log and performs the logical updates to the system in the order the transactions were originally updated to restore the system to a transactionally consistent state at the time the system was interrupted.
During normal operation, the transactional updates that are contained within the bufferpool must be flushed to disk periodically to allow IDS to recover from an unexpected outage in a timely manner. If updates are never flushed to disk from the bufferpool, IDS must recover the system starting with the first update ever done. By periodically flushing the updates contained in the bufferpool to disk, IDS can limit the amount of updates required to be replayed during fast recovery.
The points in time when IDS periodically flushes the bufferpool to disk are known as checkpoints. Previous versions of IDS support a blocking checkpoint algorithm that causes the system to block all transactional updates while the bufferpool is flushed to disk. With the introduction of IDS, Version 9, an additional checkpoint algorithm, called Fuzzy Checkpoint, was introduced. This was introduced as an attempt to limit the number of updates required to be flushed to disk during checkpoint processing. Unfortunately, the algorithm did not entirely alleviate the blocking of transactions during the flush of the bufferpool to disk. Furthermore, it introduced unpredictability to database recovery after an unexpected outage.
A major issue for many IDS DBAs is determining how to tune checkpoints. Applications that are sensitive to response time requirements tune LRU flushing to be very aggressive to limit the transactional blocking during a checkpoint. Applications that have a recovery time objective (RTO) policy try to control the frequency of checkpoints to limit the time IDS takes to recover from an unexpected outage. Applications that require both policies find that they work against each other. Too frequent checkpoints cause unwanted transaction blocking; checkpoints that are not frequent enough expose IDS to a potential long fast recovery time. Tuning a server to meet both policies can be frustrating, especially if the application workload is variable.
IDS, Version 11.10 introduces a new checkpoint algorithm that is virtually non-blocking. Transactional updates are blocked for a very short time (typically, a fraction of one second) while a checkpoint point is started. Then transactions are free to perform updates while the bufferpool containing all the transactional updates is flushed to disk. By not blocking during the bufferpool flush, LRU flushing can be less aggressive, which in turn provides more CPU to the application to handle transactions. Also, because checkpoints do not block transactional updates, checkpoints can be performed more frequently to help meet RTO policies.
IDS, Version 11.10 also introduces a new ONCONFIG file parameter, RTO_SERVER_RESTART. The name RTO_SERVER_RESTART stands for Recovery Time Objective Server Restart. This configuration parameter allows the DBA to specify a target amount of time, in seconds, that IDS can utilize during fast recovery to bring the server back online after an unexpected outage. When the RTO_SERVER_RESTART configuration parameter is defined, IDS will control the frequency of checkpoints, including automatically adjusting for variable workloads, to make sure IDS can meet the target fast recovery time.
As part of normal transactional updates (see Figure 2: Anatomy of a transaction), IDS transaction management uses the physical log to save before images of pages that are being updated and the logical log to record transactional updates. The physical log restore step of fast recovery restores the database to a physically consistent state prior to the log replay step. The log replay step applies log records to bring the database to a transactionally consistent state at the time of the unexpected outage.
A problem that can occur as part of logical replay is that each of the log records can generate an I/O. If that I/O requires a page to be read from disk, log replay performance can be dramatically reduced. Not knowing how many I/Os might occur during fast recovery can make fast recovery time unpredictable.
In order to make fast recovery more predictable, IDS must control the amount of I/O that will occur during fast recovery. When the RTO_SERVER_RESTART configuration parameter is configured to be used, IDS utilizes the physical log to save additional before images as part of transaction management with the intent that during fast recovery, no random I/Os will occur because all the pages required by log replay are first seeded into the bufferpool during physical log restore.
To be effective, the RTO_SERVER_RESTART configuration parameter must be turned on prior to IDS instantiation. Turning on RTO_SERVER_RESTART will not impact the current fast recovery; it will only impact the next fast recovery, should an unexpected outage occur.
For IDS Version 11.10, the PHYSFILE (physical log size) configuration parameter in the ONCONFIG file should be set to 110 percent of the combined size of all bufferpools. This is to enable fast recovery to utilize all the bufferpool resources for recovering the database as quickly as possible to maintain the RTO_SERVER_RESTART policy. While setting PHYSFILE to 110 percent of the combined size of all bufferpools is not a requirement for transaction correctness, it provides the optimum resources for peak fast recovery performance whether RTO_SERVER_RESTART is active or not.
If the system has a huge bufferpool where only a portion of the bufferpool is filled with updates, then a smaller physical log size will work well. Having a large physical log also will not impact performance. Typically, a physical log size of 1GB to 4GB is sufficient, depending on the transactional workload and speed of the disks.
The new checkpoint algorithm requires more physical log activity. This is because the Fuzzy Checkpoint algorithm has been deprecated. Applications configured for IDS, Version 7.x should notice little or no change in the rate of physical logging activity. Applications that are tuned for the Fuzzy Checkpoint algorithm, which was introduced in IDS, Version 9.x, might experience an increase in physical logging, depending on the type of transactions.
When a server is configured with RTO_SERVER_RESTART, there is even more physical logging activity. The increase in physical logging will generate more frequent checkpoints. This can be easily remedied by increasing the physical log size. A new feature for IDS, Version 11.10 is the ability to change the physical log size and/or location using onparams without requiring quiescent mode or a system reboot.
In a worse case scenario, as demonstrated by the TPCC benchmark, there was a dramatic increase in physical logging because TPCC transactions update mostly data records and avoid updating indexes. Once the physical log size was increased, there was a negligible decrease in transaction performance (less than five percent) because of the additional physical log activity. The non-blocking checkpoint algorithm and the removal of blocking during checkpoints more than compensated for this performance decrease with an overall net increase in transaction performance when checkpoints are included in the benchmark testing.
In previous versions of IDS, four conditions can trigger a checkpoint:
- Administrative events, for example, archives, adding a dbspace, booting and shutting down the server, using the onmode -c command, and so on
- A 75 percent full physical log
- One checkpoint in the logical log space (IDS cannot overwrite the logical log containing the current checkpoint, so IDS triggers a checkpoint prior to moving into that logical log)
- The CKPTINTVL configuration parameter in the ONCONFIG file
With the introduction of IDS, Version 11.10, a new feature is also introduced that allows IDS to automatically trigger checkpoints to either maintain RTO_SERVER_RESTART policy or maintain good checkpoint performance, or both, regardless of variation in transaction workload.
The CKPTINTVL configuration parameter, which specifies the frequency of checkpoints, enables the DBA to configure IDS to trigger a checkpoint at specific time intervals. This configuration parameter is the primary means of controlling fast recovery time; however, it does not take into consideration workload activity and therefore is very inflexible, often leading to unpredictable results.
When IDS is configured with the RTO_SERVER_RESTART configuration parameter turned on, IDS looks at past fast recovery performance along with current transaction activity and automatically triggers a checkpoint in order to maintain both the RTO_SERVER_RESTART policy and predictable transaction response times.
The RTO_SERVER_RESTART and CKPTINTVL configuration parameters are mutually exclusive; only one of these parameters can be active at a time. If the server is configured with RTO_SERVER_RESTART, CKPTINTVL is ignored.
During checkpoint processing, transactions continue to consume both physical and logical log resources. IDS does not allow transactions to overwrite the physical log (known as a physical log overflow) or logical log, so update transactions are blocked until the checkpoint completes. Future checkpoints will take into consideration past checkpoint performance and will trigger checkpoints more frequently to avoid transaction blocking. If checkpoints are triggered because the system is running out of physical or logical log resources, consider increasing those resources to reduce checkpoint frequency.
As mentioned above, IDS, Version 11.10 automatically triggers checkpoints to help maintain optimal performance. IDS also includes a few more automatic tuning features. As with most features, some customers might not want to take advantage of them. Therefore, IDS additionally has new ONCONFIG parameters to control the usage of these features.
You can set the AUTO_CKPTS configuration parameter in the ONCONFIG file to turn off automatic triggering of checkpoints by ignoring past checkpoint performance. This has the effect of returning the server to triggering checkpoints based only on the four events noted above in the Tuning checkpoints section.
You can also control automatic checkpoints using
–wf. Automatic checkpoints are on by default.
In previous versions of IDS, applications that were sensitive to transaction response time required aggressive LRU flushing to reduce the transaction blocking that occurred during a checkpoint. With IDS, Version 11.10, transactions are not blocked during checkpoint processing and therefore LRU flushing can be relaxed. LRU flushing needs only to deal with page replacement; that is, when a new page that is not in the bufferpool replaces a page that has not been accessed in awhile. If the server detects a condition where frequently accessed read-only pages are being replaced, the server will automatically make LRU flushing more aggressive to help maintain good performance.
A good starting point for setting the LRU flushing parameters is:
You can use the AUTO_LRU_TUNING configuration parameter to turn automatic LRU
flushing on or off. You can also control this (along with adjusting the LRU min and
max settings) using
In some situations, its not possible for checkpoint processing to flush the bufferpool to disk in a timely manner. This can occur because the amount of transaction activity creates so much data (so many dirty buffers) that the I/O subsystem simply cannot accommodate the flushing activity. This creates a situation where it takes longer to flush the bufferpool during checkpoint processing than the RTO_SERVER_RESTART policy. When this occurs, a performance advisory is generated.
Listing 1. Performance advisory - Bufferpool flushing
Performance advisory: The time to flush the bufferpool ## is longer than RTO_SERVER_RESTART ##. Results: The IDS server can't meet the RTO policy Action: Automatically adjusting LRU flushing to be more aggressive. Adjusting LRU for bufferpool - id ## size ##k Old max ## min ## New max ## min ##
If AUTO_LRU_TUNING is on, IDS will automatically adjust the LRU flushing to be 10% more aggressive. If AUTO_LRU_TUNING is off, the Action of message of the performance advisory is:
Action: Automatic LRU tuning is off. Either turn on automatic LRU tuning or change LRU flushing to be more aggressive.
By making LRU flushing more aggressive during normal processing, there will be less buffers to flush during checkpoint processing, allowing checkpoint processing to complete in a timely manner. Making LRU flushing more aggressive might impact transaction performance because some CPU cycles used for transactions will now be consumed by LRU flushing.
The use of cooked file chunks is becoming more prevalent. Configuring the server
with the correct number of AIO VPs can be difficult. IDS, Version 11.10 will
automatically monitor AIO VP performance. When IDS detects that there are not enough
AIO VPs, a new AIO VP will be added. IDS will also monitor the number of cleaner
threads and will add more cleaner threads as needed. You can turn this feature on or
off using the AUTO_AIO_VPS configuration parameter as well as
onmode –wm and
The RAS_PLOG_SPEED and RAS_LLOG_SPEED configuration parameters are used to store the rate at which the physical and logical log can be recovered during fast recovery. RAS_PLOG_SPEED is initially set when the physical log is initialized. RAS_LLOG_SPEED is initialized to 1/8 of the RAS_PLOG_SPEED. Each time a fast recovery occurs, IDS updates these values to reflect what the real recovery speed is. The units are pages per second.
Why ONCONFIG parameters?
Making these ONCONFIG parameters allows customers who embed IDS into their applications to provide pre-computed values, so no tuning is required to get optimal performance. DBAs should never change these values unless directed to do so by IBM Technical Support.
The following is a list of ONCONFIG file parameters that might require additional tuning with the introduction of IDS, Version 11.10:
The default value for the physical log buffer size should be 128KB. When the RTO_SERVER_RESTART configuration parameter is enabled, the default physical log buffer size is 512KB. If you decide to use a smaller value, IDS generates a message indicating that optimal performance might not be attained. Using a physical log buffer smaller than the default size will only impact performance, not transaction integrity.
The default value for the logical log buffer size is 64KB. If you decide to use a smaller value, a message will be generated indicating that optimal performance might not be attained. Using a logical log buffer smaller than 64KB will only impact performance, not transaction integrity. If the database or application is defined to use buffered logging, increasing the LOGBUFF size beyond 64KB will improve performance.
It is difficult to determine the amount of log space (LOGFILES * LOGSIZE) that is optimal because the amount depends on the application requirements and usage. The amount of log space really depends on a few factors:
- Amount of update activity. More update activity requires more log space.
- Recovery point objective (RPO). In the case of a catastrophic event, how much data loss can be tolerated? By doing more frequent log backups, you reduce your risk of transaction loss.
- Enterprise and HDR replication. Both of these replication services can influence the number and size of log files. If your system uses either or both of these replication services, please refer to the sections on sizing the logical log in your IDS user guides.
Here are a few guidelines:
- It is easier to manage fewer larger log files than to manage a lot of smaller log files. Having too much log space will not impact performance, but not having enough log files and log space can impact performance through the triggering of frequent checkpoints.
- Blobspace blobs are not logged, but they are included on the log backup in which the blob was created. This means that blobs are not freed until the log in which they were created is backed up. Therefore, if blobspace blobs are frequently updated, you might need more frequent log backups to acquire more free space within a blobspace.
- A good starting point for applications that generate a small amount of log data is 10 log files of 10MB each. A good starting point for applications that generate a large amount of log data is 10 log files with 100MB each.
- In the past, users would try to configure the size of a log file to meet the RPO
policy. During steady state, given a certain transaction rate, the log file would
fill and generate an automatic log backup. If there is no RPO policy that needs to be maintained, this method works well. But, if a RPO policy is required, a better method is to use the Scheduler, a tool that manages and executes scheduled maintenance, monitoring, and administration tasks (also introduced in IDS, Version 11.10) to insert a task that executes at the desired frequency to maintain the policy.
With the Scheduler, you can have IDS automatically back up log files at certain times within the daily cycle. If the log space fills before the logs are backed up and recycled, you can back up the logs, you can add an additional log file to allow transaction processing to continue, or you can add a task to detect this situation and perform either operation automatically.
Additional log files can always be added at any time, and the server will automatically add log files when required for transaction consistency, for example, with long transactions, which can consume large amounts of log space.
IDS, Version 11.10 attempts to look at the server configuration and transaction workload to determine if the system is optimally tuned. If IDS detects that the server is not configured optimally, it generates a performance advisory that is put into the message log (MSGPATH). Each warning message typically contains a suggested configuration change that will improve the server's performance.
Below are some examples of performance advisories and what they mean:
Listing 2. Performance advisory - Physical log too small for RTO_SERVER_RESTART
Performance advisory: The physical log size is smaller than the recommended size for a server configured with RTO_SERVER_RESTART. Results: Fast recovery performance may not be optimal. Action: For best fast recovery performance when RTO_SERVER_RESTART is enabled, increase the physical log size to at least ## Kb. For servers configured with a large bufferpool, this may not be necessary. Refer to the IBM Informix Administration Guide for more information.
Typically, the physical log should be 110 percent of the total size of the combined bufferpools. This is to make sure fast recovery performs little or no I/O during roll forward. The actual size that is needed could be quite a bit less than the recommended size, especially for systems configured with large bufferpools. This document contains some suggestions on sizing the physical log.
Listing 3. Performance advisory - IDS server boot time too long
Performance advisory: Boot Time ‘##’ is 50 percent more than RTO_SERVER_RESTART ## Results: Server initialization is taking a long time and IDS is unable to meet the RTO_SERVER_RESTART policy. Increase RTO_SERVER_RESTART to at least ## seconds. Action: Disabling RTO_SERVER_RESTART.
This assertion occurs when a normal restart of the server has occurred and, for whatever reason, it takes so long for the server to reboot that it is not possible to maintain the RTO_SERVER_RESTART policy.
Listing 4. Performance advisory - Physical log too small
Performance advisory: Physical log is running out of room. Results: Blocking transactions until checkpoint is complete. Action: Increase physical log size.
This assertion can occur when a checkpoint is being processed and the physical log is depleted to a critical point where further transactions would cause the physical log to overflow.
Listing 5. Performance advisory - Logical log too small
Performance advisory: Logical log is running out of room. Results: Blocking transactions until checkpoint is complete. Action: Increase logical log size.
This assertion can occur when a checkpoint is being processed and the logical log is depleted to a critical point where further transactions would cause the logical log to overflow.
Listing 6. Performance advisory - Long transactions blocking checkpoints
Performance advisory: Long transactions are triggering blocking checkpoints. Results: Blocking transactions until checkpoint is complete. Action: Increase logical log size.
Long transactions are triggering frequent checkpoints and transaction blocking must be done to protect critical logical log resources to maintain transactional consistency.
Listing 7. Performance advisory - Physical log too small to accommodate bufferpool flushing
Performance advisory: The physical log is too small to accommodate the time it takes to flush the bufferpool. Results: Transactions may block during checkpoints. Action: Increase the size of the physical to at least ## Kb.
IDS started checkpoint processing but cannot flush the bufferpool in time before transaction activity consumes the remaining 25 percent of the physical log. Increasing the size of the physical log size to the recommended value should help remedy this problem.
Listing 8. Performance advisory - Logical log too small to accommodate bufferpool flushing
Performance advisory: The logical log space is too small to accommodate the time it takes to flush the bufferpool. Results: Transactions may block during checkpoints. Action: Increase the size of the logical log space to at least ## Kb.
IDS started checkpoint processing but cannot flush the bufferpool before transaction activity consumes the remaining logical log resources. Increasing the size of the logical log space to the recommended value should help remedy this problem.
Listing 9. Performance advisory - Physical log too small to accommodate automatic checkpoints
Performance advisory: The physical log is too small for automatic checkpoints. Results: Automatic checkpoints are disabled. Action: Increase the physical log size to at least ## Kb.
If the server can generate physical log activity faster than automatic checkpoints can handle, you should turn off automatic checkpoints. If the physical log is less than 10MB (10000KB) or the server can generate physical log activity so fast that a checkpoint would be generated approximately every 35 seconds, then automatic checkpoints are turned off. This can often occur when IDS uses the default settings in the ONCONFIG.std file with no regard for tuning the server. Increasing the physical log size to the recommended value should remedy this problem.
Listing 10. Performance advisory - Logical log too small to accommodate automatic checkpoints
Performance advisory: The logical log space is too small for automatic checkpoints. Results: Automatic checkpoints are disabled. Action: Increase the logical log space to at least ## Kb.
If the server can generate logical log activity faster than automatic checkpoints can handle, you should turn off automatic checkpoints. If the logical log is less than 20MB (20000KB) or the server can generate logical log activity so fast that a checkpoint would be generated approximately every 35 seconds, the automatic checkpoints are turned off. This can often occur when the IDS server uses the default settings in the ONCONFIG.std file with no regard for tuning the server. Increasing the logical log space to the recommend value should remedy this problem.
With the introduction of IDS V11.1, the IDS server now keeps track of how checkpoints are performing. The checkpoint information can be retrieved from onstat or sysmaster.
For more information on monitoring checkpoints with onstat -ckp and sysmaster, see Tables 1 and 2.
In Figure 1, you see the secret to an IDS non-blocking checkpoint. For reference, the figure uses the log stream (logical log) to indicate the passage of time. From Figure 2, you know that each transaction inserts log records into the log stream. The log stream never goes back in time; it's always increasing.
Figure 1. Anatomy of a non-blocking checkpoint
In Figure 1, checkpoint interval 9 is the current interval. At some point, a checkpoint is requested and interval 10 begins. The start of interval 10 overlaps with the completion of interval 9 while interval 9 is processing the checkpoint. When all the pages that have been modified up to the start of interval 10 have been flushed to disk, then interval 9 ends. Once interval 9 ends, fast recovery can then start recovery from the beginning of interval 9.
Why is it called a non-blocking checkpoint?
Checkpoint processing does involve a transaction quiescent time to gather checkpoint information. The duration of this quiescent time is so small that, typically, most transactions never block for it. If they do block, the duration of the blocking period is very short. Figure 1 contrasts the blocking period of non-blocking checkpoints with the old checkpoint algorithm used in previous IDS versions.
Figure 2. Anatomy of a transaction
Before IDS V11.1, checkpoint performance administration was sometimes a frustrating tuning experience. DBAs have spent countless hours tweaking the ONCONFIG file to gain the best transaction performance. It will be difficult for some IDS DBAs to trust IDS to automatically tune for variable workloads. Hopefully, this article will help convince you that IDS has finally conquered this issue in the typical IDS fashion… with performance and simplicity.
Zone: Read articles and tutorials and connect to other resources to expand your Informix skills.
developerWorks Information Management
zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
Stay current with developerWorks
technical events and webcasts.
Technology bookstore: Browse for books on these and other technical topics.
Get products and technologies
Cheetah beta: Get a
free trial of "Cheetah," and participate in the next evolution of Informix
Dynamic Server: Download a free trial version of Informix Dynamic Server Enterprise
Build your next development project with
trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
Participate in developerWorks blogs and get involved in the developerWorks community.