What are all the bad things which happen when a CHECKPOINT occurs?

[Breck_Carter]

The V12 Help says "Side effects - None", and while that may be true from a semantic point of view, I know for a fact it is not true from a performance point of view.

Also, I believe the effects on performance have evolved from release to release, AND that these effects are probably difficult to describe.

But please, someone take a shot (V12 only).

Justification: There are reasons, possibly not great reasons but reasons nonetheless, that someone may wish to issue an explicit CHECKPOINT. For example, SELECT count FROM SYSTAB is much faster than SELECT COUNT(*) but the value is only up-to-date after a checkpoint... as are other interesting counters in the system tables.

Also, folks might want to know what can happen when an automatic checkpoint occurs... all in one place in the docs (say, where the CHECKPOINT statement is described).

[VolkerBarth]

Not as an answer, but as a connected topic: Cf. http://sqlanywhere-forum.sap.com/questions/683, dealing with the statements that issue an implicit checkpoint. That could be documented together, I guess.

[johnsmirnios · Accepted solution]

Remember, you asked for it...

1. Acquire the "checkpoint gate": ie, wait until a checkpoint can be performed (no backups in progress, etc) then prevent others from performing checkpoints.

2. Pre-flight for updates of system tables. Pre-flight means we reference all the pages we will need to update so that the pages are in cache and we won't need to wait from them to be read while running in exclusive mode.

3. Suspend deferred growth. This will wait for any deferred growth of dbspaces to complete

4. Pre-flight for update of orphaned blob information

5. Enter exclusive mode: no other tasks can be executing on the database after we enter exclusive mode. You may also have heard us call it "Forbid" mode -- named after an AmigaOS API which has a similar function

6. Display the "Starting checkpoint" message

7. Actually update system tables & orphaned blobs. System table updates include users (login failure attempts, last login time), tables (row counts, index info), sequences, and statistics

8. Log the checkpoint_begin in the redo log

9. Flush the checkpoint log

10. Write all dirty pages from cache

11. Update the checkpoint log to complete the checkpoint, update last checkpoint time, clear the "active" (aka "dirty") bits. After this step, the checkpoint has officially happened.

12. Log the checkpoint_end in the redo log

13. Display the "Finished Checkpoint" message

14. Truncate the redo log if using "-m"

15. Remove unreferenced entries from the identifier hash table

16. Purge any shared heaps (parsed proc definitions, etc) that are unreferenced but were held in cache because they had been referenced frequently

17. Coalesce the main heaps

18. Exit exclusive mode

19. Resume deferred growth feature

20. Release the checkpoint gate

[VolkerBarth · Accepted solution]

Point 7 includes updating max_identity values, right? (And I love the AmigaOS reference - has it ever been a supported platform?)

[johnsmirnios · Accepted solution]

Yes, point 7 includes updating the max_identity values. I'm pretty sure there was never an AmigaOS port of SQLAnywhere. That might be fun -- I wonder what state the tools are in 🙂 I was an Amiga programmer a long time ago and I still have my Amiga 1000 and 3000. I even had the A3000 running a few months ago and though it definitely felt foreign, it was surprising how quickly some of the AmigaDOS insanity came flooding back. Insanity such as "dir #?.c". Yeah, "#?" instead of "*". Strange, but it came back to me.

[jeff_albion · Accepted solution]

For 'Background checkpoints' (also known 'lazy', 'idle', 'background' or 'overlapping' checkpoints) there is an unlocking/locking step just prior to and after step "10" in the above article:

(9.5) Exit exclusive mode (10) Write all dirty pages from cache (10.5) Enter exclusive mode

[VolkerBarth · Accepted solution]

Who does trigger such background checkpoints - are these the automatic checkpoints the server issues when he thinks it's time to do so - in contrast to checkpoints done explicitly or implicitly by certain statements?

[johnsmirnios · Accepted solution]

That is correct.

[VolkerBarth · Accepted solution]

So it would mean automatic checkpoints allow tasks to do some work during the "Forbid" phase - by temporarily "unlock the gate"?