Re: [sqlite] Mozilla's method

[Bert Nelsen]

Thanks Olaf.

On Sat, Jan 2, 2010 at 12:49 AM, Olaf Schmidt  wrote:

>
> "Artur Reilin"  schrieb im
> Newsbeitrag news:op.u5vno6hp1pq...@rear...
>
> > If you are using something like an log system it
> > would be better in this way, but in apps like an
> > shop what wouldn't be so great. (thinking about
> > ebay with the bets and such..)
>
> Of course, but I think I made that already clear,
> that the approach should not to be misunderstood as a
> "general recommendation" - it really should be used only within
> smaller Apps, which don't need e.g. "stacked transactions",
> or "complex transactions which could fail" ... Apps which
> also only work singlethreaded within a single process ...
> ...the timer-based transaction-syncing then only an
> "easier applicable workaround" in environments which
> cannot - (or don't want to) make use of the more efficient
> working async-writer-thread implementation of the SQLite-engine).
>
> Olaf Schmidt
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Artur Reilin"  schrieb im
Newsbeitrag news:op.u5vno6hp1pq...@rear...

> If you are using something like an log system it
> would be better in this way, but in apps like an
> shop what wouldn't be so great. (thinking about
> ebay with the bets and such..)

Of course, but I think I made that already clear,
that the approach should not to be misunderstood as a
"general recommendation" - it really should be used only within
smaller Apps, which don't need e.g. "stacked transactions",
or "complex transactions which could fail" ... Apps which
also only work singlethreaded within a single process ...
...the timer-based transaction-syncing then only an
"easier applicable workaround" in environments which
cannot - (or don't want to) make use of the more efficient
working async-writer-thread implementation of the SQLite-engine).

Olaf Schmidt




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

Re: [sqlite] Mozilla's method

[Artur Reilin]

If you are using something like an log system it would be better in this  
way, but in apps like an shop what wouldn't be so great. (thinking about  
ebay with the bets and such..)

But the idea itself is nice.

Am 02.01.2010, 00:14 Uhr, schrieb Olaf Schmidt :

>
> "Artur Reilin"  schrieb im
> Newsbeitrag news:op.u5vlqcps1pq...@rear...
>
>> But that means, if there is a power off or an system crash,
>> your data which you send at this moment, goes nirvana.
>
> Yep, as I wrote at the end of my post:
>   "...in case of an unexpected Close of the App (due to
>whatever reason), you will lose only the new data which
>was gathered within the last timer-interval."
>
> The Timer-interval in question should therefore not be
> too large - also with regards to "palpable App-Blocking"
> in the continously (timer-triggered) "syncing Events" ... but also
> not too small, to achieve the expected "performance effect" -
> so, at least "more than only one single new log-record" should
> be gathered (on average) within the interval, to work with a
> somewhat better write-efficiency.
>
> Would require some experimenting first, which timer-interval
> works best (depends somewhat on the frequency and size of
> the incoming new data-records, but also on the underlying
> storage-media, the DB is hosted on - be it flash-based media,
> as USB-sticks for example - or "real Hard-Disks").
>
> Olaf Schmidt
>
>
>
> ___
> 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] Mozilla's method

[Olaf Schmidt]

"Artur Reilin"  schrieb im
Newsbeitrag news:op.u5vlqcps1pq...@rear...

> But that means, if there is a power off or an system crash,
> your data which you send at this moment, goes nirvana.

Yep, as I wrote at the end of my post:
  "...in case of an unexpected Close of the App (due to
   whatever reason), you will lose only the new data which
   was gathered within the last timer-interval."

The Timer-interval in question should therefore not be
too large - also with regards to "palpable App-Blocking"
in the continously (timer-triggered) "syncing Events" ... but also
not too small, to achieve the expected "performance effect" -
so, at least "more than only one single new log-record" should
be gathered (on average) within the interval, to work with a
somewhat better write-efficiency.

Would require some experimenting first, which timer-interval
works best (depends somewhat on the frequency and size of
the incoming new data-records, but also on the underlying
storage-media, the DB is hosted on - be it flash-based media,
as USB-sticks for example - or "real Hard-Disks").

Olaf Schmidt



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

Re: [sqlite] Mozilla's method

[Artur Reilin]

But that means, if there is a power off or an system crash, your data  
which you send at this moment, goes nirvana. But indeed it would be faster  
and also would save the hard disc from some writing operations.

