Re: [sqlite] VACUUM requires 6.7 times space ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/2015 10:25 AM, Jim Wilcoxson wrote: > It's easy to do this in Python using iterdump(), Be aware that the pysqlite dump implementation is incomplete in many ways. Some issues: - - Doesn't dump in a transaction so will have irregularities if things change during the dump - - Deletes the entire sqlite_sequence table instead of the entry for the table being dumped/restored - - Runs ANALYZE on all tables during restore if sqlite_stat1 exists (ignores later versions of stat), for every table restored - - Doesn't handle virtual tables (certainly can't restore them - probably chokes on backing them up) - - Messes up if any table names or columns have a double quote in their name (probably single quotes too) - - Doesn't disable foreign key processing during restore which can result in a huge mess - - Who knows how many other issues pysqlite's iterdump is 50 lines long. The APSW dump code (also in Python) is 230 lines, and the SQLite shell C code is about 200 lines. pysqlite is definitely missing many of the finer details. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSrRo8ACgkQmOOfHg372QQnfACgz5idM01KvQEDcuXWKaU9M21R OqkAoN/TDCCgOOD5jW2Iqi/obGt57dRY =4OWi -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On Jan 5, 2015, at 5:03 PM, Eduardo Morraswrote: > On Mon, 5 Jan 2015 14:42:28 -0600 > Jay Kreibich wrote: > >> >> >> On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson wrote: >> >>> Simon - instead of using vacuum, it's much faster to create a new >>> database from the old one, then rename it. It's easy to do this in >>> Python using iterdump(), or you can connect to the new (empty) >>> database, do your create table statements, attach the old database >>> as olddb, then do: >>> >>> insert into table1 select * from olddb.table1; >>> insert into table2 select 8 from olddb.table2; >>> >>> This also lets you do the create table stmts w/o indexes, and add >>> the indexes after the inserts. Not sure if that is faster or not >>> for your data. >> >> If you look at code for VACUUM, that’s more or less what it does… >> only it is very smart about it, properly preserving ROWID values, as >> well as exact table definitions, sequences, analytics, and all the >> meta-data in the database. > > Sqlite3 Backup API should work too, and covers your vacuum to file proposal. No, the backup API does a page-by-page copy. It’s goal is to make a bit-perfect copy of the original database file. It makes no attempt to VACUUM the database as it is backed-up, and has no understanding of the data it is copying. The backup-API does not remove empty space or re-order pages, nor does it allow changes to database page size and other alterations supported by VACUUM. The backup API also operates outside the SQL transaction system. VACUUM TO would be a simplified version of VACUUM that only requires 2x the disk space (rather than 3x) and much, much less I/O (possibly 1/3 or so). It would be faster for many applications that control all access to a database, such as applications that use SQLite DBs as their document file format. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On Mon, 5 Jan 2015 14:42:28 -0600 Jay Kreibichwrote: > > > On Jan 5, 2015, at 12:25 PM, Jim Wilcoxson wrote: > > > Simon - instead of using vacuum, it's much faster to create a new > > database from the old one, then rename it. It's easy to do this in > > Python using iterdump(), or you can connect to the new (empty) > > database, do your create table statements, attach the old database > > as olddb, then do: > > > > insert into table1 select * from olddb.table1; > > insert into table2 select 8 from olddb.table2; > > > > This also lets you do the create table stmts w/o indexes, and add > > the indexes after the inserts. Not sure if that is faster or not > > for your data. > > If you look at code for VACUUM, that’s more or less what it does… > only it is very smart about it, properly preserving ROWID values, as > well as exact table definitions, sequences, analytics, and all the > meta-data in the database. Sqlite3 Backup API should work too, and covers your vacuum to file proposal. You can, from sqlite3 shell, .dump the database to .sql file or pipe output to gzip or xz and use the compressed file for import again. Don't need full compression to minimize the io, -6 for gzip and -3 for xz is enough. > > -j --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On Jan 5, 2015, at 12:25 PM, Jim Wilcoxsonwrote: > Simon - instead of using vacuum, it's much faster to create a new > database from the old one, then rename it. It's easy to do this in > Python using iterdump(), or you can connect to the new (empty) > database, do your create table statements, attach the old database as > olddb, then do: > > insert into table1 select * from olddb.table1; > insert into table2 select 8 from olddb.table2; > > This also lets you do the create table stmts w/o indexes, and add the > indexes after the inserts. Not sure if that is faster or not for your > data. If you look at code for VACUUM, that’s more or less what it does… only it is very smart about it, properly preserving ROWID values, as well as exact table definitions, sequences, analytics, and all the meta-data in the database. The “copy to new database” accounts for the 2x size requirement. In the case of VACUUM, the data is then copied from the fresh DB back to the original database file in a transaction-safe way; this touches every page in the original file, requiring a rollback log of some sort (journal or WAL)— and that accounts for the 3x space requirement. While you can roll your own, the method you propose is not transaction safe and is difficult to use in a production environment unless you know you can shut down all services using the DB and force them to re-open connections to the new file. That’s true of some applications, but far from all of them. About five years ago I proposed a VACUUM TO variant of VACUUM that would more or less do what you’re talking about, without the copy-back operation. It would only require 2x the disk space, and be much faster for applications that were able to use it. I still think it would be a useful, low-cost feature. http://www.mail-archive.com/sqlite-users@sqlite.org/msg50941.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VACUUM requires 6.7 times space ?
Simon - instead of using vacuum, it's much faster to create a new database from the old one, then rename it. It's easy to do this in Python using iterdump(), or you can connect to the new (empty) database, do your create table statements, attach the old database as olddb, then do: insert into table1 select * from olddb.table1; insert into table2 select 8 from olddb.table2; This also lets you do the create table stmts w/o indexes, and add the indexes after the inserts. Not sure if that is faster or not for your data. Good luck! Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On Jan 5, 2015, at 8:43 AM, Nelson, Erik - 2wrote: > RSmith wrote on Monday, January 05, 2015 7:43 AM >> >> >>> On 01/05/2015 06:22 PM, Simon Slavin wrote: I have a database file which is 120GB in size. It consists of two huge tables and an index. //... >>> Probably running out of space wherever temp files are created. >> > > I ran into this a while ago- used pragma temp_store_directory Which is, for good or bad, depreciated. Not sure why. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
Simon Slavin wrote on Monday, January 05, 2015 10:40 AM > > On 5 Jan 2015, at 2:43pm, Nelson, Erik - 2 >wrote: > > > RSmith wrote on Monday, January 05, 2015 7:43 AM > > > >> I haven't done this, but I seem to remember there was a way to tell > >> SQLite where to make temp files, or override the system default at > >> any rate - which may help. > > > > I ran into this a while ago- used pragma temp_store_directory > > That's what I'm trying now. Unfortunately the directory I'm trying to > use has spaces in and is several folders down a hierarchy. I'm just > hoping that the string I've used to set the path doesn't need spaces or > slashes escaped. > > Simon. If you're using the command line, you could try a relative reference like "." -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On 5 Jan 2015, at 2:43pm, Nelson, Erik - 2wrote: > RSmith wrote on Monday, January 05, 2015 7:43 AM > >> I haven't done this, but I seem to remember there was a way to tell >> SQLite where to make temp files, or override the system default at any >> rate - which may help. > > I ran into this a while ago- used pragma temp_store_directory That's what I'm trying now. Unfortunately the directory I'm trying to use has spaces in and is several folders down a hierarchy. I'm just hoping that the string I've used to set the path doesn't need spaces or slashes escaped. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
RSmith wrote on Monday, January 05, 2015 7:43 AM > > On 2015/01/05 13:32, Dan Kennedy wrote: > > On 01/05/2015 06:22 PM, Simon Slavin wrote: > >> I have a database file which is 120GB in size. It consists of two > huge tables and an index. //... > > Probably running out of space wherever temp files are created. > > I haven't done this, but I seem to remember there was a way to tell > SQLite where to make temp files, or override the system default at any > rate - which may help. > I ran into this a while ago- used pragma temp_store_directory -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On 2015/01/05 13:32, Dan Kennedy wrote: On 01/05/2015 06:22 PM, Simon Slavin wrote: I have a database file which is 120GB in size. It consists of two huge tables and an index. //... Probably running out of space wherever temp files are created. I haven't done this, but I seem to remember there was a way to tell SQLite where to make temp files, or override the system default at any rate - which may help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On 5 Jan 2015, at 11:32am, Dan Kennedywrote: > Probably running out of space wherever temp files are created. Oh, that makes a lot of sense. The free space on the boot volume for that system is only 37GB. Okay, I can move the file to another computer. Thanks for the fast and useful answer and to Igor for another possibility. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
Hi, Simon, On Mon, Jan 5, 2015 at 6:32 AM, Dan Kennedywrote: > On 01/05/2015 06:22 PM, Simon Slavin wrote: >> >> I have a database file which is 120GB in size. It consists of two huge >> tables and an index. >> Its journal_mode is DELETE. >> >> It is on a partition with 803GB of free space. By my calculations I have >> 6.7 times the amount of free space as the database is taking up. >> >> I use the SQLite shell tool version 3.7.9 to run VACUUM on it. The Shell >> tool bails out reporting >> >> CPU Time: user 2113.596836 sys 437.660032 >> Error: near line 5 : database or disk full. >> >> My understanding is that VACUUM can't take more than three times the >> current size of the database file. What does the above error mean under >> these circumstances ? > > > Probably running out of space wherever temp files are created. Or the disk is failing... Thank you. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
On 01/05/2015 06:22 PM, Simon Slavin wrote: I have a database file which is 120GB in size. It consists of two huge tables and an index. Its journal_mode is DELETE. It is on a partition with 803GB of free space. By my calculations I have 6.7 times the amount of free space as the database is taking up. I use the SQLite shell tool version 3.7.9 to run VACUUM on it. The Shell tool bails out reporting CPU Time: user 2113.596836 sys 437.660032 Error: near line 5 : database or disk full. My understanding is that VACUUM can't take more than three times the current size of the database file. What does the above error mean under these circumstances ? Probably running out of space wherever temp files are created. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VACUUM requires 6.7 times space ?
I have a database file which is 120GB in size. It consists of two huge tables and an index. Its journal_mode is DELETE. It is on a partition with 803GB of free space. By my calculations I have 6.7 times the amount of free space as the database is taking up. I use the SQLite shell tool version 3.7.9 to run VACUUM on it. The Shell tool bails out reporting CPU Time: user 2113.596836 sys 437.660032 Error: near line 5 : database or disk full. My understanding is that VACUUM can't take more than three times the current size of the database file. What does the above error mean under these circumstances ? I'm currently running "PRAGMA integrity_check" but I have no reason to believe that the database is corrupt, I'm just desperate. If nobody comes up with any suggestions my next tactic is to DROP the index, do the VACUUM, then rebuild the index. I suspect that all three of those operations will be overnight runs so again I'll write a script which does all three and use the shell tool's ".read" function. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users