On Sun, Mar 14, 2010 at 9:18 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > Are you sure it is the journal file that is growing too large? > ... > > Now, if I'm following you correctly, the numbers you gave seem to > indicate that this should work... If the old database is 100MB and > the new database is 50MB and I'm saying the journal file is small, > then 80MB free before you start should be enough. >
I'm sure that it's the journal file. This is happening on an embedded device in a flash-based filesystem which is rather slow, so I was logged in via SSH and could see the journal file growing unusually large as free space shrunk to zero. To double check, I just tried a similar test on my desktop: $ ls -l deadband.db -rw-r--r-- 1 mlcreech mlcreech 85209088 Mar 15 01:35 deadband.db $ ./sqlite3 deadband.db SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT count() FROM val_table; 663552 sqlite> DELETE FROM val_table WHERE idx < 60; sqlite> SELECT count() FROM val_table; 51840 sqlite> VACUUM; In another shell, I've got a script monitoring the size of deadband.db-journal every 100ms. When I do the VACUUM, it shows: Journal size: 4096 bytes Journal size: 4096 bytes Journal size: 7389184 bytes Journal size: 14774272 bytes Journal size: 22159360 bytes Journal size: 29544448 bytes Journal size: 36929536 bytes Journal size: 44314624 bytes Journal size: 51699712 bytes Journal size: 59080704 bytes Journal size: 66658304 bytes Journal size: 81235968 bytes Journal size: 85393408 bytes That last size is actually larger than the original database (additional transaction metadata and what not, I guess). After it's done, though, the file size is appropriately smaller: $ ls -l deadband.db -rw-r--r-- 1 mlcreech mlcreech 6709248 Mar 15 01:43 deadband.db I notice that when I've mostly emptied the database (as in this example), the VACUUM completes quickly, roughly in proportion to how many real entries are left. But the journal size still follows a near-linear growth throughout the operation, regardless. So presumably it's not really doing anything with the old data, but still copying it over to the journal as it goes through the old DB page-by-page or something. You mentioned that it's creating a temp database, and looking at sqlite3RunVacuum() I see 'vacuum_db' which seems to be just that. However, I'm not sure where that data is actually going. Setting temp_store_directory to my current directory didn't actually generate any files while the VACUUM was happening, that I could see. > > PRAGMA journal_mode = OFF; > > I would do this on a test system. If the problem really is the > journal file, this should allow things to work. If you still get a > space error, we're dealing with something else. > This could work as a last resort, although I'd hoped to find a better way - these are embedded devices, so power failure or reboot during the middle of this operation is a possibility. For this one-time case, though, I may be able to swing it if there's no other choice. By the way, there's one single read/write filesystem in flash on these systems, so in my case "/tmp" is no different than the directory that the database & journal are stored in. There's also not nearly enough free memory to hold the small database, so I can't use temp_store = MEMORY, unfortunately. I'll give this a try tomorrow on a real device with journaling off, and see how much space it uses in /tmp with journaling turned off. Thanks for the response! -- Matthew L. Creech _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
