I assume you are batching your inserts? How many inserts/sec do you need to do to keep up?
Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-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 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 tables independently, occasionally do single-table reads, and at the end of the day create a report that joins all the tables together. So, my main goal is to give maximum speed to writes by compartmentalizing the locks that the incoming reads will cause. It sounds like this use case won't hit any of the disadvantages mentioned above. In the past, I've tried to do this in SQLite and MySQL by putting all the data in a single table (timestamp, variable name, value). But, if you index the variable name then writes become too slow (by the end of a day's worth of data collection, the next round of data comes in before the previous round is written); if you don't index then the table is impossible to select from in any reasonable amount of time. So, the solution seems to be splitting every variable into its own table -- not very good normalization, but retaining good read performance without having write performance degrade over time. The join-all-tables-together query would be used for generating a logfile in the old format, just in case we need it. Am I missing any features of SQLite that would solve this problem in a different/better way? -Ian On Wed, Apr 11, 2012 at 12:20 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > On Wed, Apr 11, 2012 at 12:01 PM, Ian Katz <ifreeca...@gmail.com> 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 into separate databases and using the "attach database" option >> outlined here: >> http://stackoverflow.com/questions/6671678/objective-c-sqlite-join-tables-from-multiple-database >> >> I would assume that the databases will not become locked until the >> statement is executed (i.e., preparing the statement won't lock it). >> Is that correct? > > Yes, that's correct, although I don't see a link between this > statement and "attache database" discussion above. > >> If so, is there a significant disadvantage or >> performance hit to using this workaround? > > The first performance hit that comes to mind is either you won't be > able to use WAL mode (which is a significant performance hit) or you > lose overall atomicity of transactions (see disadvantage point 3 here > http://www.sqlite.org/wal.html). > > So I wouldn't do that if I were you. > > > Pavel > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Ian Katz Research Software Engineer, MIT LAMSS i...@mit.edu _______________________________________________ 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
