Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Kim Gräsman]

Hi Bob,

Den 18 maj 2017 9:15 em skrev "Bob Friesenhahn" <
bfrie...@simple.dallas.tx.us>:

On Thu, 18 May 2017, Kim Gräsman wrote:

>
>> The request is issued early on when the connection is first opened so no
>> actual queries have been issued at that time.
>>
>
> Then my (black-box) guess is that you're seeing the bump from
> allocating heap space for whatever structures the schema needs.
>

Our schema takes a bit over 1MB to load on a 32-bit CPU.  The increased
usage we are seeing is on the order of 200k so it is not the schema.
Regardless, the application is using the database immediately so it must
always consume the schema.


Well, when you run pragma cache_size before any other queries, it *will*
load the schema. So unless you are also seeing a 1MB bump besides the 200K,
it must be schema data. Maybe it occupies less memory than you've
estimated?

Perhaps the SQLite devs can think of a way to diagnose this more exactly.

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

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Bob Friesenhahn]

On Thu, 18 May 2017, Kim Gräsman wrote:


The request is issued early on when the connection is first opened so no
actual queries have been issued at that time.


Then my (black-box) guess is that you're seeing the bump from
allocating heap space for whatever structures the schema needs.


Our schema takes a bit over 1MB to load on a 32-bit CPU.  The 
increased usage we are seeing is on the order of 200k so it is not the 
schema.  Regardless, the application is using the database immediately 
so it must always consume the schema.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Kim Gräsman]

On Thu, May 18, 2017 at 8:27 PM, Bob Friesenhahn
 wrote:
> On Thu, 18 May 2017, Kim Gräsman wrote:
>
>> On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
>>  wrote:
>>>
>>> Does anyone know why using 'PRAGMA cache_size=0' (or some other small
>>> value)
>>> to attempt to decrease memory usage (and it is reported as immediately
>>> decreased in the shell by .stats) actually significantly increases heap
>>> memory usage?
>>
>>
>> It sounds like you have active operation with a larger cache size
>> before issuing the PRAGMA, is that so?
>
>
> The request is issued early on when the connection is first opened so no
> actual queries have been issued at that time.

Then my (black-box) guess is that you're seeing the bump from
allocating heap space for whatever structures the schema needs.

> An earlier developer had tried the same thing almost 5 years ago (with a
> much older sqlite) and noticed a 200k jump in heap usage.

We're at 3.14.1, so I'm also speaking from an older timeframe.

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

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Bob Friesenhahn]

On Thu, 18 May 2017, Kim Gräsman wrote:


On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
 wrote:

Does anyone know why using 'PRAGMA cache_size=0' (or some other small value)
to attempt to decrease memory usage (and it is reported as immediately
decreased in the shell by .stats) actually significantly increases heap
memory usage?


It sounds like you have active operation with a larger cache size
before issuing the PRAGMA, is that so?


The request is issued early on when the connection is first opened so 
no actual queries have been issued at that time.


An earlier developer had tried the same thing almost 5 years ago (with 
a much older sqlite) and noticed a 200k jump in heap usage.



If not, 'PRAGMA cache_size' loads the schema under the hood, and
depending on how large/complex it is, this can make quite a footprint.


Our schema is quite large/complex.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Kim Gräsman]

On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
 wrote:
> Does anyone know why using 'PRAGMA cache_size=0' (or some other small value)
> to attempt to decrease memory usage (and it is reported as immediately
> decreased in the shell by .stats) actually significantly increases heap
> memory usage?

It sounds like you have active operation with a larger cache size
before issuing the PRAGMA, is that so?

If not, 'PRAGMA cache_size' loads the schema under the hood, and
depending on how large/complex it is, this can make quite a footprint.

I noticed this first hand when I issued 'PRAGMA cache_size=128' with a
fixed heap on a large schema and immediately ran out of memory.

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

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Bob Friesenhahn]

On Thu, 18 May 2017, Simon Slavin wrote:



On 18 May 2017, at 5:10pm, Bob Friesenhahn  wrote:


Does anyone know why using 'PRAGMA cache_size=0' (or some other small value) to 
attempt to decrease memory usage (and it is reported as immediately decreased 
in the shell by .stats) actually significantly increases heap memory usage?


Which OS ?


Linux with uclibc.


What are you using to report heap memory size/usage ?


A Python script named 'ps_mem.py' which is available from 
"https://github.com/pixelb/ps_mem;.  It tallies data from /proc so it 
is very accurate about actual usage.


It is not clear to me if setting the pragma jumbles up the heap a bit 
so more memory is consumed, or if the cache is more efficient than the 
alternative.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

[Simon Slavin]

On 18 May 2017, at 5:10pm, Bob Friesenhahn  wrote:

> Does anyone know why using 'PRAGMA cache_size=0' (or some other small value) 
> to attempt to decrease memory usage (and it is reported as immediately 
> decreased in the shell by .stats) actually significantly increases heap 
> memory usage?

