Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

[Keith Medcalf]

On Tuesday, 14 January, 2020 16:04, Simon Slavin  wrote:

>On 14 Jan 2020, at 10:56pm, Alexandre Doubov  
>wrote:

>> 1) Does the act of bumping this limit up have an effect on memory at
>all (assuming that no more than 999 arguments are passed into
>statements)?

>Section 9:

>

>If I read this correctly, memory is reserved only to the highest number
>you actually bind to, not to the number you set as
>SQLITE_MAX_VARIABLE_NUMBER.

You mean of course COULD bind to, or rather the number of bind parameter slots 
used in the prepared statement, not the number that you actually bind to.  For 
example, you could prepare the following statement:

select * from t where x == ?473

which will require the space that the required 473 bound variable "slots" take, 
even though all those slots are pointing to nothing (which is interpreted as a 
null value).  If you bind something to one of these slots you will also consume 
whatever space is required for the value object that you created for that slot 
to point at.

That is, when you call the one of the sqlite3_bind* interfaces you are 
requiring SQLite to do two things:
(1) convert your "external" data that you passed into an internal value object 
of some description
(2) having the (already allocated slot) point to that internal value object 
(and releasing the value object that slot was previously pointing to, if any)

When you do an sqlite3_clear_bindings the internal value objects pointed to by 
the slots is released, and the slot it set to point to nothing.
When you do an sqlite3_finalize on the prepared statement, the bindings are 
cleared (as above) and the whole statement and slot management space is 
released.

So in the above select, when it is prepared, you can expect that it will 
require space for 473 pointers to represent the parameter slots, plus of course 
whatever space is required for ancilliary management stuff such as the name <-> 
slot mapping hash table for the non-anonymous parameters.  This storage is 
allocated at prepare time and is not released until the statement is finalized. 
 SQLITE_MAX_VARIABLE_NUMBER operates so as to place an upper bound on the size 
of this array.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

[Simon Slavin]

On 14 Jan 2020, at 10:56pm, Alexandre Doubov  wrote:

> 1) Does the act of bumping this limit up have an effect on memory at all 
> (assuming that no more than 999 arguments are passed into statements)?

Section 9:



If I read this correctly, memory is reserved only to the highest number you 
actually bind to, not to the number you set as SQLITE_MAX_VARIABLE_NUMBER.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory usage after close database on linux arm

[Simon Slavin]

On 2 Jun 2017, at 10:18am, Stephane Guibert  
wrote:

> sqlite3_finalize(...
> sqlite3_close_v2(...
> }while

First, check out the result returned by sqlite3_close() as Clemens wrote.  If 
that doesn’t give a useful result code, continue.

After closing the file, execute sqlite3_shutdown().  Does this release the 
extra memory ?  If not, the memory is in use by your own code.  If that’s not 
the case, continue.

Remove the last sqlite3_step() command.  Does this remove the extra memory ?

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

Re: [sqlite] memory usage after close database on linux arm

[Clemens Ladisch]

Stephane Guibert wrote:
> I get memory usage not freed at close.
>
> the return is SQLITE_OK at each step:
>
>  do{
> sqlite3_open_v2(...,,SQLITE_OPEN_READWRITE,0);
> sqlite3_exec(db,"PRAGMA journal_mode = MEMORY",0,0,0);
> sqlite3_prepare_v2(db...
> sqlite3_step( statement Begin //BEGIN EXCLUSIVE TRANSACTION
> sqlite3_reset( statement request
> sqlite3_step( statement request
> sqlite3_column_int64( statement request
> sqlite3_step( statement Commit
> sqlite3_finalize(...
> sqlite3_close_v2(...

Use sqlite3_close() instead to get an error if there are unfinalized
statements.  (sqlite3_close_v2() is intended for garbage-collected
languages where the statement might be destroyed after the connection.)


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

Re: [sqlite] Memory Usage

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/02/2014 03:12 PM, David Muchene wrote:
> I was wondering what options I can tune to make sqlite use more
> memory.

Have you tried using memory mapping (there is a pragma).

  https://sqlite.org/mmap.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlQyunsACgkQmOOfHg372QQtIgCbBEBxTOeztFZcDRi2f1mCtVI3
Z5AAn39YIdXxxsc6CA6DrwSx94r9Ck6D
=KBxA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage

[Clemens Ladisch]

David Muchene wrote:
> I was wondering what options I can tune to make sqlite use more memory.

You did not mention PRAGMA cache_size, so I guess you are not using it?



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

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

> Some changes, if blob is bigger than a few bytes, you should normalize them. 
> If 2 blobs are equal, their id must be equal and you don't waste time 
> comparing nor memory joining blob content. So you get:
They are quite small (max ~70 bytes...)

>  DROP TABLE IF EXISTS tour_blob;
>  CREATE TABLE tour_blob (id INTEGER PRIMARY KEY,
>n_blob blob);
>
>  DROP TABLE IF EXISTS tour;
>  CREATE TABLE tour (id integer,
>score integer NOT NULL,
>cost integer NOT NULL,
>last_poi integer,
>FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON 
> DELETE CASACADE,
>FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) 
> ON DELETE CASACADE,
>PRIMARY KEY(id));
>
> You can replace tour_unsorted_path_idx with a new index too:
>
>> DROP INDEX IF EXISTS tour_unsorted_path_idx;
>> CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);
>
>  DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx;
>  CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, 
> last_poi, cost);
>
> Take care and make cost the last one, because cost is compared with 
> inequality.
Very interesting, this sounds a very good point!

> You use '==' instead '=', take care too. I made the same error in a mail some 
> weeks ago.
Ops...

> For this query:
>
>>
>> SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1
>>  8< -
>> Query:   SELECT id FROM tour ORDER BY id LIMIT ?1
>> Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows)
>>  - >8
>
> I don't know why it doesn't use the primary index. Perhaps analyze statistics 
> before solves the problem.
It seems Explain does not consider the LIMIT, it should not be
something to worry about.

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

Re: [sqlite] Memory usage of sqlite3

[Eduardo Morras]

On Wed, 17 Jul 2013 12:04:52 +0200
Paolo Bolzoni  wrote:

> On Tue, Jul 16, 2013 at 8:13 PM, Eduardo  wrote:
> 
> > Can you show us the query and/or schemas? If not:
> Sure, I appended everything in the bottom of this email.
> Unfortunately gmail will mess-up the layout, I hope it will be
> readable.
> 
> (See here, it seems google does not know the mean of "should")
> https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04
> 
> > a) Does it JOIN multiple big tables (in rows and/or columns)?
> No, but I got a sub query is probably the culprit.
> 
> > e) Did you normalize the database?
> Should be, yes.
> 
> > In both cases (if you can show us the query/schema or not) what do you 
> > really want to ask to the database? (not to us but to sqlite3, perhaps the 
> > query can be reformulated)
> 
> 
> Here is everything... I think the problem is shown in the:
> Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
> that is part of a sub-query.
> 
> 
> -- First statement (sets the pragmas):
> 
> PRAGMA foreign_keys = ON;
> PRAGMA synchronous = OFF;
> PRAGMA journal_mode = MEMORY;
> PRAGMA cache_size = -10240;
> PRAGMA auto_vacuum = NONE;
> PRAGMA locking_mode = EXCLUSIVE;
> PRAGMA secure_delete = OFF;
> PRAGMA temp_store = 0;
> 
> -- Second statement (creates/clean the schema):
> BEGIN TRANSACTION;
> DROP TABLE IF EXISTS tour;
> CREATE TABLE tour (id integer,
>   score integer NOT NULL,
>   cost integer NOT NULL,
>   last_poi integer,
>   unsorted_path blob,
>   unsorted_path_tmp blob,
>   PRIMARY KEY(id));
> 

Some changes, if blob is bigger than a few bytes, you should normalize them. If 
2 blobs are equal, their id must be equal and you don't waste time comparing 
nor memory joining blob content. So you get:

 DROP TABLE IF EXISTS tour_blob;
 CREATE TABLE tour_blob (id INTEGER PRIMARY KEY,
   n_blob blob);

 DROP TABLE IF EXISTS tour;
 CREATE TABLE tour (id integer,
   score integer NOT NULL,
   cost integer NOT NULL,
   last_poi integer,
   FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON 
DELETE CASACADE,
   FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) ON 
DELETE CASACADE,
   PRIMARY KEY(id));

You can replace tour_unsorted_path_idx with a new index too:

> DROP INDEX IF EXISTS tour_unsorted_path_idx;
> CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);

 DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx;
 CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, 
last_poi, cost);

Take care and make cost the last one, because cost is compared with inequality.

You use '==' instead '=', take care too. I made the same error in a mail some 
weeks ago.

For this query:

> 
> SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1
>  8< -
> Query:   SELECT id FROM tour ORDER BY id LIMIT ?1
> Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows)
>  - >8

I don't know why it doesn't use the primary index. Perhaps analyze statistics 
before solves the problem.

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

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Tue, Jul 16, 2013 at 8:13 PM, Eduardo  wrote:

> Can you show us the query and/or schemas? If not:
Sure, I appended everything in the bottom of this email.
Unfortunately gmail will mess-up the layout, I hope it will be
readable.

(See here, it seems google does not know the mean of "should")
https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04

> a) Does it JOIN multiple big tables (in rows and/or columns)?
No, but I got a sub query is probably the culprit.

> e) Did you normalize the database?
Should be, yes.

> In both cases (if you can show us the query/schema or not) what do you really 
> want to ask to the database? (not to us but to sqlite3, perhaps the query can 
> be reformulated)


Here is everything... I think the problem is shown in the:
Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
that is part of a sub-query.


-- First statement (sets the pragmas):

PRAGMA foreign_keys = ON;
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
PRAGMA cache_size = -10240;
PRAGMA auto_vacuum = NONE;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA secure_delete = OFF;
PRAGMA temp_store = 0;

-- Second statement (creates/clean the schema):
BEGIN TRANSACTION;
DROP TABLE IF EXISTS tour;
CREATE TABLE tour (id integer,
  score integer NOT NULL,
  cost integer NOT NULL,
  last_poi integer,
  unsorted_path blob,
  unsorted_path_tmp blob,
  PRIMARY KEY(id));

DROP INDEX IF EXISTS tour_unsorted_path_idx;
CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);

DROP TABLE IF EXISTS categories;
CREATE TABLE categories (tour_id integer NOT NULL,
order_idx integer NOT NULL,
value integer NOT NULL,
FOREIGN KEY (tour_id) REFERENCES tour(id) ON
DELETE CASCADE);

DROP INDEX IF EXISTS cats_tour_id_idx;
CREATE INDEX cats_tour_id_idx ON categories (tour_id);

DROP TABLE IF EXISTS path;
CREATE TABLE path (tour_id integer NOT NULL,
  order_idx integer NOT NULL,
  node_id integer NOT NULL,
  FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE);

DROP INDEX IF EXISTS path_tour_id_idx;
CREATE INDEX path_tour_id_idx ON path (tour_id);

DROP TRIGGER IF EXISTS set_last_poi_trg;
CREATE TRIGGER set_last_poi_trg AFTER INSERT ON path BEGIN
  UPDATE tour SET last_poi = ( SELECT node_id
   FROM path
   WHERE tour_id == NEW.tour_id
   ORDER BY order_idx DESC LIMIT 1 )
  WHERE id == NEW.tour_id; END;
COMMIT;
VACUUM;



-- Finally the statement executed in a normal program
-- execution with the explain if appliable.

-- I prepare all this  statements and put in hash table
-- and finalize them at the end of the program.
-- This output comes from the first time, when I store them.


SQL STATEMENT: INSERT INTO path (tour_id, order_idx, node_id) VALUES
(?1, ?2, ?3)

SQL STATEMENT: INSERT INTO tour (score, cost) VALUES (?1, ?2)

SQL STATEMENT: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2
 8< -
Query:   UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
 - >8

SQL STATEMENT: UPDATE tour SET unsorted_path=unsorted_path_tmp,
unsorted_path_tmp = NULL WHERE id = ?1;
 8< -
Query:   UPDATE tour SET unsorted_path=unsorted_path_tmp,
unsorted_path_tmp = NULL WHERE id = ?1;
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
 - >8


SQL STATEMENT: INSERT INTO categories (tour_id, order_idx, value)
VALUES (?1, ?2, ?3)

