Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Adam Jensen]

On 08/26/2016 01:20 PM, David Raymond wrote:
[snip]
> Many do not persist after closing your connection, including synchronous and 
> foreign_keys that you mentioned. In fact, I'd say that the number that do 
> persist is pretty small, and those that do usually require a vacuum or such 
> after being issued so that a new value can get put in the header, or the file 
> is reorganized, etc.
[snip]

Wow, thanks! That's a very relevant detail that doesn't seem to be
documented anywhere and I probably would have continued with the
misunderstanding until something went very wrong (or worse,
incomprehensible system behavior led to frustration and eventual
abandonment).

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[David Raymond]

Pragmas are listed out here: http://www.sqlite.org/pragma.html

Many do not persist after closing your connection, including synchronous and 
foreign_keys that you mentioned. In fact, I'd say that the number that do 
persist is pretty small, and those that do usually require a vacuum or such 
after being issued so that a new value can get put in the header, or the file 
is reorganized, etc.

Another way of looking at it is to look at what's in the header here: 
http://www.sqlite.org/fileformat2.html If it doesn't have a spot in the header 
then there's no way to save the change for subsequent connections.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Adam Jensen
Sent: Friday, August 26, 2016 12:55 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 08/25/2016 04:41 PM, Adam Jensen wrote:
...
And, as far as I can tell, all of these PRAGMA's seem to persist - they
do not need to be reasserted in any way during subsequent database
accesses.

Do all PRAGMA's, once set to a specific value, stay set? Does anyone
have links to the related documentation?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Adam Jensen]

On 08/25/2016 04:41 PM, Adam Jensen wrote:
> On 08/20/2016 01:01 PM, Simon Slavin wrote:
>> On 20 Aug 2016, at 5:56pm, Lev  wrote:
> [snip]
>>> So this 'setting' is stored in the database file? Is it enough to do the
>>> PRAGMA when the database is created?
>>
>> Yes and yes, but do it this way.
>>
>> 1) Create the database file by opening it.
>> 2) Do something that makes the file non-blank, like creating a table.
>> 3) Issue "PRAGMA journal_mode=WAL"
>> 4) Close the file
>>
>> From that point onwards that database is in WAL mode and everything opening 
>> it automatically knows that.  You do the above sequence using your own 
>> software or the SQLite command-line shell tool.
> 
> 
> When in WAL mode, when would `PRAGMA synchronous = NORMAL` be issued and
> does it persist?

During database creation, this order seems to work:

 PRAGMA page_size = 2048;
 PRAGMA synchronous = NORMAL;
 PRAGMA foreign_keys = ON;

 CREATE TABLE ...;
 CREATE TABLE ...;

 PRAGMA journal_mode = WAL;

.quit

# https://www.sqlite.org/wal.html
# https://www.sqlite.org/pragma.html#pragma_wal_checkpoint

And, as far as I can tell, all of these PRAGMA's seem to persist - they
do not need to be reasserted in any way during subsequent database
accesses.

Do all PRAGMA's, once set to a specific value, stay set? Does anyone
have links to the related documentation?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Adam Jensen]

On 08/20/2016 01:01 PM, Simon Slavin wrote:
> On 20 Aug 2016, at 5:56pm, Lev  wrote:
[snip]
>> So this 'setting' is stored in the database file? Is it enough to do the
>> PRAGMA when the database is created?
> 
> Yes and yes, but do it this way.
> 
> 1) Create the database file by opening it.
> 2) Do something that makes the file non-blank, like creating a table.
> 3) Issue "PRAGMA journal_mode=WAL"
> 4) Close the file
> 
> From that point onwards that database is in WAL mode and everything opening 
> it automatically knows that.  You do the above sequence using your own 
> software or the SQLite command-line shell tool.


When in WAL mode, when would `PRAGMA synchronous = NORMAL` be issued and
does it persist?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Kees Nuyt]

One minor optional addition below

On Sat, 20 Aug 2016 18:01:46 +0100, Simon Slavin
 wrote:

>
>On 20 Aug 2016, at 5:56pm, Lev  wrote:
[...] 
>> So this 'setting' is stored in the database file? Is it enough to do the
>> PRAGMA when the database is created?
>
> Yes and yes, but do it this way.
>
> 1) Create the database file by opening it.

1a) If you need it, before creating any tables:
PRAGMA page_size=;

