Probably I will express just my opinion but still... Gabriel, what you described is clearly not a good or anywhere intended use of SQLite. If you need writing at the rate of 800,000 records per second you can't afford using database engine for this. Much better option for you will be to have some file mapped to your application memory and to write directly to that memory in your proprietary format. Then you should think how you will deal with crashes and inconsistencies inside the file (of course if you need to store those data for long time and don't use your database as simple pipe between applications).
And below are some comments about your vision of how things work. > Sure the indexes might create problems... I only use the build-in rowid > during writing. What do you mean by "during writing"? Indexes either exist in database or not and so they used during writing or not used at all. So do you have indexes? Do you have only one table in the database? > So the question are: how is expanding working? would it fit such model? Definitely not. You may want to read this http://www.sqlite.org/fileformat.html. > A trivial example: > page count = 5, page list = 1,2,3,4,5 > after writing 2 new pages > page list append 6,7 (does not change the old list entries) then set page > count = 7 (atomic) > The reader reads either 5 or 7; in any case, the page lists are valid. > If necessary, a (transaction) counter might be used to detect "dirty" reads > (when really incompatible). Don't forget that tables are recorded in a form of B-trees, not as simple heaps (as probably is in some other DBMS). So even if you just append to the table and never delete or update it changes go to several different pages anyway. Look what happens. First of all there's no "page count" for the table - everything is made using references. So let's say page 1 is a head of B-tree for the table, it contains link to page 2, it contains links to page 3, 4 and 5. Let's say you're adding one more page 6. It means you have to add link to it to page 2 (jeopardizing the contents that reader should read). Let's say you're adding page 7, it overflows page 2, so it should be split (added one more page 8) and link should be added to page 1. Things are even worse if page 1 overflows - you have to split it, add new head of B-tree, rewrite head into page 1 and move old contents of page one into some other page... As you see there're a lot of places where reader could read inconsistent information from database. Of course you can find a way to solve all those problems by selecting correct sequence of writing to disk. But SQLite doesn't do that and won't do that because it has locking and it is won't be adjusted to server such very specific usage - it's a general database engine. Pavel On Tue, Mar 9, 2010 at 7:43 AM, Gabriel Corneanu <gabrielcorne...@gmail.com> wrote: > Hello, > Thanks for your attention, > > >> Although speaking generally such method could be used in some situations, I >> don't think it's good to allow to use it even with a "i know what I'm >> doing" >> pragma. Any structured file (sqlite is an example) have internal >> dependencies. One of the reasons to block is to write different parts of >> structured data together without intervention from other parties in order >> to >> keep the data integral. Imagine writing cache that kept changes for your >> writer and finally it needed to flush the data and at the same time your >> "anytime" reader started to perform some query in the middle of this >> multiply pages writing process. I can't predict whether the reader will end >> with some valid result or it will return with "database malformed" error. >> >> Instead consider changing your own logic. You wrote "without* any >> possibility to be blocked". I suppose you already have a perfect writer >> that >> fits your needs, but if you post some info about the nature of your writer >> and reader (records per second and something like this), it would help to >> be >> more specific with answers. >> >> Max > > > The writer application must be failsafe, as much as possible (acoustic > emission recording devices); I simply can not afford that a reader makes a > select and because of a programming error the acquisition be blocked. I had > this just by opening sqliteman. > > The recording rate is variable; using a test structure (~14 fields in 1 > table, all integers 32/64bit) I was able to achieve ~90000 records /sec with > sqlite API, which was not really good enough (there are is no jurnal; I > don't need any rollback / consistency check). > Then I was able to make a virtual table wrapper and insert/select and > reached ~170000 rec/sec, which is already a big step forward. I think is not > planned, but I would like to have some bulk insert API (not sql) to speed up > things; hdf5 with packet table API reached ~7-8 00 000 rec/sec and is > essentially IO bound. I do not expect this kind of performance from sqlite > soon... Maybe cache settings might also help, suggestions are welcome. > For reading it is much better; here I also used an workaround, a fake > aggregated function ("store(...)") is working much faster (and simpler to > implement than a virtual table). > So the reader can read faster anyway, it just needs to "follow" the data; > small delays are normal and acceptable. > > > Back to the technical problem; roughly described, I would expect that a > table is expanded first with new data then (on transaction end, I used > blocks of ~1000 records) update some metadata information about table pages > (which pages are used for the data). I image that it could be made somehow > safe (I'm sorry but I have no idea about the actual implementation): write > data (old data remains valid), invalidate page list (if necessary, one > single value which would be atomic on most systems if properly aligned), > write the new page list, then mark it as valid again (nothing affects > current behavior when using existing locking anyway). > The reader could read without any problem "old" data; it would only need to > detect a "dirty" read if the page list is changed to be incompatible (if new > pages are appended only, the page list might always be valid). In this case > the reader would work in a short loop (similar to the current behavior when > locking) trying to get a "clean" page list. > > A trivial example: > page count = 5, page list = 1,2,3,4,5 > after writing 2 new pages > page list append 6,7 (does not change the old list entries) then set page > count = 7 (atomic) > The reader reads either 5 or 7; in any case, the page lists are valid. > If necessary, a (transaction) counter might be used to detect "dirty" reads > (when really incompatible). > > So the question are: how is expanding working? would it fit such model? > Sure the indexes might create problems... I only use the build-in rowid > during writing. > > Thanks again, > Gabriel > _______________________________________________ > 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
