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

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?

asked 15 Jul '14, 14:55

pmiller's gravatar image

pmiller
206162024
accept rate: 37%

edited 15 Jul '14, 15:21


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

permanent link

answered 15 Jul '14, 17:40

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 15 Jul '14, 17:45

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

permanent link

answered 16 Jul '14, 09:18

Ian%20McHardy's gravatar image

Ian McHardy
3.4k23557
accept rate: 40%

Your answer
toggle preview

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

Question tags:

×275
×119
×12
×10

question asked: 15 Jul '14, 14:55

question was seen: 9,028 times

last updated: 16 Jul '14, 09:18