Which OS ?
What are you using to report heap memory size/usage ?

> I find this to be an interesting phenomena.

Indeed.

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

Re: [sqlite] PRAGMA cache_size = 0

[Martin Jenkins]

B V, Phanisekhar wrote:

What exactly happens when I change the cache_size (both increase and
decrease size)?



A variable is set.


It seems this term is a misnomer. What are we achieving by setting this
variable?


[...]


Will there be any memory that will be freed up when I reduce the size
of result cache?



No.


So whats there in this cache?


I'd have thought setting the cache size was (meant to be) a one-off 
operation? Changing the cache size on the fly and expecting the cache to 
be valid must be an unusual case (well, it is to me and I've not seen 
anyone else asking about it) so I reckon you'd be better off looking at 
the source code, doing a few tests of your own and reporting back.


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[B V, Phanisekhar]

>> What exactly happens when I change the cache_size (both increase and
>> decrease size)?

> A variable is set.

It seems this term is a misnomer. What are we achieving by setting this
variable? 

This is what is mentioned in the documentation of SQLITE:

PRAGMA cache_size;
PRAGMA cache_size = Number-of-pages;

Query or change the maximum number of database disk pages that SQLite
will hold in memory at once. Each page uses about 1.5K of memory. The
default cache size is 2000. If you are doing UPDATEs or DELETEs that
change many rows of a database and you do not mind if SQLite uses more
memory, you can increase the cache size for a possible speed
improvement.

So without allocation extra memory how can I increase the size of
cache_size?


>> What happens to the data that's there in the result cache at the time
>> when the instruction PRAGMA cache_size = 0 is executed? 

> Nothing. The aforementioned variable is set to 10 instead of the
> specified value.

>> Will there be any memory that will be freed up when I reduce the size
of
>> result cache?

> No.

So whats there in this cache?




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PRAGMA cache_size = 0

[Dan Kennedy]

> What exactly happens when I change the cache_size (both increase and
> decrease size)?

A variable is set.

> What happens to the data that's there in the result cache at the time
> when the instruction PRAGMA cache_size = 0 is executed? 

Nothing. The aforementioned variable is set to 10 instead of the
specified value.

> Will there be any memory that will be freed up when I reduce the size of
> result cache?

No.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[Joe Wilson]

> I tried to set the cache size to 0 (after sqlite3_open0, and then query
> for pragma cache_size which returns 2000 (default cache size). Why its
> not returning 10 (according to Weiyang Wang)?

It does report 0, even though internally it is using a value of 10.

 SQLite version 3.3.17
 Enter ".help" for instructions
 sqlite> pragma cache_size;
 2000
 sqlite> pragma cache_size=0;
 sqlite> pragma cache_size;
 0

http://www.sqlite.org/cvstrac/tktview?tn=2393


   

Moody friends. Drama queens. Your life? Nope! - their life, your story. Play 
Sims Stories at Yahoo! Games.
http://sims.yahoo.com/  

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[B V, Phanisekhar]

I tried to set the cache size to 0 (after sqlite3_open0, and then query
for pragma cache_size which returns 2000 (default cache size). Why its
not returning 10 (according to Weiyang Wang)?


Regards,
Phani



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 12, 2007 3:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] PRAGMA cache_size = 0

"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answers for the following questions.
> 

Weiyang Wang correctly answered your question at

   http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[B V, Phanisekhar]

What exactly happens when I change the cache_size(both increase and
decrease size)? What happens to the data that's there in the result
cache at the time when the instruction PRAGMA cache_size = 0 is
executed? Will there be any memory that will be freed up when I reduce
the size of result cache?

Regards,
Phani




-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 12, 2007 3:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] PRAGMA cache_size = 0

"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answers for the following questions.
> 

Weiyang Wang correctly answered your question at

   http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PRAGMA cache_size = 0

[drh]

"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answers for the following questions.
> 

Weiyang Wang correctly answered your question at

   http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[B V, Phanisekhar]

I am yet to get answers for the following questions.

 

> What happens if I set the cache_size to 0? Will I be able to do any of

> update/delete/insert/select operations?

> 

> When I set the cache_size to 0, is there any freeing up of memory by

> sqlite?

 

Regards,

Phani

Re: [sqlite] PRAGMA cache_size = 0

[weiyang wang]

correct my former message, the smallest is 10, (10 pages in ram)

On 6/11/07, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:


What happens if I set the cache_size to 0? Will I be able to do any of
update/delete/insert/select operations?

When I set the cache_size to 0, is there any freeing up of memory by
sqlite?



Regards,

Phani

Re: [sqlite] PRAGMA cache_size = 0

[weiyang wang]

the smallest cache_size is 512k, i guess.

On 6/11/07, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:


What happens if I set the cache_size to 0? Will I be able to do any of
update/delete/insert/select operations?

When I set the cache_size to 0, is there any freeing up of memory by
sqlite?



Regards,

Phani