How can we speed up SQL Anywhere automatic recovery?
When a database is shut down, the database server performs a checkpoint so that information in the database is held in the database file. The process is called a clean shutdown. Each time that you start a database, the database server checks whether the last shutdown was clean or the result of a system failure. If the database did not shut down cleanly, then it automatically takes the following steps to recover from a system failure:
- Recover to the most recent checkpoint
- Apply changes made since the chekpoint
- Roll back uncommitted transactions
There is no way to speed up the automatic recovery process.
However, in SAP SQL Anywhere 17, performance enhancements have been introduced to improve performance during recovery ("parallel recovery").
The speed up of recovery depends on the following factors:
- The concurrency level of the workload: the greater the level of concurrency of the workload the greater recovery time is improved.
- The hardware resources that are available on the machine performing the recovery: the more CPU cores and disk I/O spindles available, the less time required for recovery.
- The mix of DDL and DML operations in the transaction log: DML operations are better candidates to benefit from parallel recovery than DDL operations, which must be serialized, adding to the time it takes to recover the database.
- Existence of primary keys: operations on tables with primary keys greatly speed up the recovery process
These database recovery improvements affect the following features:
Database mirroring: The mirror server can now more quickly catch up to changes happening on the primary server because log operations are applied in parallel instead of sequentially.
Normal database recovery: Normal database recovery is improved. In addition, applying log operations using the server switch –a is also improved.
SQL Anywhere On-Demand Edition: The SQL Anywhere On-Demand Edition uses database mirroring to propagate changes in the cloud infrastructure. Wait time on the primary server is reduced with these changes, which means better response times across the cloud infrastructure.