Re: [sqlite] Using \"attach database\" to work around DB locking

2012-04-13 Thread Stephan Beal
On Fri, Apr 13, 2012 at 9:56 AM, Simon Slavin wrote: > Actually I'd like to apologise for posting the above. I forgot that every > process has its own ':memory:'. If you use a different process to look at > ':memory:' it won't see the database. Sorry about that. You

Re: [sqlite] Using \"attach database\" to work around DB locking

2012-04-13 Thread Simon Slavin
On 13 Apr 2012, at 8:41am, "Gabriel Corneanu" wrote: >> Or you can do your immediate writing to a database in memory, and have >> anotherprocess dump memory to disk in the background. Depending on how >> recent youneed reading you can read the one in memory or the

Re: [sqlite] Using \"attach database\" to work around DB locking

2012-04-13 Thread Gabriel Corneanu
Or you can do your immediate writing to a database in memory, and have anotherprocess dump memory to disk in the background. Depending on how recent youneed reading you can read the one in memory or the one on disk. It seems I have reached the CPU boundary (>90% one 1 core), not waiting

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-12 Thread Simon Slavin
On 12 Apr 2012, at 3:30pm, Gabriel Corneanu wrote: > Using WAL mode and lots of optimizations I am able to write >15 records/s > (one table with ~20 fields) and at the same time reading(processing) >30 > records/s. > > There are lots of tricks/variables

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-12 Thread Gabriel Corneanu
Hi, I have a similar problem; I need to reach writing 10 records/s and parallel reading without blocking the writer. While previously it was not possible (I was considering HDF5 for this), now I decided to go back to sqlite. Using WAL mode and lots of optimizations I am able to write

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Ian Katz
OK. I think you guys have answered my question: I'm not asking the right question. I need to get better data on my performance requirements if I'm going to start asking the right sort of questions. For now, I'll see what I can learn about using WAL mode and go from there. Thanks for the

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Marcus Grimm
> Converting variable name to variable ID (with separate lookup table) > was one of my first ideas, but turns out that the lookup itself was a > bigger hit in performance than the indexing. I'll revisit that and > see if I failed to tweak something properly. To me it sounds that it will just

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Simon Slavin
On 11 Apr 2012, at 6:11pm, Ian Katz wrote: > The database system that I'm designing is for an autonomous vehicle; > it collects a lot of (data which is currently getting stored as a flat > text file). So, it's going to write a LOT of data into many tables > independently,

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Black, Michael (IS)
-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ian Katz [ifreeca...@gmail.com] Sent: Wednesday, April 11, 2012 12:11 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Using "attach database" to work around DB locking These are all good points, and

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Ian Katz
Converting variable name to variable ID (with separate lookup table) was one of my first ideas, but turns out that the lookup itself was a bigger hit in performance than the indexing. I'll revisit that and see if I failed to tweak something properly. -Ian On Wed, Apr 11, 2012 at 1:22 PM, Pavel

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Pavel Ivanov
> Am I missing any features of SQLite that would solve this problem in a > different/better way? If you didn't try it I would suggest to try a single table (timestamp, variable id, value). Index on integer variable id will work faster than text variable name. Other than that I'd say your design

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Ian Katz
These are all good points, and introduce some features of sqlite that I didn't know existed! The database system that I'm designing is for an autonomous vehicle; it collects a lot of (data which is currently getting stored as a flat text file). So, it's going to write a LOT of data into many

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Pavel Ivanov
On Wed, Apr 11, 2012 at 12:01 PM, Ian Katz wrote: > The Sqlite3 manual says that any locking operations affect the entire > database, not individual tables. > http://www.sqlite.org/lockingv3.html > > I was wondering if this effect could be compensated for by splitting >

Re: [sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Simon Slavin
On 11 Apr 2012, at 5:01pm, Ian Katz wrote: > The Sqlite3 manual says that any locking operations affect the entire > database, not individual tables. > http://www.sqlite.org/lockingv3.html > > I was wondering if this effect could be compensated for by splitting > tables

[sqlite] Using "attach database" to work around DB locking

2012-04-11 Thread Ian Katz
Hello, The Sqlite3 manual says that any locking operations affect the entire database, not individual tables. http://www.sqlite.org/lockingv3.html I was wondering if this effect could be compensated for by splitting tables into separate databases and using the "attach database" option outlined