Re: [sqlite] Fine tuning of Sqlite

[jerome moliere]

2011/9/6 Alexey Pechnikov :
> 2011/9/6 jerome moliere :
>> Could you give me more  details about corruption cases ?
>> Is there a list of contexts where we can get corrupted tables ?
>
> http://www.sqlite.org/howtocorrupt.html

Just one word : perfect
sorry for not having found it by myself


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

Re: [sqlite] Fine tuning of Sqlite

[Alexey Pechnikov]

2011/9/6 jerome moliere :
> Could you give me more  details about corruption cases ?
> Is there a list of contexts where we can get corrupted tables ?

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


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fine tuning of Sqlite

[jerome moliere]

>> Indicies can be very
>>> slow... may be the FTS4 table will be much faster for your queries.
>>
>> can you give more details about this concept ? what is the FTS4 table
>> ? I am sorry don't know what is is !!!
>
> See
> http://www.sqlite.org/fts3.html
> The full-text index is very fast and scalable. You can use it instead of
> a lot of btree-indicies when  you can rewrite your search queries as FTS.
>

Thanks for the pointer ,I did not notice this feature
There may be one candidate for such feature but it  's not involved in
our current performance problem ...

>>> Did you try to use in-memory database as temp storage and
>>> copy set of records into main database in single transaction?
>>
>> it's one of the goal of the benchmark but this solution has av ery
>> high level of database corruption isn't it ..so I prefer to keep it as
>> a joker...
>
> Database corruption can't be the result of the coping from temp in-memory
> database to main disk-database!


Could you give me more  details about corruption cases ?
Is there a list of contexts where we can get corrupted tables ?


Thanks for all your answers

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

Re: [sqlite] Fine tuning of Sqlite

[Alexey Pechnikov]

2011/9/6 jerome moliere :
>  Did you set corresponding pragmas
>> page_size, cache_size, journal_mode?
> page_size yes
> cache_size no because we are using a higher level cache (in Java)
> journal_mode is in the list of the pragmas to be tested through the
> benchmark setting this parameter to OFF speeds up transactions for
> around 20%

I think your cache size in smaller than the modified indicies size. Use
more big cache or reduce indicies size.

> Indicies can be very
>> slow... may be the FTS4 table will be much faster for your queries.
>
> can you give more details about this concept ? what is the FTS4 table
> ? I am sorry don't know what is is !!!

See
http://www.sqlite.org/fts3.html
The full-text index is very fast and scalable. You can use it instead of
a lot of btree-indicies when  you can rewrite your search queries as FTS.

>> Did you try to use in-memory database as temp storage and
>> copy set of records into main database in single transaction?
>
> it's one of the goal of the benchmark but this solution has av ery
> high level of database corruption isn't it ..so I prefer to keep it as
> a joker...

Database corruption can't be the result of the coping from temp in-memory
database to main disk-database!

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fine tuning of Sqlite

[jerome moliere]

Hi once again I will answer to these questions (most of them)
J.MOLIERE - Mentor/J
auteur Eyrolles





2011/9/6 Alexey Pechnikov :
> Jerome, can you show your database structure and queries?
no definetely not I can't for legal reasons..sorry
> Which FS is used?
the only one  available on Windows Mobile 6.5..

internal flash disk is used for storage because there 's no SD card
available on the devices as bought by our customers

 Did you set corresponding pragmas
> page_size, cache_size, journal_mode?
page_size yes
cache_size no because we are using a higher level cache (in Java)
journal_mode is in the list of the pragmas to be tested through the
benchmark setting this parameter to OFF speeds up transactions for
around 20%

Indicies can be very
> slow... may be the FTS4 table will be much faster for your queries.


can you give more details about this concept ? what is the FTS4 table
? I am sorry don't know what is is !!!
> Do you use a prepared queries?..
yes of course we do

Which type of storage are you
> use?

internal flash disk

Can you group a set of inserts into single transaction?

yes I previously refactored  part of this code to do such job, I
divided by 4 responses times ...
But in the meanwhile customers requested changes and we added other
inserts/selects...
> Did you try to use in-memory database as temp storage and
> copy set of records into main database in single transaction?

it's one of the goal of the benchmark but this solution has av ery
high level of database corruption isn't it ..so I prefer to keep it as
a joker...
> There are a lot of questions without answers.
>

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

Re: [sqlite] Fine tuning of Sqlite

[Alexey Pechnikov]

