Re: [sqlite] WAL mode is reliable?

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Richard Hipp

> (2) WAL really does give better concurrency since it allows other
processes
> to continue reading while a single process is writing to the database.
In
> rollback mode you can have one writer *or* multiple readers.  In WAL mode
> you can have one writer *and* multiple readers.  On a busy system, or in a
> system with long-running transactions, the difference can be significant.
> 
> (3) Transactions commit a lot faster with WAL.  Depending on your app, you
> might see a measurable performance improvement just by switching to WAL.
> 
> (4) WAL mode is much more resistant to corruption following a power
failure
> that occurs soon after the disk drive lied and said that content was
> committed to oxide when in fact it was still in a volatile track buffer.
> And pretty much all consumer-grade disk drives tell such lies these days.

I just recently started to use WAL and had the following experience with
above:

I work with large databases (e.g. 40 Gb) and when loading data to these, I
will usually get my program to make copy of the database through the OS and
then during heavy data loads run the database with: 

PRAGMA journal_mode = OFF;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA synchronous = 0;
PRAGMA cache_size = 40;

 This increases the load spead tremendously. However, as the database
increases the time spend copying the database increases with it,  although
it is still much faster than using the default journal_mode (DELETE).
Also, the database is locked for even read access during the loads.

Reading about WAL (http://sqlite.org/wal.html) I decided to try it out
during data loads with:

PRAGMA journal_mode = WAL;  
PRAGMA locking_mode = NORMAL;  
PRAGMA synchronous = 1;  
PRAGMA cache_size = 40;

(Thus using the same size cache and now just the default settings for
locking_mode and synchronous).

The result was that the data loads were running at virtually the same speed
as when journal_mode was set to OFF! 

Also, the database can now be opened for reading by other processes and I no
longer have the hassle of waiting for OS copies to be done for every load,
so having had only positive results and no adverse effects I can really
recommend the write ahead logging. 


Best regards,

Frank

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

Re: [sqlite] WAL mode is reliable?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/11/11 11:32, Paxdo Presse wrote:
> Roger, I meant: I feel that WAL is rarely used with its new
> capabilities.

Outside almost 100 million Android devices?

> SQLite + WAL may, perhaps, now compete with Mysql / postgres to handle
> web apps with thousands of users. But what is its limit?

Limit of what?  The number of concurrent requests you can serve will be
limited by hardware, operating system, and all the other code that runs in
order to service a request.  SQLite could be anywhere from 95% of the code
run to service a request to 2%.

You can of course find out exactly what happens in your exact setup by
implementing a representative benchmark.  There are plenty of testing
tools (eg ab).

> In this context, there is currently not a lot of feedback, I feel. 
> There are not enough people know about these new opportunities.

I'm still very confused what exactly it is you want.  Do you want some
reassurance that SQLite will never give wrong answers?

  http://www.sqlite.org/testing.html

Do you want reassurance it can handle any load thrown at it?  The code is
performant, but what can be handled will depend a lot more on the
non-SQLite code.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6y7UgACgkQmOOfHg372QTi7wCfdlTRXDdTUwfNZ9ndJfxKXGRg
CMIAnj7X1GZXvLHMnyY6CO7K/jqkhjqp
=kTgr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

> 
>> I feel that WAL is rarely used now.
> 
> It is used on my Android phone running 2.3.  That is almost 100 million
> devices with all sorts of programs and crash scenarios.  Is that rare?


Roger, I meant: I feel that WAL is rarely used with its new capabilities.

SQLite + WAL may, perhaps, now compete with Mysql / postgres to handle web apps 
with thousands of users. But what is its limit?

In this context, there is currently not a lot of feedback, I feel. 
There are not enough people know about these new opportunities.

Feedback is welcome!

Thank you Simon for your answer.

Olivier Vidal


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

Re: [sqlite] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 6:05pm, Paxdo Presse wrote:

> You think SQLite can handle a website / web app that has 1000 readers per 
> day, including 200 to 300 who will write regularly?

You should be able to handle 10,000 posts a day without problems.  A single 
INSERT command keeps the database locked only for a very short time.

Your bone of contention is when you have lots of readers all logged in at the 
same time, all loading pages which list forum threads and clicking 'next ... 
next ... next'.  If you have 200 or 300 of those all logged on at the same time 
(not at different times of the same day), you may have problems.  But you 
should be able to deal with even that if you write your code carefully, so as 
not to keep the database locked for longer than necessary.

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

Re: [sqlite] WAL mode is reliable?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/11/11 11:05, Paxdo Presse wrote:
> I feel that WAL is rarely used now.

It is used on my Android phone running 2.3.  That is almost 100 million
devices with all sorts of programs and crash scenarios.  Is that rare?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6y2lMACgkQmOOfHg372QSCgQCgzcD037y//w2RnBRGbzm1db+W
Ka4AoJI/1nt24age9bjrS+WQovI8IaK6
=f2ac
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 5:44pm, Richard Hipp wrote:

> On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin  wrote:
> 
>> 
>> If I start getting contention issues (locks failing because of too many
>> concurrent attempts at access) then I'll look into using some PRAGMAs,
>> possibly switching to WAL.
>> 
>> 
> Several points:

Fair enough.  So if I have a database created with version 3.6.x, but are now 
using 3.7.3, all I need to convert it to WAL mode is to open it, then issue

PRAGMA journal_mode = WAL

, then close it properly, right ?

(I can't update past 3.7.3 for SQLite as I know you're bursting to tell me: 
it's the version compiled into PHP 5.3.4 on that server.  It will change when 
they update the version of PHP.)

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

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

Thank you M. Hipp !

I feel that WAL is rarely used now. 

It is difficult to get opinions / feedback on this feature. 

And for users of database servers (MySQL, Postgres, etc.). It is impossible to 
have a reliable database without a server.

You think SQLite can handle a website / web app that has 1000 readers per day, 
including 200 to 300 who will write regularly?

Olivier Vidal


Le 3 nov. 2011 à 18:44, Richard Hipp a écrit :

> On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin  wrote:
> 
>> 
>> If I start getting contention issues (locks failing because of too many
>> concurrent attempts at access) then I'll look into using some PRAGMAs,
>> possibly switching to WAL.
>> 
>> 
> Several points:
> 
> (1) You only have to set WAL mode once for the database.  The database
> remembers that it is in WAL mode and all subsequent opens bring it back up
> in WAL mode again (until you deliberately change it out of WAL mode.)  You
> do *not* have to set WAL mode every time you open the database file.
> 
> (2) WAL really does give better concurrency since it allows other processes
> to continue reading while a single process is writing to the database.   In
> rollback mode you can have one writer *or* multiple readers.  In WAL mode
> you can have one writer *and* multiple readers.  On a busy system, or in a
> system with long-running transactions, the difference can be significant.
> 
> (3) Transactions commit a lot faster with WAL.  Depending on your app, you
> might see a measurable performance improvement just by switching to WAL.
> 
> (4) WAL mode is much more resistant to corruption following a power failure
> that occurs soon after the disk drive lied and said that content was
> committed to oxide when in fact it was still in a volatile track buffer.
> And pretty much all consumer-grade disk drives tell such lies these days.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] WAL mode is reliable?

