On 23 Jul 2010, at 2:11am, Taras Glek wrote: > 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.
If I understand correctly, there are two levels of fragmentation involved: disk fragmentation (where the sectors of the disk file are spread about) and database fragmentation (where the pages of database information are spread about the disk file). > First on fixing fragmentation: > Currently we write pretty heavily to our databases. This causes the > databases to grow, queries to slow down. Can I check that you are not just seeing fragmentation, but are actually seeing performance vary with fragmentation ? Because having that happen to an extent that's noticeable is something traditionally associated only with Windows, and your blog entry says you're using ext4 on Linux. Other platforms and file systems /have/ fragmentation, of course, but it doesn't normally slow them down as much as fragmentation slows down Windows. Some platforms handle this in unexpected ways. For instance, OS X will automatically defragment files smaller than 20MB each time they're opened. It won't defragment the database pages because, of course, it doesn't understand SQLite format. The easiest way to make a defragmented copy of a SQLite file would be to use the command-line tool to .dump a copy of a database to a text file, then again to .read that textfile into a database. Under Unix you can do it in one command: sqlite3 old_database.sqlite .dump | sqlite3 new_database.sqlite The resulting SQLite database file will not only be defragmented but will have some other optimal characteristics. I would be interested to know if you really do see performance improvements by doing this then replacing old_database.sqlite with new_database.sqlite . Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
