Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [checkpoint]

checkpoint is a database event. when checkpoint happens, all dirty blocks(pages) in the memory are written to database data files.

37 questions
18
votes
1 answer

Checkpoints are occurring too frequently during pg_restore

Under PostgreSQL 9.2.2 (Windows 32 bits), I have a pg_restore command that systematically results in log warnings about the checkpoint frequency, for example: LOG: checkpoints are occurring too frequently (17 seconds apart) HINT: Consider…
Sébastien Clément
  • 1,501
  • 3
  • 14
  • 26
11
votes
1 answer

"truncate log on checkpoint" option in SQL Server

Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB option named truncate log on checkpoint. Their claim…
user3885315
  • 113
  • 1
  • 1
  • 6
10
votes
2 answers

Increased waits during checkpoint after upgrading to better storage

When we migrated from an older all flash array, to a newer all flash array (different, but well established vendor), we started seeing increased waits in SQL Sentry during checkpoints. Version: SQL Server 2012 Sp4 On our old storage our waits…
Doley
  • 111
  • 5
9
votes
1 answer

What Happens to dirty pages if the system fails before the next checkpoint?

Assuming a database using full recovery model, when a record is written in SQL Server (by INSERT / UPDATE etc) write ahead logging will ensure the change is written to the log file before modifying the data page. Both the log and data page entries…
SEarle1986
  • 1,366
  • 2
  • 13
  • 38
6
votes
1 answer

How can I solve postgresql problem after deleting wal files?

I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. When I tried to restart postgresql I got the…
Sina
  • 85
  • 1
  • 2
  • 7
6
votes
2 answers

MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH

We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file. This never seemed to happen before. I understand that SLEEP_BPOOL_FLUSH is a normal…
apt605
  • 73
  • 1
  • 1
  • 4
5
votes
1 answer

Dirty buffer pages after issuing CHECKPOINT

I am currently working on a test system and due to the nature of the queries I want to optimise, I am trying to simulate a "cold" read a well as I can. Part of that is clearing the buffer cache before performing the queries. From everything I can…
Florian
  • 331
  • 1
  • 7
5
votes
1 answer

Checkpoints on secondary replica AlwaysOn AG

Setup 3 Node Alwayson cluster - 1 sync and 1 async secondary replica - SQL Server 2012 Situation We are witnessing PageIOLatches when reading from the asynchronous secondary replica. This is mostly caused by the throughput of the SAN that has…
Thomas Costers
  • 658
  • 3
  • 10
5
votes
2 answers

PostgreSQL checkpoint log explained

I know what PostgreSQL checkpoint is and when it is happening. I need some additional information about the logs produced by the log_checkpoints = on parameter, so please explain some points of it to me: 2017-09-09 16:31:37 EEST [6428-6524] LOG: …
inivanoff1
  • 153
  • 1
  • 3
5
votes
2 answers

Using CHECKPOINT vs GO

Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is too large) I recommend to them to do something…
Alf47
  • 931
  • 1
  • 8
  • 21
3
votes
3 answers

What does "thread" mean in "thread checkpoints" context?

The following excerpt is from Oracle concepts. Maybe this is a stupid question, but I really need your help to understand it. My question is: What's does the thread mean in the context thread checkpoints? Does it means redo log thread(all redo log…
Just a learner
  • 2,492
  • 5
  • 29
  • 46
3
votes
1 answer

Checkpoints and Logging

I'm working on getting full grasp of logging architecture. Please correct me if I'm wrong here: I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the disk. Disk here means transaction log file no…
Stackoverflowuser
  • 1,506
  • 2
  • 23
  • 40
3
votes
1 answer

High number of Checkpoint pages/sec and memory pressure

Recently I read a blog post on mssqltips.com about memory bottlenecks on SQL Server. In this article I read following: The following performance counters on SQL Server: Buffer Manager object can also indicate memory pressure: High number of…
Frederik Vanderhaegen
  • 1,950
  • 1
  • 11
  • 29
3
votes
1 answer

What happens when checkpoint_segments reached?

I'm a bit confused with checkpoint_segments definition in PostgreSQL documentation. What happens when checkpoint_segments reached? The dirty pages in shared_buffers written to WAL. Then what is the usage of background writer? WAL is merged to…
Majid Azimi
  • 1,851
  • 3
  • 21
  • 23
3
votes
1 answer

MongoDB responds slowly during the WiredTiger Checkpoint writing process

We have 3 instances of MongoDB 3.2.12 configured as a replica set. Journaling is enabled, the storage engine is WiredTiger. Most of the time our application produces a very low load on the database server and it works well, but rarely we launch a…
RomanG
  • 183
  • 6
1
2 3