Hello, Recently I spent some time investigating sqlite IO patterns in Mozilla. Two issues came up: keeping sqlite files from getting fragmented and fixing fragmented sqlite files.
First on fixing fragmentation: Currently we write pretty heavily to our databases. This causes the databases to grow, queries to slow down. VACUUM shrinks the databases down to a reasonable size(among other things) and queries speed up again, as expected. Problem is that databases get fragmented as they grow. VACUUM only reduces filesystem fragmentation proportionally to reduction in file size. It seems like the most obvious solution is to do some sort of a hot copy and switch to a new database connection to the copy. It would be nice if sqlite could do this by default. Is process described in http://www.sqlite.org/backup.html the best way to accomplish this? Avoiding Fragmentation: Writing to the db file causes fragmentation when the underlying filesystem fails to anticipate the sqlite growth pattern. Seems like the easiest fix here is to add a pragma fs_allocation_size. It would preallocate a continuous chunk of diskspace. Sqlite would behave exactly as it does now, except it would avoid truncating the file beyond a multiple of the fs_allocation_size. For example, pragma fs_allocation_size=50M would grow the db file to 50megabytes. Once the db grows to beyond 50mb the underlying file would get resized to 100mb. Clearly one can implement something like this via VFS handlers, but it would be nice to do this on a slightly higher level. Where in the code would this be appropriate? It would be great to get some help so I could submit a patch for this. I am pretty new to sqlite, so I could be on the wrong track here. Would like to see an expert opinion on these two approaches. There is also approach #3 of running a defrag tool to fix this, but I'd rather fix the cause of fragmentation. Thanks, Taras ps. For details see my blog post on fragmentation: http://blog.mozilla.com/tglek/2010/07/22/file-fragmentation/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