SQL STATEMENT: DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 EXECUTE LIST SUBQUERY 0
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi 

Re: [sqlite] Memory usage of sqlite3

[Eduardo]

On Tue, 16 Jul 2013 18:17:41 +0200
Paolo Bolzoni  wrote:

> I tried the experiment again with -g3 -O0, I got less
> information than expected (there are still many unknown
> symbols in libsqlite3.so), but the function requiring all
> this memory is sqlite3_step.

Can you show us the query and/or schemas? If not:

a) Does it JOIN multiple big tables (in rows and/or columns)?
b) If it's joining multiple tables, is the select something like select * .?
c) Do you really need '*' or only use some columns?
d) Do you use LEFT JOINS? Has the joining key an index?
e) Did you normalize the database?


In both cases (if you can show us the query/schema or not) what do you really 
want to ask to the database? (not to us but to sqlite3, perhaps the query can 
be reformulated)

> So maybe it is one complex query?
> I would like to avoid excessive swapping on the
> production server, maybe I should not worry at all?

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

Re: [sqlite] Memory usage of sqlite3

[Simon Slavin]

On 16 Jul 2013, at 5:17pm, Paolo Bolzoni  wrote:

> the function requiring all
> this memory is sqlite3_step.
> 
> So maybe it is one complex query?

Possibly a query for which no good index exists, so SQLite decides to make up 
its own temporary index.

If you consider this amount of memory usage to be excessive (which I am not 
saying is or is not the case) drop your indexes and make up better ones.  
Remember that the purpose of an index is to be ideal for the clauses of a 
SELECT or UPDATE statement.  There is no point in indexing a column just 
because that column gets used a lot.

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

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

I tried the experiment again with -g3 -O0, I got less
information than expected (there are still many unknown
symbols in libsqlite3.so), but the function requiring all
this memory is sqlite3_step.

So maybe it is one complex query?
I would like to avoid excessive swapping on the
production server, maybe I should not worry at all?



On Tue, Jul 16, 2013 at 1:11 PM, Paolo Bolzoni
 wrote:
> On Tue, Jul 16, 2013 at 1:00 PM, Dan Kennedy  wrote:
>> On 07/16/2013 01:49 AM, Paolo Bolzoni wrote:
>
>> A very large blob or string result?
> I would exclude this, I do use blobs... but they are at most
> few dozen of bytes...
>
>> Code allocates (or leaks)
>> tremendous numbers of sqlite3_stmt* handles?
> Thanks to RAII the code should not leak (also valgrind
> confirm this). I allocate statements, but I deallocate only
> at the end. So it cannot explain a peak in memory usage.
>
>> SQLite has various APIs for querying memory usage:
>>
>>   http://www.sqlite.org/c3ref/memory_highwater.html
>>   http://www.sqlite.org/c3ref/c_status_malloc_count.html
>>
>> Or, using the shell tool, the ".stats" command can be used
>> to access the same values.
> I guess I can see something.
>
> At the moment I am running the test again using a sqlite3
> version compiled with -g3 and -O0 so I hope I can get more
> insight...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Tue, Jul 16, 2013 at 1:00 PM, Dan Kennedy  wrote:
> On 07/16/2013 01:49 AM, Paolo Bolzoni wrote:

> A very large blob or string result?
I would exclude this, I do use blobs... but they are at most
few dozen of bytes...

> Code allocates (or leaks)
> tremendous numbers of sqlite3_stmt* handles?
Thanks to RAII the code should not leak (also valgrind
confirm this). I allocate statements, but I deallocate only
at the end. So it cannot explain a peak in memory usage.

> SQLite has various APIs for querying memory usage:
>
>   http://www.sqlite.org/c3ref/memory_highwater.html
>   http://www.sqlite.org/c3ref/c_status_malloc_count.html
>
> Or, using the shell tool, the ".stats" command can be used
> to access the same values.
I guess I can see something.

At the moment I am running the test again using a sqlite3
version compiled with -g3 and -O0 so I hope I can get more
insight...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Dan Kennedy]

On 07/16/2013 01:49 AM, Paolo Bolzoni wrote:

 From 35-40MB to 940MB; I would put massif result but I think the
list deletes attachments.


A very large blob or string result? Code allocates (or leaks)
tremendous numbers of sqlite3_stmt* handles?

SQLite has various APIs for querying memory usage:

  http://www.sqlite.org/c3ref/memory_highwater.html
  http://www.sqlite.org/c3ref/c_status_malloc_count.html

Or, using the shell tool, the ".stats" command can be used
to access the same values.








On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal  wrote:

On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni <
paolo.bolzoni.br...@gmail.com> wrote:


So, sorry if the question sounds very vague. But what can
cause high memory usage in sqlite?  A large transaction
maybe?


What is "high"? In my apps sqlite tends to use 200-400kb or so, which i
don't consider to be all that high considering what it's capable of doing
for me.

--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
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


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

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

The test ended sometime during the night and setting temp_store to 0
the result is exactly the same. I suspect it was the default anyway.



On Mon, Jul 15, 2013 at 9:20 PM, Paolo Bolzoni
 wrote:
> On Mon, Jul 15, 2013 at 9:08 PM, Eduardo Morras  wrote:
>> On Mon, 15 Jul 2013 20:49:52 +0200
>> Paolo Bolzoni  wrote:
>>
>>> From 35-40MB to 940MB; I would put massif result but I think the
>>> list deletes attachments.
>>
>> What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile 
>> with SQLITE_TEMP_STORE set to 3?
> This ones, so no...
> -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1
> -DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_SECURE_DELETE
> "Recheck" needs some time. The test lasted almost 7 hours...
> I start it now...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Mon, Jul 15, 2013 at 9:08 PM, Eduardo Morras  wrote:
> On Mon, 15 Jul 2013 20:49:52 +0200
> Paolo Bolzoni  wrote:
>
>> From 35-40MB to 940MB; I would put massif result but I think the
>> list deletes attachments.
>
> What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile 
> with SQLITE_TEMP_STORE set to 3?
This ones, so no...
-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1
-DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_SECURE_DELETE
"Recheck" needs some time. The test lasted almost 7 hours...
I start it now...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Eduardo Morras]

On Mon, 15 Jul 2013 20:49:52 +0200
Paolo Bolzoni  wrote:

> From 35-40MB to 940MB; I would put massif result but I think the
> list deletes attachments.

What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile with 
SQLITE_TEMP_STORE set to 3?


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

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Mon, Jul 15, 2013 at 8:59 PM, Simon Slavin  wrote:
>
> On 15 Jul 2013, at 7:49pm, Paolo Bolzoni  
> wrote:
>
>> From 35-40MB to 940MB; I would put massif result but I think the
>> list deletes attachments.
>
> Do you have in-memory tables ?
No.

> Do you use sqlite3_exec() ?
Twice, to activate the pragma and to create the db.

> Do you have SELECTs for which there is no good index, forcing sqlite3 to make 
> up its own ?
I checked with EXPLAIN and no. Seems not.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Jay A. Kreibich]

On Mon, Jul 15, 2013 at 08:49:52PM +0200, Paolo Bolzoni scratched on the wall:
> >From 35-40MB to 940MB; I would put massif result but I think the
> list deletes attachments.

  By default, the page-cache is 2000.  Pages are typically 1KB, but
  have some minor overhead in the cache.  Assuming you haven't turned
  the page cache down, until you top ~3MB there is nothing unusual
  at all.

   -j



> On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal  wrote:
> > On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni <
> > paolo.bolzoni.br...@gmail.com> wrote:
> >
> >> So, sorry if the question sounds very vague. But what can
> >> cause high memory usage in sqlite?  A large transaction
> >> maybe?
> >>
> >
> > What is "high"? In my apps sqlite tends to use 200-400kb or so, which i
> > don't consider to be all that high considering what it's capable of doing
> > for me.
> >
> > --
> > - stephan beal
> > http://wanderinghorse.net/home/stephan/
> > http://gplus.to/sgbeal
> > ___
> > 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

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Simon Slavin]

On 15 Jul 2013, at 7:49pm, Paolo Bolzoni  wrote:

> From 35-40MB to 940MB; I would put massif result but I think the
> list deletes attachments.

Do you have in-memory tables ?

Do you use sqlite3_exec() ?

Do you have SELECTs for which there is no good index, forcing sqlite3 to make 
up its own ?

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

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

>From 35-40MB to 940MB; I would put massif result but I think the
list deletes attachments.

On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal  wrote:
> On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> So, sorry if the question sounds very vague. But what can
>> cause high memory usage in sqlite?  A large transaction
>> maybe?
>>
>
> What is "high"? In my apps sqlite tends to use 200-400kb or so, which i
> don't consider to be all that high considering what it's capable of doing
> for me.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> 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] Memory usage of sqlite3

[Stephan Beal]

On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni <
paolo.bolzoni.br...@gmail.com> wrote:

> So, sorry if the question sounds very vague. But what can
> cause high memory usage in sqlite?  A large transaction
> maybe?
>

What is "high"? In my apps sqlite tends to use 200-400kb or so, which i
don't consider to be all that high considering what it's capable of doing
for me.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Usage/ Drawbacks of Statements

[Mohit Sindhwani]

Hi Eric,

On 24/4/2012 10:03 PM, Eric Minbiole wrote:

Similar to Pavel's suggestion, our implementation maintains a simple
cache of prepared statements, keyed by the SQL query that created
them.  For example:

   pStatement = Cache.GetQuery("SELECT * FROM xyz");

would return the cached statement if the query had been seen before,
or would auto-create it, if needed.

This (attempts to) give the best of both worlds: Statements are only
created / prepared once, improving run-time performance.  In addition,
the programmers aren't burdened with trying to figure out up-front
which queries might / might not be used later, simplifying
development.

This approach has worked well in our application.


Thanks!  This is similar to what we were thinking... it's good to know 
that you're successfully using it.


Best Regards,
Mohit.
25/4/2012 | 12:01 AM.

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

Re: [sqlite] Memory Usage/ Drawbacks of Statements

[Eric Minbiole]

Similar to Pavel's suggestion, our implementation maintains a simple
cache of prepared statements, keyed by the SQL query that created
them.  For example:

  pStatement = Cache.GetQuery("SELECT * FROM xyz");

would return the cached statement if the query had been seen before,
or would auto-create it, if needed.

This (attempts to) give the best of both worlds: Statements are only
created / prepared once, improving run-time performance.  In addition,
the programmers aren't burdened with trying to figure out up-front
which queries might / might not be used later, simplifying
development.

This approach has worked well in our application.

On 4/23/12, Mohit Sindhwani  wrote:
> Thanks Pavel,
>
> That gives me something new to do with SQLite over the next few weeks.
>
> On 23/4/2012 8:47 PM, Pavel Ivanov wrote:
>>> 1. Do statements do any thing that would require a lot of memory to be
>>> maintained?
>> No, they don't need a lot of memory, but still some memory is used. So
>> if you have like thousands of statements you should worry about this.
>> If you have 20 or 30 statements your database cache will likely
>> consume much more memory, so don't worry.
>>
>>> 2. Are there any known drawbacks of doing this?
>> Preparing all statements takes some time which adds to startup time of
>> your application. Also you could prepare some statements which won't
>> be used later. If those are not problems for you then preparing all
>> statements at startup is a way to go.
>>
>>> 3. Finally, if sqlite3_reset is called multiple times before a bind, is
>>> there a problem?
>> No, there's no problem in here.
>>
>>
>> Pavel
>>
>
> ___
> 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] Memory Usage/ Drawbacks of Statements

[Mohit Sindhwani]

Thanks Pavel,

That gives me something new to do with SQLite over the next few weeks.

On 23/4/2012 8:47 PM, Pavel Ivanov wrote:

1. Do statements do any thing that would require a lot of memory to be
maintained?

No, they don't need a lot of memory, but still some memory is used. So
if you have like thousands of statements you should worry about this.
If you have 20 or 30 statements your database cache will likely
consume much more memory, so don't worry.


2. Are there any known drawbacks of doing this?

Preparing all statements takes some time which adds to startup time of
your application. Also you could prepare some statements which won't
be used later. If those are not problems for you then preparing all
statements at startup is a way to go.


3. Finally, if sqlite3_reset is called multiple times before a bind, is
there a problem?

No, there's no problem in here.


Pavel



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

