On Wed, Apr 6, 2011 at 9:27 AM, Filip Navara <filip.nav...@gmail.com> wrote: > On Wed, Apr 6, 2011 at 6:36 AM, Dan Kennedy <danielk1...@gmail.com> wrote: >> On 04/05/2011 04:49 PM, Filip Navara wrote: >>> Hello, >>> >>> we are having problem with database that originated on computer of one >>> of our customers. >>> >>> The database is used in WAL mode with auto_vacuum=1 and page_size=1024. >>> >>> When running the "pragma incremental_vacuum(1);" command the WAL file >>> grows to 14Mb, while we would expect it to grow only to about 4Kb (1Kb >>> per page + some overhead). This causes the transaction to run for much >>> longer time than expected and eventually the WAL file grows to several >>> gigabytes when we try to run incremental_vacuum for 4096 pages. >>> >>> Additional facts: >>> - The database was created with SQLite 3.7.5 running on Windows >>> - "pragma integrity_check;" reports ok >>> - Some free trunk list pages contain weird values in the "number of >>> entries" field >>> >>> I have attached most of the dumps that I could create with the regular >>> SQLite tools (showdb dbheader, sqlite3_analyzer output, beginning of >>> the free list dump). I'm willing to send the whole database file >>> (~5Gb) to sqlite developers on request. >>> >>> My questions are: >>> - Is is expected behavior that "pragma incremental_vacuum(1);" can >>> create 14Mb WAL file even though the page size is just 1Kb? >>> - If not, is it a know bug? If yes, in what circumstances? >>> - Is the free list corrupted? And if it is, how could this happen and >>> should "pragma integrity_check;" verify it? >> >> Was there a *-wal file in the file-system when you ran the >> [showdb] tool? [showdb] just reads the database file, it is >> not smart enough to know when it should read pages out of the >> wal file instead of the db. So if there is a wal file that >> contains content, [showdb] could report corruption. >> >> Also, can you try with this patch? >> >> http://www.sqlite.org/src/ci/311d0b613d >> >> It might help with the 14MB wal files. >> >> Dan. > > Hi Dan, > > there was no -wal file when I ran the showdb tool. > > I recompiled sqlite from the current 3.7.6 snapshot > (sqlite-amalgamation-201104052208.zip) and verified that it contained > your patch. Unfortunately it didn't help with the large WAL file for > this particular database. > > Best regards, > Filip Navara >
After some more diagnosis I discovered that the free list is not corrupted afterall. It's just that the showdb tool fails to work properly with databases larger than 4Gb and since the free list trunk pages are located beyond the 4Gb barrier it caused problems. This leaves me with the incremental vacuum problem, where the journal/WAL grows uncontrollably. Also I have created an anonymized version of the database that doesn't contain most of the original data and where all free list leaf pages are overwritten with zeroes. It can be downloaded from http://www.emclient.com/temp/mail_data.zip (~ 30Mb). Best regards, Filip Navara _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
