On Thu, Oct 21, 2010 at 1:27 PM, Dustin Sallings <dus...@spy.net> wrote:
> > On Oct 21, 2010, at 10:05, Pavel Ivanov wrote: > > > I think it's not related to fragmentation, but to fill percentage of > > b-tree pages. I guess your reconstructed table is much less in total > > size than your initial one. Also does changing cache_size changes > > above numbers? > > Interesting. We have been using sqlite3_analyze on some tests and > finding that we can get our dbs very fragmented. It doesn't report fill > size as far as I can tell. > > It does report fill percentage, as "Bytes of user payload stored" > We'll play around with cache_size to see if that does anything > useful for us in the meantime. > > > What size do these tables have? > > About 2.2GB with about 4 million rows. > > > What bottleneck appears to be in 3-hour query execution? Is it disk > thrashing? > > Yes. > > I've tried different strategies in the past. Vacuum and the rebuild > both seem to help quite a bit. I don't understand the file layout all that > well right now, so I don't completely understand how the index is traversed. > How much memory do you have in the test system? Because if you rebuild, you are loading the whole db into the filesystem cache, if it'll fit. Then you are no longer accessing the file randomly. You might get the same performance increase by just catting the db (sequentially reading it into the FS cache) and then running your test. It's a good idea to make sure you purge your filesystem cache before running any performance tests related to fragmentation. On OSX, it's purge. On Linux, its echo 3>/proc/sys/vm/drop_caches. Otherwise, it's easy to fool yourself. But I agree with you that fragmentation can be a big issue, depending on how the data is loaded and how it is subsequently accessed. Jim -- HashBackup, LLC http://sites.google.com/site/hashbackup ** _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