Re: [sqlite] Memory Usage/ Drawbacks of Statements

[Pavel Ivanov]

> 1. Do statements do any thing that would require a lot of memory to be
> maintained?

No, they don't need a lot of memory, but still some memory is used. So
if you have like thousands of statements you should worry about this.
If you have 20 or 30 statements your database cache will likely
consume much more memory, so don't worry.

> 2. Are there any known drawbacks of doing this?

Preparing all statements takes some time which adds to startup time of
your application. Also you could prepare some statements which won't
be used later. If those are not problems for you then preparing all
statements at startup is a way to go.

> 3. Finally, if sqlite3_reset is called multiple times before a bind, is
> there a problem?

No, there's no problem in here.


Pavel


On Mon, Apr 23, 2012 at 8:02 AM, Mohit Sindhwani  wrote:
> Hi, our system does fairly predictable queries when it runs.  A number of
> modules all access data using a handful of queries of each.  We open the
> database at the start and close it at the end of the program.
>
> Each query follows the usual pattern of prepare - bind - step - reset -
> (eventually) finalize.
>
> I was wondering if there is any known drawback in creating statements
> up-front when the system is started and use them as and when they are
> needed.
>
> 1. Do statements do any thing that would require a lot of memory to be
> maintained?
>
> 2. Are there any known drawbacks of doing this?
>
> 3. Finally, if sqlite3_reset is called multiple times before a bind, is
> there a problem?
>
> Thanks,
> Mohit.
>
> ___
> 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] memory usage after VACUUM

[Philip Graham Willoughby]

On 9 Mar 2011, at 15:23, Nick Hodapp wrote:

> I'm using sqlite in an iOS app, via the popular FMDB wrapper.
> 
> My profiling tool is showing me that the app is using 2.5 MB of memory
> before a VACUUM, and nearly 6MB after.  The tool shows that the extra memory
> was allocated by sqlite3MemMalloc().  If I close and re-open the database
> then the extra memory usage goes away, returning to 2.5 MB.
> 
> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?
> 

See http://www.sqlite.org/c3ref/release_memory.html - if you invoke
  sqlite3_release_memory(INT_MAX)
it will free up as much as it can at that point in time.

6.5Mb isn't that much for an iOS app to be using - are you getting memory usage 
warnings?

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] memory usage after VACUUM

[Pavel Ivanov]

> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?

Yes, execute "pragma cache_size = 100" for example, or put other
number of your liking into there.

If closing and re-opening of the database makes extra memory usage to
go away then it was used by database cache. Default cache size is 2000
pages. Command shown above sets it to 100 pages. I don't know what
size of cache is best for you, just beware that usually with smaller
cache SQLite needs more frequently to go to disk and thus queries are
slower. But cache works only if you repeatedly access the same
tables/indexes over the same connection. And I don't know how much
slower queries are without cache on iOS.


Pavel

On Wed, Mar 9, 2011 at 10:23 AM, Nick Hodapp  wrote:
> I'm using sqlite in an iOS app, via the popular FMDB wrapper.
>
> My profiling tool is showing me that the app is using 2.5 MB of memory
> before a VACUUM, and nearly 6MB after.  The tool shows that the extra memory
> was allocated by sqlite3MemMalloc().  If I close and re-open the database
> then the extra memory usage goes away, returning to 2.5 MB.
>
> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?
>
> It's possible but unlikely that the FMDB wrapper is affecting things.  I
> haven't removed it from the equation to test, however.
>
> Nick Hodap
> ___
> 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] memory usage after VACUUM

[Enrico Thierbach]

On 09.03.2011, at 16:23, Nick Hodapp wrote:

> I'm using sqlite in an iOS app, via the popular FMDB wrapper.
> 
> My profiling tool is showing me that the app is using 2.5 MB of memory
> before a VACUUM, and nearly 6MB after.  The tool shows that the extra memory
> was allocated by sqlite3MemMalloc().  If I close and re-open the database
> then the extra memory usage goes away, returning to 2.5 MB.
> 
> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?
> 
> It's possible but unlikely that the FMDB wrapper is affecting things.  I
> haven't removed it from the equation to test, however.
> 
> Nick Hodap

Hi nick,

just a shot in the dark: is there any chance that probably FMDB releases the 
memory properly, but the corresponding auto release pool
is not yet released? In that case the memory would still be held there. 

For anyone not into iOS development: that is Cocoa's memory management solution.

/eno


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

Re: [sqlite] Memory usage ??? one data base versus two smaller ones

[Jay A. Kreibich]

On Fri, Feb 19, 2010 at 09:39:08AM -0300, Israel Lins Albuquerque scratched on 
the wall:
> Samuel, 
> 
> Each one attached database has its own page cache with 2000 
> (default number of pages in cache) * 1024 (default size in
> bytes of a page),

  On many Windows systems it will default to 4096.  It tries to match the
  cluster size on NTFS volumes.

> totaling 2 Mb of ram. 

  Actually, it is closer to 3MB (or 9MB) of used memory, as each entry
  in the page cache has some overhead.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage – one data base versus tw o smaller ones

[Israel Lins Albuquerque]

Samuel, 

Each one attached database has its own page cache with 2000 (default number of 
pages in cache) * 1024 (default size in bytes of a page), totaling 2 Mb of ram. 
But you can define the number of pages in cache. 
I work using 5 attached databases this needs 10Mb, but some devices (PalmOS) 
has only 2.5 Mb!!! And I resolve using the SQL command 'PRAGMA 
[DB_ALIAS].cache_size = [NEW_CACHE_SIZE]'. 

P.S. 2000 is maximum number of pages cached, not used at all instantly on 
attach. 

- Mensagem original - 
De: "a1rex"  
Para: sqlite-users@sqlite.org 
Enviadas: Quarta-feira, 17 de Fevereiro de 2010 16:38:27 (GMT-0300) 
Auto-Detected 
Assunto: [sqlite] Memory usage – one data base versus two smaller ones 


For some reasons it is more convenient for the project to 
have a few smaller databases with unrelated data than one containing 
everything. My only concern is RAM memory. 
How much burden/memory overhead an additional database would introduce? 

Thank you for your input, 

Samuel 


__ 
Ask a question on any topic and get answers from real people. Go to Yahoo! 
Answers and share what you know at http://ca.answers.yahoo.com 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] memory usage and queries

[Christopher Taylor]

I fixed this by reducing the cache size from 2000 to 200.  I may want to
adjust some more but will continue to test.

Chris

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

Re: [sqlite] Memory Usage

[D . Richard Hipp]

On Apr 21, 2009, at 7:12 PM, Marco Bambini wrote:

> Hello Dr. Hipp,
>
> I was finally able to track down the issue ... the problem is due to  
> the fact that after each write operation the client executes a query  
> like:
> "SELECT 123 AS changes;"  (the number 123 changes all the time)
> this select statement was performed in an in-memory database ...  
> then I tried to perform it in a disk based db ... but the behavior  
> doesn't change, the memory used by sqlite continue to increase and  
> it is never released (until the db is closed).
>
> So, my new question is... how I can return a fixed value from an  
> sqlite3 database without having this memory issue?
>

Are you sure you are calling sqlite3_finalize() on the statement once  
you are done with it?  You can use sqlite3_next_stmt() interface to  
get a listing of all unfinalized statements for a database  
connection.  Count them, and if the number keeps growing, that is your  
problem.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 10:54 AM, Marco Bambini wrote:

> and the read lock is released when the virtual machine is finalized  
> right? ... even if the writer is inside a BEGIN IMMEDIATE transaction?
>

Correct.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 9:25 AM, Marco Bambini wrote:

> cache_size is set to default 2000, page size is 1K...
>
> here you go the output of sqlite3_status:
> 2009-04-21 15:24:25   SQLITE_STATUS_MEMORY_USED current: 106704136 high:
> 109873952
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_OVERFLOW current: 4816736
> high: 4819808

Dan tells me that if another process has a read lock on the database  
file while your process is running the large transaction, then the  
size of the page cache can grow without bound.  That seem likely what  
is happening here.

>
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_OVERFLOW current: 0 high:  
> 6664
> 2009-04-21 15:24:25   SQLITE_STATUS_MALLOC_SIZE current: 1014 high:  
> 52000
> 2009-04-21 15:24:25   SQLITE_STATUS_PARSER_STACK current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_SIZE current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_SIZE current: 4480 high:  
> 6664
>
> -- 
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
> On Apr 21, 2009, at 3:12 PM, D. Richard Hipp wrote:
>
>>
>> On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:
>>
>>> The database is on-disk ... does huge not committed transactions  
>>> uses
>>> memory?
>>>
>>
>> It should do writes to disk periodically to free up memory, once you
>> hit your cache_size limit.
>>
>> What is cache_size set to.  What are the output from sqlite3_status()
>> telling you about memory usage?
>>
>> D. Richard Hipp
>> d...@hwaci.com
>>
>>
>>
>> ___
>> 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

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 9:25 AM, Marco Bambini wrote:

> cache_size is set to default 2000, page size is 1K...
>
> here you go the output of sqlite3_status:
> 2009-04-21 15:24:25   SQLITE_STATUS_MEMORY_USED current: 106704136 high:
> 109873952
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_OVERFLOW current: 4816736
> high: 4819808

OK.  I'll see if I can reproduce the problem here


>
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_USED current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_OVERFLOW current: 0 high:  
> 6664
> 2009-04-21 15:24:25   SQLITE_STATUS_MALLOC_SIZE current: 1014 high:  
> 52000
> 2009-04-21 15:24:25   SQLITE_STATUS_PARSER_STACK current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_PAGECACHE_SIZE current: 0 high: 0
> 2009-04-21 15:24:25   SQLITE_STATUS_SCRATCH_SIZE current: 4480 high:  
> 6664
>
> -- 
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
> On Apr 21, 2009, at 3:12 PM, D. Richard Hipp wrote:
>
>>
>> On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:
>>
>>> The database is on-disk ... does huge not committed transactions  
>>> uses
>>> memory?
>>>
>>
>> It should do writes to disk periodically to free up memory, once you
>> hit your cache_size limit.
>>
>> What is cache_size set to.  What are the output from sqlite3_status()
>> telling you about memory usage?
>>
>> D. Richard Hipp
>> d...@hwaci.com
>>
>>
>>
>> ___
>> 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

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Memory Usage

[Marco Bambini]

cache_size is set to default 2000, page size is 1K...

here you go the output of sqlite3_status:
2009-04-21 15:24:25 SQLITE_STATUS_MEMORY_USED current: 106704136 high:  
109873952
2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_USED current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_OVERFLOW current: 4816736  
high: 4819808
2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_USED current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_OVERFLOW current: 0 high: 6664
2009-04-21 15:24:25 SQLITE_STATUS_MALLOC_SIZE current: 1014 high: 52000
2009-04-21 15:24:25 SQLITE_STATUS_PARSER_STACK current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_SIZE current: 0 high: 0
2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_SIZE current: 4480 high: 6664

-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On Apr 21, 2009, at 3:12 PM, D. Richard Hipp wrote:

>
> On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:
>
>> The database is on-disk ... does huge not committed transactions uses
>> memory?
>>
>
> It should do writes to disk periodically to free up memory, once you
> hit your cache_size limit.
>
> What is cache_size set to.  What are the output from sqlite3_status()
> telling you about memory usage?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote:

> The database is on-disk ... does huge not committed transactions uses
> memory?
>

It should do writes to disk periodically to free up memory, once you  
hit your cache_size limit.

What is cache_size set to.  What are the output from sqlite3_status()  
telling you about memory usage?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Memory Usage

[Marco Bambini]

The database is on-disk ... does huge not committed transactions uses  
memory?

-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On Apr 21, 2009, at 2:27 PM, D. Richard Hipp wrote:

>
> On Apr 21, 2009, at 8:22 AM, Marco Bambini wrote:
>
>> Yes, executing sqlite3_memory_used () after 183,000 INSERT statement
>> returns: 106,766,848.
>> Database is never closed during application lifetime.
>> Each statement is prepared, stepped and properly finalized.
>>
>
> We do that kind of test all the time but we never get huge memory
> usage like this.  Are you sure you have opened on on-disk database and
> not an in-memory database?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 8:22 AM, Marco Bambini wrote:

> Yes, executing sqlite3_memory_used () after 183,000 INSERT statement
> returns: 106,766,848.
> Database is never closed during application lifetime.
> Each statement is prepared, stepped and properly finalized.
>

