I see your point and your algorithm looks pretty reasonable. Except that again it can be reasonable for you but not for general case which is SQLite for. You have one flaw: SQLite doesn't "read page list in cache", it reads every page that is needed during query execution. So if one applied your algorithm generally then he would read first page of database (to check change counter which is already there now) after reading of each database page. This will double disk I/O at cold start and significantly slow down execution if all data that query needs is already in cache (first page should still be read from disk at least after each step through result set).
I understand your desire to use SQLite for your application. So if you can afford paying some fee for its customization for your specific needs (instead of trying to change sources by yourself) then I'd suggest you to contact company developing it (http://www.hwaci.com/contact.html). I don't know their pricing but I believe they will be able to help you. Pavel On Tue, Mar 9, 2010 at 9:43 AM, Gabriel Corneanu <gabrielcorne...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
