Re: [sqlite] PRAGMA user_version

[Igor Tandetnik]

On 9/2/2011 11:02 PM, Walter wrote:

Is there any way to get the user_version from an Attached database


PRAGMA attachedName.user_version;

--
Igor Tandetnik

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

[sqlite] PRAGMA user_version

[Walter]

Hi All

Is there any way to get the user_version from an Attached database

PRAGMA user_version only get it from the Opened database.

If not perhaps a request for it.

Thank you

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

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

[Simon Slavin]

On 2 Sep 2011, at 6:34pm, Terry Cumaranatunge wrote:

> This does suggest that we should re-evaluate our design and not open as many 
> databases from a single process. The original motivation was to limit the 
> size the database for performance reasons, which resulted in a large number 
> of database files. I think we are going to try creating multiple tables per 
> database and evaluate its performance.
>  
> Are there any general guidelines on a tested limit on the number of tables in 
> a database or a general rule of thumb on the maximum recommended size for a 
> database file?

I'm concerned that you might be involved in premature optimization.  The 
performance of SQLite does not greatly degrade with larger tables or larger 
databases.  In fact you might be slowing your system down more by trying to 
arrange multiple databases.  Have you tried just using SQLite in the naive way 
-- with one big table in one database -- and seeing whether that provides a 
solution too slow for you ?

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

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

[Terry Cumaranatunge]

Thanks everyone for your responses.
 
We modified the sqlite findReusableFd function call to return 0 immediately 
(i.e. no file descriptor to reuse) and it significantly improved the 
performance. The time to open 20K db's went down from 75 secs to 24 secs. The 
modification to findReusableFd works for us because we don't plan on opening 
multiple connections to the same database from one process.
 
This does suggest that we should re-evaluate our design and not open as many 
databases from a single process. The original motivation was to limit the size 
the database for performance reasons, which resulted in a large number of 
database files. I think we are going to try creating multiple tables per 
database and evaluate its performance.
 
Are there any general guidelines on a tested limit on the number of tables in a 
database or a general rule of thumb on the maximum recommended size for a 
database file?

From: Pavel Ivanov 
>To: General Discussion of SQLite Database 
>Sent: Friday, September 2, 2011 9:28 AM
>Subject: Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens 
>increase
>
>> Does anyone know why the performance degrades this way and what can be done
>> to resolve the problem?
>
>Do you have by any chance shared cache turned on? I believe when
>shared cache is used SQLite searches through all open databases to
>understand if the one you want to open is already opened and available
>for cache reuse.
>
>Also I believe there's some similar search happens through all open
>file descriptors in unix VFS. So that if several file descriptors to
>the same file are open there was no issues with dropping advisory
>locks on one descriptor while second is closing. And data below seem
>to prove that this is the problem for you.
>
>> I don't see the times you're seeing using this program...
>> time ./db 5000
>> 1.602s
>>
>> time ./db 1
>> 5.357s
>>
>> time ./db 15000
>> 11.280s
>>
>> time ./db 2
>> 19.570s
>>
>> time ./db 25000
>> 28.166s
>
>Michael,
>So in your test first 5000 are opened in 1.6 seconds and last 5000 are
>opened in 8.6 seconds. I'd say it's a noticeable increase that can't
>be attributed to OS.
>
>> Each sample counts as 0.01 seconds.
>>  %   cumulative   self              self     total
>>  time   seconds   seconds    calls  us/call  us/call  name
>>  52.06      0.76     0.76     5000   152.00   152.00  findInodeInfo
>>  43.84      1.40     0.64     5000   128.00   128.00  findReusableFd
>
>That's exactly what I talked about above. Roughly speaking
>findReusableFd searches through all open file descriptors to
>understand if the process already have some for the file you want to
>open. Or more precisely it looks for fd for the same file which is
>still open when corresponding sqlite3* connection is already closed
>(and yes if you open 5 connections to the same file and then close 4
>of them SQLite will still keep 5 file descriptors open to avoid
>problems with POSIX advisory locks).
>
>
>Pavel
>
>
>On Fri, Sep 2, 2011 at 10:08 AM, Black, Michael (IS)
> wrote:
>> I profiled the sqlite3 test program below and I get this for 25,000 files
>>
>>
>>
>> Flat profile:
>>
>> Each sample counts as 0.01 seconds.
>>  %   cumulative   self              self     total
>>  time   seconds   seconds    calls  ms/call  ms/call  name
>>  61.15     17.61    17.61    25000     0.70     0.70  findInodeInfo
>>  36.67     28.17    10.56    25000     0.42     0.42  findReusableFd
>>
>> For 5,000 files looks like this:
>>
>> Each sample counts as 0.01 seconds.
>>  %   cumulative   self              self     total
>>  time   seconds   seconds    calls  us/call  us/call  name
>>  52.06      0.76     0.76     5000   152.00   152.00  findInodeInfo
>>  43.84      1.40     0.64     5000   128.00   128.00  findReusableFd
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> NG Information Systems
>>
>> Advanced Analytics Directorate
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
>> behalf of Black, Michael (IS) [michael.bla...@ngc.com]
>> Sent: Friday, September 02, 2011 8:00 AM
>> To: General Discussion of SQLite Database
>> Subject: EXT :Re: [sqlite] sqlite3_open_v2 performance degrades as number of 
>> opens increase
>>
>> I assume you've overridden the system default for 1024 files in ulimit for # 
>> of open files?
>>
>> I don't see the times you're seeing using this program...though my databases 
>> are empty which probably makes a difference.
>> I do see the gradual increase in time...I think this is almost all due to 
>> the OS when you try and open thousands of files in one process.
>>
>> I ran this once to create 25,000 databases
>> ./db 25000
>> ls -l file* | wc -l
>> 25000
>> The tested opening them
>> time ./db 5000
>> 1.602s
>>
>> time ./db 1
>> 5.357s
>>
>> time ./db 15000
>> 11.280s
>>
>> time ./db 2
>> 19.570s
>>
>> time ./db 