Am 01.01.2010, 23:33 Uhr, schrieb Olaf Schmidt :

>
> "Bert Nelsen"  schrieb
> im Newsbeitrag
> news:a5ffd531001010911r3de60ec1o44e2c14bce7a7...@mail.gmail.com...
>
>> So SQLite looks at both the database on the disk
>> and in memory?
>> Wouldn't that be difficult???
>
> Of course... ;-)
> And what's so amazing with the SQLite-engine -
> is, that all that comes in such a small package.
>
> Regarding "aggregated writes" and transactions again...
>
> What SQLites async-writer thread does, is to
> implement something like a "delayed write" at
> the DB-engine-level (instead to rely on such a
> feature to be implemented at the filesystem-level).
>
> Delayed writes can ensure better performance, since
> the new gathered (to be written) data can be grouped
> (sometimes also reordered) into larger chunks,
> to let the "real disk actions" happen within a more
> optimal (more efficient) "operation-window", so to say.
>
> In case of your (smaller) VB-based application you can
> achieve something like that also with an "always opened"
> transaction, gathering the new to be written data (records)
> using SQLites internal transaction-cache-mechanisms -
> and then writing it out (syncing it) to disk in a somewhat more
> "relaxed" fashion (with better efficiency) from within a timer-
> event - thereby avoiding the usage of threads.
>
> That said, I'd recommend the following "approach" only
> for smaller Apps/Tools which "own the DB exclusively" -
> and are not expected to grow much over time (implementing
> smaller logging-scenarios for example - as in your case for
> the incoming GPS-data).
>
> At App-startup (e.g. in Form_Load of the VB-App):
>
> Private Sub Form_Load()
>   InstantiateAndOpenTheConnection
>   Cnn.BeginTrans 'ensure an opened transaction
>   '... other init-stuff
> End Sub
>
> 'in a Timer on that Form (e.g. set to 200-500msec)
> Private Sub tmrDelayedWrite_Timer()
> If Cnn.TransactionStackCounter = 0 Then
> Cnn.BeginTrans 'just in case... (we're not expecting to reach  
> here)
> Else
> Cnn.CommitTrans 'sync the currently "cached" content
> Cnn.BeginTrans 'and reopen a new transaction after that
> End If
> End Sub
>
> And on App-ShutDown just ensure, that "all the rest" is written too
> Private Sub Form_Unload(Cancel As Integer)
> tmrDelayedWrite.Enabled = False 'disable the timer first
>
> If Cnn.TransactionStackCounter Then
> Cnn.CommitTrans 'write the remaining stuff
> End If
> End Sub
>
> As said, in that mode you should work only with "proven
> commands" which are expected to "never fail" (with regards
> to an then unnecessary Cnn.RollBackTrans, which could "mess
> up" the whole simplified or "globalized" approach above).
> And you should *not* work with the wrappers
> Recordset.UpdateBatch-feature in that mode, since this
> method performs an implicit transaction on the (Rs-internally)
> gathered data (from Rs.AddNew, Rs.Delete or  Rs-FieldChanges).
>
> So, to be entirely sure, you could open all the Recordsets
> ReadOnly (using the Optional Flag in the OpenRecordset-
> method) - and then work only with Cnn.Executes or better
> the Command-Objects in write-direction.
>
> This way the "real writes" - the syncing - only happens within
> the timer-event - you will risk no DB-corruption this way -
> and in case of an unexpected Close of the App (due to whatever
> reason), you will lose only the new data which was gathered
> within the last timer-interval.
>
> Olaf Schmidt
>
>
>
> ___
> 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] Mozilla's method

[Olaf Schmidt]

"Bert Nelsen"  schrieb
im Newsbeitrag
news:a5ffd531001010911r3de60ec1o44e2c14bce7a7...@mail.gmail.com...

> So SQLite looks at both the database on the disk
> and in memory?
> Wouldn't that be difficult???

Of course... ;-)
And what's so amazing with the SQLite-engine -
is, that all that comes in such a small package.

Regarding "aggregated writes" and transactions again...

What SQLites async-writer thread does, is to
implement something like a "delayed write" at
the DB-engine-level (instead to rely on such a
feature to be implemented at the filesystem-level).

Delayed writes can ensure better performance, since
the new gathered (to be written) data can be grouped
(sometimes also reordered) into larger chunks,
to let the "real disk actions" happen within a more
optimal (more efficient) "operation-window", so to say.