> 2) Do something that makes the file non-blank,
>like creating a table.
> 3) Issue "PRAGMA journal_mode=WAL"
> 4) Close the file
>
> From that point onwards that database is in WAL mode
> and everything opening it automatically knows that. 
> You do the above sequence using your own software
> or the SQLite command-line shell tool.
>
> Simon.

-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Lev]

On Sat, 20 Aug 2016 18:01:46 +0100
Simon Slavin  wrote:

> Yes and yes, but do it this way.
> 
> 1) Create the database file by opening it.
> 2) Do something that makes the file non-blank, like creating a table.
> 3) Issue "PRAGMA journal_mode=WAL"
> 4) Close the file
> 
> From that point onwards that database is in WAL mode and everything
> opening it automatically knows that.  You do the above sequence using
> your own software or the SQLite command-line shell tool.

Thanks!
Levente

-- 
73 de HA5OGL
Op.: Levente
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Simon Slavin]

On 20 Aug 2016, at 5:56pm, Lev  wrote:

> I read in the documentation:
> 
> The WAL journaling mode uses a write-ahead log instead of a rollback journal
> to implement transactions. The WAL journaling mode is persistent; after being
> set it stays in effect across multiple database connections and after closing
> and reopening the database. A database in WAL journaling mode can only be
> accessed by SQLite version 3.7.0 or later.
> 
> So this 'setting' is stored in the database file? Is it enough to do the
> PRAGMA when the database is created?

Yes and yes, but do it this way.

1) Create the database file by opening it.
2) Do something that makes the file non-blank, like creating a table.
3) Issue "PRAGMA journal_mode=WAL"
4) Close the file

From that point onwards that database is in WAL mode and everything opening it 
automatically knows that.  You do the above sequence using your own software or 
the SQLite command-line shell tool.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Lev]

On Sat, 6 Aug 2016 17:03:30 -0400
Richard Hipp  wrote:

> Doing "PRAGMA journal_mode=WAL;" on your database (just once, perhaps
> from a command-line shell) will fix this for you.

I read in the documentation:

The WAL journaling mode uses a write-ahead log instead of a rollback journal
to implement transactions. The WAL journaling mode is persistent; after being
set it stays in effect across multiple database connections and after closing
and reopening the database. A database in WAL journaling mode can only be
accessed by SQLite version 3.7.0 or later.

So this 'setting' is stored in the database file? Is it enough to do the
PRAGMA when the database is created?


Thanks,
Levente

-- 
73 de HA5OGL
Op.: Levente
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Jean-Christophe Deschamps]

Rob,

At 18:27 07/08/2016, you wrote:

Too little sleep and far too much coffee.


I was in the same situation, multiplying by 2 instead of dividing, as 
Ryan pointed out.


Nice to see that WAL fits your use case. I for one found it rock solid 
and very useful.


--
Jean-Christophe 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Rob Willett]

Jean-Christophe

Thanks for the update on wal-mode. Your explanation is clear and makes 
sense to us. We can see what we would have a 224MB -wal file, we 
experimented with killing processes whilst updating and generally 
messing around and SQLite did what is was supposed to do. I wouldn’t 
say we were exhaustively testing it and to be honest, I know we can’t 
push SQLite to its limits with the little things we use it for.


We did understand the differences in 3.11.10 and 3.8 re the size of the 
-wal mode, its just that I communicated it poorly. Too little sleep and 
far too much coffee.


We are going to do some more tests, more about familiarising ourselves 
with WAL rather than expecting it to break to be honest. WAL seems to 
work well enough for us and assuming our last conversion tests work OK, 
we’ll shine it in tomorrow night when we get some downtime.


Thanks for you help and elegant description

Rob

On 7 Aug 2016, at 9:59, Jean-Christophe Deschamps wrote:


Rob,

At 08:55 07/08/2016, you wrote:
We think that using WAL mode works for us, indeed inspection seems to 
indicate it does, but the size of the -wal file appears to be far 
larger than would be expected. Is there a problem here? It doesn't 
appear to be a problem but would welcome any comments.


After reading your post I'd like to clear up a few points about WAL 
mode.