Re: [sqlite] Track DML (from certain clients only)

[Tim Streater]

On 02 Sep 2011 at 16:58, Stephan Beal  wrote: 

> On Fri, Sep 2, 2011 at 5:32 PM, Frans Knibbe  wrote:
>
>> If SQLite could log this statement including the comment, it would still be
>> possible to distinguish sources. Or reject the logging of statement with a
>> trigger similar to the one you proposed..
>>
>
> Why not just write a small wrapper function which does the logging and
> executes the statements? It could take additional arguments, e.g. a symbolic
> name for the source of the query (e.g. "app2.funcA()").

I use a wrapper function anyway, for these reasons:

1) So I can use try/catch. That allows me to handle any errors I may get in 
SQLite properly - logging the error and cleaning up.

2) So I can pass a small string which, when logged with any error message, can 
locate where the error occurred.


This would also allow me to implement a (circular) traceback buffer of 
statements.

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

Re: [sqlite] Speeding up index creation

[Dan Kennedy]

On 09/02/2011 07:32 PM, Filip Navara wrote:

On Fri, Sep 2, 2011 at 11:04 AM, Filip Navara  wrote:
*snip*

The time to create an index on my 266 Mb experimental database is more
than 9 minutes.

*snip*

I erroneously measured the time with DEBUG build, so I've rerun the
experiment with several SQLite versions:

[2869ed2829] Leaf: Avoid using uninitialized variables after failures
in the merge sort code. (user: drh, tags: trunk)
CPU Time: user 107.359888 sys 135.050066

[7769fb988d] Instead of a temporary b-tree, use a linked-list and
merge-sort to sort records in main memory in vdbesort.c. (user: dan,
tags: merge-sort)
CPU Time: user 118.451559 sys 132.117247

[71075673c6] Leaf: If all data being sorted fits in memory, avoid
writing any data out to temporary files in vdbesort.c. (user: dan,
tags: merge-sort)
CPU Time: user 116.813549 sys 132.710051


Thanks for doing this. There is (hopefully) a fix for the performance
regression up now.

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

Re: [sqlite] Speeding up index creation

[Dan Kennedy]

On 09/02/2011 09:24 PM, Simon Slavin wrote:


On 2 Sep 2011, at 10:04am, Filip Navara wrote:


The time to create an index on my 266 Mb experimental database is more
than 9 minutes. The database is available at
http://www.emclient.com/temp/mail_index.zip and the command I use to
create the index is

  CREATE INDEX "idx_MailAddresses_address" ON "MailAddresses" ("type",
"address", "parentId");

I had run the shell under profiler


Strangely, on my Mac running the shell tool provided with OS X 10.7.1, SQLite 
3.7.5, there seems to be a problem.  It's still going after more than 2 hours.

Loading your database and running your CREATE INDEX command, the application 
only seems to be using about 1% of one of my CPUs.  I looked to see if it was 
i/o bound instead of CPU bound but it seems only to be reading 320KB/s and my 
computer can handle a lot more than that.  (All above figures from Activity 
Monitor.)


We were just wondering a half hour ago how long this would
take with 3.7.7. Thanks!

Released versions of SQLite build an index by inserting
all values from the indexed column(s) in whatever order
they appear in the table (i.e. unsorted order) into a new
b-tree. This is fine if the index b-tree you are constructing
fits in the cache.

If it doesn't fit in the cache you have a problem. Each
time you go to insert a new entry into the b-tree you have
to find the leaf page that the new entry will be added to.
Since your b-tree doesn't fit in the cache, odds are that
this means reading the page from the file-system. And since
you are inserting in arbitrary order, the page could be
anywhere in the database (or WAL) file. In the worst case,
if your page is not cached in OS memory, you may even have
to shift the disk arm to get at it. Way slow.

The result is that reading data from disk becomes the
bottleneck when writing unsorted values to a b-tree. Hence
your 1% CPU measurement.

The new version uses a merge-sort to sort all the index
entries before it inserts them into the b-tree. This way
it doesn't matter if your b-tree is larger than the cache,
as you are always inserting into the right-most leaf node.
No need to go searching through the file-system/disk for
pages while building the b-tree.

Dan.





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

Re: [sqlite] Track DML (from certain clients only)

[Stephan Beal]

On Fri, Sep 2, 2011 at 5:32 PM, Frans Knibbe  wrote:

> If SQLite could log this statement including the comment, it would still be
> possible to distinguish sources. Or reject the logging of statement with a
> trigger similar to the one you proposed..
>

Why not just write a small wrapper function which does the logging and
executes the statements? It could take additional arguments, e.g. a symbolic
name for the source of the query (e.g. "app2.funcA()").


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Track DML (from certain clients only)

[Black, Michael (IS)]

You will, of course, be slowing down your app to do this but if all you're 
wanting to do is log SQL statements then sqlite3_profile sounds like the way to 
go (although it is subject to change).



All you would do is open your own database connection inside your callback and 
insert the SQL string passed in.

You could add or refer to the process id or name if you want to filter things.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Frans Knibbe [frans.kni...@geodan.nl]
Sent: Friday, September 02, 2011 10:32 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Track DML (from certain clients only)

Hello Michael,

I am sorry for being unclear, I will try to explain:

What would work is each application writing its own DML to a trace
table. For example, Application A might do this (in pseudocode):

