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

2014-02-21 Thread veeresh kumar 
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  wrote:
 





On Thu, Feb 20, 2014 at 7:41 PM, veeresh kumar  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
d...@sqlite.org 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2014-02-20 Thread Simon Slavin 

On 21 Feb 2014, at 12:45am, Richard Hipp  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2014-02-20 Thread Richard Hipp 
On Thu, Feb 20, 2014 at 7:41 PM, veeresh kumar  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2014-02-20 Thread 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  wrote:
 





On Thu, Feb 20, 2014 at 7:08 PM, veeresh kumar  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
d...@sqlite.org 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2014-02-20 Thread Richard Hipp 
On Thu, Feb 20, 2014 at 7:08 PM, veeresh kumar  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users