We can also see that the main sqlite database is NOT updated (or at 
least the timestamp isn't) whilst we are running the updates in WAL 
mode. This appears to be correct as the updates would be in the -wal 
file.


The WAL mode is persistant and consistant. That means that once 
successfully put in his mode the DB itself will remain in WAL mode for 
every (new) connection. Thus your updates and the long-running query 
are both running under WAL mode. That is, provided the WAL mode was 
set prior to the start of the long-running query, but that detail 
doesn't matter for reads in this case.


It doesn't matter whether your query is a single query statement 
(hence in auto-commit mode) or a huge transaction extracting and 
massaging data in multiple temp tables and myriads of read/write 
statements, all inside an explicit transaction), ACID properties 
guarantee that once your query is started, it will see the DB in the 
state prior to any updates that could occur during its run. Else you 
would obtain potentially dangerously inconsistant data of course.


We have not set the journal_size_limit and we have a -wal file which 
is 224MB in size, somewhat larger than 4MB. We are running


3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d

which does not appear to have the code in 3.11.0 so that the WAL file 
is proportional to the size of the transaction. From the same page of 
the manual:


```
Very large write transactions.

A checkpoint can only complete when no other transactions are 
running, which
means the WAL file cannot be reset in the middle of a write 
transaction. So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed 
once the write transaction completes
(assuming there are no other readers blocking it) but in the 
meantime, the file can grow very big.


As of SQLite version 3.11.0, the WAL file for a single transaction 
should be proportional in size to
the transaction itself. Pages that are changed by the transaction 
should only be written into the WAL
file once. However, with older versions of SQLite, the same page 
might be written into the WAL file multiple

times if the transaction grows larger than the page cache.
```


Reread the quoted part again: only with SQLite versions 3.11.0 and 
above will a given page be written only once in the WAL file when 
initiated within a given transaction.


Since you're running a prior version, it's pretty logical to expect 
that your updates will cause writes of multiple distinct copies of the 
same pages in the WAL file. You should update your SQLite version to 
see a possible improvement there. That, or refer only to the old 3.8.2 
documentation, but this is an inferior option because there have been 
significant improvements meanwhile.


Also the ratio in the help file (1000 pages or about 4Mb) applies to 
the default page size (4Kb).


Finally, while the long-running query is running, no checkpoint can 
run to completion. Doc states under "Checkpoint starvation.":


However, if a database has many concurrent overlapping readers and 
there is always at least one active reader, then no checkpoints will 
be able to complete and hence the WAL file will grow without bound.


Since you clearly can't introduce a read-gap inside your read query, 
the .wal file will grow as large as it needs until completion of the 
query. You mentionned that you tested with much more frequent updates 
than the real-world case (120x actually), so .wal file size shouldn't 
be an issue in your actual use case.


HTH

--
Jean-Christophe

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Rob Willett]

Ryan,

Thanks for the update.

We have done a few more tests during the day and not had any issues to 
date. This is still on a test version but we are getting a warm, cuddly 
feeling about using WAL mode.


The -wal file grows as you describe and you have explained it very well. 
We were groping in the twilight to get to where we wanted to go, your 
explanation brought a bright beacon of light onto the proceedings. (I 
have been watching too many speeches from the various US political 
conventions in the US though I am British).


We will investigate changing the page size. We would need to work out 
the row size.


I will note in future your OCD and ensure that I am accurate in 
reporting numbers rather than have self inflicted rounding errors, 60x 
is a nicer number than 50x as it maps to mins and secs more easily :)


Thanks again for the help.

Rob

On 7 Aug 2016, at 12:11, R Smith wrote:


On 2016/08/07 8:55 AM, Rob Willett wrote:

Richard, Ryan,

Thanks for this. We were dimly aware of WAL but until now hadn’t 
needed to use it.


We’ve done a quick check with it and it *seems* to work on a test 
database. We’ve all read the docs again and paid attention to 
https://www.sqlite.org/wal.html#bigwal


To test if it works we started our long running analytics query, on 
our test machine it takes around 8 mins. We then speed up the rate of 
updating our database with external data. In the real world an update 
comes along every 3-5 mins, in our test system we queue them up so we 
have them every 6-10 secs so they are around 60x quicker. The updates 
are real data around 3-5MB in size.


We monitored the -wal and the .shm files created as we throw data in 
the database.


The .wal file gets larger and larger until it hits 224MB and then 
stays constant, the .shm file is only 1.8MB and seems to stay at that 
size. We can also see that the main sqlite database is NOT updated 
(or at least the timestamp isn’t) whilst we are running the updates 
in WAL mode. This appears to be correct as the updates would be in 
the -wal file.