We do that kind of test all the time but we never get huge memory  
usage like this.  Are you sure you have opened on on-disk database and  
not an in-memory database?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Memory Usage

[Marco Bambini]

Yes, executing sqlite3_memory_used () after 183,000 INSERT statement  
returns: 106,766,848.
Database is never closed during application lifetime.
Each statement is prepared, stepped and properly finalized.

P.S. the database is encrypted using your modules.
-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On Apr 21, 2009, at 2:11 PM, D. Richard Hipp wrote:

>
> On Apr 21, 2009, at 8:08 AM, Marco Bambini wrote:
>
>> Hello guys,
>>
>> I am trying to write you again about a simple question... how can I
>> limit sqlite memory usage during insert commands? It seems that the
>> amount of memory usage increases when the number of objects inserted
>> into the database is increased and memory is never freed.
>>
>> I tried to set PRAGMA cache_size with no luck ... I tried also to
>> recompile with SQLITE_ENABLE_MEMORY_MANAGMENT and use
>> sqlite3_soft_heap_limit but nothing seems to change.
>
>
> How do you know that memory usage is increasing?  Are you using
> sqlite3_status() and/or sqlite3_memory_used() to measure memory usage?
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] Memory Usage

[D. Richard Hipp]

On Apr 21, 2009, at 8:08 AM, Marco Bambini wrote:

> Hello guys,
>
> I am trying to write you again about a simple question... how can I
> limit sqlite memory usage during insert commands? It seems that the
> amount of memory usage increases when the number of objects inserted
> into the database is increased and memory is never freed.
>
> I tried to set PRAGMA cache_size with no luck ... I tried also to
> recompile with SQLITE_ENABLE_MEMORY_MANAGMENT and use
> sqlite3_soft_heap_limit but nothing seems to change.


How do you know that memory usage is increasing?  Are you using  
sqlite3_status() and/or sqlite3_memory_used() to measure memory usage?


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Memory Usage

[Joe Wilson]

Once again you're missing the point.

Of course you can get a malloc/free implementation that performs 
garbage collection, such as Boehm's conservative GC. But C garbage 
collection and malloc/free memory fragmentation are quite different 
things. You can still get heavily fragmented memory with a C garbage 
collector - or even with traditional malloc/free with an ideal 
free()ing scheme.

Java and C garbage collectors are not directly comparable. Java is 
able to compact memory by copying it and adjusting the references 
transparently such that no unusable memory gaps are left. This is 
not possible in C due to its cavalier pointer casting and raw memory 
manipulation. Successful C garbage collectors instead opt for 
conservative GC leaving the memory at the same location.
(I am not aware of any precise C garbage collector that works with
multi-threaded code or runs in a comparable time to malloc/free.)

Nevermind that typical garbage collectors (Java or C) need 3 times
or more working memory as a typical malloc/free scheme in order 
to achieve decent timings. If memory is tight and performance is
paramount, then garbage collection is not the answer.

--- John Stanton <[EMAIL PROTECTED]> wrote:
> You confused my point which is that your usual malloc/free definitely 
> does no garbage collection.  That does not mean that a C language 
> program cannot perform garbage collection, just look at a Java run time 
> package for an example.
> 
> If you never execute a free your dynamic memory is essentially contiguous.
> 
> Joe Wilson wrote:
> > --- John Stanton <[EMAIL PROTECTED]> wrote:
> > 
> >>Malloc is a concept implemented in various ways, some more successful 
> >>than others but all of them hidden from the programmer.  Free tries to 
> >>give back memory but as you can appreciate unless you use some garbage 
> >>collection scheme with backwards pointers fragmentation and 
> >>checkerboarding is very difficult to avoid.
> > 
> > 
> > You seem to be confusing the topics of malloc/free memory fragmentation 
> > with C garbage collection - they are orthogonal concepts.
> > 
> > C's free() has no power to free up any extra memory as active memory 
> > must stay where it was allocated until freed or else you'll have 
> > dangling pointers. A single call to free() typically releases only memory 
> > allocated by a single malloc() call. If, as result of the free, there 
> > happens to be block(s) of memory immediately before or after that 
> > released memory then that entire memory region can be coalesced and 
> > be made available as a larger block to future mallocs.
> > 
> > If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no 
> > memory leaks, then garbage collection is irrelevant to the topic of
> > memory fragmentation. It's not like C can employ a copying garbage 
> > collector that moves memory blocks after free() without the knowledge 
> > or participation of the host program. The malloc() call is where 
> > fragmentation happens. Fragmentation in malloc depends on your allocation 
> > strategy: first-fit, best-fit, short-lived versus long-lived pools, 
> > per-allocation-size pools, statistical prediction, etc. Malloc must 
> > try to guess where an allocation must go to try to prevent future 
> > memory fragmentation. 



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

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

Re: [sqlite] Memory Usage

[Trevor Talbot]

On 11/19/07, John Stanton <[EMAIL PROTECTED]> wrote:
> Joe Wilson wrote:

> > If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no
> > memory leaks, then garbage collection is irrelevant to the topic of
> > memory fragmentation. It's not like C can employ a copying garbage
> > collector that moves memory blocks after free() without the knowledge
> > or participation of the host program. The malloc() call is where
> > fragmentation happens. Fragmentation in malloc depends on your allocation
> > strategy: first-fit, best-fit, short-lived versus long-lived pools,
> > per-allocation-size pools, statistical prediction, etc. Malloc must
> > try to guess where an allocation must go to try to prevent future
> > memory fragmentation.

> If you never execute a free your dynamic memory is essentially contiguous.

Not necessarily, and that was his point about where fragmentation happens.

Many of the common allocators maintain multiple size classes to reduce
degenerative fragmentation under most workloads.  If you allocate
several different sizes, your allocations will in fact be spread all
over the available memory pool, and therefore be fragmented without
ever calling free().

Most common allocators are optimized to reach a steady-state quickly,
so they have the least fragmentation necessary to handle most
arbitrary workloads.  That means putting up with some fragmentation so
that applications that don't leak memory at the interface level will
also not leak memory due to the allocator's internal management, no
matter what allocation pattern they use.  The allocation pattern used
by the application can still affect how much fragmentation there is,
of course.

The overall point here, though, is that even commonly implemented
malloc/free interfaces can be reliable enough to keep applications
running for years without trouble.  Completely deterministic behavior
is not required when probabilistic determinism is sufficient.  (Sorry,
I just had to use big words there.  IOW, building an application to
average perfect behavior is fine when you don't need to guarantee
perfect behavior at every arbitrary point.  Most applications don't
measurably benefit from such a guarantee.)

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

Re: [sqlite] Memory Usage

[John Stanton]

You confused my point which is that your usual malloc/free definitely 
does no garbage collection.  That does not mean that a C language 
program cannot perform garbage collection, just look at a Java run time 
package for an example.


If you never execute a free your dynamic memory is essentially contiguous.

Joe Wilson wrote:

--- John Stanton <[EMAIL PROTECTED]> wrote:

Malloc is a concept implemented in various ways, some more successful 
than others but all of them hidden from the programmer.  Free tries to 
give back memory but as you can appreciate unless you use some garbage 
collection scheme with backwards pointers fragmentation and 
checkerboarding is very difficult to avoid.



You seem to be confusing the topics of malloc/free memory fragmentation 
with C garbage collection - they are orthogonal concepts.


C's free() has no power to free up any extra memory as active memory 
must stay where it was allocated until freed or else you'll have 
dangling pointers. A single call to free() typically releases only memory 
allocated by a single malloc() call. If, as result of the free, there 
happens to be block(s) of memory immediately before or after that 
released memory then that entire memory region can be coalesced and 
be made available as a larger block to future mallocs.


If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no 
memory leaks, then garbage collection is irrelevant to the topic of
memory fragmentation. It's not like C can employ a copying garbage 
collector that moves memory blocks after free() without the knowledge 
or participation of the host program. The malloc() call is where 
fragmentation happens. Fragmentation in malloc depends on your allocation 
strategy: first-fit, best-fit, short-lived versus long-lived pools, 
per-allocation-size pools, statistical prediction, etc. Malloc must 
try to guess where an allocation must go to try to prevent future 
memory fragmentation. 





  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 


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




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

Re: [sqlite] Memory Usage

[Trevor Talbot]

On 11/19/07, John Stanton <[EMAIL PROTECTED]> wrote:

> Malloc is a concept implemented in various ways, some more successful
> than others but all of them hidden from the programmer.  Free tries to
> give back memory but as you can appreciate unless you use some garbage
> collection scheme with backwards pointers fragmentation and
> checkerboarding is very difficult to avoid.

Malloc and free as a concept do not imply fragmentation; it depends on
the implementation and how you use it.

> Various OS's have ways of allocating memory under control of the virtual
> memory manager so that it is not in the heap and can be completely
> returned.

This is no different than the malloc and free interface: you can
easily fragment virtual address space.

One form of allocator that is sometimes used in large task-oriented
programs is an arena-style one.  Essentially, a given task creates an
arena and performs malloc/free as necessary within the arena.  When
the task completes, the entire arena is destroyed, much like cleaning
up a stack.  Thus no memory-related issue such as fragmentation is
propagated between separate tasks.  It can be quite reliable when used
properly.

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

Re: [sqlite] Memory Usage

[Mark Spiegel]

Well said.

While it may be true that some memory allocators are lacking, the ones I 
use are quite good.  I view with great suspicion developers who thinks 
they can outsmart the pool allocator.  These folks usually add great 
complexity while having at best a neutral impact on performance and 
robustness.  As you point out, they can only optimize for their module, 
not globally.  Any changes of this type should be carefully tested of 
course, but just as importantly backed up by thorough performance data.


Joe Wilson wrote:

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
  

Our studies to date indicate that SQLite neither leaks nor fragments
memory.  Preventing leaks is relatively easy.  Preventing memory
fragmentation less so.  Yet we are not seeing memory fragmentation
as a problem for the workloads we have tested.

Nevertheless, we cannot *prove* that SQLite, in its current form,
will never fragment memory.  However, we are working toward a
future release where such a proof will be possible, at least for
certain well-defined operating parameters.   We just are not quite
there yet.



Pool allocators can be effective for certain classes of problems
and can exhibit desirable deterministic properties. But a library does 
not exist in isolation. You must consider the entire program memory space.
If every library used its own distinct pools then a program that 
uses many of such libraries (sqlite, apache portable runtime, GNU STL,
whatever) may ultimately end up with is sub-optimal memory utilization 
for the entire program. Space reserved for one library, but not currently 
in use might be better put to use by another library's short-lived 
operation, for example. 

Using the same allocator for the entire program can give it optimization 
opportunities that may not necessarily exist with distinct library-specific 
memory pools. 


An example from Hoard's docs (mostly speed related, as opposed to space):

http://www.cs.umass.edu/~emery/hoard/faqs.html

  I'm using the STL but not seeing any performance improvement. Why not?

  In order to benefit from Hoard, you have to tell STL to use malloc 
  instead of its internal custom memory allocator:


  typedef list mylist;

For some problems library-specific allocators are very useful. 
You have to consider other factors as well.




  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 


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


  

Re: [sqlite] Memory Usage

[John Stanton]

James Dennett wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 12:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Dynamic allocation is not the problem, it is malloc and free.  there

is

a difference between being certain and being lucky.


Could/would you expand on that?  There's limited difference between
malloc/free and other dynamic allocation systems, and I'm assuming that
we're not considering transparent garbage-collection as an option.

What about malloc/free do you claim is problematic, and why is it not
problematic with other dynamic allocation approaches?  Do you speak of a
problem with the interface, or with specific implementations?

-- James

Malloc is a concept implemented in various ways, some more successful 
than others but all of them hidden from the programmer.  Free tries to 
give back memory but as you can appreciate unless you use some garbage 
collection scheme with backwards pointers fragmentation and 
checkerboarding is very difficult to avoid.


If you allocate memory directly and have your program reuse it in a way 
which cannot fragment you achieve dynamic memory allocation which is 
truly reversible.


When you think carefully about the memory usage in your program you can 
often use the stack rather than the heap and avoid problems.  Local 
memory on the stack never fragments.