SqlStatement = "insert into tab values (1, 'hello')";
SqlLite.Execute(SqlStatement);
LogStatement = "insert into dml_tab values ('" || SqlStatement || "')";
SqlLite.Execute(LogStatement);

Note that in this case an identification of the application would not
have to be written to the DML log table, because I only need to
distinguish between the P2P module and all other applications as sources
of DML. If the P2P module does not write to the DML log table, all
entries will be made by the other applications.

But I think it would be nicer if each application would not have to
implement tracing like this, if it could just issue a statement and the
database would capture the statement and put it in a table. So in
Application A the only code would be:

SqlStatement = "insert into tab values (1, 'hello')";
SqlLite.Execute(SqlStatement);

Couldn't a callback function registered with sqlite3_trace put
statements in a table?  With some kind of identifier of the source of
the statement?

I was just thinking about this some more.. Perhaps it is possible to log
statements but not their source. In that case, could applications insert
comments for identification? Application A could issue the following
statement

insert into tab values (1, 'hello') /* $$$ application A $$$*/;

If SQLite could log this statement including the comment, it would still
be possible to distinguish sources. Or reject the logging of statement
with a trigger similar to the one you proposed..

Greetings,
Frans



On 2011-09-02 16:51, Black, Michael (IS) wrote:
> Maybe I don't understand your setup completely...but can't you have your 
> applications send a string identifying who they are?  Like the 1st argument 
> to whatever function you're setting up?
>
>
>
> I don't think sqlite3_trace is going to let you stop or modify the SQL insert 
> or suchall you could do is watch the SQL fly by
>
>
>
> Maybe if you could explain your API a bit better that would help.  You say 
> you don't want application to have to insert rows but you said you want only 
> rows from certain processes to insertso I'm confusedif your 
> applications don't insert rows what exactly is the problem?
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Frans Knibbe [frans.kni...@geodan.nl]
> Sent: Friday, September 02, 2011 9:16 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Track DML (from certain clients only)
>
> Hello Michael,
>
> Thanks for the quick response. Yes, I could add a field to put the
> source string in. But I would not like to bother applications with
> having to insert rows in the DML table. I would prefer that to happen
> automatically because I want to prevent entanglement of separate modules.
>
> I have just read about the sqlite3_trace function. That might be what I
> am looking for, although it might be a tough job getting it to do what I
> want (with me not knowing C). For now I am not concerned yet with making
> it actually happen. My project is a pilot study.  But I would like to
> make sure that it is really possible, i.e. that the building blocks are
> there. So I wonder if a hypothetical callback function that is
> registered with sqlite3_trace would have access to some kind of
> identifier of the program or process that has executed each SQL statement?
>
> Regards,
> Frans
>
> On 2011-09-02 12:58, Black, Michael (IS) wrote:
>> If you can add a field that you can put a source string in you can do this:
>>
>> create table dml (dmlstuff text, source text);
>> create trigger before insert on dml
>> for each row when new.source not like 'p2p'
>> begin
>>select raise(rollback,'Not p2p source');
>> end;
>>
>> sqlite>   insert into dml values('dml1','p2p');
>> sqlite>   select * from dml;
>> dml1|p2p
>> sqlite>   insert into dml values('dml2','other');
>> 

Re: [sqlite] Speeding up index creation

[Simon Slavin]

On 2 Sep 2011, at 4:38pm, Filip Navara wrote:

> With SQLite 3.7.7 and older the index creation takes eons since the
> file is in WAL mode and the journal file grows uncontrollably.

Yeah.  It was 5.1Gig, and the underlying database was only 266Meg.  Heh.

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

Re: [sqlite] Speeding up index creation

[Filip Navara]

On Fri, Sep 2, 2011 at 5:23 PM, Simon Slavin  wrote:
>
> On 2 Sep 2011, at 3:24pm, Simon Slavin wrote:
>
>> On 2 Sep 2011, at 10:04am, Filip Navara wrote:
>>
>>> The time to create an index on my 266 Mb experimental database is more
>>> than 9 minutes. The database is available at
>>> http://www.emclient.com/temp/mail_index.zip and the command I use to
>>> create the index is
>>>
>>> CREATE INDEX "idx_MailAddresses_address" ON "MailAddresses" ("type",
>>> "address", "parentId");
>>>
>>> I had run the shell under profiler
>>
>> Strangely, on my Mac running the shell tool provided with OS X 10.7.1, 
>> SQLite 3.7.5, there seems to be a problem.  It's still going after more than 
>> 2 hours.
>
> I correct myself: it had finished.  It just wasn't showing the next prompt, 
> for some reason.  After force-quitting and restarting the shell it showed the 
> index as existing and the index worked.
>
> Simon.

With SQLite 3.7.7 and older the index creation takes eons since the
file is in WAL mode and the journal file grows uncontrollably. Since I
run it on small SSD I was never able to let it finish, it always
filled up the disk first.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Track DML (from certain clients only)

[Frans Knibbe]

Hello Michael,

I am sorry for being unclear, I will try to explain:

What would work is each application writing its own DML to a trace 
table. For example, Application A might do this (in pseudocode):


SqlStatement = "insert into tab values (1, 'hello')";
SqlLite.Execute(SqlStatement);
LogStatement = "insert into dml_tab values ('" || SqlStatement || "')";
SqlLite.Execute(LogStatement);

Note that in this case an identification of the application would not 
have to be written to the DML log table, because I only need to 
distinguish between the P2P module and all other applications as sources 
of DML. If the P2P module does not write to the DML log table, all 
entries will be made by the other applications.


But I think it would be nicer if each application would not have to 
implement tracing like this, if it could just issue a statement and the 
database would capture the statement and put it in a table. So in 
Application A the only code would be:


SqlStatement = "insert into tab values (1, 'hello')";
SqlLite.Execute(SqlStatement);