Jerome, can you show your database structure and queries?
Which FS is used? Did you set corresponding pragmas
page_size, cache_size, journal_mode? Indicies can be very
slow... may be the FTS4 table will be much faster for your queries.
Do you use a prepared queries?.. Which type of storage are you
use? Can you group a set of inserts into single transaction?
Did you try to use in-memory database as temp storage and
copy set of records into main database in single transaction?
There are a lot of questions without answers.

2011/9/6 jerome moliere :
> Hi Alexey,
> I thought I answered to those questions but once again :
> * my application runs on an embedded device (Honeywell 9900) , it's a
> very complex application (too much I think)
> and we are facing performance problems while inserting datas (about
> 500 objects to be inserted inducing about 2000 queries and the same
> amount of transactions because the application runs with NON EXCLUSIVE
> flag, using serializable default isolation level). As any application
> running on an embedded device it 's not well suited for multi users!!!
> The application stores data for ONE employee of the company (our
> customers)...
> We have indexes (too much I think) but as far as I know in our case
> indexing is one performance problem rather than a solution (indexing
> in an INSERT phase adds overheads..)
>
> But now my initial problem is solved , it was a problem with Xerial
> JDBC classes , sqlite manages well group of PRAGMA (where Xerial
> SQLiteConfig objects don't)
>
> I'd like to use this post to thank you because I did not see such a
> good group for a while , with people reacting quickly and with a lot
> of value added comments...
> So thank you guys ,keep this very special Open Source mind alive for a while
>
>
> I just post an entry on my blogger.com page..
> For those interested :
> http://romjethoughts.blogspot.com/2011/09/sqlite-performance-tuning.html
>
>
> kind regards
> Jerome
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fine tuning of Sqlite

[jerome moliere]

Hi Alexey,
I thought I answered to those questions but once again :
* my application runs on an embedded device (Honeywell 9900) , it's a
very complex application (too much I think)
and we are facing performance problems while inserting datas (about
500 objects to be inserted inducing about 2000 queries and the same
amount of transactions because the application runs with NON EXCLUSIVE
flag, using serializable default isolation level). As any application
running on an embedded device it 's not well suited for multi users!!!
The application stores data for ONE employee of the company (our
customers)...
We have indexes (too much I think) but as far as I know in our case
indexing is one performance problem rather than a solution (indexing
in an INSERT phase adds overheads..)

But now my initial problem is solved , it was a problem with Xerial
JDBC classes , sqlite manages well group of PRAGMA (where Xerial
SQLiteConfig objects don't)

I'd like to use this post to thank you because I did not see such a
good group for a while , with people reacting quickly and with a lot
of value added comments...
So thank you guys ,keep this very special Open Source mind alive for a while


I just post an entry on my blogger.com page..
For those interested :
http://romjethoughts.blogspot.com/2011/09/sqlite-performance-tuning.html


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

Re: [sqlite] Fine tuning of Sqlite

[Alexey Pechnikov]

2011/9/2 Simon Slavin :
>
> On 2 Sep 2011, at 7:39am, jerome moliere wrote:
>
>> I must give some real clues to my customers to fine tune Sqlite
>
> 1) What problem(s) are you trying to solve ?
>
> 2) Must your system be set up for multi-process (or multi-user) access, or 
> can we ignore all problems concerning those ?
>
> 3) Are you using transactions to batch together data changes which relate to 
> one-another ?
>
> 4) If speed is a problem, do you have indexes defined appropriate to your 
> commands ?

Really, these are nice questions! Jerome, different environments may
have different pragmas...
we don't know about your environment enough. You may show some
problematic tests as example.



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fine tuning of Sqlite

[jerome . moliere]

Thanks for your reply Pavel.I hope that the provided example has some sense 
for sqlite but with the version used and from the java layer I was not able to 
grab connections with such config...removing the journal mode off in my code 
and i get an usable connection 
Regards
Jerome
 Envoyé avec BlackBerry® d'Orange 

-Original Message-
From: Pavel Ivanov <paiva...@gmail.com>
Sender: sqlite-users-boun...@sqlite.org
Date: Fri, 2 Sep 2011 10:42:06 
To: General Discussion of SQLite Database<sqlite-users@sqlite.org>
Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Fine tuning of Sqlite

>> Do you set of PRGAMAS uncompatible ? e;g:
>> setting synchronous=true + journal_mode=off is a non sense for sqlite
>> so engine can't deliver connections
>
> I think I now see what you want: a table saying if you have PRAGMA A set to 
> THIS, then PRAGMA B has no effect.