Various OS's have ways of allocating memory under control of the virtual 
memory manager so that it is not in the heap and can be completely 
returned.  For example it can be an anonymous shared file.  If the 
machine has a disk, using virtual memory when suitable will avoid a heap 
overflow and crash, but be aware that you can have the situation where 
it will perform a lazy write through to disk.


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

Re: [sqlite] Memory Usage

[John Stanton]

D. Richard Hipp wrote:


On Nov 19, 2007, at 12:36 PM, James Dennett wrote:


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.



Our studies to date indicate that SQLite neither leaks nor fragments
memory.  Preventing leaks is relatively easy.  Preventing memory
fragmentation less so.  Yet we are not seeing memory fragmentation
as a problem for the workloads we have tested.

Nevertheless, we cannot *prove* that SQLite, in its current form,
will never fragment memory.  However, we are working toward a
future release where such a proof will be possible, at least for
certain well-defined operating parameters.   We just are not quite
there yet.

To be highly reliable and portable we tend to use brute force and 
ignorance and maintain free lists of memory.  It works reliably and 
programs run for years but it is tedious to implement.  It will 
eventually reach a high water point which represents the worst case 
memory allocation.



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

Re: [sqlite] Memory Usage

[John Stanton]

Dynamic memory allocation is not the problem, it it memory fragmentation 
and checkerboarding produced by "free".  Avoid the fragmentation and you 
can run forever.


James Dennett wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.  


-- James


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




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

RE: [sqlite] Memory Usage

[James Dennett]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 19, 2007 12:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Memory Usage
> 
> Dynamic allocation is not the problem, it is malloc and free.  there
is
> a difference between being certain and being lucky.

Could/would you expand on that?  There's limited difference between
malloc/free and other dynamic allocation systems, and I'm assuming that
we're not considering transparent garbage-collection as an option.

What about malloc/free do you claim is problematic, and why is it not
problematic with other dynamic allocation approaches?  Do you speak of a
problem with the interface, or with specific implementations?

-- James


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

Re: [sqlite] Memory Usage

[John Stanton]

Dynamic allocation is not the problem, it is malloc and free.  there is 
a difference between being certain and being lucky.


James Dennett wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.  


-- James


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




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

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Nov 19, 2007, at 12:36 PM, James Dennett wrote:


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses  
dynamic

allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software  
must

run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage  
its own

fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.



Our studies to date indicate that SQLite neither leaks nor fragments
memory.  Preventing leaks is relatively easy.  Preventing memory
fragmentation less so.  Yet we are not seeing memory fragmentation
as a problem for the workloads we have tested.

Nevertheless, we cannot *prove* that SQLite, in its current form,
will never fragment memory.  However, we are working toward a
future release where such a proof will be possible, at least for
certain well-defined operating parameters.   We just are not quite
there yet.



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

RE: [sqlite] Memory Usage

[James Dennett]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 19, 2007 7:36 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Memory Usage
> 
> Not only applicable to real time systems.  If you want a program to
run
> with stability over a long time the first step it to eliminate frees
and
> if malloc is used confine it to the intialization.

I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.  

-- James


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

Re: [sqlite] Memory Usage

[John Stanton]

Not only applicable to real time systems.  If you want a program to run 
with stability over a long time the first step it to eliminate frees and 
if malloc is used confine it to the intialization.


Jim Dodgen wrote:
One other note, just about all real-time systems limit the dynamic 
allocation of memory because you lose the deterministic behavior, 
typically all memory is allocated when the task starts, memory is 
usually managed internally in standard sized chunks.


Also for long running tasks (months/years) you would suffer 
fragmentation and for limited memory systems a malloc/new  may fail 
weeks after the task starts and that would not be pretty. Also it is 
very hard or even impossible to completely test,  it is best to design 
the problem out.


Thus it is nice to have the ability to turnoff sqlite's ability to malloc

D. Richard Hipp wrote:



On Nov 18, 2007, at 8:12 AM, Russell Leighton wrote:



On Nov 17, 2007, at 4:56 PM, [EMAIL PROTECTED] wrote:



If you compile with -DSQLITE_MEMORY_SIZE= then SQLite
will *never* call malloc().  Instead, it uses a static
array that is  bytes in size for all of its memory
needs.  You can get by with as little as 100K or so of
memory, though the more memory you provide, the faster
it will run.  5MB is a good value.



Does using this setting (and eliminating malloc/free overhead) result
in a significant performance increase?



That depends on how good of a malloc you have on your system.
On Linux systems that typically use Doug Lea's malloc, there is
no measurable performance difference.  But I have had some
people running embedded systems tell me that using the
malloc-free SQLite results in a significant speed boost.  Your
mileage may vary.

D. Richard Hipp
[EMAIL PROTECTED]




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Memory Usage

[Jim Dodgen]

One other note, just about all real-time systems limit the dynamic 
allocation of memory because you lose the deterministic behavior, 
typically all memory is allocated when the task starts, memory is 
usually managed internally in standard sized chunks.


Also for long running tasks (months/years) you would suffer 
fragmentation and for limited memory systems a malloc/new  may fail 
weeks after the task starts and that would not be pretty. Also it is 
very hard or even impossible to completely test,  it is best to design 
the problem out.


Thus it is nice to have the ability to turnoff sqlite's ability to malloc

D. Richard Hipp wrote:


On Nov 18, 2007, at 8:12 AM, Russell Leighton wrote:



On Nov 17, 2007, at 4:56 PM, [EMAIL PROTECTED] wrote:



If you compile with -DSQLITE_MEMORY_SIZE= then SQLite
will *never* call malloc().  Instead, it uses a static
array that is  bytes in size for all of its memory
needs.  You can get by with as little as 100K or so of
memory, though the more memory you provide, the faster
it will run.  5MB is a good value.



Does using this setting (and eliminating malloc/free overhead) result
in a significant performance increase?


That depends on how good of a malloc you have on your system.
On Linux systems that typically use Doug Lea's malloc, there is
no measurable performance difference.  But I have had some
people running embedded systems tell me that using the
malloc-free SQLite results in a significant speed boost.  Your
mileage may vary.

D. Richard Hipp
[EMAIL PROTECTED]




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








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

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Nov 18, 2007, at 8:12 AM, Russell Leighton wrote:



On Nov 17, 2007, at 4:56 PM, [EMAIL PROTECTED] wrote:



If you compile with -DSQLITE_MEMORY_SIZE= then SQLite
will *never* call malloc().  Instead, it uses a static
array that is  bytes in size for all of its memory
needs.  You can get by with as little as 100K or so of
memory, though the more memory you provide, the faster
it will run.  5MB is a good value.



Does using this setting (and eliminating malloc/free overhead) result
in a significant performance increase?


That depends on how good of a malloc you have on your system.
On Linux systems that typically use Doug Lea's malloc, there is
no measurable performance difference.  But I have had some
people running embedded systems tell me that using the
malloc-free SQLite results in a significant speed boost.  Your
mileage may vary.

D. Richard Hipp
[EMAIL PROTECTED]




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

Re: [sqlite] Memory Usage

[Russell Leighton]

On Nov 17, 2007, at 4:56 PM, [EMAIL PROTECTED] wrote:



If you compile with -DSQLITE_MEMORY_SIZE= then SQLite
will *never* call malloc().  Instead, it uses a static
array that is  bytes in size for all of its memory
needs.  You can get by with as little as 100K or so of
memory, though the more memory you provide, the faster
it will run.  5MB is a good value.



Does using this setting (and eliminating malloc/free overhead) result
in a significant performance increase?


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

Re: [sqlite] Memory Usage

[ScottDerrick]

Dr. Hipp,

thanks, I'm sure I can use one or more of your solutions below to solve my
problem...

Scott
 
SQLite does maintain a cache of the database file.  It
will hold up to 2000 pages by default.  You can change
the cache size by using the "PRAGMA cache_size=N" pragma.
You can set N as small as 10.

The cache does not grow beyond its limit.

If you compile with -DSQLITE_ENABLE_MEMORY_MANAGMENT=1
the you can use the sqlite3_soft_heap_limit() interface to
limit the total amount of memory SQLite will use.  You can
also use sqlite3_release_memory() to get SQLite to give
up memory out of its cache.

If you compile with -DSQLITE_MEMORY_SIZE= then SQLite
will *never* call malloc().  Instead, it uses a static
array that is  bytes in size for all of its memory
needs.  You can get by with as little as 100K or so of
memory, though the more memory you provide, the faster
it will run.  5MB is a good value.

In the next release, we might provide a new C interface
or a pragma or both that will flush the cache.

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


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




-- 
View this message in context: 
http://www.nabble.com/Memory-Usage-tf4822840.html#a13815730
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Memory Usage

[drh]

Raymond Hurst <[EMAIL PROTECTED]> wrote:
> Hi Scott,
> 
> Ooops..meant to say the following.
> 
> My initial evaluation of this database was that it allocates memory for 
> each operation on the database. It returns the memory only when the 
> database is CLOSED. So the behavior you see is normal.
> Ray Hurst
> 

No.

SQLite does maintain a cache of the database file.  It
will hold up to 2000 pages by default.  You can change
the cache size by using the "PRAGMA cache_size=N" pragma.
You can set N as small as 10.

The cache does not grow beyond its limit.

If you compile with -DSQLITE_ENABLE_MEMORY_MANAGMENT=1
the you can use the sqlite3_soft_heap_limit() interface to
limit the total amount of memory SQLite will use.  You can
also use sqlite3_release_memory() to get SQLite to give
up memory out of its cache.

If you compile with -DSQLITE_MEMORY_SIZE= then SQLite
will *never* call malloc().  Instead, it uses a static
array that is  bytes in size for all of its memory
needs.  You can get by with as little as 100K or so of
memory, though the more memory you provide, the faster
it will run.  5MB is a good value.

In the next release, we might provide a new C interface
or a pragma or both that will flush the cache.

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


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

Re: [sqlite] Memory Usage

[ScottDerrick]

There must be a way to flush what ever is being cached.  Help

It's hard to believe I'm the only guy that wants to keep the database open
and just do inserts, to save CPU time.  


Scott


rhurst2 wrote:
> 
> Hi Scott,
> 
> Ooops..meant to say the following.
> 
> My initial evaluation of this database was that it allocates memory for 
> each operation on the database. It returns the memory only when the 
> database is CLOSED. So the behavior you see is normal.
> Ray Hurst
> 
> ScottDerrick wrote:
>> I am using sqlite3 in a DAQ device.  Data can be viewed on the unit using
>> a
>> Rails enabled web server.
>> 
>> The data is being stored to the database every 1 to 5 seconds.  I wanted
>> to
>> leave the the database open for as long as teh application is running and
>> then use a IMMEDIATE, PREPARE-INSERT(x), FINALIZE, COMMIT sequence.   The
>> application may run as long as 30 days at a time.
>> 
>> To save disk access and CPU time I was not going to open and close the
>> database for every insertion iteration.  However I have noticed that my
>> application seems use more and more memory as time goes bye.  If I open
>> and
>> close the database for the insertion cycle the memory usage is stable.
>> 
>> It seems like a bug to me but I assume the database is keeping some kind
>> of
>> rollback or restore data in memory.  Any way I can tel the database not
>> to
>> do save this data in memory if I don't close the database, so my memory
>> usage is stable?
>> 
>> thanks,
>> 
>> Scott
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Memory-Usage-tf4822840.html#a13813367
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Memory Usage

[Raymond Hurst]

Hi Scott,

Ooops..meant to say the following.

My initial evaluation of this database was that it allocates memory for 
each operation on the database. It returns the memory only when the 
database is CLOSED. So the behavior you see is normal.

Ray Hurst

ScottDerrick wrote:

I am using sqlite3 in a DAQ device.  Data can be viewed on the unit using a
Rails enabled web server.

The data is being stored to the database every 1 to 5 seconds.  I wanted to
leave the the database open for as long as teh application is running and
then use a IMMEDIATE, PREPARE-INSERT(x), FINALIZE, COMMIT sequence.   The
application may run as long as 30 days at a time.

To save disk access and CPU time I was not going to open and close the
database for every insertion iteration.  However I have noticed that my
application seems use more and more memory as time goes bye.  If I open and
close the database for the insertion cycle the memory usage is stable.

It seems like a bug to me but I assume the database is keeping some kind of
rollback or restore data in memory.  Any way I can tel the database not to
do save this data in memory if I don't close the database, so my memory
usage is stable?

thanks,

