Hello again, I start with your final words, "it's a general database engine". On the main page it writes: "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()" That's why I try sqlite and not other database (I actually tried embedded innodb but sqlite was muuuch better / faster from the first try).
Now the rest. I'm not saying that I need 800000 rec/s, just that I reached this speed with hdf5. Our devices are currently rated at "only" >30000 "hits"/sec; therefore my results at >150000 rec/sec are good enough for the start. I MIGHT need high data rate (>20MB/s), but that includes blobs (waveform) data. When using blobs sqlite can also achieve high numbers in MB/s (of course less records). Obviously we do have now a proprietary format; but as with any format, changes are required to make room for new features. We tried to design a much better, self describing, extensible file format; at the end I realize that it's not that far from existing solutions like hdf5 or sqlite. That's why I'm here... Currently I have only 1 binary structure (union), but I would change that to 2 or 3 tables, one for each record type. I don't need any specific indexes during writing (other than native order); for later analysis optimizations, I might create some other indexes. Similar to what you wrote about B-trees for pages, I assumed that indexes are equally problematic on writing. I do understand that it is not feasible to change much about how it works right now, to make it valid for concurrent access. But I still have the feeling that it could work pretty well using this kind of model (let's call it "optimistic" locking): for the writer: 1. on write begin (write lock), increase a special counter (n -> n+1, odd value); this would mark pending changes 2. write new data/pages/references as usual 3. on write end (release write lock), increase the counter again (even value); this would mark the page structure as valid for the reader: 4. on begin read (shared lock), read (and store) the counter; if odd, a writer is active and should return "busy" immediately (just like now). 5. read the page list in cache (I assume it is doing this right now) 6. read the counter again and compare with the initial value; if no change is detected then page list is valid and it can read existing data. If change is detected, it should signal "busy" just like 4. 7. when releasing the shared lock, the counter can be checked again (against the value read on 4) and signal whether the data was changed or not. It is up to the user to decide what to do with the "dirty" data. For points 4, 6 it should either fail, or trying like now in a loop for a certain period. Even better, the change counter could (or actually "should") be per table. So for writing there would only be trivial changes (simple counter increment); for reading there would be some simple read/check of a value before using the page list. These relatively simple changes would open it for other applications; by searching I saw that I'm not the only one who needs this kind of behavior. Regards, Gabriel Corneanu _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