Couldn't a callback function registered with sqlite3_trace put 
statements in a table?  With some kind of identifier of the source of 
the statement?


I was just thinking about this some more.. Perhaps it is possible to log 
statements but not their source. In that case, could applications insert 
comments for identification? Application A could issue the following 
statement


insert into tab values (1, 'hello') /* $$$ application A $$$*/;

If SQLite could log this statement including the comment, it would still 
be possible to distinguish sources. Or reject the logging of statement 
with a trigger similar to the one you proposed..


Greetings,
Frans



On 2011-09-02 16:51, Black, Michael (IS) wrote:

Maybe I don't understand your setup completely...but can't you have your 
applications send a string identifying who they are?  Like the 1st argument to 
whatever function you're setting up?



I don't think sqlite3_trace is going to let you stop or modify the SQL insert 
or suchall you could do is watch the SQL fly by



Maybe if you could explain your API a bit better that would help.  You say you 
don't want application to have to insert rows but you said you want only rows 
from certain processes to insertso I'm confusedif your applications 
don't insert rows what exactly is the problem?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Frans Knibbe [frans.kni...@geodan.nl]
Sent: Friday, September 02, 2011 9:16 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Track DML (from certain clients only)

Hello Michael,

Thanks for the quick response. Yes, I could add a field to put the
source string in. But I would not like to bother applications with
having to insert rows in the DML table. I would prefer that to happen
automatically because I want to prevent entanglement of separate modules.

I have just read about the sqlite3_trace function. That might be what I
am looking for, although it might be a tough job getting it to do what I
want (with me not knowing C). For now I am not concerned yet with making
it actually happen. My project is a pilot study.  But I would like to
make sure that it is really possible, i.e. that the building blocks are
there. So I wonder if a hypothetical callback function that is
registered with sqlite3_trace would have access to some kind of
identifier of the program or process that has executed each SQL statement?

Regards,
Frans

On 2011-09-02 12:58, Black, Michael (IS) wrote:

If you can add a field that you can put a source string in you can do this:

create table dml (dmlstuff text, source text);
create trigger before insert on dml
for each row when new.source not like 'p2p'
begin
   select raise(rollback,'Not p2p source');
end;

sqlite>   insert into dml values('dml1','p2p');
sqlite>   select * from dml;
dml1|p2p
sqlite>   insert into dml values('dml2','other');
Error: Not p2p source
sqlite>   select * from dml;
dml1|p2p



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Frans Knibbe [frans.kni...@geodan.nl]
Sent: Friday, September 02, 2011 4:21 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Track DML (from certain clients only)


Hello all,

I am trying to use SQLite as a data store for a P2P (peer to peer)
system. There could be several applications reading from and writing to
the database. One of these is the P2P module. It reads local additions
to the database (only INSERTs are allowed, rows are never DELETEd or
UPDATEd) and distributes to to other peers. The P2P module also insert
data it receives from other peers.

I would like to be able to distinguish data that has been written by the
P2P 

Re: [sqlite] Speeding up index creation

[Simon Slavin]

On 2 Sep 2011, at 3:24pm, Simon Slavin wrote:

> On 2 Sep 2011, at 10:04am, Filip Navara wrote:
> 
>> The time to create an index on my 266 Mb experimental database is more
>> than 9 minutes. The database is available at
>> http://www.emclient.com/temp/mail_index.zip and the command I use to
>> create the index is
>> 
>> CREATE INDEX "idx_MailAddresses_address" ON "MailAddresses" ("type",
>> "address", "parentId");
>> 
>> I had run the shell under profiler
> 
> Strangely, on my Mac running the shell tool provided with OS X 10.7.1, SQLite 
> 3.7.5, there seems to be a problem.  It's still going after more than 2 hours.

I correct myself: it had finished.  It just wasn't showing the next prompt, for 
some reason.  After force-quitting and restarting the shell it showed the index 
as existing and the index worked.

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]

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 
Sender: sqlite-users-boun...@sqlite.org
Date: Fri, 2 Sep 2011 10:42:06 
To: General Discussion of SQLite Database
Reply-To: General Discussion of SQLite Database 
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  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] Track DML (from certain clients only)

[Black, Michael (IS)]

Maybe I don't understand your setup completely...but can't you have your 
applications send a string identifying who they are?  Like the 1st argument to 
whatever function you're setting up?



I don't think sqlite3_trace is going to let you stop or modify the SQL insert 
or suchall you could do is watch the SQL fly by



Maybe if you could explain your API a bit better that would help.  You say you 
don't want application to have to insert rows but you said you want only rows 
from certain processes to insertso I'm confusedif your applications 
don't insert rows what exactly is the problem?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Frans Knibbe [frans.kni...@geodan.nl]
Sent: Friday, September 02, 2011 9:16 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Track DML (from certain clients only)

Hello Michael,

Thanks for the quick response. Yes, I could add a field to put the
source string in. But I would not like to bother applications with
having to insert rows in the DML table. I would prefer that to happen
automatically because I want to prevent entanglement of separate modules.

I have just read about the sqlite3_trace function. That might be what I
am looking for, although it might be a tough job getting it to do what I
want (with me not knowing C). For now I am not concerned yet with making
it actually happen. My project is a pilot study.  But I would like to
make sure that it is really possible, i.e. that the building blocks are
there. So I wonder if a hypothetical callback function that is
registered with sqlite3_trace would have access to some kind of
identifier of the program or process that has executed each SQL statement?

Regards,
Frans

