On Wed, Apr 09, 2008 at 05:14:33PM +0200, Aladdin Lamp? scratched on the wall: > > Hi all! > Following the recent thread "Virtual tables used to query big external > database", and the discussion with Mike Owens and Jay A. Kreibich, it > seems that : > > - The "old" way of dealing with dirty pages with bitmaps limited SQLite > to an approximate maximal capacity of 10s of GBs, as opposed to therical > TBs, because it imposed to malloc 256 bytes for every 1Mb of database > during each transaction. > > - The "new" way of dealing with dirty pages with a bitvec structure > (introduced in SQLite v3.5.7) allows for sparse bitmaps and is then > supposed to push away the "10s of GBs" limit.
Just to be clear, the bitvec stuff can greatly reduce memory use for the average-case, but doesn't change the worst-case. If you have a transaction that touches a lot of pages (especially if they're spread out in the file) the bitvec can still grow to be quite large. > Now the questions are: > 1) What are the new practical limits with SQLite v3.5.7? Depends on your environment. A full-blown desktop with 4GB of RAM is going to have much different practical limits than an iPhone. It also depends on what you're doing. None of this really matters if you're using the database read-only. > 2) Does somebody have any real-life experience (or home-made tests and > figures) on SQLite v3.5.7 and really big tables? (say 100 000 000 lines). Personally, I've only gotten to about five or six million rows in a ~6GB db. That was pre-3.5.7 anyways. > 3) Does the new "bitvec" algorithm really help with such a big table? The bitvec stuff has nothing directly to do with table size, only the total database size. That said, if a single table makes up most of a database, it might be easier to dirty a larger number of pages with a single transaction. I'm less clear on that aspect, however. > I am mainly interested in performance of INSERTs If you mean "speed" when you use the word "performance", the bitvec changes aren't likely to have any significant impact unless the old bit-vector was getting so huge it was forcing the VM system to page things out to disk. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
