Time taken to perform checkpoint operation and does it lock database during this operation?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Time taken to perform checkpoint operation and does it lock database during this operation?

veeresh kumar
Hi,
 I am using "PRAGMA
journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",with
default auto check point and page size = 1024 bytes.  Since checkpoint
occurs automatically after every 1 MB, how much time it checkpoint operation
would take to complete as the database size grows large (range 1GB -  50
GB). I understand that it depends on the hardrive, but on a very good configuration,
will this operation ever exceeds 10 sec?Also during this operation, does the
database gets locked?
Thank you-Veeresh
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time taken to perform checkpoint operation and does it lock database during this operation?

Richard Hipp-3
On Thu, Feb 20, 2014 at 7:08 PM, veeresh kumar <[hidden email]> wrote:

> Hi,
>  I am using "PRAGMA
> journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",with
> default auto check point and page size = 1024 bytes.  Since checkpoint
> occurs automatically after every 1 MB, how much time it checkpoint
> operation
> would take to complete as the database size grows large (range 1GB -  50
> GB). I understand that it depends on the hardrive, but on a very good
> configuration,
> will this operation ever exceeds 10 sec?Also during this operation, does
> the
> database gets locked?
>

The time needed for a checkpoint depends much more on the size of the WAL
file than on the size of the database.  For a 1MB WAL file on modern
hardware, I would think a checkpoint would require perhaps 50 to 100
milliseconds.  Have you done measurements to see how long it takes on your
system?

The database cannot be written while a checkpoint is underway.  But reads
can run concurrently with a checkpoint.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time taken to perform checkpoint operation and does it lock database during this operation?

veeresh kumar
Haven't measured the time, but I have seen a pause before
commit happens. As stated during my earlier discussion, my service cannot pause
for more than 10 sec as it would be result in time out for the clients that are
connected to this service.
 
What my understanding towards the checkpoint is taking my
below settings as example, WAL size would never grow beyond 1MB because check
point occurs after WAL file size reaches 1 MB. Please correct me if I am wrong.
 
Settings:
PRAGMA synchronous=NORMAL;",with default auto check
point and page size = 1024 bytes.



On Thursday, 20 February 2014 4:21 PM, Richard Hipp <[hidden email]> wrote:
 





On Thu, Feb 20, 2014 at 7:08 PM, veeresh kumar <[hidden email]> wrote:

Hi,
> I am using "PRAGMA
>journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",with
>default auto check point and page size = 1024 bytes.  Since checkpoint
>occurs automatically after every 1 MB, how much time it checkpoint operation
>would take to complete as the database size grows large (range 1GB -  50
>GB). I understand that it depends on the hardrive, but on a very good configuration,
>will this operation ever exceeds 10 sec?Also during this operation, does the
>database gets locked?
>

The time needed for a checkpoint depends much more on the size of the WAL file than on the size of the database.  For a 1MB WAL file on modern hardware, I would think a checkpoint would require perhaps 50 to 100 milliseconds.  Have you done measurements to see how long it takes on your system?


The database cannot be written while a checkpoint is underway.  But reads can run concurrently with a checkpoint.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time taken to perform checkpoint operation and does it lock database during this operation?

Richard Hipp-3
On Thu, Feb 20, 2014 at 7:41 PM, veeresh kumar <[hidden email]> wrote:

>  Haven't measured the time, but I have seen a pause before commit
> happens. As stated during my earlier discussion, my service cannot pause
> for more than 10 sec as it would be result in time out for the clients that
> are connected to this service.
>
> What my understanding towards the checkpoint is taking my below settings
> as example, WAL size would never grow beyond 1MB because check point occurs
> after WAL file size reaches 1 MB. Please correct me if I am wrong.
>
>

Long-running or overlapping readers can prevent the checkpoint from
occurring.  The checkpoint will be retried again and again, but if there is
always a read transaction open on a transaction other than the most recent
transaction, the the checkpoint will never have an opportunity to run to
completion and reset the WAL file.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time taken to perform checkpoint operation and does it lock database during this operation?

Simon Slavin-3

On 21 Feb 2014, at 12:45am, Richard Hipp <[hidden email]> wrote:

> Long-running or overlapping readers can prevent the checkpoint from
> occurring.  The checkpoint will be retried again and again, but if there is
> always a read transaction open on a transaction other than the most recent
> transaction, the the checkpoint will never have an opportunity to run to
> completion and reset the WAL file.

Suppose this happens and another process tried to write.  Will the write be held up until the checkpoint can complete, or will the expected WAL size be exceeded ?

Reading blocks checkpointing blocks writing ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Time taken to perform checkpoint operation and does it lock database during this operation?

veeresh kumar
In reply to this post by Richard Hipp-3
I guess this is what happening in my application. The WAL size has grown to 1 GB...But again my concern is why each commit is taking long time. I see a big pause before commit happens.


In a multi-threaded application, sqlite may have below limitations. Is this a fair statement?

- Response time to the client application would increase because
  only 1 thread would be able to update database.

- WAL size may grow since successful checkpoint operation
  is dependent on if any read operation are in a open
  transaction?

Is there any improvements or suggestions or best practices that are being followed for Multi threaded application.

Thank you,

-Veeresh






On Thursday, 20 February 2014 4:46 PM, Richard Hipp <[hidden email]> wrote:
 





On Thu, Feb 20, 2014 at 7:41 PM, veeresh kumar <[hidden email]> wrote:

Haven't measured the time, but I have seen a pause before
commit happens. As stated during my earlier discussion, my service cannot pause
for more than 10 sec as it would be result in time out for the clients that are
connected to this service.

>What my understanding towards the checkpoint is taking my
below settings as example, WAL size would never grow beyond 1MB because check
point occurs after WAL file size reaches 1 MB. Please correct me if I am wrong.
>


Long-running or overlapping readers can prevent the checkpoint from occurring.  The checkpoint will be retried again and again, but if there is always a read transaction open on a transaction other than the most recent transaction, the the checkpoint will never have an opportunity to run to completion and reset the WAL file.

 --
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users