[sqlite] PRAGMA cache_size problem

[Jaco Breitenbach]

Dear all,

I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
application is written in C, so uses the SQLite C interface.

At startup I connect to an admin datafile, and then attach 20 more datafiles
to the connection.  After creating the initial connection (before attaching
to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
the cache size with "PRAGMA cache_size", the correct value is returned.
However, this appears to have no actual effect on the application.  As I
proceed to insert data randomly into the 20 attached datafiles, the
application's memory footprint is capped at 64 MB, even though the combined
size of the datafiles is well over 400 MB.

As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
Without issuing the PRAGMA, the memory (cache) size now continues to grow to
match the size of the datafiles as expected.

Has any of you encountered this problem before?  Am I doing something wrong,
or could there be a problem with this release of SQLite?

Any ideas or comments will be much appreciated.

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

Re: [sqlite] PRAGMA cache_size problem

[Jaco Breitenbach]

Hi Dan,

That fixed the problem for me.  Thanks a lot, much appreciated!
Best regards,
Jaco
On 23 March 2011 09:10, Dan Kennedy  wrote:

> On 03/23/2011 03:26 PM, Jaco Breitenbach wrote:
> >   Dear experts,
> >
> > I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
> > application is written in C, so uses the SQLite C interface.
> >
> > At startup I connect to an admin datafile, and then attach 20 more
> datafiles
> > to the connection.  After creating the initial connection (before
> attaching
> > to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
> > the cache size with "PRAGMA cache_size", the correct value is returned.
> > However, this appears to have no actual effect on the application.  As I
> > proceed to insert data randomly into the 20 attached datafiles, the
> > application's memory footprint is capped at 64 MB, even though the
> combined
> > size of the datafiles is well over 400 MB.
> >
> > As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
> > Without issuing the PRAGMA, the memory (cache) size now continues to grow
> to
> > match the size of the datafiles as expected.
>
> Each attached database has a separate limit. You will need to do:
>
>   ATTACH 'new.db' AS aux;
>   PRAGMA aux.cache_size = 1048576;
>
> or something.
>
> Dan.
> ___
> 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] PRAGMA cache_size problem

[Dan Kennedy]

On 03/23/2011 03:26 PM, Jaco Breitenbach wrote:
>   Dear experts,
>
> I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
> application is written in C, so uses the SQLite C interface.
>
> At startup I connect to an admin datafile, and then attach 20 more datafiles
> to the connection.  After creating the initial connection (before attaching
> to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
> the cache size with "PRAGMA cache_size", the correct value is returned.
> However, this appears to have no actual effect on the application.  As I
> proceed to insert data randomly into the 20 attached datafiles, the
> application's memory footprint is capped at 64 MB, even though the combined
> size of the datafiles is well over 400 MB.
>
> As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
> Without issuing the PRAGMA, the memory (cache) size now continues to grow to
> match the size of the datafiles as expected.

Each attached database has a separate limit. You will need to do:

   ATTACH 'new.db' AS aux;
   PRAGMA aux.cache_size = 1048576;

or something.

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

[sqlite] PRAGMA cache_size problem

[Jaco Breitenbach]

 Dear experts,

I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
application is written in C, so uses the SQLite C interface.

At startup I connect to an admin datafile, and then attach 20 more datafiles
to the connection.  After creating the initial connection (before attaching
to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
the cache size with "PRAGMA cache_size", the correct value is returned.
However, this appears to have no actual effect on the application.  As I
proceed to insert data randomly into the 20 attached datafiles, the
application's memory footprint is capped at 64 MB, even though the combined
size of the datafiles is well over 400 MB.

As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
Without issuing the PRAGMA, the memory (cache) size now continues to grow to
match the size of the datafiles as expected.

Has any of you encountered this problem before?  Am I doing something wrong,
or could there be a problem with this release of SQLite?

Any ideas or comments will be much appreciated.

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