[Richard Hipp]

On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin  wrote:

>
> If I start getting contention issues (locks failing because of too many
> concurrent attempts at access) then I'll look into using some PRAGMAs,
> possibly switching to WAL.
>
>
Several points:

(1) You only have to set WAL mode once for the database.  The database
remembers that it is in WAL mode and all subsequent opens bring it back up
in WAL mode again (until you deliberately change it out of WAL mode.)  You
do *not* have to set WAL mode every time you open the database file.

(2) WAL really does give better concurrency since it allows other processes
to continue reading while a single process is writing to the database.   In
rollback mode you can have one writer *or* multiple readers.  In WAL mode
you can have one writer *and* multiple readers.  On a busy system, or in a
system with long-running transactions, the difference can be significant.

(3) Transactions commit a lot faster with WAL.  Depending on your app, you
might see a measurable performance improvement just by switching to WAL.

(4) WAL mode is much more resistant to corruption following a power failure
that occurs soon after the disk drive lied and said that content was
committed to oxide when in fact it was still in a volatile track buffer.
And pretty much all consumer-grade disk drives tell such lies these days.

-- 
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] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 3:57pm, Paxdo Presse wrote:

> Even if the requests are very fast, it seems risky not to use WAL?

As opposed to no journal ?  Yes, using no journal at all is risky.  But the 
older style rollback journal system was about as fast and as trustworthy as WAL 
journals.  I see no reason not to use it as that's what SQLite defaults to 
using.

