Re: [sqlite] VACUUM and PRAGMA temp_store

[Mario M. Westphal]

Great :-)
 
I guess that PRAGMA temp_store=MEMORY then does not add additional
performance on Windows and I can safely let it to DEFAULT or FILE.
This will avoid the excessive memory usage during VACUUM for my use case.
 
Thanks.
 
-- Mario
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM and PRAGMA temp_store

[Richard Hipp]

On Sun, Jun 23, 2013 at 5:06 AM, Mario M. Westphal  wrote:

>
> When creating/opening a file in Windows, an application can mark this file
> as "temporary" (flag: FILE_ATTRIBUTE_TEMPORARY) and Windows will try to
> keep
> it in memory if sufficient memory is available, avoiding all writes to the
> medium. Does SQLite use this feature on Windows when it creates temporary
> files?
>

Yes.  It sets FILE_ATTRIBUTE_TEMPORARY, FILE_ATTRIBUTE_HIDDEN, and
FILE_ATTRIBUTE_DELETE_ON_CLOSE.

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

[sqlite] VACUUM and PRAGMA temp_store

[Mario M. Westphal]

Hello List
 
the SQLite databases I use on Windows can become fairly large (several GB).
I just noticed that running a VACCUM on such a large database (where several
of the tables are also real large) can cause excessive memory usage (more
than 2.5 GB RAM in peak).
 
I tracked this down to using  "PRAGMA temp_store=MEMORY" when opening the
database.
 
When I change this to 
 
"PRAGMA temp_store=DEFAULT" or "PRAGMA temp_store=FILE"
 
the VACUUM requires almost no RAM, even for large databases.
 
 
Question:
 
Can I change PRAGMA temp_store for an open database before I run the VACUUM?
I would set it to FIILE before and back to MEMORY afterwards.
 
Is PRAGMA temp_store useful at all when used on Windows? Or can one rely on
the Windows built-in file system cache?
 
When creating/opening a file in Windows, an application can mark this file
as "temporary" (flag: FILE_ATTRIBUTE_TEMPORARY) and Windows will try to keep
it in memory if sufficient memory is available, avoiding all writes to the
medium. Does SQLite use this feature on Windows when it creates temporary
files?
 
 
Thanks for your support.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users