PRAGMA cache_size=0 increases memory usage

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

PRAGMA cache_size=0 increases memory usage

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

I find this to be an interesting phenomena.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: PRAGMA cache_size=0 increases memory usage

Simon Slavin-3

On 18 May 2017, at 5:10pm, Bob Friesenhahn <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: PRAGMA cache_size=0 increases memory usage

Kim Gräsman
In reply to this post by Bob Friesenhahn
On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
<[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
> <[hidden email]> 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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: PRAGMA cache_size=0 increases memory usage

Kim Gräsman
On Thu, May 18, 2017 at 8:27 PM, Bob Friesenhahn
<[hidden email]> wrote:

> On Thu, 18 May 2017, Kim Gräsman wrote:
>
>> On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn
>> <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: PRAGMA cache_size=0 increases memory usage

Kim Gräsman
Hi Bob,

Den 18 maj 2017 9:15 em skrev "Bob Friesenhahn" <
[hidden email]>:

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users