I'm truncating this post for brevity - but basically your concern 
about the size (voiced later in the post) is not a concern. What 
happens is the stated 4MB is simply 1000 pages x 4KB default page size 
- your page size might be a lot bigger (and should be set higher 
looking at your DB size and data entry sizes - I think it is "nicer" 
if, at a minimum, a complete row can fit on a page). Further, the WAL 
for your version of SQLite will grow with copies of data and multiple 
inserts in it because of the long-running query not allowing push-back 
check points for the time - and looking at your insert frequency and 
size, your WAL size seems pretty normal. (If you manage it wrong, it 
will fill up Terrabytes - this is the situation you want to avoid, but 
I think you've got it sorted).


The Documentation simply describes the normal situation, which yours 
isn't.


Also, on a point of satisfying my OCD... going on your quoted averages 
- 5 minutes vs. 10 secs is a 30:1 ratio (5 mins = 300 seconds vs. 10 
secs) so the speed is only 30x faster, not 60) - And before anyone 
asks, yes the low end of the ranges given is 3mins (180s) vs. 6 secs 
which is also a 30:1 ratio.  Even if I take the opposite range 
extremes (5 mins vs. 6s) I still only get 50x speedup.  LoL - Sorry, 
I'll shush now :)


As an aside, I think Richard posted a small study of testing multiple 
DB ops with varying page sizes and varying hardware page sizes, and 
basically, IIRC, the Jury was out on best size in the general case 
with 8192 seeming to be a good standard and the idea that the page 
size should try match the underlying OS page size for best performance 
turned out to be a bit of a "sometimes maybe", but the point was made 
that every implementation should experiment to find the optimum size. 
That said, my memory cannot be trusted - could someone re-post that or 
point us to an on-line page somewhere? Thanks!


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[R Smith]

On 2016/08/07 8:55 AM, Rob Willett wrote:

Richard, Ryan,

Thanks for this. We were dimly aware of WAL but until now hadn’t 
needed to use it.


We’ve done a quick check with it and it *seems* to work on a test 
database. We’ve all read the docs again and paid attention to 
https://www.sqlite.org/wal.html#bigwal


To test if it works we started our long running analytics query, on 
our test machine it takes around 8 mins. We then speed up the rate of 
updating our database with external data. In the real world an update 
comes along every 3-5 mins, in our test system we queue them up so we 
have them every 6-10 secs so they are around 60x quicker. The updates 
are real data around 3-5MB in size.


We monitored the -wal and the .shm files created as we throw data in 
the database.


The .wal file gets larger and larger until it hits 224MB and then 
stays constant, the .shm file is only 1.8MB and seems to stay at that 
size. We can also see that the main sqlite database is NOT updated (or 
at least the timestamp isn’t) whilst we are running the updates in WAL 
mode. This appears to be correct as the updates would be in the -wal 
file.


I'm truncating this post for brevity - but basically your concern about 
the size (voiced later in the post) is not a concern. What happens is 
the stated 4MB is simply 1000 pages x 4KB default page size - your page 
size might be a lot bigger (and should be set higher looking at your DB 
size and data entry sizes - I think it is "nicer" if, at a minimum, a 
complete row can fit on a page). Further, the WAL for your version of 
SQLite will grow with copies of data and multiple inserts in it because 
of the long-running query not allowing push-back check points for the 
time - and looking at your insert frequency and size, your WAL size 
seems pretty normal. (If you manage it wrong, it will fill up Terrabytes 
- this is the situation you want to avoid, but I think you've got it 
sorted).


The Documentation simply describes the normal situation, which yours isn't.

Also, on a point of satisfying my OCD... going on your quoted averages - 
5 minutes vs. 10 secs is a 30:1 ratio (5 mins = 300 seconds vs. 10 secs) 
so the speed is only 30x faster, not 60) - And before anyone asks, yes 
the low end of the ranges given is 3mins (180s) vs. 6 secs which is also 
a 30:1 ratio.  Even if I take the opposite range extremes (5 mins vs. 
6s) I still only get 50x speedup.  LoL - Sorry, I'll shush now :)


As an aside, I think Richard posted a small study of testing multiple DB 
ops with varying page sizes and varying hardware page sizes, and 
basically, IIRC, the Jury was out on best size in the general case with 
8192 seeming to be a good standard and the idea that the page size 
should try match the underlying OS page size for best performance turned 
out to be a bit of a "sometimes maybe", but the point was made that 
every implementation should experiment to find the optimum size. That 
said, my memory cannot be trusted - could someone re-post that or point 
us to an on-line page somewhere? Thanks!


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Jean-Christophe Deschamps]

