Re: [sqlite] Vacuum into
Hmm. So after a normal vacuum, "Non-sequential pages" is basically 1 for everything, tables and indexes alike. On a read-only "vacuum into" it's anywhere from 22% to 99.5%, usually being more towards 99%. Numbers for "Table X without any indicies" seem identical apart from the Non-sequential pages being close to 99%. So the bloat seems to be all in the indexes. Brain now fried. Can provide any additional requested info next week. -Original Message- From: David Raymond Sent: Friday, February 08, 2019 2:18 PM To: SQLite mailing list Subject: RE: [sqlite] Vacuum into But now, here's the weird part ladies and gentlemen. I started this on one drive, vacuum into completed, but then I had to do real work on that drive, so copied the file over to an unused drive to do the previously reported timing without it being biased by other things going on. But I saw something weird, which I just ran again to confirm. "vacuum into" created a larger file when the original file was read only than it did when the original file was writable. Have run this now twice with the same output file size, so my bad eyes are at least being consistent in what they're seeing. Am on Windows 7 here. When the original db file was read only (Windows properties, not any flag to the CLI) the resulting file from "vacuum into" was 6% bigger. When I undid the read only checkbox and ran the same thing again, the resulting file was the same size as the original. Original file size: 20,467,359,744 Vacuum into size when original is writable: 20,467,359,744 Vacuum into size when original is read only: 21,760,516,096 Timing was also between the vacuum into, and regular vacuum times for the writable file. Well, wall clock time anyway. "user" was way up. sqlite> vacuum into 'vac_into_from_ro_file.sqlite'; Run Time: real 1107.448 user 853.185069 sys 87.048558 Gonna run sqlite3_analyzer.exe on them now to see what's up. But closing in on the end of the work day/week here. -Original Message- From: David Raymond Sent: Friday, February 08, 2019 1:53 PM To: SQLite mailing list Subject: RE: [sqlite] Vacuum into Non-scientific "let's just try it" results Short version: Original file had been vacuumed already as the last thing that had happened to it. File size: 20,467,359,744 sqlite> vacuum into 'vac_into.sqlite'; Run Time: real 589.577 user 222.941029 sys 57.829571 sqlite> vacuum; Run Time: real 1429.063 user 236.325915 sys 199.322478 Synchronous was off, journal mode was normal rollback journal. Was using the computer for normal work while doing it, but nothing else was using the physical drive these were on. Resulting files were the exact same size as the originals (it had been previously vacuumed as mentioned) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 08, 2019 11:29 AM To: SQLite mailing list Subject: Re: [sqlite] Vacuum into On 2/8/19, David Raymond wrote: > So to make sure I'm understanding it ok, with the new vacuum into command, > if I'm the only user of a file, then the sequence... > > open file1 > vacuum into file2 > close file1 > delete file1 > rename file2 to file1 > > ...is going to be potentially more than twice as fast as the old... Think you will be hard-pressed to get a 2x performance increase. Faster, yes. 2x faster, I'm doubtful. But perhaps I'm wrong. Please try it and see and report back. > > open file1 > vacuum > > ...as it saves the whole re-write of the original file, along with all the > rollback journal or wal writes that entails. Correct? > > (With of course the whole "make sure it actually finished and didn't just > die" caveats before doing the delete and rename) > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum into
But now, here's the weird part ladies and gentlemen. I started this on one drive, vacuum into completed, but then I had to do real work on that drive, so copied the file over to an unused drive to do the previously reported timing without it being biased by other things going on. But I saw something weird, which I just ran again to confirm. "vacuum into" created a larger file when the original file was read only than it did when the original file was writable. Have run this now twice with the same output file size, so my bad eyes are at least being consistent in what they're seeing. Am on Windows 7 here. When the original db file was read only (Windows properties, not any flag to the CLI) the resulting file from "vacuum into" was 6% bigger. When I undid the read only checkbox and ran the same thing again, the resulting file was the same size as the original. Original file size: 20,467,359,744 Vacuum into size when original is writable: 20,467,359,744 Vacuum into size when original is read only: 21,760,516,096 Timing was also between the vacuum into, and regular vacuum times for the writable file. Well, wall clock time anyway. "user" was way up. sqlite> vacuum into 'vac_into_from_ro_file.sqlite'; Run Time: real 1107.448 user 853.185069 sys 87.048558 Gonna run sqlite3_analyzer.exe on them now to see what's up. But closing in on the end of the work day/week here. -Original Message- From: David Raymond Sent: Friday, February 08, 2019 1:53 PM To: SQLite mailing list Subject: RE: [sqlite] Vacuum into Non-scientific "let's just try it" results Short version: Original file had been vacuumed already as the last thing that had happened to it. File size: 20,467,359,744 sqlite> vacuum into 'vac_into.sqlite'; Run Time: real 589.577 user 222.941029 sys 57.829571 sqlite> vacuum; Run Time: real 1429.063 user 236.325915 sys 199.322478 Synchronous was off, journal mode was normal rollback journal. Was using the computer for normal work while doing it, but nothing else was using the physical drive these were on. Resulting files were the exact same size as the originals (it had been previously vacuumed as mentioned) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 08, 2019 11:29 AM To: SQLite mailing list Subject: Re: [sqlite] Vacuum into On 2/8/19, David Raymond wrote: > So to make sure I'm understanding it ok, with the new vacuum into command, > if I'm the only user of a file, then the sequence... > > open file1 > vacuum into file2 > close file1 > delete file1 > rename file2 to file1 > > ...is going to be potentially more than twice as fast as the old... Think you will be hard-pressed to get a 2x performance increase. Faster, yes. 2x faster, I'm doubtful. But perhaps I'm wrong. Please try it and see and report back. > > open file1 > vacuum > > ...as it saves the whole re-write of the original file, along with all the > rollback journal or wal writes that entails. Correct? > > (With of course the whole "make sure it actually finished and didn't just > die" caveats before doing the delete and rename) > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum into
On 2/8/19, David Raymond wrote: > Non-scientific "let's just try it" results > > Short version: > Original file had been vacuumed already as the last thing that had happened > to it. > File size: 20,467,359,744 > > sqlite> vacuum into 'vac_into.sqlite'; > Run Time: real 589.577 user 222.941029 sys 57.829571 > > sqlite> vacuum; > Run Time: real 1429.063 user 236.325915 sys 199.322478 > > Synchronous was off, journal mode was normal rollback journal. Fair enough. I wasn't thinking about the overhead of journaling when a normal VACUUM copies the new database back over top of itself. It is having to do two complete copies of the database, not just one. So you may well get more than 2x better performance. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum into
Non-scientific "let's just try it" results Short version: Original file had been vacuumed already as the last thing that had happened to it. File size: 20,467,359,744 sqlite> vacuum into 'vac_into.sqlite'; Run Time: real 589.577 user 222.941029 sys 57.829571 sqlite> vacuum; Run Time: real 1429.063 user 236.325915 sys 199.322478 Synchronous was off, journal mode was normal rollback journal. Was using the computer for normal work while doing it, but nothing else was using the physical drive these were on. Resulting files were the exact same size as the originals (it had been previously vacuumed as mentioned) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 08, 2019 11:29 AM To: SQLite mailing list Subject: Re: [sqlite] Vacuum into On 2/8/19, David Raymond wrote: > So to make sure I'm understanding it ok, with the new vacuum into command, > if I'm the only user of a file, then the sequence... > > open file1 > vacuum into file2 > close file1 > delete file1 > rename file2 to file1 > > ...is going to be potentially more than twice as fast as the old... Think you will be hard-pressed to get a 2x performance increase. Faster, yes. 2x faster, I'm doubtful. But perhaps I'm wrong. Please try it and see and report back. > > open file1 > vacuum > > ...as it saves the whole re-write of the original file, along with all the > rollback journal or wal writes that entails. Correct? > > (With of course the whole "make sure it actually finished and didn't just > die" caveats before doing the delete and rename) > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum into
If you renamed file1.db to file1.bak, opened file1.bak, vacuum into file1.db, close file1.bak, you have a backup pre-vacuum (just in case...) and 'streamlines' the process some-what. Obviously, you'd have to rename the file back again if the vacuum failed (out of disk space, etc) Just a thought Chris On Fri, Feb 8, 2019 at 4:22 PM David Raymond wrote: > So to make sure I'm understanding it ok, with the new vacuum into command, > if I'm the only user of a file, then the sequence... > > open file1 > vacuum into file2 > close file1 > delete file1 > rename file2 to file1 > > ...is going to be potentially more than twice as fast as the old... > > open file1 > vacuum > > ...as it saves the whole re-write of the original file, along with all the > rollback journal or wal writes that entails. Correct? > > (With of course the whole "make sure it actually finished and didn't just > die" caveats before doing the delete and rename) > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum into
On 2/8/19, David Raymond wrote: > So to make sure I'm understanding it ok, with the new vacuum into command, > if I'm the only user of a file, then the sequence... > > open file1 > vacuum into file2 > close file1 > delete file1 > rename file2 to file1 > > ...is going to be potentially more than twice as fast as the old... Think you will be hard-pressed to get a 2x performance increase. Faster, yes. 2x faster, I'm doubtful. But perhaps I'm wrong. Please try it and see and report back. > > open file1 > vacuum > > ...as it saves the whole re-write of the original file, along with all the > rollback journal or wal writes that entails. Correct? > > (With of course the whole "make sure it actually finished and didn't just > die" caveats before doing the delete and rename) > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Vacuum into
So to make sure I'm understanding it ok, with the new vacuum into command, if I'm the only user of a file, then the sequence... open file1 vacuum into file2 close file1 delete file1 rename file2 to file1 ...is going to be potentially more than twice as fast as the old... open file1 vacuum ...as it saves the whole re-write of the original file, along with all the rollback journal or wal writes that entails. Correct? (With of course the whole "make sure it actually finished and didn't just die" caveats before doing the delete and rename) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
