On Fri, Jul 23, 2010 at 5:11 AM, Taras Glek <tg...@mozilla.com> wrote:
> 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. > > Funny, that's why I like reading someone's questions in this list. It helps sometimes solving old tasks :). As many noticed Windows system cache is hard to control. So for example it almost impossible to clear reading cache for testing purposes, once you read the file, it's in the cache so the following timing numbers are irrelevant. There's an option for CreateFile, FILE_FLAG_NO_BUFFERING, it can be used to disable the cache for a file when one wants to work with it. I thought maybe to change the sources and prepare a special version of sqlite allowing to open without cache. But a better solution at least on XP came, if I "touch" a file with CreateFile(..FILE_FLAG_NO_BUFFERING) and close it, it won't use the cache for the next file opening, so for testing purposes I just made a checkbox in an admin that "touches" the file before passing it to sqlite. And it seems it really works. So, Taras, thank for your post ) Also with this approach I tried to test places.sqlite, particularly moz_places table, the query was SELECT * FROM moz_places WHERE url Like "%double%" I suppose that mozilla team probably uses different queries, but since there are no fts table recognizable, there should be some kind of full-scan. So, my tests on two hard drives showed that windows fragmentation had small effect on the performance of the query, while VACUUM; results had significant. Before Vacuum, my long time places.sqlite 13M in size, having moz_places with 16893 records, return results after 8-10 seconds, depending on the place it lived, but after VACUUM, the results were between 150ms and 300ms. I think that this can be due to the nature of windows read ahead cache, so when sqlite tables are placed in consequent blocks of file, Windows loads the same pages sqlite expects. So before any file system defragmentation, internal sqlite defragmentation (VACUUM) have to be applied. Max Vlasov, maxerist.net _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
