Re: [sqlite] Balance between keeping DBs open vs repeated open/close

[Pavel Ivanov]

> Keep in mind that sqlite has a limit of 32 open databases.

32 _attached_ databases, not opened. With different sqlite3* handlers
you can open as many databases as you want (and your memory permits).


Pavel


On Thu, Apr 7, 2011 at 6:05 AM, Eduardo  wrote:
> At 18:11 06/04/2011, you wrote:
>>Thanks Stephan and Simon - I kind of figured it was one of those "how
>>long is a ball of string" questions, but I just wanted to check there
>>weren't any particular gotchas to watch out for.
>>
>>Regarding the separate files, it seems the best way to go. Each
>>individual table could get pretty large (possibly several gb), and the
>>one table per DB approach means that should any one file get damaged in
>>some way, only that one set of data would be lost.
>
> Keep in mind that sqlite has a limit of 32 open databases.
>
>>--
>>Paul Roberts
>
> ___
> 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] Balance between keeping DBs open vs repeated open/close

[Eduardo]

At 18:11 06/04/2011, you wrote:
>Thanks Stephan and Simon - I kind of figured it was one of those "how
>long is a ball of string" questions, but I just wanted to check there
>weren't any particular gotchas to watch out for.
>
>Regarding the separate files, it seems the best way to go. Each
>individual table could get pretty large (possibly several gb), and the
>one table per DB approach means that should any one file get damaged in
>some way, only that one set of data would be lost.

Keep in mind that sqlite has a limit of 32 open databases.

>--
>Paul Roberts

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

Re: [sqlite] Balance between keeping DBs open vs repeated open/close

[Stephan Beal]

On Wed, Apr 6, 2011 at 6:11 PM, Technology Lighthouse wrote:

> Thanks Stephan and Simon - I kind of figured it was one of those "how
> long is a ball of string" questions, but I just wanted to check there
> weren't any particular gotchas to watch out for.
>

LOL, i'll have to remember the ball of string comparison.

In the off chance that you don't know about it already:

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

with that you can attach several db files as one virtual db. That won't
inherently cut the number of open file handles (it should be the same as
manually opening them), but it might simplify the usage of multiple dbs.

-- 
- 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

[sqlite] Balance between keeping DBs open vs repeated open/close

[Technology Lighthouse]

Thanks Stephan and Simon - I kind of figured it was one of those "how 
long is a ball of string" questions, but I just wanted to check there 
weren't any particular gotchas to watch out for.

Regarding the separate files, it seems the best way to go. Each 
individual table could get pretty large (possibly several gb), and the 
one table per DB approach means that should any one file get damaged in 
some way, only that one set of data would be lost.
-- 
Paul Roberts
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Balance between keeping DBs open vs repeated open/close

[Simon Slavin]

On 5 Apr 2011, at 3:05pm, Technology Lighthouse wrote:

> My application makes use of a number of separate SQLite DB files (in 
> some cases > 100). Each DB is handled by its own thread, which may be 
> making frequent small writes, or sleeping extensively then occasionally 
> making a more substantial number of writes. I'm trying to decide on a 
> policy for keeping DBs open versus repeatedly opening/closing them.
> 
> Are there any hard or practical limits on the number of SQLite DBs that 
> can be held open at the same time? And is there any data on the relative 
> performance cost of the open operation?

As Stephan remarks this is more to do with the operating system than SQLite.  
In fact I think all your questions are tightly bound to characteristics of your 
operating system, several of them giving completely different answers even 
between different versions of Windows.  Windows 7, for instance, is slower at 
opening files than Windows XP, but does more caching once a file is open.  
Unix, on the other hand, does caching on the first read rather than when the 
file is initially opened.

So I suspect you should run some experiments with your own particular setup: 
the OS your customers will use, the amount of memory they'll have in their 
computers, and the access speeds their hard disks will have.

The natural question is whether all these really need to be in separate files.  
Do all these databases have the same schemas (same tables with the same 
columns) ?

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

Re: [sqlite] Balance between keeping DBs open vs repeated open/close

[Stephan Beal]

On Tue, Apr 5, 2011 at 4:05 PM, Technology Lighthouse wrote:

> Are there any hard or practical limits on the number of SQLite DBs that
> can be held open at the same time?


Not quite an answer your whole question, but possibly of interest...

The OS environment determines how many file descriptor can be open at one
time. If your OS runs out of file descriptor, it won't be able to open any
more. Note that "file descriptor" does not just mean files, but also network
connection (at the kernel level, sockets and files are, at least on Unix
systems, basically the same thing).

i have NO idea what the file handle limits are on non-Unix systems, but
having, say, 200 dbs, each with 2(?) file handles (one for the journal and
one for the db?), shouldn't be a problem on default Unix configurations (on
some systems the limit can be raised if needed: google for "man 1 ulimit").

If some of your DBs spuriously cannot be opened then you are possibly
running into the file descriptor limit and you may be forced to raise that
limit (if possible) or start closing DBs which you do not currently need.

-- 
- 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

[sqlite] Balance between keeping DBs open vs repeated open/close

[Technology Lighthouse]

My application makes use of a number of separate SQLite DB files (in 
some cases > 100). Each DB is handled by its own thread, which may be 
making frequent small writes, or sleeping extensively then occasionally 
making a more substantial number of writes. I'm trying to decide on a 
policy for keeping DBs open versus repeatedly opening/closing them.

Are there any hard or practical limits on the number of SQLite DBs that 
can be held open at the same time? And is there any data on the relative 
performance cost of the open operation?

Any info or rules of thumb would be appreciated!

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