The following configuration parameters affect checkpoints:
The CKPTINTVL configuration parameter specifies the maximum interval between fuzzy checkpoints. As your IBM Informix: Dynamic Server Administrator's Guide describes, the database server takes either a full checkpoint or a fuzzy checkpoint:
The database server writes a message to the message log to note the time that it completes a checkpoint. To read these messages, use onstat -m.
Checkpoints also occur whenever the physical log becomes 75 percent full. If you set CKPTINTVL to a long interval, you can use physical-log capacity to trigger checkpoints based on actual database activity instead of an arbitrary time unit. However, a long checkpoint interval can increase the time needed for recovery in the event of a failure. Depending on your throughput and data-availability requirements, you can choose an initial checkpoint interval of 5, 10, or 15 minutes, with the understanding that checkpoints might occur more often, depending on physical-logging activity.
The LOGSIZE and LOGFILES configuration parameters indirectly affect checkpoints because they specify the size and number of logical-log files. A checkpoint can occur when the database server detects that the next logical-log file to become current contains the most-recent checkpoint record. If you need to free the logical-log file that contains the last checkpoint, the database server must write a new checkpoint record to the current logical-log file. So if the frequency with which logical-log files are backed up and freed increases, the frequency at which checkpoints occur increases. Although checkpoints block user processing, they no longer last as long. Because other factors (such as the physical-log size) also determine the checkpoint frequency, this effect might not be significant.
When the dynamic log allocation feature is on, the size of the logical log does not affect the thresholds for long transactions as much as it did in previous versions of the database server. For details, see LTXHWM and LTXEHWM.
The LOGSIZE, LOGFILES, and LOGBUFF parameters also affect logging I/O activity and logical backups. For more information, see Configuration Parameters That Affect Logging.
The PHYSFILE configuration parameter specifies the size of the physical log. This parameter indirectly affects checkpoints because whenever the physical log becomes 75 percent full, a checkpoint occurs.
If your workload is update intensive and updates tend not to occur on the same pages, you can use the following formula to calculate the size of the physical log where PHYSFILE equals the physical-log size:
PHYSFILE = (users * max_log_pages_per_crit_sect * 4 * pagesize) / 1024
The last line of the onstat -u output contains the maximum number of concurrent user threads.
If you set the NETTYPE parameter, sum the values specified in the users field of each NETTYPE parameter in your ONCONFIG file.
Substitute one of the following values:
users * max_log_pages_per_crit_sec
The buffer size field in the last line of this output displays the page size.
This formula is based on how much physical logging space the database server needs in a worst-case scenario. This scenario takes place when a checkpoint occurs because the log becomes 75 percent full. If all the update threads are in a critical section and perform physical logging of the maximum number of pages in their critical section, the database server must fit this logging into the final 25 percent of the physical log to prevent a physical-log overflow.
The exception to this rule occurs if you are using simple large objects in a dbspace in a database without logging. In that case, substitute the size of the most-frequently occurring simple large object in the dbspace for the maximum log pages per critical section.
Also consider the following issues:
Operations that do not perform updates do not generate before-images. If the size of the database is growing, but applications rarely update the data, not much physical logging occurs, so you might not need a very big physical log.
Fuzzy checkpoints keep the physical log from filling up too quickly when applications are doing intensive updates. Fuzzy operations do not generate before-images and include inserts, updates, and deletes for rows that contain built-in data types. You can decrease the size of the physical log if you intend to use physical-log fullness to trigger checkpoints or when applications require less intensive updates or when updates tend to cluster within the same pages.
The database server writes the before-image of only the first nonfuzzy update made to a page. Nonfuzzy updates include the following operations:
Thus, you can define a smaller physical log in the following situations:
Because the physical log is emptied after each checkpoint, the physical log only needs to be large enough to hold before-images from changes between checkpoints. If your physical log frequently approaches full, you might consider decreasing the checkpoint interval, CKPTINTVL, so that checkpoints occur more frequently. However, decreasing the checkpoint interval beyond a certain point has an impact on performance.
If you increase the checkpoint interval or if you anticipate increased nonfuzzy update activity, you will probably want to increase the size of the physical log.
For information on PHYSFILE and ckptintvl, see the chapter on configuration parameters in the IBM Informix: Dynamic Server Administrator's Reference. For information on the physical log and fuzzy checkpoints, see your IBM Informix: Dynamic Server Administrator's Guide.
The ONDBSPDOWN configuration parameter specifies the response that the database server makes when an I/O error indicates that a dbspace is down. By default, the database server marks any dbspace that contains no critical data as down and continues processing. Critical data includes the root dbspace, the logical log, or the physical log. To restore access to that database, you must back up all logical logs and then perform a warm restore on the down dbspace.
The database server halts operation whenever a disabling I/O error occurs on a nonmirrored dbspace that contains critical data, regardless of the setting for ONDBSPDOWN. In such an event, you must perform a cold restore of the database server to resume normal database operations.
When ONDBSPDOWN is set to 2, the database server continues processing to the next checkpoint and then suspends processing of all update requests. The database server repeatedly retries the I/O request that produced the error until the dbspace is repaired and the request completes or the database server administrator intervenes. The administrator can use onmode -O to mark the dbspace down and continue processing while the dbspace remains unavailable or use onmode -k to halt the database server.
When you set ONDBSPDOWN to 1, the database server treats all dbspaces as though they were critical. Any nonmirrored dbspace that becomes disabled halts normal processing and requires a cold restore. The performance impact of halting and performing a cold restore when any dbspace goes down can be severe.