Personally I don't know of any incompatible pragmas. And concerning
the given example synchronous=true and journal_mode=off is not a
nonsense for SQLite. It will happily work exactly as you asked -
without journal and using fsyncs. It may be or may be not a nonsense
from the application point of view, or it's better to say application
can easily break with such settings in certain situations. But it
depends on the application and for some of them it could be pretty
acceptable.


Pavel


On Fri, Sep 2, 2011 at 8:26 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 2 Sep 2011, at 8:08am, jerome moliere wrote:
>
>> I want to setup a benchmark injecting different configurations of the
>> sqlite engine and doing different queries (insert/select ) into
>> different transactional contexts over dummy data..
>> For such job I need to inject different PRAGMAs isn't it ?
>> Setting up :
>> synchronous , read_uncommitted, and so on
>
> I'm sorry but I can't help with this.  I hope someone else reading this can.
>
>> Do you set of PRGAMAS uncompatible ? e;g:
>> setting synchronous=true + journal_mode=off is a non sense for sqlite
>> so engine can't deliver connections
>
> I think I now see what you want: a table saying if you have PRAGMA A set to 
> THIS, then PRAGMA B has no effect.  I do not know of anyone who has made a 
> table like that.  You might be able to work down
>
> http://www.sqlite.org/pragma.html
>
> and make part of one yourself, but I could do no better.
>
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fine tuning of Sqlite

[Pavel Ivanov]

>> Do you set of PRGAMAS uncompatible ? e;g:
>> setting synchronous=true + journal_mode=off is a non sense for sqlite
>> so engine can't deliver connections
>
> I think I now see what you want: a table saying if you have PRAGMA A set to 
> THIS, then PRAGMA B has no effect.

Personally I don't know of any incompatible pragmas. And concerning
the given example synchronous=true and journal_mode=off is not a
nonsense for SQLite. It will happily work exactly as you asked -
without journal and using fsyncs. It may be or may be not a nonsense
from the application point of view, or it's better to say application
can easily break with such settings in certain situations. But it
depends on the application and for some of them it could be pretty
acceptable.


Pavel


On Fri, Sep 2, 2011 at 8:26 AM, Simon Slavin  wrote:
>
> On 2 Sep 2011, at 8:08am, jerome moliere wrote:
>
>> I want to setup a benchmark injecting different configurations of the
>> sqlite engine and doing different queries (insert/select ) into
>> different transactional contexts over dummy data..
>> For such job I need to inject different PRAGMAs isn't it ?
>> Setting up :
>> synchronous , read_uncommitted, and so on
>
> I'm sorry but I can't help with this.  I hope someone else reading this can.
>
>> Do you set of PRGAMAS uncompatible ? e;g:
>> setting synchronous=true + journal_mode=off is a non sense for sqlite
>> so engine can't deliver connections
>
> I think I now see what you want: a table saying if you have PRAGMA A set to 
> THIS, then PRAGMA B has no effect.  I do not know of anyone who has made a 
> table like that.  You might be able to work down
>
> http://www.sqlite.org/pragma.html
>
> and make part of one yourself, but I could do no better.
>
> 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] Fine tuning of Sqlite

[jerome moliere]

Hi Simon,
thanks for your answer
comments below
>> I want to setup a benchmark injecting different configurations of the
>> sqlite engine and doing different queries (insert/select ) into
>> different transactional contexts over dummy data..
>> For such job I need to inject different PRAGMAs isn't it ?
>> Setting up :
>> synchronous , read_uncommitted, and so on
>
> I'm sorry but I can't help with this.  I hope someone else reading this can.
>
>> Do you set of PRGAMAS uncompatible ? e;g:
>> setting synchronous=true + journal_mode=off is a non sense for sqlite
>> so engine can't deliver connections
>
> I think I now see what you want: a table saying if you have PRAGMA A set to 
> THIS, then PRAGMA B has no effect.  I do not know of anyone who has made a 
> table like that.  You might be able to work down
>

yes it's exactly what I want 
I just planned to use ethe Java layer instead of the direct setting of
pRAGMA with SQL queries(but it should be the same while reading
the code)

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

Re: [sqlite] Fine tuning of Sqlite

[Simon Slavin]

On 2 Sep 2011, at 8:08am, jerome moliere wrote:

> I want to setup a benchmark injecting different configurations of the
> sqlite engine and doing different queries (insert/select ) into
> different transactional contexts over dummy data..
> For such job I need to inject different PRAGMAs isn't it ?
> Setting up :
> synchronous , read_uncommitted, and so on