On 2011-09-02 12:58, Black, Michael (IS) wrote:
> If you can add a field that you can put a source string in you can do this:
>
> create table dml (dmlstuff text, source text);
> create trigger before insert on dml
> for each row when new.source not like 'p2p'
> begin
>   select raise(rollback,'Not p2p source');
> end;
>
> sqlite>  insert into dml values('dml1','p2p');
> sqlite>  select * from dml;
> dml1|p2p
> sqlite>  insert into dml values('dml2','other');
> Error: Not p2p source
> sqlite>  select * from dml;
> dml1|p2p
>
>
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
>
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Frans Knibbe [frans.kni...@geodan.nl]
> Sent: Friday, September 02, 2011 4:21 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Track DML (from certain clients only)
>
>
> Hello all,
>
> I am trying to use SQLite as a data store for a P2P (peer to peer)
> system. There could be several applications reading from and writing to
> the database. One of these is the P2P module. It reads local additions
> to the database (only INSERTs are allowed, rows are never DELETEd or
> UPDATEd) and distributes to to other peers. The P2P module also insert
> data it receives from other peers.
>
> I would like to be able to distinguish data that has been written by the
> P2P module from data that has been written by other local applications.
> The latter data need to be handled by the P2P module, the former not
> (otherwise the data would de replicated over and over again).
>
> I thought I could make use of a table that stores all DML statements
> from all applications except the P2P module. The P2P module could use
> that table as a task list, and periodically check if there is any
> unhandled DML in that table. All other applications could be made to
> write their DML to that table, but that is not very elegant. I would
> rather like this to be done automatically.
>
> Is it possible to create a process (a trigger maybe) that monitors all
> DML statements and writes them to a table, except when the statement is
> issued by a specific application?
>
> Or are there smarter ways of doing what I am trying to do?
>
> Thanks in advance!
>
> Frans
> ___
> 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
___
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] sqlite3_open_v2 performance degrades as number of opens increase

[Jay A. Kreibich]

On Fri, Sep 02, 2011 at 06:30:57AM -0500, Terry Cumaranatunge scratched on the 
wall:
> Hello,
> 
> We have an application that creates many small databases (over 100K) to be
> able to control the size of the database and provide more deterministic
> performance. At process startup, it opens many of these databases in a loop
> to keep them open for faster transaction response times. The behavior we are
> noticing is that the it takes progressively a longer time for each
> sqlite3_open_v2 to complete as the number of databases kept
> opened increases. These are some of the measurements:
> 
> 5000 DBs = 3 secs
> 1 DBs = 11 secs
> 15000 DBs = 35 secs
> 2 DBs = 75 secs
> 
> Many processes can concurrently open 5000 db's at the same time and it takes
> about the same time as a single process doing the work. So, it doesn't
> appear to be related to the OS related issue with the number of opens.

  I'd guess the issue is OS related, but is at the process level, not
  the system level.  For example, if a process holds open file
  descriptors in an array, many operations (such as scanning for the
  lowest unused descriptor number) are going to be O(n).

  I would run a quick test that just calls the system level open(2)
  type call, and see if you observe the same type of slow-down.

   -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

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

[Pavel Ivanov]

> Does anyone know why the performance degrades this way and what can be done
> to resolve the problem?

Do you have by any chance shared cache turned on? I believe when
shared cache is used SQLite searches through all open databases to
understand if the one you want to open is already opened and available
for cache reuse.

Also I believe there's some similar search happens through all open
file descriptors in unix VFS. So that if several file descriptors to
the same file are open there was no issues with dropping advisory
locks on one descriptor while second is closing. And data below seem
to prove that this is the problem for you.

> I don't see the times you're seeing using this program...
> time ./db 5000
> 1.602s
>
> time ./db 1
> 5.357s
>
> time ./db 15000
> 11.280s
>
> time ./db 2
> 19.570s
>
> time ./db 25000
> 28.166s

Michael,
So in your test first 5000 are opened in 1.6 seconds and last 5000 are
opened in 8.6 seconds. I'd say it's a noticeable increase that can't
be attributed to OS.

> Each sample counts as 0.01 seconds.
>  %   cumulative   self              self     total
>  time   seconds   seconds    calls  us/call  us/call  name
>  52.06      0.76     0.76     5000   152.00   152.00  findInodeInfo
>  43.84      1.40     0.64     5000   128.00   128.00  findReusableFd

That's exactly what I talked about above. Roughly speaking
findReusableFd searches through all open file descriptors to
understand if the process already have some for the file you want to
open. Or more precisely it looks for fd for the same file which is
still open when corresponding sqlite3* connection is already closed
(and yes if you open 5 connections to the same file and then close 4
of them SQLite will still keep 5 file descriptors open to avoid
problems with POSIX advisory locks).


Pavel


On Fri, Sep 2, 2011 at 10:08 AM, Black, Michael (IS)
 wrote:
