Vacuum into

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Vacuum into

David Raymond
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum into

Richard Hipp-3
On 2/8/19, David Raymond <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum into

Chris Locke-3
In reply to this post by David Raymond
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum into

David Raymond
In reply to this post by Richard Hipp-3
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:[hidden email]] 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum into

Richard Hipp-3
On 2/8/19, David Raymond <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum into

David Raymond
In reply to this post by David Raymond
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:[hidden email]] 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Vacuum into

David Raymond
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:[hidden email]] 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users