Rob,

At 08:55 07/08/2016, you wrote:
We think that using WAL mode works for us, indeed inspection seems to 
indicate it does, but the size of the -wal file appears to be far 
larger than would be expected. Is there a problem here? It doesn't 
appear to be a problem but would welcome any comments.


After reading your post I'd like to clear up a few points about WAL mode.

We can also see that the main sqlite database is NOT updated (or at 
least the timestamp isn't) whilst we are running the updates in WAL 
mode. This appears to be correct as the updates would be in the -wal file.


The WAL mode is persistant and consistant. That means that once 
successfully put in his mode the DB itself will remain in WAL mode for 
every (new) connection. Thus your updates and the long-running query 
are both running under WAL mode. That is, provided the WAL mode was set 
prior to the start of the long-running query, but that detail doesn't 
matter for reads in this case.


It doesn't matter whether your query is a single query statement (hence 
in auto-commit mode) or a huge transaction extracting and massaging 
data in multiple temp tables and myriads of read/write statements, all 
inside an explicit transaction), ACID properties guarantee that once 
your query is started, it will see the DB in the state prior to any 
updates that could occur during its run. Else you would obtain 
potentially dangerously inconsistant data of course.


We have not set the journal_size_limit and we have a -wal file which 
is 224MB in size, somewhat larger than 4MB. We are running


3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d

which does not appear to have the code in 3.11.0 so that the WAL file 
is proportional to the size of the transaction. From the same page of 
the manual:


```
Very large write transactions.

A checkpoint can only complete when no other transactions are running, 
which
means the WAL file cannot be reset in the middle of a write 
transaction. So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed 
once the write transaction completes
(assuming there are no other readers blocking it) but in the meantime, 
the file can grow very big.


As of SQLite version 3.11.0, the WAL file for a single transaction 
should be proportional in size to
the transaction itself. Pages that are changed by the transaction 
should only be written into the WAL
file once. However, with older versions of SQLite, the same page might 
be written into the WAL file multiple

times if the transaction grows larger than the page cache.
```


Reread the quoted part again: only with SQLite versions 3.11.0 and 
above will a given page be written only once in the WAL file when 
initiated within a given transaction.


Since you're running a prior version, it's pretty logical to expect 
that your updates will cause writes of multiple distinct copies of the 
same pages in the WAL file. You should update your SQLite version to 
see a possible improvement there. That, or refer only to the old 3.8.2 
documentation, but this is an inferior option because there have been 
significant improvements meanwhile.


Also the ratio in the help file (1000 pages or about 4Mb) applies to 
the default page size (4Kb).


Finally, while the long-running query is running, no checkpoint can run 
to completion. Doc states under "Checkpoint starvation.":


However, if a database has many concurrent overlapping readers and 
there is always at least one active reader, then no checkpoints will 
be able to complete and hence the WAL file will grow without bound.


Since you clearly can't introduce a read-gap inside your read query, 
the .wal file will grow as large as it needs until completion of the 
query. You mentionned that you tested with much more frequent updates 
than the real-world case (120x actually), so .wal file size shouldn't 
be an issue in your actual use case.


HTH

--
Jean-Christophe  


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Rob Willett]

Richard, Ryan,

Thanks for this. We were dimly aware of WAL but until now hadn’t 
needed to use it.


We’ve done a quick check with it and it *seems* to work on a test 
database. We’ve all read the docs again and paid attention to 
https://www.sqlite.org/wal.html#bigwal


To test if it works we started our long running analytics query, on our 
test machine it takes around 8 mins. We then speed up the rate of 
updating our database with external data. In the real world an update 
comes along every 3-5 mins, in our test system we queue them up so we 
have them every 6-10 secs so they are around 60x quicker. The updates 
are real data around 3-5MB in size.


We monitored the -wal and the .shm files created as we throw data in the 
database.


The .wal file gets larger and larger until it hits 224MB and then stays 
constant, the .shm file is only 1.8MB and seems to stay at that size. We 
can also see that the main sqlite database is NOT updated (or at least 
the timestamp isn’t) whilst we are running the updates in WAL mode. 
This appears to be correct as the updates would be in the -wal file.


The times taken for each updates seems a little slower (10% possibly but 
that could be just because we are looking at it) but since the data is 
real and variable in size, it might be just our subjective impression.


Once the long running read-only analytics query completes, the main 
sqlite database appears to get updated (or at least the timestamp on the 
file is updated) as we are still updating with our test data and the 
-wal files are still being used.