In case of your (smaller) VB-based application you can
achieve something like that also with an "always opened"
transaction, gathering the new to be written data (records)
using SQLites internal transaction-cache-mechanisms -
and then writing it out (syncing it) to disk in a somewhat more
"relaxed" fashion (with better efficiency) from within a timer-
event - thereby avoiding the usage of threads.

That said, I'd recommend the following "approach" only
for smaller Apps/Tools which "own the DB exclusively" -
and are not expected to grow much over time (implementing
smaller logging-scenarios for example - as in your case for
the incoming GPS-data).

At App-startup (e.g. in Form_Load of the VB-App):

Private Sub Form_Load()
  InstantiateAndOpenTheConnection
  Cnn.BeginTrans 'ensure an opened transaction
  '... other init-stuff
End Sub

'in a Timer on that Form (e.g. set to 200-500msec)
Private Sub tmrDelayedWrite_Timer()
If Cnn.TransactionStackCounter = 0 Then
Cnn.BeginTrans 'just in case... (we're not expecting to reach here)
Else
Cnn.CommitTrans 'sync the currently "cached" content
Cnn.BeginTrans 'and reopen a new transaction after that
End If
End Sub

And on App-ShutDown just ensure, that "all the rest" is written too
Private Sub Form_Unload(Cancel As Integer)
tmrDelayedWrite.Enabled = False 'disable the timer first

If Cnn.TransactionStackCounter Then
Cnn.CommitTrans 'write the remaining stuff
End If
End Sub

As said, in that mode you should work only with "proven
commands" which are expected to "never fail" (with regards
to an then unnecessary Cnn.RollBackTrans, which could "mess
up" the whole simplified or "globalized" approach above).
And you should *not* work with the wrappers
Recordset.UpdateBatch-feature in that mode, since this
method performs an implicit transaction on the (Rs-internally)
gathered data (from Rs.AddNew, Rs.Delete or  Rs-FieldChanges).

So, to be entirely sure, you could open all the Recordsets
ReadOnly (using the Optional Flag in the OpenRecordset-
method) - and then work only with Cnn.Executes or better
the Command-Objects in write-direction.

This way the "real writes" - the syncing - only happens within
the timer-event - you will risk no DB-corruption this way -
and in case of an unexpected Close of the App (due to whatever
reason), you will lose only the new data which was gathered
within the last timer-interval.

Olaf Schmidt



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

Re: [sqlite] Mozilla's method

[Max Vlasov]

On Fri, Jan 1, 2010 at 8:11 PM, Bert Nelsen wrote:

> So SQLite looks at both the database on the disk and in memory?
> Wouldn't that be difficult???
>


I don't think that only the memory can be used. Imagine you can have a very
big transaction, 1,000,000 inserts. As long as I understand the
architecture, all affected sectors are saved in .db3-journal file so the db3
file itself always reflects the current state of the database. For example
you have a table with 100 records and you're inserting 1,000,000 records, so
at the final stage, right before Commit, your db3 file contains all these
1,000,100 records and the indexes changed accordingly, while the
corresponding db-journal consists of the data needed to restore the db file
to the state it was before the transaction began
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Igor Tandetnik]

Bert Nelsen wrote:
> So SQLite looks at both the database on the disk and in memory?
> Wouldn't that be difficult???

The in-memory cache is an integral part of the architecture. The whole 
transaction model depends on it in large part. For details, see

http://www.sqlite.org/arch.html
http://www.sqlite.org/lockingv3.html


Igor Tandetnik

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

Re: [sqlite] Mozilla's method

[Bert Nelsen]

So SQLite looks at both the database on the disk and in memory?
Wouldn't that be difficult???
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Igor Tandetnik]

