Re: [sqlite] Vacuum command fails

[Simon Slavin]

On 18 Jul 2014, at 4:45pm, Carlos Ferreira  wrote:

> This is on a standard windows 7 64 bits laptop.
> 
> I am not using a shell command. I am using the win32/win64 precompiled Dlls.

Can you please check to see if using VACUUM in the shell tool has the same 
problem with the same database ?  This is because the SQLite team wrote the 
shell tool themselves and if there's anything wrong with it they have full 
source and can do lots of testing.  Precompiled shell tool can be downloaded 
from the sqlite download page.

If the shell tool handles VACUUM okay and your code doesn't, that indicates 
that the problem is in your code.  That doesn't mean we won't help you figure 
out what's wrong, it just tells us where to look for anything unusual.

> I will check the pragma integrity check...

Please do.  You can use the shell tool for that too, before you try VACUUM in 
it.

> One thing I noticed before was that the maximum size for a blob inside a
> record field is much smaller than what I imagined.
> 
> If I use blobs with more than 200 Mb, things go wrong when trying to read it
> back using the direct blob functions.
> 
> I am going to check carefully if there is any other limit I am hitting

Limits are here:



I don't think your problem is with limits, it's more likely to be memory 
handling in someone's code, but if the shell tool fails to do VACUUM we'll be 
very interested.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Carlos Ferreira]

Hi,

This is on a standard windows 7 64 bits laptop.

I am not using a shell command. I am using the win32/win64 precompiled Dlls.

I will check the pragma integrity check...

One thing I noticed before was that the maximum size for a blob inside a
record field is much smaller than what I imagined.

If I use blobs with more than 200 Mb, things go wrong when trying to read it
back using the direct blob functions.

I am going to check carefully if there is any other limit I am hitting
because in this case the only difference between the database that fails and
the database that works is that the working database has much smaller
blobs...and proximately the same number of records.
 
Regards.

Carlos


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: sexta-feira, 18 de Julho de 2014 16:34
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vacuum command fails


On 18 Jul 2014, at 4:11pm, Carlos Ferreira <car...@csiberkeley.com> wrote:

> I also have a problem with VACCUM. ( both 32 and 64 bits )
> 
> Whenever I deal with small databases, it works fine, but as soon as 
> the DB is more than 200 Mb, the vaccum command bails out with code 21.
>   or 

Is this on a standard type of computer, or in a small device ?

If you use the VACUUM command from the SQLite shell tool (copy the database
onto a standard computer if necessary), rather than your own application, do
you get the same result ?

Does your database show any corruption when you use "PRAGMA
integrity_check;" ?

Simon.
___
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 command fails

[Simon Slavin]

On 18 Jul 2014, at 4:11pm, Carlos Ferreira  wrote:

> I also have a problem with VACCUM. ( both 32 and 64 bits )
> 
> Whenever I deal with small databases, it works fine, but as soon as the DB
> is more than 200 Mb, the vaccum command bails out with code 21.
>   or 

Is this on a standard type of computer, or in a small device ?

If you use the VACUUM command from the SQLite shell tool (copy the database 
onto a standard computer if necessary), rather than your own application, do 
you get the same result ?

Does your database show any corruption when you use "PRAGMA integrity_check;" ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Carlos Ferreira]

Hi,

I also have a problem with VACCUM. ( both 32 and 64 bits )

Whenever I deal with small databases, it works fine, but as soon as the DB
is more than 200 Mb, the vaccum command bails out with code 21.

  or 

Any idea ?

I can make my own copy and rename ..that is probably what Vaccum does...but
using vaccum would be very nice if it would work ok...

Thanks

Carlos



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RSmith
Sent: sexta-feira, 18 de Julho de 2014 15:41
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vacuum command fails


On 2014/07/18 16:30, Nelson, Erik - 2 wrote:
> veeresh kumar wrote:
>
>   
>> Now I am running the the application on D: Drive (it has 841 GB free
>>   space) and C: drive has space (333 GB free). It looks like VACUUM 
>> uses C:drive space ??? Also command seems to be working fine...
>>
> Maybe I'm missing something, but I felt like this was pretty clearly
answered before.
>
> PRAGMA temp_store_directory returns and controls the directory that is
used for temp files.  You can query it, and it will  probably resolve to
your C: drive, which is probably where your TEMP or TMP environment
variables point.
>
> If your temp store directory doesn't have enough space, the VACUUM will
fail.

And to add, you can change this by changing the TEMP or TMP directive in
your OS, it is not up to SQLite to decide where your OS intends temp files
to be, but it is changable.



___
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 command fails

[RSmith]

On 2014/07/18 16:30, Nelson, Erik - 2 wrote:

veeresh kumar wrote:

  

Now I am running the the application on D: Drive (it has 841 GB free
  space) and C: drive has space (333 GB free). It looks like VACUUM uses
C:drive space ??? Also command seems to be working fine...


Maybe I'm missing something, but I felt like this was pretty clearly answered 
before.

