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

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).

performance checkpoint

asked 31 Oct '10, 12:16

Breck Carter
32.5k●539●724●1050
accept rate: 20%

edited 31 Oct '10, 20:14

Volker Barth
39.5k●355●539●811

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.

(31 Oct '10, 20:19) Volker Barth

flat view

One Answer:

active answers oldest answers newest answers popular answers

Remember, you asked for it...

Acquire the "checkpoint gate": ie, wait until a checkpoint can be performed (no backups in progress, etc) then prevent others from performing checkpoints.
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.
Suspend deferred growth. This will wait for any deferred growth of dbspaces to complete
Pre-flight for update of orphaned blob information
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
Display the "Starting checkpoint" message
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
Log the checkpoint_begin in the redo log
Flush the checkpoint log
Write all dirty pages from cache
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.
Log the checkpoint_end in the redo log
Display the "Finished Checkpoint" message
Truncate the redo log if using "-m"
Remove unreferenced entries from the identifier hash table
Purge any shared heaps (parsed proc definitions, etc) that are unreferenced but were held in cache because they had been referenced frequently
Coalesce the main heaps
Exit exclusive mode
Resume deferred growth feature
Release the checkpoint gate

permanent link

answered 02 Nov '10, 13:47

John Smirnios
11.4k●3●94●154
accept rate: 38%

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

(02 Nov '10, 14:41) Volker Barth

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.

(02 Nov '10, 21:13) John Smirnios

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

(28 Jul '11, 11:23) Jeff Albion

Replies hidden

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?

(28 Jul '11, 11:39) Volker Barth

That is correct.

(28 Jul '11, 13:49) John Smirnios

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

(28 Jul '11, 16:45) Volker Barth

showing 3 of 6 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

performance ×273
checkpoint ×12

question asked: 31 Oct '10, 12:16

question was seen: 2,967 times

last updated: 28 Jul '11, 16:45

SAP SQL Anywhere

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

Follow this question

Related questions