I'm sorry but I can't help with this.  I hope someone else reading this can.

> Do you set of PRGAMAS uncompatible ? e;g:
> setting synchronous=true + journal_mode=off is a non sense for sqlite
> so engine can't deliver connections

I think I now see what you want: a table saying if you have PRAGMA A set to 
THIS, then PRAGMA B has no effect.  I do not know of anyone who has made a 
table like that.  You might be able to work down

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

and make part of one yourself, but I could do no better.

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

Re: [sqlite] Fine tuning of Sqlite

[jerome moliere]

I'll give you some elements regarding your questions but there 's no
immediate link with my current problem...




2011/9/2 Simon Slavin :
>
> On 2 Sep 2011, at 7:39am, jerome moliere wrote:
>
>> I must give some real clues to my customers to fine tune Sqlite
>
> 1) What problem(s) are you trying to solve ?

inserts are very slow in my context and we are doing a lot of network
synchronizations inducing database inserts
flash memory and windows mobile are not innocents in our troubles ...

>
> 2) Must your system be set up for multi-process (or multi-user) access, or 
> can we ignore all problems concerning those ?
>

the EXCLUSIVE MODE is well adapted to our case is not set up yet but
it 's one ogf the goal of my benchmark to prove that it could have
very signifcant influence in our whole performance results
> 3) Are you using transactions to batch together data changes which relate to 
> one-another ?
>

no application is not designed in this way and I can't refactor it to
use such very nice practice
> 4) If speed is a problem, do you have indexes defined appropriate to your 
> commands ?
>

INSERT are our main problem , we are using a Java cache above the SQL
queries so select queries are not our major problem

thanks
jerome
> 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] Fine tuning of Sqlite

[jerome moliere]

Hi Simon,
thanks for your reply but try to forget the general introduction of my
context 
I want to setup a benchmark injecting different configurations of the
sqlite engine and doing different queries (insert/select ) into
different transactional contexts over dummy data..
For such job I need to inject different PRAGMAs isn't it ?
Setting up :
synchronous , read_uncommitted, and so on

Do you set of PRGAMAS uncompatible ? e;g:
setting synchronous=true + journal_mode=off is a non sense for sqlite
so engine can't deliver connections


Thanks
jerome
J.MOLIERE - Mentor/J
auteur Eyrolles





2011/9/2 Simon Slavin :
>
> On 2 Sep 2011, at 7:39am, jerome moliere wrote:
>
>> I must give some real clues to my customers to fine tune Sqlite
>
> 1) What problem(s) are you trying to solve ?
>
> 2) Must your system be set up for multi-process (or multi-user) access, or 
> can we ignore all problems concerning those ?
>
> 3) Are you using transactions to batch together data changes which relate to 
> one-another ?
>
> 4) If speed is a problem, do you have indexes defined appropriate to your 
> commands ?
>
> 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] Fine tuning of Sqlite

[Simon Slavin]

On 2 Sep 2011, at 7:39am, jerome moliere wrote:

> I must give some real clues to my customers to fine tune Sqlite

1) What problem(s) are you trying to solve ?

2) Must your system be set up for multi-process (or multi-user) access, or can 
we ignore all problems concerning those ?

3) Are you using transactions to batch together data changes which relate to 
one-another ?

4) If speed is a problem, do you have indexes defined appropriate to your 
commands ?

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

[sqlite] Fine tuning of Sqlite

[jerome moliere]

Hi all sqlite users,
I'm using Sqlite for an application using 150 Mb databases running on
an embedded device (Honeywell Dolphin 9900) into an OSGi Java context.
I must give some real clues to my customers to fine tune Sqlite, so I
discovered among different ways to have better results PRAGMAs...
I wrote an OSGi application used to inject set of configurations and
running different tests : inserts, inserts into many transactions,
read, may be different threads in the future...
It could work fine but it seems that most  sets of configurations used
induce null connections...
Example given I can have connections with SYNCHRONOUS_MODE but I can't
turn off JOIURNAL_MODE neither set the SHARED_CACHE and set the
READ_UNCOMMITTED isolation level...
So I wondered if theer was an (un)official matrix of (un)compatible PRAGMAs...

I am using for my benchmark demo the Xerial JDBC driver (layer above
org.sqlite JDBC driver)

Thanks for your feedback
kind regards

J.MOLIERE - Mentor/J
auteur Eyrolles
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users