PRAGMA temp_store_directory returns and controls the directory that is used for 
temp files.  You can query it, and it will  probably resolve to your C: drive, 
which is probably where your TEMP or TMP environment variables point.

If your temp store directory doesn't have enough space, the VACUUM will fail.


And to add, you can change this by changing the TEMP or TMP directive in your OS, it is not up to SQLite to decide where your OS 
intends temp files to be, but it is changable.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Nelson, Erik - 2]

veeresh kumar wrote:

 
> Now I am running the the application on D: Drive (it has 841 GB free
>  space) and C: drive has space (333 GB free). It looks like VACUUM uses
> C:drive space ??? Also command seems to be working fine...
> 

Maybe I'm missing something, but I felt like this was pretty clearly answered 
before.  

PRAGMA temp_store_directory returns and controls the directory that is used for 
temp files.  You can query it, and it will  probably resolve to your C: drive, 
which is probably where your TEMP or TMP environment variables point.

If your temp store directory doesn't have enough space, the VACUUM will fail.

Erik

--
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 command fails

[veeresh kumar]

Hi Simon,,

Happy to provide you with answers...Answers Inline...

One thing i would like to know is does the VACUUM command uses C: drive space 
or the drive where my application is running...It seems like it requires C: 
drive to have enough space.




On Thursday, 17 July 2014 2:14 PM, Simon Slavin  wrote:
 



On 17 Jul 2014, at 10:03pm, veeresh kumar  wrote:

> I did google aswell :-)...I did set PRAGMA temp_store = 1 (PRAGMA temp_store 
> = FILE). But it did not help. I get SQLITE_NOMEM error message.

What version of SQLite are you using ?  The simplest way is probably to tell us 
the result of "sqlite_version()" give by your own application.


Veeresh: Sqlite Version 3.8.4.3

Are you using any PRAGMAs at all in your code ?  In other words, can we assume 
that all PRAGMAs are set to their defaults ?


Veeresh: Yes, we are using PRAGMA and
all PRAGMA are set to default except PRAGMA temp_store which is set to 1

If you open your database in the SQLite shell tool and execute "PRAGMA 
integrity_check;" on it, do you get an error ?

If you open your database in the SQLite shell tool and execute "VACUUM;" on it, 
do you still get an error ?

> Its not a space issue as I do have enough space in the hard drive.

Just to make this clear, do you have at least three times as much free space on 
your hard drive as the size of the database ?

Veeresh: The application runs on Windows 

Earlier I was running the application on another machine where C: drive was 
having less space (like 1 GB) and D Drive had (20 GB free space) and the VACUUM 
failed there.

Now I am running the the application on D: Drive (it has 841 GB free
 space) and C: drive has space (333 GB free). It looks like VACUUM uses 
C:drive space ??? Also command seems to be working fine...

I am sorry for these detailed questions which may seem to be attacking you, but 
we have run out of obvious causes for this problem and are trying to check all 
the unlikely things.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[veeresh kumar]

My earlier email didnt went due to size limit. May be it might come later. Here 
is my summary

What I understood after this exercise i.e executing VACUUM command is

- It requires C: drive free space and not the drive where your application is 
running. 

Since PRAGMA temp_store_directory is depricated, we dont have option to set the 
temp path.

- We have to forcefully set PRAGMA temp_store = FILE in order for the command 
to use disk space and not RAM space.Default setting of temp_store isn't working 
in my case.


I ran the application by making sure it meets above criteria and the command 
was successful. If my above understanding is correct, i guess we need to update 
help document. 

Thank you
-Veeresh



On Thursday, 17 July 2014 10:30 AM, Simon Slavin  wrote:
 



On 17 Jul 2014, at 6:24pm, veeresh kumar  wrote:

> I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
> with an error "out of memory". I was under the impression that it copies the 
> database file and then performs vacuum on that file. Here it looks like its 
> using RAM.

What's your

PRAGMA temp_store

set to ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[RSmith]

On 2014/07/17 23:03, veeresh kumar wrote:

I did google aswell :-)...I did set PRAGMA temp_store = 1 (PRAGMA temp_store = 
FILE). But it did not help. I get SQLITE_NOMEM error message.


Might I offer one more possible (although probably not very likely) problem - I have just recently posted an experiment in memory 
usage where I had the SQLITE_NOMEM error returned after trying to execute a query for which the cache size limit exceeded available 
system resources in 32-bit mode of the test software. Could it be your page size multiplied by the cache pages (both selectable or 
viewable via their respective pragmas) exceeds about 2GB? If so a 14GB Vacuum would probably try to use a lot of memory and if the 
cache limit exceeds the resource limit that error might happen.


Not very likely, but possible. Good luck!




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Simon Slavin]

On 17 Jul 2014, at 10:03pm, veeresh kumar  wrote:

> I did google aswell :-)...I did set PRAGMA temp_store = 1 (PRAGMA temp_store 
> = FILE). But it did not help. I get SQLITE_NOMEM error message.