Scott


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

Re: [sqlite] Memory Usage

[Raymond Hurst]

Hi Scott,
My initial evaluation of this database was that it allocates memory for 
each operation on the database. It returns the memory only when the 
database is committed. So the behavior you see is normal.

Ray Hurst

ScottDerrick wrote:

I am using sqlite3 in a DAQ device.  Data can be viewed on the unit using a
Rails enabled web server.

The data is being stored to the database every 1 to 5 seconds.  I wanted to
leave the the database open for as long as teh application is running and
then use a IMMEDIATE, PREPARE-INSERT(x), FINALIZE, COMMIT sequence.   The
application may run as long as 30 days at a time.

To save disk access and CPU time I was not going to open and close the
database for every insertion iteration.  However I have noticed that my
application seems use more and more memory as time goes bye.  If I open and
close the database for the insertion cycle the memory usage is stable.

It seems like a bug to me but I assume the database is keeping some kind of
rollback or restore data in memory.  Any way I can tel the database not to
do save this data in memory if I don't close the database, so my memory
usage is stable?

thanks,

Scott


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

Re: [sqlite] Memory Usage

[Eduardo]

At 14:40 30/10/2006, you wrote:

I am suffering a 4GB memory 64-bit Zeon Linux box, which keeps 
crashing with 'No available memory'.  I'm finding it quite hard to 
break down the memory into what processes are paged-in and using 
what's available. Sqlite seemed to be the smoking gun, so although 
I'm glad it was not the case, it means I must start again on my search...


Perhaps it's begining to be a bit offtopic, but, what's your swap 
partition size? Could you check it for physical damage? Or make another swap? 



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

Re: [sqlite] Memory Usage

[Dennis Jenkins]

   If you are seeing different memory usage patterns for identical code 
based on if it is run from xinetd or on your command line, then I would 
check the process environment that xinetd creates.  Maybe some component 
that sqlite uses is acting differently based on environment variables?


   I don't know with any certainty, but I'm fairly sure that sqlite 
doesn't use env vars for anything.  It's just a thought...




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

Re: [sqlite] Memory Usage

[John Stanton]

What happens when you write a simple test program to open the DB?  You 
can certainly run Valgrind on that or even put in your own debug 
statements to examine the heap usage.


This would be the first thing to do in indentifying the problem.  It 
will tell you whether to look at Sqlite or your application.


Ben Clewett wrote:

Hi Numo and others,

I am very glad to hear the consensus is that there is nothing wrong with 
 libsqlite3.so.0.8.6.


However the fact is that the 'open' still acquires 16MB of memory.

Immediately Before:

  VmSize:   8572 kB
  VmLck:   0 kB
  VmRSS:2252 kB
  VmData:484 kB
  VmStk:  88 kB
  VmExe:  20 kB
  VmLib:6772 kB
  VmPTE:  20 kB

Immediately After:

sqlite3_open(sDatabaseFile, ) (= SQLITE_OK)

  VmSize:  24960 kB
  VmLck:   0 kB
  VmRSS:2368 kB
  VmData:  16872 kB
  VmStk:  88 kB
  VmExe:  20 kB
  VmLib:6772 kB
  VmPTE:  24 kB

I can't use valgrind as this is a TCP daemon, so I will have to build 
some form of simulator to investigate further


Thanks,

Ben


Nuno Lucas wrote:


On 10/27/06, Ben Clewett <[EMAIL PROTECTED]> wrote:


I am linking to libsqlite3.so.0.8.6.  After calling sqlite3_open(...) I
find my programs data memory jumps by 16392 Kb.

This seems a lot.  The database I am opening is only 26K in size.



There are many different ways of memory "jump" (like linking with a
lot of dynamic libraries), but one thing is certain: sqlite is not
responsible for that.


I have a similar process opening about 90 times.  This obviously
consumes a very large amount of memory, 1.4G with 90 processes.



It's a memory leak in your program, for sure. Run some memory leak
tool (e.g. valgrind).


May I ask if this is what would be expected, and whether there is
anything I can do to lower this loading?



Unless you decided to mess with sqlite internals, it's not expected in 
any way.



Thanks for your help,

Ben.



Best regards,
~Nuno Lucas

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









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

Re: [sqlite] Memory Usage

[Martin Jenkins]

Ben Clewett wrote:

If you know a good URL on Linux virtual memory and allocation, I
would be extremely interested.


You could try:

http://virtualthreads.blogspot.com/2006/02/understanding-memory-usage-on-linux.html

The next two link to pages with links to a PDF of the "gorman" book 
"Understanding the Linux Virtual Memory Manager", which looks like a 
good read.


http://www.skynet.ie/~mel/projects/vm/
http://www.phptr.com/promotions/promotion.asp?promo=1484=1=1

Martin

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

Re: [sqlite] Memory Usage

[Ben Clewett]

Hi Nuno,

Sqlite is one mailing list I have consistently found absolutely excelent 
knowledge, thanks again for your information.  I don't know whether this 
should be off-thread now, but I don't have your email address.


I'll have to research memory allocation further.  But I'm glad to know 
that the 16MB 'VmData' is not reserved per-process.  If you know a good 
URL on Linux virtual memory and allocation, I would be extremely interested.


My code is c++, it's a manifold for many TCP remote mobile devices, like 
GPS tracking equipment.  Therefore it's evolved to link to half the 
libraries on the system.


If I am to correctly account for data used by a collection of processes, 
would this be the sum of VmRSS?


I am suffering a 4GB memory 64-bit Zeon Linux box, which keeps crashing 
with 'No available memory'.  I'm finding it quite hard to break down the 
memory into what processes are paged-in and using what's available. 
Sqlite seemed to be the smoking gun, so although I'm glad it was not the 
case, it means I must start again on my search...


Thanks for the advise,

Ben




Nuno Lucas wrote:

On 10/30/06, Ben Clewett <[EMAIL PROTECTED]> wrote:

Nuno,

Thanks for the excelent description of my error.  I have learnt a little
more about Linux virtual memory model.  Very glad to hear Sqlite is as
perfect as ever :)

My problem, which is definitely my problem, is that 90 x 16MB of
reserved memory is still a loss of 1.4G.  Especially as I use hardly any
of it.


Each process has it's own virtual address space, so 16MB of reserved
virtual addresses (except when they are kernel addresses) for one
process doesn't do nothing to the ammount of virtual addresses free
for other processes. And as each process usually has 2/3GB of virtual
addresses for it's own use, 16MB is pretty low (it depends on the
system, but 2 GB is the most common minimum, on 32 bits).

I still find strange that your program uses so much virtual addresses,
but you didn't specify (or I don't recall) what language you are using
and what libraries you are linking to. You may want to investigate
this further.


Regards,
~Nuno Lucas

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






--
Ben Clewett
+44(0)1923 46
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com

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

Re: [sqlite] Memory Usage

[Lloyd]

I don't know whether I am right in this perspective. Just to know
whether sqlite is causing the high memory usage, comment the commands
(statements)  related to sqlite and check the memory status.  

Thanks,
  Lloyd.

On Mon, 2006-10-30 at 12:45 +, Nuno Lucas wrote:
> On 10/30/06, Ben Clewett <[EMAIL PROTECTED]> wrote:
> > Nuno,
> >
> > Thanks for the excelent description of my error.  I have learnt a little
> > more about Linux virtual memory model.  Very glad to hear Sqlite is as
> > perfect as ever :)
> >
> > My problem, which is definitely my problem, is that 90 x 16MB of
> > reserved memory is still a loss of 1.4G.  Especially as I use hardly any
> > of it.
> 
> Each process has it's own virtual address space, so 16MB of reserved
> virtual addresses (except when they are kernel addresses) for one
> process doesn't do nothing to the ammount of virtual addresses free
> for other processes. And as each process usually has 2/3GB of virtual
> addresses for it's own use, 16MB is pretty low (it depends on the
> system, but 2 GB is the most common minimum, on 32 bits).
> 
> I still find strange that your program uses so much virtual addresses,
> but you didn't specify (or I don't recall) what language you are using
> and what libraries you are linking to. You may want to investigate
> this further.
> 
> 
> Regards,
> ~Nuno Lucas
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -


__
Scanned and protected by Email scanner

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

Re: [sqlite] Memory Usage

[Nuno Lucas]

On 10/30/06, Ben Clewett <[EMAIL PROTECTED]> wrote:

Nuno,

Thanks for the excelent description of my error.  I have learnt a little
more about Linux virtual memory model.  Very glad to hear Sqlite is as
perfect as ever :)

My problem, which is definitely my problem, is that 90 x 16MB of
reserved memory is still a loss of 1.4G.  Especially as I use hardly any
of it.


Each process has it's own virtual address space, so 16MB of reserved
virtual addresses (except when they are kernel addresses) for one
process doesn't do nothing to the ammount of virtual addresses free
for other processes. And as each process usually has 2/3GB of virtual
addresses for it's own use, 16MB is pretty low (it depends on the
system, but 2 GB is the most common minimum, on 32 bits).

I still find strange that your program uses so much virtual addresses,
but you didn't specify (or I don't recall) what language you are using
and what libraries you are linking to. You may want to investigate
this further.


Regards,
~Nuno Lucas

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

Re: [sqlite] Memory Usage 2

[drh]

Ben Clewett <[EMAIL PROTECTED]> wrote:
> 
> Is the allocation of 4108 KB normal for Sqlite?

No.  Not for me.  If you open the same database using the
command-line client, how much memory does it use?

> Why, then run from xined, is the memory allocation four times as much?
> Why is just Sqlite effected by being run from xinetd?
> 

What else does your program do?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Memory Usage

[Ben Clewett]

Nuno,

Thanks for the excelent description of my error.  I have learnt a little 
more about Linux virtual memory model.  Very glad to hear Sqlite is as 
perfect as ever :)


My problem, which is definitely my problem, is that 90 x 16MB of 
reserved memory is still a loss of 1.4G.  Especially as I use hardly any 
of it.


If you can drop me a hint about how to sort this, this would be 
extremely useful.  Otherwise I'll close this thread and look elsewhere...


Kind regards,

Ben Clewett.


Nuno Lucas wrote:

On 10/30/06, Ben Clewett <[EMAIL PROTECTED]> wrote:

Hi Numo and others,

I am very glad to hear the consensus is that there is nothing wrong with
  libsqlite3.so.0.8.6.

However the fact is that the 'open' still acquires 16MB of memory.

Immediately Before:

   VmSize:   8572 kB
   VmLck:   0 kB
   VmRSS:2252 kB
   VmData:484 kB
   VmStk:  88 kB
   VmExe:  20 kB
   VmLib:6772 kB
   VmPTE:  20 kB

Immediately After:

sqlite3_open(sDatabaseFile, ) (= SQLITE_OK)

   VmSize:  24960 kB
   VmLck:   0 kB
   VmRSS:2368 kB
   VmData:  16872 kB
   VmStk:  88 kB
   VmExe:  20 kB
   VmLib:6772 kB
   VmPTE:  24 kB


I guess that info is from /proc//status (the nomeclature somewhat
differs for other programs).

The program actually only allocated 2368-2252=116 KB, but reserved
16MB of virtual addresses (which is different from actual allocated
memory, as you can check by running free or other tool). That is
normal if it's the first file you open, as the kernel and libc reserve
a bunch of addresses before for internal buffers (to speed up your
I/O).

RSS (the Resident Set Size), is the important one here (unless your
program had parts of it swaped out, which would make it less usefull
for what we want).


Regards,
~Nuno Lucas

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






--
Ben Clewett
+44(0)1923 46
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com

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

Re: [sqlite] Memory Usage

[Nuno Lucas]

On 10/30/06, Ben Clewett <[EMAIL PROTECTED]> wrote:

Hi Numo and others,

I am very glad to hear the consensus is that there is nothing wrong with
  libsqlite3.so.0.8.6.

However the fact is that the 'open' still acquires 16MB of memory.

Immediately Before:

   VmSize:   8572 kB
   VmLck:   0 kB
   VmRSS:2252 kB
   VmData:484 kB
   VmStk:  88 kB
   VmExe:  20 kB
   VmLib:6772 kB
   VmPTE:  20 kB

Immediately After:

