SAP Sybase IQ – checkpoints information, causes, diagnostics
A checkpoint marks a significant point in a transaction, when Sybase IQ writes to disk certain information it tracks internally.
IQ uses this information in the event you need to recover your database.
You can adjust the time between checkpoints when you start your server by changing the -gc and -gr options in the start_iq command or in the dbname.cfg configuration file.
The -gc switch specifies the number of minutes for the checkpoint timeout period. The -gr switch specifies the number of minutes for the maximum recovery time. The database engine uses both switches to calculate the checkpoint time.
Different causes of Checkpoints:
1) When a transaction issues a CHECKPOINT command.
2) When the CHECKPOINT_TIME is exceeded.
3) At the start and end of the backup process.
4) When the database server is shut down.
5) On secondary node TLV replay causes checkpoint. On a busy multiplex system this could lead to lot of checkpoints.
6) DDL statements perform implicit checkpoints.
7) Many DML options causes checkpoint, such as drop index, alter table, create table, drop table, create index.
8) Multiplex events could cause some checkpoints.
Following CR (Change Request) has been addressed recently related to checkpoints:
– CR# 672411 – (Title: IQ server logging checkpoint msg too frequently in srvlog, every 2 sec).
This was fixed in 15.4 esd5, 16.0 SP09 (and all later releases).
If you see frequent checkpoints or burst of checkpoints, providing -zr log, and .iqmsg from secondary and Coordinator is helpful to troubleshoot the issue.