checkpoint_time-Option / multi-programming-level [slow querys]

[Former Member]

We have a database server with high availability (2 Server, 1 Arbiter). The topic is about this two articles:

• Checkpoint-Time
• Multiprogramming-Level

The standard value of checkpoint_time-Option is 60 Minutes, so the database server have to write all transaction after 60 minutes in the database. (or earlier when there is no heavy load on the database)

We have observed that writing all the data from the transactionlog to database (making the checkpoint) takes up to four minutes. During the four minutes all queries (Select, Update, Insert) wait until all data are in the database. If we choose checkpoint_time-Option = 10 Minutes it takes only half a minute.

We monitor that with windows performance monitor (the half minute making the checkpoint). Between start und end of the checkpoint there are no requests to the database.

What's going on there, any ideas to solve that? Why is it not possible to get requests during the database is performing the checkpoint? Is it possible the multi-programming-level (standard 20 paralell threads) to little?

[VolkerBarth · Accepted solution]

that writing all the data from the transactionlog to database (making the checkpoint)

FWIW, to be precise, a checkpoint does not write data from the transaction log to the database file, it writes dirty pages from the database cache to the permanently stored database file (and removes the original pages from the checkpoint log, which is also a part of the database file) - cf. this description...

The following FAQ describes what happens during a CHECKPOINT - and tells what steps are really done in exclusive mode and therefore will delay current requests:

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

From that I would conclude that a higher multiprogramming level would not help here - it would just possibly increase the number of concurrent requests that would then have to wait for the CHECKPOINT to complete...

---

In contrast, if a smaller checkpoint_time value seems to improve the throughput, why don't you simply use it? (The default 60 minutes seem rather high for a HA system)...

[ian_mchardy]

Since you are using high-availability, I wanted to recommend that you consider updating to SQL Anywhere 16. There have been a large number of bug fixes and improvements made to high-availability over the past number of years that are not available in SQL Anywhere 10. Note SQL Anywhere 10 has been past "End of Engineering Support" for over two years, and before that it was in limited support. (Note that SQL Anywhere 12.0.1 has most but not all fixes that SQL Anywhere 16 has).