> I profiled the sqlite3 test program below and I get this for 25,000 files
>
>
>
> Flat profile:
>
> Each sample counts as 0.01 seconds.
>  %   cumulative   self              self     total
>  time   seconds   seconds    calls  ms/call  ms/call  name
>  61.15     17.61    17.61    25000     0.70     0.70  findInodeInfo
>  36.67     28.17    10.56    25000     0.42     0.42  findReusableFd
>
> For 5,000 files looks like this:
>
> Each sample counts as 0.01 seconds.
>  %   cumulative   self              self     total
>  time   seconds   seconds    calls  us/call  us/call  name
>  52.06      0.76     0.76     5000   152.00   152.00  findInodeInfo
>  43.84      1.40     0.64     5000   128.00   128.00  findReusableFd
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Black, Michael (IS) [michael.bla...@ngc.com]
> Sent: Friday, September 02, 2011 8:00 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] sqlite3_open_v2 performance degrades as number of 
> opens increase
>
> I assume you've overridden the system default for 1024 files in ulimit for # 
> of open files?
>
> I don't see the times you're seeing using this program...though my databases 
> are empty which probably makes a difference.
> I do see the gradual increase in time...I think this is almost all due to the 
> OS when you try and open thousands of files in one process.
>
> I ran this once to create 25,000 databases
> ./db 25000
> ls -l file* | wc -l
> 25000
> The tested opening them
> time ./db 5000
> 1.602s
>
> time ./db 1
> 5.357s
>
> time ./db 15000
> 11.280s
>
> time ./db 2
> 19.570s
>
> time ./db 25000
> 28.166s
>
> #include 
> #include 
> #include 
> #include "sqlite3.h"
>
> int main(int argc,char *argv[])
> {
>  int i,rc;
>  sqlite3 *db;
>  for(i=0;i    char name[4096];
>    sprintf(name,"file%d",i);
>    
> rc=sqlite3_open_v2(name,,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_NOMUTEX,NULL);
>    if (rc != SQLITE_OK) {
>        printf("%s",sqlite3_errmsg(db));
>        exit(1);
>    }
>    //sqlite3_close(db); // if you close the database it runs a LOT faster.  
> So I don't think it's sqlite3 causing it.
>  }
>  return 0;
> }
>
> If you run this you'll see similar behavior
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
>
> int main(int argc,char *argv[])
> {
>  int i;
>  int fd;
>  for(i=0;i    char name[4096];
>    sprintf(name,"%dfile",i);
>    fd=open(name,O_RDWR|O_CREAT);
>    if (fd==-1) perror("open error:");
>    write(fd,name,strlen(name));
>    //close(fd); // again it runs a lot faster if you close the file 
> descriptor each time -- so it's the OS slowing you down.
>    fsync(fd);
>  }
>  return 0;
> }
>
>
>
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
>
> From: sqlite-users-boun...@sqlite.org 

Re: [sqlite] Speeding up index creation

[Simon Slavin]

On 2 Sep 2011, at 10:04am, Filip Navara wrote:

> The time to create an index on my 266 Mb experimental database is more
> than 9 minutes. The database is available at
> http://www.emclient.com/temp/mail_index.zip and the command I use to
> create the index is
> 
>  CREATE INDEX "idx_MailAddresses_address" ON "MailAddresses" ("type",
> "address", "parentId");
> 
> I had run the shell under profiler

Strangely, on my Mac running the shell tool provided with OS X 10.7.1, SQLite 
3.7.5, there seems to be a problem.  It's still going after more than 2 hours.

Loading your database and running your CREATE INDEX command, the application 
only seems to be using about 1% of one of my CPUs.  I looked to see if it was 
i/o bound instead of CPU bound but it seems only to be reading 320KB/s and my 
computer can handle a lot more than that.  (All above figures from Activity 
Monitor.)

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

Re: [sqlite] Track DML (from certain clients only)

[Frans Knibbe]

Hello Michael,

Thanks for the quick response. Yes, I could add a field to put the 
source string in. But I would not like to bother applications with 
having to insert rows in the DML table. I would prefer that to happen 
automatically because I want to prevent entanglement of separate modules.


I have just read about the sqlite3_trace function. That might be what I 
am looking for, although it might be a tough job getting it to do what I 
want (with me not knowing C). For now I am not concerned yet with making 
it actually happen. My project is a pilot study.  But I would like to 
make sure that it is really possible, i.e. that the building blocks are 
there. So I wonder if a hypothetical callback function that is 
registered with sqlite3_trace would have access to some kind of 
identifier of the program or process that has executed each SQL statement?


Regards,
Frans

On 2011-09-02 12:58, Black, Michael (IS) wrote:

If you can add a field that you can put a source string in you can do this:

create table dml (dmlstuff text, source text);
create trigger before insert on dml
for each row when new.source not like 'p2p'
begin
  select raise(rollback,'Not p2p source');
end;

sqlite>  insert into dml values('dml1','p2p');
sqlite>  select * from dml;
dml1|p2p
sqlite>  insert into dml values('dml2','other');
Error: Not p2p source
sqlite>  select * from dml;
dml1|p2p



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Frans Knibbe [frans.kni...@geodan.nl]
Sent: Friday, September 02, 2011 4:21 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Track DML (from certain clients only)


Hello all,

I am trying to use SQLite as a data store for a P2P (peer to peer)
system. There could be several applications reading from and writing to
the database. One of these is the P2P module. It reads local additions
to the database (only INSERTs are allowed, rows are never DELETEd or
UPDATEd) and distributes to to other peers. The P2P module also insert
data it receives from other peers.

I would like to be able to distinguish data that has been written by the
P2P module from data that has been written by other local applications.
The latter data need to be handled by the P2P module, the former not
(otherwise the data would de replicated over and over again).

I thought I could make use of a table that stores all DML statements
from all applications except the P2P module. The P2P module could use
that table as a task list, and periodically check if there is any
unhandled DML in that table. All other applications could be made to
write their DML to that table, but that is not very elegant. I would
rather like this to be done automatically.

Is it possible to create a process (a trigger maybe) that monitors all
DML statements and writes them to a table, except when the statement is
issued by a specific application?

Or are there smarter ways of doing what I am trying to do?

Thanks in advance!

Frans
___
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] sqlite3_open_v2 performance degrades as number of opens increase

[Black, Michael (IS)]

I profiled the sqlite3 test program below and I get this for 25,000 files



Flat profile:

Each sample counts as 0.01 seconds.
  %   cumulative   self  self total
 time   seconds   secondscalls  ms/call  ms/call  name
 61.15 17.6117.6125000 0.70 0.70  findInodeInfo
 36.67 28.1710.5625000 0.42 0.42  findReusableFd