Once we stop updating with our test data, the -wal files and .shm files 
disappear (as expected).


A quick check of the database appears to show its correct.

One question though, the size of the -wal file worries us. 
https://www.sqlite.org/wal.html#bigwal states


```
Avoiding Excessively Large WAL Files

In normal cases, new content is appended to the WAL file until the WAL 
file accumulates about 1000 pages (and is
thus about 4MB in size)  at which point a checkpoint is automatically 
run and the WAL file is recycled.
The checkpoint does not normally truncate the WAL file (unless the 
journal_size_limit pragma is set).
Instead, it merely causes SQLite to start overwriting the WAL file from 
the beginning. This is done because
it is normally faster to overwrite an existing file than to append. When 
the last connection to a database
closes, that connection does one last checkpoint and then deletes the 
WAL and its associated shared-memory

file, to clean up the disk.
```

We have not set the journal_size_limit and we have a -wal file which is 
224MB in size, somewhat larger than 4MB. We are running


3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d

which does not appear to have the code in 3.11.0 so that the WAL file is 
proportional to the size of the transaction. From the same page of the 
manual:


```
Very large write transactions.

A checkpoint can only complete when no other transactions are running, 
which
means the WAL file cannot be reset in the middle of a write transaction. 
So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed once 
the write transaction completes
(assuming there are no other readers blocking it) but in the meantime, 
the file can grow very big.


As of SQLite version 3.11.0, the WAL file for a single transaction 
should be proportional in size to
the transaction itself. Pages that are changed by the transaction should 
only be written into the WAL
file once. However, with older versions of SQLite, the same page might 
be written into the WAL file multiple

times if the transaction grows larger than the page cache.
```

We think that using WAL mode works for us, indeed inspection seems to 
indicate it does, but the size of the -wal file appears to be far larger 
than would be expected. Is there a problem here? It doesn’t appear to 
be a problem but would welcome any comments.


Thanks for taking the time to reply.

Rob


On 6 Aug 2016, at 22:35, R Smith wrote:


On 2016/08/06 10:50 PM, Rob Willett wrote:


Our understanding of this is that many processes can READ the 
database at the same time but NO process can INSERT/UPDATE if another 
is reading. We had thought that one process can write and multiple 
processes can read. Our reading (no pun intended) now of this 
paragraph from the manual is that you cannot write if one or more 
processes is reading. Have we understood this correctly? If so is 
there an easy way to get around this?


The Write-Ahead-Log (WAL) journal mode will help you. It basically 
allows a writer to write to the WAL Log in stead of the main database 
so that any amount of readers can still do their thing reading the 
database (and the parts of the WAL journal that is already committed, 
or even parts still in progress if you use "read_uncommitted" mode). 
SQLite then pushes committed data into the DB file based on 
Checkpoints which 

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[R Smith]

On 2016/08/06 10:50 PM, Rob Willett wrote:


Our understanding of this is that many processes can READ the database 
at the same time but NO process can INSERT/UPDATE if another is 
reading. We had thought that one process can write and multiple 
processes can read. Our reading (no pun intended) now of this 
paragraph from the manual is that you cannot write if one or more 
processes is reading. Have we understood this correctly? If so is 
there an easy way to get around this?


The Write-Ahead-Log (WAL) journal mode will help you. It basically 
allows a writer to write to the WAL Log in stead of the main database so 
that any amount of readers can still do their thing reading the database 
(and the parts of the WAL journal that is already committed, or even 
parts still in progress if you use "read_uncommitted" mode). SQLite then 
pushes committed data into the DB file based on Checkpoints which you 
can invoke directly or set up to happen every so often.


This is the new way to do things and the way you should always use 
unless you have a specific reason not to (which might include file 
restrictions, needing read-only-ness, separate speedy DBs that doesn't 
fsync() so much, etc.)


More information here:
https://www.sqlite.org/wal.html

Your DB is quite big and it seems you write often, so please take 
special note of this section:

https://www.sqlite.org/wal.html#bigwal


HTH - Cheers,
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Richard Hipp]

On 8/6/16, Rob Willett  wrote:
>
> What we have now found is that when we are running the analytics query
> in one Perl process, we can no longer UPDATE the main database through
> another Perl process. We are getting “database is locked” errors.

Doing "PRAGMA journal_mode=WAL;" on your database (just once, perhaps
from a command-line shell) will fix this for you.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users