What version of SQLite are you using ?  The simplest way is probably to tell us 
the result of "sqlite_version()" give by your own application.

Are you using any PRAGMAs at all in your code ?  In other words, can we assume 
that all PRAGMAs are set to their defaults ?

If you open your database in the SQLite shell tool and execute "PRAGMA 
integrity_check;" on it, do you get an error ?

If you open your database in the SQLite shell tool and execute "VACUUM;" on it, 
do you still get an error ?

> Its not a space issue as I do have enough space in the hard drive.

Just to make this clear, do you have at least three times as much free space on 
your hard drive as the size of the database ?

I am sorry for these detailed questions which may seem to be attacking you, but 
we have run out of obvious causes for this problem and are trying to check all 
the unlikely things.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[veeresh kumar]

I did google aswell :-)...I did set PRAGMA temp_store = 1 (PRAGMA temp_store = 
FILE). But it did not help. I get SQLITE_NOMEM error message.

Its not a space issue as I do have enough space in the hard drive. Whenver 
Vacuum command is executed, its using the RAM and thats were it goes out of 
memory. 



On Thursday, 17 July 2014 12:11 PM, Roger Binns  wrote:
 


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/14 10:42, veeresh kumar wrote:
> When i execute the command PRAGMA temp_store, it returned me 0. What is
> the ideal value that needs to be set?

A quick google search would have found the answer:

  https://sqlite.org/pragma.html#pragma_temp_store

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlPIH8wACgkQmOOfHg372QRtLwCfYUzGhB4UKejmTT0qcVRRHNQy
bRgAn2MdaOspER3bgVcwlgKjLq3G8akr
=B7gd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Simon Slavin]

On 17 Jul 2014, at 6:24pm, veeresh kumar  wrote:

> I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
> with an error "out of memory".

What kind of error is this ?  Are you getting SQLITE_NOMEM returned from a 
SQLite call or is this being returned from some part of your operating system ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Nelson, Erik - 2]

Luuk wrote:

>so, 0 means temp is written to disk.

>Back to the the question.

>How much free disk space is there?

>I think you need more than 14Gb of free space if your database is 14Gb in size.

>http://lmgtfy.com/?q=sqlite+vacuum+how+much+disk+space+is+needed=1

>"This means that when VACUUMing a database, as much as twice the size of the 
>original database file is required in free disk space. "

>So, you will need 28Gb of free disk space..


Sometimes *where* the free space is makes a difference... for example, if the 
temporary file is generated in /tmp, then /tmp needs to have sufficient free 
space.  In my experience, /tmp is frequently too small to vacuum large 
databases.  The tmp location can be controlled by a (deprecated, IIRC, and 
hopefully never removed) pragma.

Erik

--
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 command fails

[Luuk]

On 17-7-2014 21:11, Roger Binns wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/14 10:42, veeresh kumar wrote:

When i execute the command PRAGMA temp_store, it returned me 0. What is
the ideal value that needs to be set?


A quick google search would have found the answer:

   https://sqlite.org/pragma.html#pragma_temp_store



so, 0 means temp is written to disk.

Back to the the question.

How much free disk space is there?

I think you need more than 14Gb of free space if your database is 14Gb 
in size.


http://lmgtfy.com/?q=sqlite+vacuum+how+much+disk+space+is+needed=1

"This means that when VACUUMing a database, as much as twice the size of 
the original database file is required in free disk space. "

(source: http://sqlite.org/lang_vacuum.html)


So, you will need 28Gb of free disk space..
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/14 10:42, veeresh kumar wrote:
> When i execute the command PRAGMA temp_store, it returned me 0. What is
> the ideal value that needs to be set?

A quick google search would have found the answer:

  https://sqlite.org/pragma.html#pragma_temp_store

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlPIH8wACgkQmOOfHg372QRtLwCfYUzGhB4UKejmTT0qcVRRHNQy
bRgAn2MdaOspER3bgVcwlgKjLq3G8akr
=B7gd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[veeresh kumar]

When i execute the command PRAGMA temp_store, it returned me 0. What is the 
ideal value that needs to be set?



On Thursday, 17 July 2014 10:30 AM, Simon Slavin  wrote:
 



On 17 Jul 2014, at 6:24pm, veeresh kumar  wrote:

> I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
> with an error "out of memory". I was under the impression that it copies the 
> database file and then performs vacuum on that file. Here it looks like its 
> using RAM.

What's your

PRAGMA temp_store

set to ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum command fails

[Simon Slavin]

On 17 Jul 2014, at 6:24pm, veeresh kumar  wrote:

> I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
> with an error "out of memory". I was under the impression that it copies the 
> database file and then performs vacuum on that file. Here it looks like its 
> using RAM.

What's your

PRAGMA temp_store

set to ?

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Vacuum command fails

[veeresh kumar]

I tried to execute Vacuum on the sqlite database of size 14GB and it failed 
with an error "out of memory". I was under the impression that it copies the 
database file and then performs vacuum on that file. Here it looks like its 
using RAM. Appreciate your inputs from the experts.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users