sqlite3_open(sDatabaseFile, ) (= SQLITE_OK)

   VmSize:  24960 kB
   VmLck:   0 kB
   VmRSS:2368 kB
   VmData:  16872 kB
   VmStk:  88 kB
   VmExe:  20 kB
   VmLib:6772 kB
   VmPTE:  24 kB


I guess that info is from /proc//status (the nomeclature somewhat
differs for other programs).

The program actually only allocated 2368-2252=116 KB, but reserved
16MB of virtual addresses (which is different from actual allocated
memory, as you can check by running free or other tool). That is
normal if it's the first file you open, as the kernel and libc reserve
a bunch of addresses before for internal buffers (to speed up your
I/O).

RSS (the Resident Set Size), is the important one here (unless your
program had parts of it swaped out, which would make it less usefull
for what we want).


Regards,
~Nuno Lucas

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

Re: [sqlite] Memory Usage

[Lloyd]

Most probably it will be a memory leak in your program. We must release
the dynamically allocated memory ourselves. So check whether you are
forgetting to do that. Most probably that leak will be happening inside
some loops or repeatedly calling functions.


On Fri, 2006-10-27 at 17:00 +0100, Ben Clewett wrote:
> Dear Sqlite,
> 
> I very much enjoy using Sqlite, it is extremely useful.
> 
> I have a memory usage query.
> 
> I am linking to libsqlite3.so.0.8.6.  After calling sqlite3_open(...) I 
> find my programs data memory jumps by 16392 Kb.
> 
> This seems a lot.  The database I am opening is only 26K in size.
> 
> I have a similar process opening about 90 times.  This obviously 
> consumes a very large amount of memory, 1.4G with 90 processes.
> 
> May I ask if this is what would be expected, and whether there is 
> anything I can do to lower this loading?
> 
> Thanks for your help,
> 
> Ben.
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -


__
Scanned and protected by Email scanner

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

Re: [sqlite] Memory Usage

[Eduardo]

At 18:00 27/10/2006, you wrote:

Dear Sqlite,

I very much enjoy using Sqlite, it is extremely useful.

I have a memory usage query.

I am linking to libsqlite3.so.0.8.6.  After calling 
sqlite3_open(...) I find my programs data memory jumps by 16392 Kb.


This seems a lot.  The database I am opening is only 26K in size.

I have a similar process opening about 90 times.  This obviously 
consumes a very large amount of memory, 1.4G with 90 processes.


May I ask if this is what would be expected, and whether there is 
anything I can do to lower this loading?


Thanks for your help,

Ben.


Perhaps SQLite cache is taking that memory. Don't know if the cache 
is pre-allocated or it takes memory as it needs. Also, if your temp 
is memory any temporal table or similar takes more memory.


HTH


---
God is Real, but it can be declared as integer also... 



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

Re: [sqlite] Memory Usage

[Nuno Lucas]

On 10/27/06, Ben Clewett <[EMAIL PROTECTED]> wrote:

I am linking to libsqlite3.so.0.8.6.  After calling sqlite3_open(...) I
find my programs data memory jumps by 16392 Kb.

This seems a lot.  The database I am opening is only 26K in size.


There are many different ways of memory "jump" (like linking with a
lot of dynamic libraries), but one thing is certain: sqlite is not
responsible for that.


I have a similar process opening about 90 times.  This obviously
consumes a very large amount of memory, 1.4G with 90 processes.


It's a memory leak in your program, for sure. Run some memory leak
tool (e.g. valgrind).


May I ask if this is what would be expected, and whether there is
anything I can do to lower this loading?


Unless you decided to mess with sqlite internals, it's not expected in any way.


Thanks for your help,

Ben.


Best regards,
~Nuno Lucas

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

Re: [sqlite] Memory usage and multiple small databases

[D. Richard Hipp]

On Thu, 2005-07-14 at 08:40 -0600, Joey Ekstrom wrote:
> On 7/14/05, Christian Smith <[EMAIL PROTECTED]> wrote:
> > Each database would have it's own page cache, which will be the biggest
> > memory user. So, with 1K pages, and up to 2000 pages by default, your
> > cache footprint would be:
> > 1024 * 2000 * 500 = 102400 bytes
> >   ~ 1 GB

It is very unlikely that all 500 database connections
will be using maximal cache all at the same time.  This
seems like a very pessimistic upper bound.

> 
> That looks really good to me.  What about the memory cost of prepared
> statements?  I know with some database drivers that I've used, having
> a large number of prepared statements caused the memory usage of the
> connections to grow significantly.
> 

Prepared statements use a few KiB each.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Memory usage and multiple small databases

[Fred Williams]

Seems a  client-server DB like MySQL would be much more practical.
Simply append a "User-ID" Column to the DB tables.  I doubt the Internet
users are going to be hammering on the DB hard enough to cause
server/network issues.

But then again, you could front end SQLite with some good thread design,
as long as you promise not to start whining about multi user issues. :-)
FOOTPRINT, FOOTPRINT, FOOTPRINT...

Fred

-Original Message-
From: Christian Smith [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 14, 2005 9:23 AM
To: sqlite-users@sqlite.org; Joey Ekstrom
Subject: Re: [sqlite] Memory usage and multiple small databases


On Wed, 13 Jul 2005, Joey Ekstrom wrote:

>Would sqlite perform well in a web application where each user got
>their own database to operate on?  I know that the size of the sqlite
>library is small, but what is the memory footprint after opening the
>database?  Would it be prohibitive to having a 500+ relatively small
>database open concurrently?

Re: [sqlite] Memory usage and multiple small databases

[Joey Ekstrom]

On 7/14/05, Christian Smith <[EMAIL PROTECTED]> wrote:
> On Wed, 13 Jul 2005, Joey Ekstrom wrote:
> 
> >Would sqlite perform well in a web application where each user got
> >their own database to operate on?  I know that the size of the sqlite
> >library is small, but what is the memory footprint after opening the
> >database?  Would it be prohibitive to having a 500+ relatively small
> >database open concurrently?
> 
> 
> Each database would have it's own page cache, which will be the biggest
> memory user. So, with 1K pages, and up to 2000 pages by default, your
> cache footprint would be:
> 1024 * 2000 * 500 = 102400 bytes
>   ~ 1 GB

That looks really good to me.  What about the memory cost of prepared
statements?  I know with some database drivers that I've used, having
a large number of prepared statements caused the memory usage of the
connections to grow significantly.

-Joey

Re: [sqlite] Memory usage and multiple small databases

[Christian Smith]

On Wed, 13 Jul 2005, Joey Ekstrom wrote:

>Would sqlite perform well in a web application where each user got
>their own database to operate on?  I know that the size of the sqlite
>library is small, but what is the memory footprint after opening the
>database?  Would it be prohibitive to having a 500+ relatively small
>database open concurrently?


Each database would have it's own page cache, which will be the biggest
memory user. So, with 1K pages, and up to 2000 pages by default, your
cache footprint would be:
1024 * 2000 * 500 = 102400 bytes
  ~ 1 GB

So, pretty big worst case. But hey, RAM is relatively cheap (< $150 at
current prices?), and this is a server, right? You can make the caches
smaller if required.

If a significant percentage of databases will be writing at any time, then
disk I/O might be an issue, in which case you *MAY* be better with a
centralised database solution, as logging puts less strain on synchronous
writes.

>
>-Joey
>

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[Thomas Briggs]

   I continue to hope that you're correct.  I'm already somewhat stumped
though.

   I think I see, at the simplest level, how to get the aggregate bucket
data into the b-tree - in AggInsert, change the data passed to
BtreeInsert to be the aggregate bucket itself, not the pointer, and
change the size appropriately; then, correspondingly in the handling of
OP_AggFocus, change the call to BtreeData to read back the entire
aggregate bucket instead of just the pointer.  Conceptually pretty
straightforward.  (I think.  Please correct me if I'm wrong.)

   Here's where I get stuck: calling BtreeInsert from AggInsert no
longer makes any sense, because the data in the aggregate bucket hasn't
yet been accumulated.  So I think, conceptually, I need another op code
that is called before OP_Next to get the data in the current aggregate
bucket written out to the b-tree.  That would seem to require changes to
the code generator, where I haven't yet ventured, so I'm hoping you can
confirm that I'm on the right track before I head off down the garden
path on my own. :)

   Just to make sure I'm not missing anything obvious: OP_Next seems to
be multi-purpose, so I don't want to just stick something in there.  If
there's a way to tell that an aggregate operation is underway such that
I can write the bucket data to the btree I'd be happy with that, but I'm
not sure that there is and the inelegance of that approach irks me
anyway.

   Thanks
   -Tom

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 24, 2005 4:26 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Memory usage for queries containing a 
> GROUP BY clause
> 
> On Thu, 2005-03-24 at 16:08 -0500, Thomas Briggs wrote:
> >Am I wrong in interpreting your comment to mean that 
> this should be
> > feasible within the current architecture, and more 
> importantly, feasible
> > for someone like me who looked at the SQLite source code 
> for the first
> > time yesterday? :)
> > 
> 
> I know of no reason why you should not be able to tackle this
> problem yourself.
> 
> 

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[D. Richard Hipp]

On Thu, 2005-03-24 at 16:08 -0500, Thomas Briggs wrote:
>Am I wrong in interpreting your comment to mean that this should be
> feasible within the current architecture, and more importantly, feasible
> for someone like me who looked at the SQLite source code for the first
> time yesterday? :)
> 

I know of no reason why you should not be able to tackle this
problem yourself.

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[Thomas Briggs]

 
> You are welcomed to experiment with changes that will store the
> entire result set row in the btree rather than just a pointer.
> If you can produce some performance improvements, we'll likely
> check in your changes.

   Am I wrong in interpreting your comment to mean that this should be
feasible within the current architecture, and more importantly, feasible
for someone like me who looked at the SQLite source code for the first
time yesterday? :)

   Thanks for all your help tracking this down.

   -Tom

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[Thomas Briggs]

   Well, I'm using the command line tool that comes with SQLite and
there is no ORDER BY clause in my query, so both the good news and the
bad news is that it certainly seems like something that SQLite is doing,
uhh... sub-optimally, shall we say. :)

   I'm working my way through the VDBE, attempting to figure out what's
going on, and there's one thing to which I can't yet find an answer, and
it's driving me crazy: when handling the AggFocus operation, AggInsert
is called if no entry is found in the b-tree for a given agg key.
AggInsert allocates the memory necessary for the new aggregate bucket,
adds it to the b-tree, and makes it the current bucket.  All well and
good.  But where does that bucket get freed?  Doesn't the next call to
AggInsert clobber the pointer to the previously-allocated bucket?
Unless I'm misunderstanding, sqlite3BtreeInsert makes a copy of the data
that needs to be stored (which only makes sense, if the b-tree is being
written to disk...), so I don't think that sqlite3BtreeInsert can be
responsible for it, right?

   Thanks
   -Tom

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 24, 2005 2:21 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Memory usage for queries containing a 
> GROUP BY clause
> 
> On Thu, 2005-03-24 at 13:59 -0500, Thomas Briggs wrote:
> >I feel like I'm missing something, but that didn't seem 
> to help.  I
> > can see in the code why it should be behaving differently 
> (many thanks
> > for the hint on where to look, BTW), but the memory usage 
> is unchanged.
> > 
> >I modified sqliteInt.h to define SQLITE_OMIT_MEMORYDB, 
> then verified
> > that it is defined by:
> >a) inserting garbage into vdbeaux.c to prevent it from 
> compiling when
> > SQLITE_OMIT_MEMORYDB is defined
> >b) tried to attach to database :memory: without success 
> (it created a
> > file name :memory: instead), and
> >c) started the command line tool without specifying a 
> database and
> > noting that the main database is a file named $CD/:memory: 
> > 
> >Am I missing something stupid here?  Or is all my memory 
> being used
> > somewhere other than constructing the b-tree used for aggregation?
> > 
> 
> It might be the case that SQLite is using memory in ways that
> are extravagent, wasteful, and unnecessary.  Or you could be doing
> something wrong in your app.  Hard to say.
> 
> Another big users of memory is ORDER BY.  If the ORDER BY clause
> cannot be satisfied by use of an index, then the entire result
> set is pulled into memory and sorted there.  Unlike the aggregate
> issue, there is no easy fix for getting ORDER BY to work off of
> disk, except appropriate use of indices in your schema.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[D. Richard Hipp]