If I start getting contention issues (locks failing because of too many 
concurrent attempts at access) then I'll look into using some PRAGMAs, possibly 
switching to WAL.

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

Re: [sqlite] WAL mode is reliable?

[Tim Streater]

On 03 Nov 2011 at 14:02, Simon Slavin  wrote: 

> On 3 Nov 2011, at 1:38pm, Paxdo Presse wrote:
>
>> How did you get concurrent connections? (about)
>
> This is related specifically to the web language I use (PHP) so it won't help
> you if you're using something else.
>
> Actually my system works like this: the web pages themselves are '.html' files
> do most of their work in JavaScript.  When they want to talk to the database
> they use XMLHTTPRequest to call other utility '.php' files which have the job
> of just a single query or execution.  Though they actually talk JSON to
> one-another, not XML.

This is pretty much what I do too. Except I don't even use JSON; I invented my 
own.

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

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

> For concurrency it works fine.  The amount of time a connection takes is very 
> small: just long enough to open, set timeout, do a single query/exec, and 
> close the connection.  So even with lots of users there aren't that many 
> concurrent connections to the database file.
> 
> Looking back at the 'Subject' header, I note that I don't use a PRAGMA to 
> specify journal mode.  It's using whatever journal mode is default for a 
> database created in the sqlite3 shell tool.


Thank you Simon.

Even if the requests are very fast, it seems risky not to use WAL?

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

Re: [sqlite] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 1:38pm, Paxdo Presse wrote:

> How did you get concurrent connections? (about)

This is related specifically to the web language I use (PHP) so it won't help 
you if you're using something else.

Actually my system works like this: the web pages themselves are '.html' files 
do most of their work in JavaScript.  When they want to talk to the database 
they use XMLHTTPRequest to call other utility '.php' files which have the job 
of just a single query or execution.  Though they actually talk JSON to 
one-another, not XML.

For concurrency it works fine.  The amount of time a connection takes is very 
small: just long enough to open, set timeout, do a single query/exec, and close 
the connection.  So even with lots of users there aren't that many concurrent 
connections to the database file.

Looking back at the 'Subject' header, I note that I don't use a PRAGMA to 
specify journal mode.  It's using whatever journal mode is default for a 
database created in the sqlite3 shell tool.

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

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

Thank you Simon!

How did you get concurrent connections? (about)

Le 3 nov. 2011 à 14:32, Simon Slavin a écrit :

> 
> On 3 Nov 2011, at 1:22pm, Paxdo Presse wrote:
> 
>> What do you think of SQLite with WAL mode? 
>> 
>> Is it reliable? 
>> 
>> It is for a web application. 
>> 
>> The database is hosted on a single computer, but there may be multiple 
>> simultaneous connections via Internet (and LAN).
>> All users access to database via a web browser.
>> 
>> In your opinion, how about concurrents access can handle this database? 
> 
> All my experience with this is using PHP's 'sqlite3' object interface.  That 
> works fine for me (once I put the TimeOutWait up to 1000 milliseconds).  But 
> my server is for internal use only and has hundreds, not tens of thousands of 
> users.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 1:22pm, Paxdo Presse wrote:

> What do you think of SQLite with WAL mode? 
> 
> Is it reliable? 
> 
> It is for a web application. 
> 
> The database is hosted on a single computer, but there may be multiple 
> simultaneous connections via Internet (and LAN).
> All users access to database via a web browser.
> 
> In your opinion, how about concurrents access can handle this database? 

All my experience with this is using PHP's 'sqlite3' object interface.  That 
works fine for me (once I put the TimeOutWait up to 1000 milliseconds).  But my 
server is for internal use only and has hundreds, not tens of thousands of 
users.

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

[sqlite] WAL mode is reliable?

[Paxdo Presse]

Hi,

What do you think of SQLite with WAL mode? 

Is it reliable? 

It is for a web application. 

The database is hosted on a single computer, but there may be multiple 
simultaneous connections via Internet (and LAN).
All users access to database via a web browser.

In your opinion, how about concurrents access can handle this database? 

Thank you,

olivier
Paxdo Presse
France
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users