> I'm really surprised you're not seeing something from an increased > cache size. It has always made a very noticeable difference in my own > manipulations, but I think the largest table I've ever worked with > "only" had 6M rows. >
Jay, increasing cache size really helps, but till some size as I mentioned. And it really makes sense. I can assume what is going on. While we're not out of cache, all this B-tree structured data is completely inside RAM and is able to update/change very quickly, but when the time for flushing comes, we have many pages forming this b-tree and the question is what pages should we flushed making this part of cache free in order to next 1,000,000 records (with absolutely unpredictable content) use this tree more effectively. Honestly I'm not aware of such algorithm. So every next mass insert produces many "collisions", moments when we need to write to some page, but it's not in the write cache. But I guess there is something sqlite could do with using existing cache more effectively. I don't know the internals very well, but it seems that both read and write cache exist in sqltie. But read cache is sometimes almost unnecessary since this data already present in the system file cache. In this case accessing some page in the cache and accessing it with xRead interface of VFS will probably take the same or similar time (sure if there's no encryption or other overhead of VFS). But I'm not sure about this guess since it's sometimes hard to separate read and write cache and also for B-tree inserts most of pages just read will probably be written soon By the way, Alexey Pechnikov's recent post about his tests shows that page_cache_size not always helps and that you should take into account not only the number of rows, but also the total index size (quote from the http://geomapx.blogspot.com/2009/11/degradation-of-indexing-speed.html: "As we can see the index creating speed degradation is result of index size more than SQLite page cache size."). Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
