Re: [sqlite] Balance between keeping DBs open vs repeated open/close
> 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
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
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
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
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
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
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