Bert Nelsen wrote:
> By "corrupted" (the meaning of this word seems to differ depending on the
> user's perspective) you mean that the db is really destroyed and cannot be
> opened anymore?

Yes it's possible, if the power loss occurs at just the wrong moment.

> Another question would be: When I use transactions, and I said ".BeginTrans"
> and insert new records and then, before saying ".CommitTrans", I query the
> records, they seem to be already saved.

A transaction can always see the changes it iself made. They are not 
necessarily "saved" in the sense of being written to the disk surface: modified 
records may come from in-memory cache.

Igor Tandetnik

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

Re: [sqlite] Mozilla's method

[Max Vlasov]

> Another question would be: When I use transactions, and I said
> ".BeginTrans"
> and insert new records and then, before saying ".CommitTrans", I query the
> records, they seem to be already saved. Can you tell me why this is so?
> Does
> a select command automatically trigger a ".CommitTrans"?
> Wishing you a happy new year!
>
>
I think it comes from the nature of transaction itself, it is not an
isolated database inside the database, it's an "undo" feature. Otherwise
many operations would have unpredictable results.
So in the example below

sqlite> Create Table [TestTable] ([Value] INTEGER);
sqlite> Begin transaction;
sqlite> INSERT INTO TestTable (Value) VALUES (11);
sqlite> SELECT Count(*) FROM TestTable;
1
sqlite> SELECT Max(rowid) FROM TestTable;
1
sqlite> SELECT * FROM TestTable;
11
sqlite> End transaction;

.. all three selects should return the same results regardless of begin/end
transaction existence in the sequence of commands and it makes sense I
think.

Max


> On Fri, Jan 1, 2010 at 9:11 AM, Olaf Schmidt  wrote:
>
> >
> > "Bert Nelsen"  schrieb
> > im Newsbeitrag
> > news:a5ffd530912311004p26a7cc5k1f1bf6f671bef...@mail.gmail.com...
> >
> > > Your .Sychronous = False property does everything
> > > as fast as I want, and I am not afraid of losing some
> > > user data (it's not a critical application) but
> > > I am very much afraid of having a corrupted db.
> > > Can anybody please confirm
> > > that there is no chance of getting my db corrupted?
> >
> > Ah, I see now, where the "confusion" came from.
> > The wrappers Synchronous-Property has nothing to do
> > with the (relative new) async-writer-feature of SQLite -
> > instead it maps to SQLites Synchronous PRAGMA
> > (as a "convenience property").
> >
> > You can set all the Pragmas alternatively also per
> > Cnn.Execute "PRAGMA pragma_name ..."
> >
> > or read out a current Pragma-Value with:
> > Cnn.OpenRecordset("PRAGMA pragma_name")(0).Value
> >
> > Please read about SQLite-Pragmas here:
> > http://www.sqlite.org/pragma.html
> > ... and what's written there about the Synchronous-Pragma-
> > Settings. With the Synchronous-Pragma at 'Off' or '0', you're
> > risking DB-corruption.
> >
> > So, I would not touch the Synchronous-Property in your
> > case (leaving it at its default FULL(2)) - instead you should
> > wrap larger insert- or update-actions within a transaction -
> > that works fast as well.
> > Also consider using the binding-support of the wrapper
> > (the Command-Objects), to achieve faster (and more
> > typesafe) operations in "write direction".
> >
> > Olaf Schmidt
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Regards,
> Bert
> ___
> 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] Mozilla's method

[Bert Nelsen]

Hello Olaf!
Thanks again for the message.
By "corrupted" (the meaning of this word seems to differ depending on the
user's perspective) you mean that the db is really destroyed and cannot be
opened anymore?
Currently I am working on an application that tracks something like a GPS
signal, and if a signal is not saved to the db it's not a big problem. But
db corruption would be.
Another question would be: When I use transactions, and I said ".BeginTrans"
and insert new records and then, before saying ".CommitTrans", I query the
records, they seem to be already saved. Can you tell me why this is so? Does
a select command automatically trigger a ".CommitTrans"?
Wishing you a happy new year!

On Fri, Jan 1, 2010 at 9:11 AM, Olaf Schmidt  wrote:

>
> "Bert Nelsen"  schrieb
> im Newsbeitrag
> news:a5ffd530912311004p26a7cc5k1f1bf6f671bef...@mail.gmail.com...
>
> > Your .Sychronous = False property does everything
> > as fast as I want, and I am not afraid of losing some
> > user data (it's not a critical application) but
> > I am very much afraid of having a corrupted db.
> > Can anybody please confirm
> > that there is no chance of getting my db corrupted?
>
> Ah, I see now, where the "confusion" came from.
> The wrappers Synchronous-Property has nothing to do
> with the (relative new) async-writer-feature of SQLite -
> instead it maps to SQLites Synchronous PRAGMA
> (as a "convenience property").
>
> You can set all the Pragmas alternatively also per
> Cnn.Execute "PRAGMA pragma_name ..."
>
> or read out a current Pragma-Value with:
> Cnn.OpenRecordset("PRAGMA pragma_name")(0).Value
>
> Please read about SQLite-Pragmas here:
> http://www.sqlite.org/pragma.html
> ... and what's written there about the Synchronous-Pragma-
> Settings. With the Synchronous-Pragma at 'Off' or '0', you're
> risking DB-corruption.
>
> So, I would not touch the Synchronous-Property in your
> case (leaving it at its default FULL(2)) - instead you should
> wrap larger insert- or update-actions within a transaction -
> that works fast as well.
> Also consider using the binding-support of the wrapper
> (the Command-Objects), to achieve faster (and more
> typesafe) operations in "write direction".
>
> Olaf Schmidt
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Bert Nelsen"  schrieb
im Newsbeitrag
news:a5ffd530912311004p26a7cc5k1f1bf6f671bef...@mail.gmail.com...

> Your .Sychronous = False property does everything
> as fast as I want, and I am not afraid of losing some
> user data (it's not a critical application) but
> I am very much afraid of having a corrupted db.
> Can anybody please confirm
> that there is no chance of getting my db corrupted?

Ah, I see now, where the "confusion" came from.
The wrappers Synchronous-Property has nothing to do
with the (relative new) async-writer-feature of SQLite -
instead it maps to SQLites Synchronous PRAGMA
(as a "convenience property").

You can set all the Pragmas alternatively also per
Cnn.Execute "PRAGMA pragma_name ..."

or read out a current Pragma-Value with:
Cnn.OpenRecordset("PRAGMA pragma_name")(0).Value

Please read about SQLite-Pragmas here:
http://www.sqlite.org/pragma.html
... and what's written there about the Synchronous-Pragma-
Settings. With the Synchronous-Pragma at 'Off' or '0', you're
risking DB-corruption.

So, I would not touch the Synchronous-Property in your
case (leaving it at its default FULL(2)) - instead you should
wrap larger insert- or update-actions within a transaction -
that works fast as well.
Also consider using the binding-support of the wrapper
(the Command-Objects), to achieve faster (and more
typesafe) operations in "write direction".

Olaf Schmidt




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

Re: [sqlite] Mozilla's method

[Bert Nelsen]

Hello Olaf!
Thank you for your reply. I am using your wrapperlib (and I love it) but I
thought it was a more general question and had nothing directly to do with
your wrapperlib. But I am not sure anymore what is yours and what is common
Sqlite.
Your .Sychronous = False property does everything as fast as I want, and I
am not afraid of losing some user data (it's not a critical application) but
I am very much afraid of having a corrupted db. Can anybody please confirm
that there is no chance of getting my db corrupted?

On Thu, Dec 31, 2009 at 6:11 PM, Olaf Schmidt  wrote:

>
> "Bert Nelsen"  schrieb
> im Newsbeitrag
> news:a5ffd530912310853t7024d908tefbf1ef40df47...@mail.gmail.com...
>
> > I would like to make writes to my SQLite db faster.
> In what regard (under which circumstances) is it slow
> currently?
>
> What's your typical recordcount per transaction?
> Do you use transactions at all?
> How many indexes are defined on the "slow tables"?
> Are you using Command-Objects (the sqlite-bind API)?
> In what language is the App-Host written (regarding
> easy "thread-control")?
> Do you work through a wrapper yet, or with the SQLite-lib
> directly?
>
> > I was thinking about the Async method, but I think I
> > remember reading somewhere that it may cause database
> > corruption.
> IMO the async-feature was not risky with regards to data-
> corruption, only with regards to durability - an (uncorrupted)
> DB could contain "lesser data" (not the last "version", which
> your successfully reported transactions suggested earlier) -
> in case of e.g. a powerfailure.
> But your "mozilla-comment-snippet" already mentioned that too.
>
> I remember, that at some point in time you were using my
> COM-wrapper, to work with SQLite. And I was thinking
> about "handing out" the async-functionality over an appropriate
> method, as the feature came up - but then stayed away from
> it, for the sake of higher stability. The COM-wrapper is
> mostly used in conjunction with VB5/6 - and there you have
> not that much control over the teardown-process of an App
> like in other, more "bare to the metal" languages - imagine
> the async SQLite-WriterThread, having "yet some stuff
> in the pipe" - and a closing VB-App, which only "knows"
> the COM-Object - and not the SQLite-lib behind it, which
> "owns" the async thread.
>
> So, just in case you're using the dhRichClient3-wrapper-lib,
> there's currently no plan (and no time), to make that feature
> available in a reliable and stable working way (playing well
> with VB5/6) over the COM-interface.
>
> Olaf Schmidt
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Mozilla's method

[Shawn Wilsher]

On Thu, Dec 31, 2009 at 9:06 AM, Bert Nelsen wrote:

> It's here:
>
> https://developer.mozilla.org/en/Storage:Performance
>
Thanks.  I've removed the outdated information.

Cheers,

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

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Bert Nelsen"  schrieb
im Newsbeitrag
news:a5ffd530912310853t7024d908tefbf1ef40df47...@mail.gmail.com...

> I would like to make writes to my SQLite db faster.
In what regard (under which circumstances) is it slow
currently?

What's your typical recordcount per transaction?
Do you use transactions at all?
How many indexes are defined on the "slow tables"?
Are you using Command-Objects (the sqlite-bind API)?
In what language is the App-Host written (regarding
easy "thread-control")?
Do you work through a wrapper yet, or with the SQLite-lib
directly?

> I was thinking about the Async method, but I think I
> remember reading somewhere that it may cause database
> corruption.
IMO the async-feature was not risky with regards to data-
corruption, only with regards to durability - an (uncorrupted)
DB could contain "lesser data" (not the last "version", which
your successfully reported transactions suggested earlier) -
in case of e.g. a powerfailure.
But your "mozilla-comment-snippet" already mentioned that too.

I remember, that at some point in time you were using my
COM-wrapper, to work with SQLite. And I was thinking
about "handing out" the async-functionality over an appropriate
method, as the feature came up - but then stayed away from
it, for the sake of higher stability. The COM-wrapper is
mostly used in conjunction with VB5/6 - and there you have
not that much control over the teardown-process of an App
like in other, more "bare to the metal" languages - imagine
the async SQLite-WriterThread, having "yet some stuff
in the pipe" - and a closing VB-App, which only "knows"
the COM-Object - and not the SQLite-lib behind it, which
"owns" the async thread.

So, just in case you're using the dhRichClient3-wrapper-lib,
there's currently no plan (and no time), to make that feature
available in a reliable and stable working way (playing well
with VB5/6) over the COM-interface.

Olaf Schmidt



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

Re: [sqlite] Mozilla's method

[Bert Nelsen]

It's here:

https://developer.mozilla.org/en/Storage:Performance

On Thu, Dec 31, 2009 at 6:04 PM, Shawn Wilsher  wrote:

> Where did you see this?  It's inaccurate, and if I can, I'll remove it.  If
> you want to use a proper asynchronous statement execution which wraps your
> statement[s] in a transaction, you want to use this:
> https://developer.mozilla.org/En/Storage#Asynchronously
>
> Cheers,
>
> Shawn Wilsher
> Mozilla Developer
>
> On Thu, Dec 31, 2009 at 8:53 AM, Bert Nelsen  >wrote:
>
> > Hello,
> >
> > I would like to make writes to my SQLite db faster.
> >
> > I was thinking about the Async method, but I think I remember reading
> > somewhere that it may cause database corruption.
> >
> > Now I read something on the Mozilla pages, and I don't understand what
> > exactely they are doing.
> >
> > Do they bundle everything in transactions only or do they in fact use the
> > Async method?
> > It is not clear to me by reading through their articles.
> >
> > It would be nice if somebody could clear me up on this issue.
> >
> > Here is the article:
> >
> > Lazy writing
> >
> > Mozilla has relaxed the ACID requirements in order to speed up commits.
> In
> > particular, we have dropped durability. This means that when a commit
> > returns, you are not guaranteed that the commit has gone through. If the
> > power goes out right away, that commit may (or may not) be lost. However,
> > we
> > still support the other (ACI) requirements. This means that the database
> > will not get corrupted. If the power goes out immediately after a commit,
> > the transaction will be like it was rolled back: the database will still
> be
> > in a consistent state.
> >
> > Higher commit performance is achieved by writing to the database from a
> > separate thread (see
> > storage/src/mozStorageAsyncIO.cpp<
> >
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncIO.cpp
> > >
> > which
> > is associated with the storage service in
> > storage/src/mozStorageService.cpp<
> >
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageService.cpp
> > >
> > ).
> > The main database thread does everything exactly as it did before.
> However,
> > we have overridden the file operations and everything comes through the
> > AsnycIO module. This file is based on
> > test_async.c<
> http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/test_async.c
> > >from
> > the sqlite distribution.
> >
> > The AsyncIO module packages writes up in messages and puts them on the
> > write
> > thread's message queue. This write thread waits for messages and
> processes
> > them as fast as it can. This means that writes, locking, and most
> > importantly, disk syncs, only block the AsyncIO thread. Reads are done
> > synchronously, taking into account unwritten data still in the buffer.
> > ___
> > 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
>



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

Re: [sqlite] Mozilla's method

[Shawn Wilsher]

Where did you see this?  It's inaccurate, and if I can, I'll remove it.  If
you want to use a proper asynchronous statement execution which wraps your
statement[s] in a transaction, you want to use this:
https://developer.mozilla.org/En/Storage#Asynchronously

Cheers,

Shawn Wilsher
Mozilla Developer

On Thu, Dec 31, 2009 at 8:53 AM, Bert Nelsen wrote:

> Hello,
>
> I would like to make writes to my SQLite db faster.
>
> I was thinking about the Async method, but I think I remember reading
> somewhere that it may cause database corruption.
>
> Now I read something on the Mozilla pages, and I don't understand what
> exactely they are doing.
>
> Do they bundle everything in transactions only or do they in fact use the
> Async method?
> It is not clear to me by reading through their articles.
>
> It would be nice if somebody could clear me up on this issue.
>
> Here is the article:
>
> Lazy writing
>
> Mozilla has relaxed the ACID requirements in order to speed up commits. In
> particular, we have dropped durability. This means that when a commit
> returns, you are not guaranteed that the commit has gone through. If the
> power goes out right away, that commit may (or may not) be lost. However,
> we
> still support the other (ACI) requirements. This means that the database
> will not get corrupted. If the power goes out immediately after a commit,
> the transaction will be like it was rolled back: the database will still be
> in a consistent state.
>
> Higher commit performance is achieved by writing to the database from a
> separate thread (see
> storage/src/mozStorageAsyncIO.cpp<
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncIO.cpp
> >
> which
> is associated with the storage service in
> storage/src/mozStorageService.cpp<
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageService.cpp
> >
> ).
> The main database thread does everything exactly as it did before. However,
> we have overridden the file operations and everything comes through the
> AsnycIO module. This file is based on
> test_async.c >from
> the sqlite distribution.
>
> The AsyncIO module packages writes up in messages and puts them on the
> write
> thread's message queue. This write thread waits for messages and processes
> them as fast as it can. This means that writes, locking, and most
> importantly, disk syncs, only block the AsyncIO thread. Reads are done
> synchronously, taking into account unwritten data still in the buffer.
> ___
> 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

[sqlite] Mozilla's method

[Bert Nelsen]

Hello,

I would like to make writes to my SQLite db faster.

I was thinking about the Async method, but I think I remember reading
somewhere that it may cause database corruption.

Now I read something on the Mozilla pages, and I don't understand what
exactely they are doing.

Do they bundle everything in transactions only or do they in fact use the
Async method?
It is not clear to me by reading through their articles.

It would be nice if somebody could clear me up on this issue.

Here is the article:

Lazy writing

Mozilla has relaxed the ACID requirements in order to speed up commits. In
particular, we have dropped durability. This means that when a commit
returns, you are not guaranteed that the commit has gone through. If the
power goes out right away, that commit may (or may not) be lost. However, we
still support the other (ACI) requirements. This means that the database
will not get corrupted. If the power goes out immediately after a commit,
the transaction will be like it was rolled back: the database will still be
in a consistent state.

Higher commit performance is achieved by writing to the database from a
separate thread (see
storage/src/mozStorageAsyncIO.cpp
which
is associated with the storage service in
storage/src/mozStorageService.cpp
).
The main database thread does everything exactly as it did before. However,
we have overridden the file operations and everything comes through the
AsnycIO module. This file is based on
test_async.cfrom
the sqlite distribution.

The AsyncIO module packages writes up in messages and puts them on the write
thread's message queue. This write thread waits for messages and processes
them as fast as it can. This means that writes, locking, and most
importantly, disk syncs, only block the AsyncIO thread. Reads are done
synchronously, taking into account unwritten data still in the buffer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users