For 5,000 files looks like this:

Each sample counts as 0.01 seconds.
  %   cumulative   self  self total
 time   seconds   secondscalls  us/call  us/call  name
 52.06  0.76 0.76 5000   152.00   152.00  findInodeInfo
 43.84  1.40 0.64 5000   128.00   128.00  findReusableFd



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Friday, September 02, 2011 8:00 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] sqlite3_open_v2 performance degrades as number of 
opens increase

I assume you've overridden the system default for 1024 files in ulimit for # of 
open files?

I don't see the times you're seeing using this program...though my databases 
are empty which probably makes a difference.
I do see the gradual increase in time...I think this is almost all due to the 
OS when you try and open thousands of files in one process.

I ran this once to create 25,000 databases
./db 25000
ls -l file* | wc -l
25000
The tested opening them
time ./db 5000
1.602s

time ./db 1
5.357s

time ./db 15000
11.280s

time ./db 2
19.570s

time ./db 25000
28.166s

#include 
#include 
#include 
#include "sqlite3.h"

int main(int argc,char *argv[])
{
  int i,rc;
  sqlite3 *db;
  for(i=0;i

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] sqlite3_open_v2 performance degrades as number of opens increase

[Black, Michael (IS)]

I assume you've overridden the system default for 1024 files in ulimit for # of 
open files?

I don't see the times you're seeing using this program...though my databases 
are empty which probably makes a difference.
I do see the gradual increase in time...I think this is almost all due to the 
OS when you try and open thousands of files in one process.

I ran this once to create 25,000 databases
./db 25000
ls -l file* | wc -l
25000
The tested opening them
time ./db 5000
1.602s

time ./db 1
5.357s

time ./db 15000
11.280s

time ./db 2
19.570s

time ./db 25000
28.166s

#include 
#include 
#include 
#include "sqlite3.h"

int main(int argc,char *argv[])
{
  int i,rc;
  sqlite3 *db;
  for(i=0;i

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

[Eric Minbiole]

My first thought would be to check the amount of memory being used by
your many connections.  Each connection will consume a non-trivial
amount of resources (page cache, file handles, OS file cache, etc.)
It's certainly plausible that your overall system performance is
reduced as you run out of physical memory (or other system resources).
 As such, I'd double check your free / available memory as you open
more and more connections-- see if there is a significant impact.

On a side note, trying to manage 100K or more separate databases
sounds excessive.  Doing so somewhat defeats the purpose of a nice
relational database.  If you properly index your tables, I would think
you could still achieve similar / reasonable performance, even after
combining the many smaller databases into fewer larger ones.  [Just my
2 cents.]

On 9/2/11, Terry Cumaranatunge  wrote:
> Hello,
>
> We have an application that creates many small databases (over 100K) to be
> able to control the size of the database and provide more deterministic
> performance. At process startup, it opens many of these databases in a loop
> to keep them open for faster transaction response times. The behavior we are
> noticing is that the it takes progressively a longer time for each
> sqlite3_open_v2 to complete as the number of databases kept
> opened increases. These are some of the measurements:
>
> 5000 DBs = 3 secs
> 1 DBs = 11 secs
> 15000 DBs = 35 secs
> 2 DBs = 75 secs
>
> Many processes can concurrently open 5000 db's at the same time and it takes
> about the same time as a single process doing the work. So, it doesn't
> appear to be related to the OS related issue with the number of opens.
>
> The open is done as:
> sqlite3_open_v2(dbName, db, SQLITE_OPEN_READWRITE  | SQLITE_OPEN_NOMUTEX,
> NULL)
>
> We are using RHEL 6 with SQLite 3.7.7.1. Each database was created with
> journaling mode set to WAL.
> Does anyone know why the performance degrades this way and what can be done
> to resolve the problem?
> ___
> 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] sqlite3_open_v2 performance degrades as number of opens increase

[Simon Slavin]

On 2 Sep 2011, at 12:30pm, Terry Cumaranatunge wrote:

> We have an application that creates many small databases (over 100K) to be
> able to control the size of the database and provide more deterministic
> performance. At process startup, it opens many of these databases in a loop
> to keep them open for faster transaction response times. The behavior we are
> noticing is that the it takes progressively a longer time for each
> sqlite3_open_v2 to complete as the number of databases kept
> opened increases. These are some of the measurements:
> 
> 5000 DBs = 3 secs
> 1 DBs = 11 secs
> 15000 DBs = 35 secs
> 2 DBs = 75 secs
> 
> Many processes can concurrently open 5000 db's at the same time and it takes
> about the same time as a single process doing the work. So, it doesn't
> appear to be related to the OS related issue with the number of opens.

I suspect that your application is having to work through a big list each time 
you specify a database to work on.  This might be caused by your own code or 
there might be something inside one of the SQLite routines that needs to keep a 
list of open connections.  How are you storing your connection handles ?  In 
other words, what are you handing sqlite3_open_v2() for its sqlite3** value ?  
Are you perhaps using a big array, or a linked list ?

As you can probably guess, creating 2 databases with the same structure is 
something one doesn't often do since this is what table columns are for.  
Although you have your reasons for doing so, you might find merging them all 
into one database may actually save you time and memory.  Of course I don't 
know how hard it will be to change your programming to run a test with this way 
of doing things.

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

Re: [sqlite] Speeding up index creation

[Filip Navara]

On Fri, Sep 2, 2011 at 11:04 AM, Filip Navara  wrote:
*snip*
> The time to create an index on my 266 Mb experimental database is more
> than 9 minutes.
*snip*

I erroneously measured the time with DEBUG build, so I've rerun the
experiment with several SQLite versions:

[2869ed2829] Leaf: Avoid using uninitialized variables after failures
in the merge sort code. (user: drh, tags: trunk)
CPU Time: user 107.359888 sys 135.050066

[7769fb988d] Instead of a temporary b-tree, use a linked-list and
merge-sort to sort records in main memory in vdbesort.c. (user: dan,
tags: merge-sort)
CPU Time: user 118.451559 sys 132.117247

[71075673c6] Leaf: If all data being sorted fits in memory, avoid
writing any data out to temporary files in vdbesort.c. (user: dan,
tags: merge-sort)
CPU Time: user 116.813549 sys 132.710051

Best regards,
Filip Navara
___
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

[sqlite] sqlite3_open_v2 performance degrades as number of opens increase

[Terry Cumaranatunge]

Hello,

We have an application that creates many small databases (over 100K) to be
able to control the size of the database and provide more deterministic
performance. At process startup, it opens many of these databases in a loop
to keep them open for faster transaction response times. The behavior we are
noticing is that the it takes progressively a longer time for each
sqlite3_open_v2 to complete as the number of databases kept
opened increases. These are some of the measurements:

5000 DBs = 3 secs
1 DBs = 11 secs
15000 DBs = 35 secs
2 DBs = 75 secs

Many processes can concurrently open 5000 db's at the same time and it takes
about the same time as a single process doing the work. So, it doesn't
appear to be related to the OS related issue with the number of opens.

The open is done as:
sqlite3_open_v2(dbName, db, SQLITE_OPEN_READWRITE  | SQLITE_OPEN_NOMUTEX,
NULL)

We are using RHEL 6 with SQLite 3.7.7.1. Each database was created with
journaling mode set to WAL.
Does anyone know why the performance degrades this way and what can be done
to resolve the problem?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Track DML (from certain clients only)

[Black, Michael (IS)]

If you can add a field that you can put a source string in you can do this:

create table dml (dmlstuff text, source text);
create trigger before insert on dml
for each row when new.source not like 'p2p'
begin
 select raise(rollback,'Not p2p source');
end;

sqlite> insert into dml values('dml1','p2p');
sqlite> select * from dml;
dml1|p2p
sqlite> insert into dml values('dml2','other');
Error: Not p2p source
sqlite> select * from dml;
dml1|p2p



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Frans Knibbe [frans.kni...@geodan.nl]
Sent: Friday, September 02, 2011 4:21 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Track DML (from certain clients only)


Hello all,

I am trying to use SQLite as a data store for a P2P (peer to peer)
system. There could be several applications reading from and writing to
the database. One of these is the P2P module. It reads local additions
to the database (only INSERTs are allowed, rows are never DELETEd or
UPDATEd) and distributes to to other peers. The P2P module also insert
data it receives from other peers.

I would like to be able to distinguish data that has been written by the
P2P module from data that has been written by other local applications.
The latter data need to be handled by the P2P module, the former not
(otherwise the data would de replicated over and over again).

I thought I could make use of a table that stores all DML statements
from all applications except the P2P module. The P2P module could use
that table as a task list, and periodically check if there is any
unhandled DML in that table. All other applications could be made to
write their DML to that table, but that is not very elegant. I would
rather like this to be done automatically.

Is it possible to create a process (a trigger maybe) that monitors all
DML statements and writes them to a table, except when the statement is
issued by a specific application?

Or are there smarter ways of doing what I am trying to do?

Thanks in advance!

Frans
___
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] Track DML (from certain clients only)

[Frans Knibbe]

Hello all,

I am trying to use SQLite as a data store for a P2P (peer to peer) 
system. There could be several applications reading from and writing to 
the database. One of these is the P2P module. It reads local additions 
to the database (only INSERTs are allowed, rows are never DELETEd or 
UPDATEd) and distributes to to other peers. The P2P module also insert 
data it receives from other peers.


I would like to be able to distinguish data that has been written by the 
P2P module from data that has been written by other local applications. 
The latter data need to be handled by the P2P module, the former not 
(otherwise the data would de replicated over and over again).


I thought I could make use of a table that stores all DML statements 
from all applications except the P2P module. The P2P module could use 
that table as a task list, and periodically check if there is any 
unhandled DML in that table. All other applications could be made to 
write their DML to that table, but that is not very elegant. I would 
rather like this to be done automatically.


Is it possible to create a process (a trigger maybe) that monitors all 
DML statements and writes them to a table, except when the statement is 
issued by a specific application?


Or are there smarter ways of doing what I am trying to do?

Thanks in advance!

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

[sqlite] Speeding up index creation

[Filip Navara]

Hi,

I'm experimenting with the new code in SQLite's trunk and measuring
the improvements in index creation. The I/O required to create index
and also the disk space requirements has reduced significantly, so the
CREATE INDEX is now dominated by CPU time. Almost no memory is used
for cache though, which I find odd.

The time to create an index on my 266 Mb experimental database is more
than 9 minutes. The database is available at
http://www.emclient.com/temp/mail_index.zip and the command I use to
create the index is

  CREATE INDEX "idx_MailAddresses_address" ON "MailAddresses" ("type",
"address", "parentId");

I had run the shell under profiler and most of the time is spent in
the following code path:

Name / Inclusive % time / Exclusive % time

sqlite3BtreeInsert / 76,68 / 0,40
.. btreeMoveto / 47,75 / 0,18
 sqlite3BtreeMovetoUnpacked / 40,49 / 3,33
.. sqlite3VdbeRecordCompare / 27,04 / 4,69
 sqlite3MemCompare / 15,36 / 7,85

I'm wondering if there's any way to speed up the index creation. Is
larger cache size supposed to improve the performance? Did I perhaps
hit some "worst case" scenario?

Best regards,
Filip Navara
___
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