Re: [sqlite] VACUUM requires 6.7 times space ?

[Roger Binns]

-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 ?

[Jay Kreibich]

On Jan 5, 2015, at 5:03 PM, Eduardo Morras  wrote:

> 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 ?

[Eduardo Morras]

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.

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 ?

[Jay Kreibich]

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.

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 ?

[Jim Wilcoxson]

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 ?

[Jay Kreibich]

On Jan 5, 2015, at 8:43 AM, Nelson, Erik - 2  
wrote:

> 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 ?

[Nelson, Erik - 2]

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 ?

[Simon Slavin]

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.
___
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 ?

[Nelson, Erik - 2]

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 ?

[RSmith]

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 ?

[Simon Slavin]

On 5 Jan 2015, at 11:32am, Dan Kennedy  wrote:

> 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 ?

[Igor Korot]

Hi, Simon,

On Mon, Jan 5, 2015 at 6:32 AM, 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.
>> 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 ?

[Dan Kennedy]

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 ?

[Simon Slavin]

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