On Thu, 2005-03-24 at 13:59 -0500, Thomas Briggs wrote:
>I feel like I'm missing something, but that didn't seem to help.  I
> can see in the code why it should be behaving differently (many thanks
> for the hint on where to look, BTW), but the memory usage is unchanged.
> 
>I modified sqliteInt.h to define SQLITE_OMIT_MEMORYDB, then verified
> that it is defined by:
>a) inserting garbage into vdbeaux.c to prevent it from compiling when
> SQLITE_OMIT_MEMORYDB is defined
>b) tried to attach to database :memory: without success (it created a
> file name :memory: instead), and
>c) started the command line tool without specifying a database and
> noting that the main database is a file named $CD/:memory: 
> 
>Am I missing something stupid here?  Or is all my memory being used
> somewhere other than constructing the b-tree used for aggregation?
> 

It might be the case that SQLite is using memory in ways that
are extravagent, wasteful, and unnecessary.  Or you could be doing
something wrong in your app.  Hard to say.

Another big users of memory is ORDER BY.  If the ORDER BY clause
cannot be satisfied by use of an index, then the entire result
set is pulled into memory and sorted there.  Unlike the aggregate
issue, there is no easy fix for getting ORDER BY to work off of
disk, except appropriate use of indices in your schema.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[Thomas Briggs]

   I feel like I'm missing something, but that didn't seem to help.  I
can see in the code why it should be behaving differently (many thanks
for the hint on where to look, BTW), but the memory usage is unchanged.

   I modified sqliteInt.h to define SQLITE_OMIT_MEMORYDB, then verified
that it is defined by:
   a) inserting garbage into vdbeaux.c to prevent it from compiling when
SQLITE_OMIT_MEMORYDB is defined
   b) tried to attach to database :memory: without success (it created a
file name :memory: instead), and
   c) started the command line tool without specifying a database and
noting that the main database is a file named $CD/:memory: 

   Am I missing something stupid here?  Or is all my memory being used
somewhere other than constructing the b-tree used for aggregation?

   Thanks
   -Tom

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 24, 2005 11:19 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Memory usage for queries containing a 
> GROUP BY clause
> 
> On Thu, 2005-03-24 at 10:57 -0500, Thomas Briggs wrote:
> >After posting my question, I found the discussion of how 
> aggregate
> > operations are performed in the VDBE Tutorial; that implies 
> that memory
> > usage will correspond with the number of unique keys 
> encountered by the
> > query, but I appreciate having it stated explicitly.
> > 
> >How difficult would it be, in concept, to change the 
> storage of the
> > hash buckets from in-memory to on-disk?  The VDBE Tutorial makes it
> > sound like it would be a matter of changing the AggFocus, 
> AggNext, and
> > maybe a few other operations to store/retrieve buckets from 
> disk before
> > operating on them in memory.  How dramatically am I oversimplifying
> > this? :)
> > 
> 
> You have the right idea.  But the job is really much easier.  All of
> the Agg* opcodes already use the standard btree mechanism for storing
> and retrieving their buckets.  But they use a ":memory:" btree by
> default.
> To make it use disk, all you have to do is get it to store 
> the btree in
> a temporary file on disk.
> 
> One easy way to make this happen is to recompile with
> -DSQLITE_OMIT_MEMORYDB.
> See source file vdbeaux.c near line 800 for additional information.
> If you want to hack together a customized version of SQLite that
> behaves differently, that would be a good place to start.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[D. Richard Hipp]

On Thu, 2005-03-24 at 10:57 -0500, Thomas Briggs wrote:
>After posting my question, I found the discussion of how aggregate
> operations are performed in the VDBE Tutorial; that implies that memory
> usage will correspond with the number of unique keys encountered by the
> query, but I appreciate having it stated explicitly.
> 
>How difficult would it be, in concept, to change the storage of the
> hash buckets from in-memory to on-disk?  The VDBE Tutorial makes it
> sound like it would be a matter of changing the AggFocus, AggNext, and
> maybe a few other operations to store/retrieve buckets from disk before
> operating on them in memory.  How dramatically am I oversimplifying
> this? :)
> 

You have the right idea.  But the job is really much easier.  All of
the Agg* opcodes already use the standard btree mechanism for storing
and retrieving their buckets.  But they use a ":memory:" btree by
default.
To make it use disk, all you have to do is get it to store the btree in
a temporary file on disk.

One easy way to make this happen is to recompile with
-DSQLITE_OMIT_MEMORYDB.
See source file vdbeaux.c near line 800 for additional information.
If you want to hack together a customized version of SQLite that
behaves differently, that would be a good place to start.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Memory usage for queries containing a GROUP BY clause

[Thomas Briggs]

   After posting my question, I found the discussion of how aggregate
operations are performed in the VDBE Tutorial; that implies that memory
usage will correspond with the number of unique keys encountered by the
query, but I appreciate having it stated explicitly.

   How difficult would it be, in concept, to change the storage of the
hash buckets from in-memory to on-disk?  The VDBE Tutorial makes it
sound like it would be a matter of changing the AggFocus, AggNext, and
maybe a few other operations to store/retrieve buckets from disk before
operating on them in memory.  How dramatically am I oversimplifying
this? :)

   Thanks
   -Tom

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 24, 2005 10:32 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Memory usage for queries containing a 
> GROUP BY clause
> 
> On Thu, 2005-03-24 at 10:09 -0500, Thomas Briggs wrote:
> 
> >I have a 1GB database containing a single table.  Simple queries
> > against this table (SELECT COUNT(*), etc.) run without 
> using more than a
> > few MBs of memory; the amount used seems to correspond 
> directly with the
> > size of the page cache, as I expected.  If I execute an 
> aggregate query
> > that contains a GROUP BY clause, however, the memory usage 
> seems to jump
> > quite a bit - the memory usage seems to correlate with the number of
> > columns in the GROUP BY.  Grouping by three columns using a couple
> > hundred megs of memory; grouping by eight columns uses more 
> than 1.3GB!.
> > 
> 
> The entire results set of an aggregate query is held in
> memory.  The only way to limit the memory usage of an
> aggregate query is to construct the query in such a way
> that the number of rows in the result set is limited.
> If you are using 1.3GB of memory to do an aggregate query,
> my guess is that there are a very large number of rows in
> the result set.
> 
> It is, in theory, possible to reduce the memory requirements
> for an aggregate query if the GROUP BY clause specifies columns
> that are indexed.  But SQLite does not currently implement
> that optimization.  You can implement the optimization manually,
> though, by turning the GROUP BY clause into an ORDER BY clause,
> dropping the aggregate functions, then doing the aggregation
> manually as the rows come out of the database in order.
> 
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

Re: [sqlite] Memory usage for queries containing a GROUP BY clause

[D. Richard Hipp]

On Thu, 2005-03-24 at 10:09 -0500, Thomas Briggs wrote:

>I have a 1GB database containing a single table.  Simple queries
> against this table (SELECT COUNT(*), etc.) run without using more than a
> few MBs of memory; the amount used seems to correspond directly with the
> size of the page cache, as I expected.  If I execute an aggregate query
> that contains a GROUP BY clause, however, the memory usage seems to jump
> quite a bit - the memory usage seems to correlate with the number of
> columns in the GROUP BY.  Grouping by three columns using a couple
> hundred megs of memory; grouping by eight columns uses more than 1.3GB!.
> 

The entire results set of an aggregate query is held in
memory.  The only way to limit the memory usage of an
aggregate query is to construct the query in such a way
that the number of rows in the result set is limited.
If you are using 1.3GB of memory to do an aggregate query,
my guess is that there are a very large number of rows in
the result set.

It is, in theory, possible to reduce the memory requirements
for an aggregate query if the GROUP BY clause specifies columns
that are indexed.  But SQLite does not currently implement
that optimization.  You can implement the optimization manually,
though, by turning the GROUP BY clause into an ORDER BY clause,
dropping the aggregate functions, then doing the aggregation
manually as the rows come out of the database in order.

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

Re: [sqlite] Memory usage (3.1.0 alpha)

[Ulrik Petersen]

Hi Clive,

To:   sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)
Subject:  Re: [sqlite] Memory usage (3.1.0 alpha)

Hi Clive,
[EMAIL PROTECTED] wrote:
 

I am benchmarking sqlite3 as a potential database for Windows and embedded
applications.
I am running the following code in a Rapid development environment  that calls
the equivalent sqlite3 functions
in a Window's DLL that I built from the release .
I am seeing that memory usage goes up and up with every loop, until Windows
   

runs
 

out of virtual memory.
Am I doing something wrong?
while(true)
   SQL exec: 'BEGIN TRANSACTION';
   for  from 1 to 1000 step 1
SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
   SQL exec: 'COMMIT TRANSACTION';

   

It looks like you've wrapped it in some sort of Visual Basic.  Is that true?
If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API,
yhe behavior you experience may be because you don't call
sqlite3_finalize.  Do you use that API?
HTH
Ulrik P.
 


[EMAIL PROTECTED] wrote:
The environment I am using is RapidPlus. It makes calls directly to the DLL. I
changed the sqlite3 functions just to return in order to eliminate the
possibility of it being a problem with the environment, and there was no memory
loss.
 

Sorry, I don't understand what you mean.  Have you changed the SQLite3 
code at all?

Since I am using sqlite3_exec I do not think I need to use sqlite3_finalize.
Is that correct?
That is correct.

Perhaps the normal behaviour of sqlite3 is to use system memory until there is
non left? 

No, that is not the case.
I cannot find a #define that specifies how many database pages are
cached in memory.
 

It is not a #define, it's PRAGMA:
http://www.sqlite.org/pragma.html
Search the page for "cache_size" and "default_cache_size".
The behavior you experience would be exhibited if:
1) The sqlite3_exec function returned an error, and you did not call 
sqlite3_free on the error message. (See 
http://www.sqlite.org/capi3ref.html#sqlite3_exec )

2) You sqlite3_open'ed a new connection every time without 
sqlite3_close'ing it.

That's all I can think of right now.
HTH
Ulrik

Re: [sqlite] Memory usage (3.1.0 alpha)

[clive]

The environment I am using is RapidPlus. It makes calls directly to the DLL. I
changed the sqlite3 functions just to return in order to eliminate the
possibility of it being a problem with the environment, and there was no memory
loss.

Since I am using sqlite3_exec I do not think I need to use sqlite3_finalize.
Is that correct?
Perhaps the normal behaviour of sqlite3 is to use system memory until there is
non left? I cannot find a #define that specifies how many database pages are
cached in memory.

Clive






Ulrik Petersen <[EMAIL PROTECTED]> on 30-01-2005 00:44:01

Please respond to sqlite-users@sqlite.org

To:   sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)

Subject:  Re: [sqlite] Memory usage (3.1.0 alpha)



Hi Clive,

[EMAIL PROTECTED] wrote:

>I am benchmarking sqlite3 as a potential database for Windows and embedded
>applications.
>I am running the following code in a Rapid development environment  that calls
>the equivalent sqlite3 functions
> in a Window's DLL that I built from the release .
>I am seeing that memory usage goes up and up with every loop, until Windows
runs
> out of virtual memory.
>Am I doing something wrong?
>
>while(true)
> SQL exec: 'BEGIN TRANSACTION';
> for  from 1 to 1000 step 1
>  SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
> SQL exec: 'COMMIT TRANSACTION';
>
>
>
>
It looks like you've wrapped it in some sort of Visual Basic.  Is that true?

If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API,
yhe behavior you experience may be because you don't call
sqlite3_finalize.  Do you use that API?

HTH

Ulrik P.

Re: [sqlite] Memory usage (3.1.0 alpha)

[Ulrik Petersen]

Hi Clive,
[EMAIL PROTECTED] wrote:
I am benchmarking sqlite3 as a potential database for Windows and embedded
applications.
I am running the following code in a Rapid development environment  that calls
the equivalent sqlite3 functions
in a Window's DLL that I built from the release .
I am seeing that memory usage goes up and up with every loop, until Windows runs
out of virtual memory.
Am I doing something wrong?
while(true)
SQL exec: 'BEGIN TRANSACTION';
for  from 1 to 1000 step 1
 SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
SQL exec: 'COMMIT TRANSACTION';
 

It looks like you've wrapped it in some sort of Visual Basic.  Is that true?
If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API, 
yhe behavior you experience may be because you don't call 
sqlite3_finalize.  Do you use that API?

HTH
Ulrik P.