Re: [sqlite] how can I disable all locking for max SELECT speed?

[Jay A. Kreibich]

On Sun, Mar 18, 2012 at 07:50:44PM +0100, Dietmar Hofer scratched on the wall:
> I use sqlite in a mobile app with a database in readonly mode.
> One operation requires a lot of select-queries and should be
> optimized as much as possible.

> I can guarantee that no concurrent write / read accesses take place.
> How can I remove all locking overhead from this queries?

  Grab the lock and never let go:
  
http://sqlite.org/pragma.html#pragma_locking_mode


  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how can I disable all locking for max SELECT speed?

[Dietmar Hofer]

I use sqlite in a mobile app with a database in readonly mode.
One operation requires a lot of select-queries and should be optimized 
as much as possible.
Profiling shows me that when scrolling the cursor with sqlite3_step(), 
most time is spent in sqlite3BtreeBeginTrans->sqlite3PagerSharedLock.
I don't know the sqlite code, but to me that looks like some locking is 
done for every query.


I can guarantee that no concurrent write / read accesses take place. How 
can I remove all locking overhead from this queries?

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

Re: [sqlite] Do we need locking with multithread off ?

[Dan Kennedy]

On 03/18/2012 01:12 PM, Arnav Aggarwal wrote:

On Sun, Mar 18, 2012 at 11:19 AM, Dan Kennedy  wrote:


  On 03/18/2012 11:26 AM, Arnav Aggarwal wrote:


On Sat, Mar 17, 2012 at 12:07 PM, Arnav Aggarwal<
arnav.aggarwal.2...@gmail.com>   wrote:




  On Sat, Mar 17, 2012 at 11:51 AM, Roger Binns**
wrote:

-BEGIN PGP SIGNED MESSAGE-

Hash: SHA1

On 16/03/12 22:50, Arnav Aggarwal wrote:


I don't have any choice of using a different filesystem..



I'd suggest you write your own VFS then.  You can choose exactly how
locking is done.  It isn't that much work and you can map to the exact
semantics of the filesystem rather than pretending it is unix which it
evidently isn't.


My system behaves quite similar to that of  unix and mostly POSIX

compliant. But, "fcntl" locks are not supported.
Is there any known problem using a "unix-dotfile" vfs ?


  In such a scenario, can I safely delete the lock file and journal file

(if size 0) ?



Depends on why they are size zero.  If your crummy filesystem doesn't
implement barriers correctly then it is quite possible that they
shouldn't
be zero length.

(I'm assuming your want your database to survive unexpected power
failures)


May be I can leave the journal files as it is. I believe sqlite code can

take care of them.
But, lock directories must be deleted else the application fails to
start.
Can these be safely deleted at apllication start up before opening the
database ?



Guys, Sorry for being impatient and posting this again.

Any comments on this issue ?

Just to summarize, I am using "dotfile" locking. Only one instance of
application will run (it may be multithreaded). The sqlite code is
statically linked with the application. This is the only application that
can access the database file.
On application crash, lockfiles are sometimes not removed due to which
application fail to restart.

My question, can I safely remove the lockfile on application startup
before
opening the database and before creating any thread ?



If, at some point, you are sure that no processes have
the database file open, you can safely delete any old
lock files.

Don't ever delete a journal or wal file though of course.a


Thanks a lot Dan for confirming this. I will remove the code to delete the
0 byte sized journal file.


A zero byte file you could safely delete (provided you can
guarantee that no other database clients exist). Although SQLite
should delete it next time a transaction is committed anyhow
so it may not be worth bothering with.

Deleting any larger journal (that may contain rollback
information from an aborted transaction) may corrupt the db
though.



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

Re: [sqlite] Do we need locking with multithread off ?

[Arnav Aggarwal]

On Sun, Mar 18, 2012 at 11:19 AM, Dan Kennedy  wrote:

>  On 03/18/2012 11:26 AM, Arnav Aggarwal wrote:
>
>> On Sat, Mar 17, 2012 at 12:07 PM, Arnav Aggarwal<
>> arnav.aggarwal.2...@gmail.com>  wrote:
>>
>>
>>>
>>>  On Sat, Mar 17, 2012 at 11:51 AM, Roger Binns**
>>> wrote:
>>>
>>> -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 On 16/03/12 22:50, Arnav Aggarwal wrote:

> I don't have any choice of using a different filesystem..
>

 I'd suggest you write your own VFS then.  You can choose exactly how
 locking is done.  It isn't that much work and you can map to the exact
 semantics of the filesystem rather than pretending it is unix which it
 evidently isn't.


 My system behaves quite similar to that of  unix and mostly POSIX
>>> compliant. But, "fcntl" locks are not supported.
>>> Is there any known problem using a "unix-dotfile" vfs ?
>>>
>>>
>>>  In such a scenario, can I safely delete the lock file and journal file
> (if size 0) ?
>

 Depends on why they are size zero.  If your crummy filesystem doesn't
 implement barriers correctly then it is quite possible that they
 shouldn't
 be zero length.

 (I'm assuming your want your database to survive unexpected power
 failures)


 May be I can leave the journal files as it is. I believe sqlite code can
>>> take care of them.
>>> But, lock directories must be deleted else the application fails to
>>> start.
>>> Can these be safely deleted at apllication start up before opening the
>>> database ?
>>>
>>>
>>>
>>> Guys, Sorry for being impatient and posting this again.
>> Any comments on this issue ?
>>
>> Just to summarize, I am using "dotfile" locking. Only one instance of
>> application will run (it may be multithreaded). The sqlite code is
>> statically linked with the application. This is the only application that
>> can access the database file.
>> On application crash, lockfiles are sometimes not removed due to which
>> application fail to restart.
>>
>> My question, can I safely remove the lockfile on application startup
>> before
>> opening the database and before creating any thread ?
>>
>
> If, at some point, you are sure that no processes have
> the database file open, you can safely delete any old
> lock files.
>
> Don't ever delete a journal or wal file though of course.


Thanks a lot Dan for confirming this. I will remove the code to delete the
0 byte sized journal file.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users