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

[sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

[Alexandre Doubov]

Hello,

I have a few questions with regards to memory impact when bumping the
default SQLITE_MAX_VARIABLE_NUMBER limit from 999 to 32768.

For reference, this is the issue that I created that brought me to this
mailing list:
https://github.com/requery/sqlite-android/issues/124

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)? I'm
assuming that's not the case and that the extra memory consumption comes
from the actual arguments passed into sqlite statements that SQLite needs
to allocate memory for. So if the limit bumped up but the actual # of
arguments that's passed in is always less than 1000, then there's no memory
penalty to bumping this limit up. Is that correct?
2) If we pass the MAX # of variables, what's the overall memory
consumption? For example, if we pass 36 character Strings (32768 of them),
will SQLite allocate 18 MBs for this data? (how I arrived at 18 MBs can be
seen in the linked github issue)

Thanks,
Alex
___
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

[sqlite] memory usage after close database on linux arm

[Stephane Guibert]

Hi SQLite creators,

for embedded Linux 2.6.35.3 on armV5, I get memory usage not freed at 
close(ltib compilation environment).

with this following test ( only one connection to the database ):

1) open database
2) prepare statement
3) SQL request inside begin commit
 4) finalize statement
 5) close database
 6 ) loop to 1 )


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(...
}while

I have got memory usage not freed after the close:

the sqlite3_memory_used()
give 0 before open
give 127376 after the first open
give 133296 after the close

the sqlite3_memory_used()
give 133296 before open
give 260624 after the second open
give 266544 after the close

the sqlite3_memory_used()
give 266544 before open
give 393872 after the third open
give 399792 after the close


/proc/2365/status

Tgid:   2365
Pid:2365
PPid:   1
TracerPid:  0
Uid:0   0   0   0
Gid:0   0   0   0
FDSize: 32
Groups: 0 1 2 3 4 6 10
VmPeak: 3316 kB
VmSize: 3316 kB
VmLck: 0 kB
VmHWM:  2000 kB
VmRSS:  2000 kB
VmData:  964 kB
VmStk:   136 kB
VmExe:   552 kB
VmLib:  1464 kB
VmPTE: 6 kB
VmSwap:0 kB
Threads:1


Tgid:   2365
Pid:2365
PPid:   1
TracerPid:  0
Uid:0   0   0   0
Gid:0   0   0   0
FDSize: 32
Groups: 0 1 2 3 4 6 10
VmPeak: 3580 kB
VmSize: 3580 kB
VmLck: 0 kB
VmHWM:  2256 kB
VmRSS:  2256 kB
VmData: 1228 kB
VmStk:   136 kB
VmExe:   552 kB
VmLib:  1464 kB
VmPTE: 6 kB
VmSwap:0 kB
Threads:1


inside /proc/2365/fd/ a new file descriptor is added at each open database, but 
never deleted at close database.

same behaviour with sqlite 3.9.2 and 3.18.0 ( compiled with -DSQLITE_CORE 
-DNDEBUG=1 -DSQLITE_THREADSAFE=2 -DSQLITE_ENABLE_ATOMIC_WRITE 
-DSQLITE_DEFAULT_AUTOMATIC_INDEX=0 -DSQLITE_ENABLE_MEMORY_MANAGEMENT 
-DSQLITE_TEMP_STORE=3)

Is any workarounds are available to close the database and freed all its 
memories allocated by sqlite inside the same application (without restart the 
application process)?

Regards,
Stephane

___
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

[sqlite] Memory Usage

[David Muchene]

Hi,

I was wondering what options I can tune to make sqlite use more memory. We are 
currently using the memsys5 allocator and giving it a 2G buffer, but it doesn't 
seem to be using any more than 32MB.

Thanks,
Dave M
___
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

[sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

I wrote an C++ application that uses sqlite3 to save part
of the data when it become larger than a known threshold.

The idea is to use at most a known quantity of memory;
to check if it is working I executed a relevant test
using valgrind's massif.

It worked fairly well most of the time, but in two cases
there is a peak of memory usage from sqlite.

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

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

[sqlite] Track SQLite memory usage from inside Java code

[mira]

Hi,

I am experiencing memory issues when inserting a large number of records
into my database.  I use a prepared statement wrapped in a transaction where
I process (insert) 10K records before I clear the batch 
using the PreparedStatement.clearBatch() method;

I would like to insert code to track SQL's memory usage.  I found a solution
for C++ but I use Java.  

Any help would be greatly appreciated!
Mira


--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Track-SQLite-memory-usage-from-inside-Java-code-tp62756.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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

[sqlite] Memory Usage/ Drawbacks of Statements

[Mohit Sindhwani]

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

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

[sqlite] memory usage after VACUUM

[Nick Hodapp]

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

Re: [sqlite] SQLite Memory Usage

[Black, Michael (IS)]

My guess is that you have a memory leak...
 
Care to post your code of how you're "creating" the database?  You're probably 
not freeing the SQL, or the statement handle.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Sachin.2.Gupta
Sent: Mon 11/8/2010 4:47 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] SQLite Memory Usage



Hi,

We are trying to Integrate SQLite in our Application and are trying to populate 
as a Cache. We are planning to use it as a In Memory Database. Using it for the 
first time. Our Application is C++ based.

Our Application interacts with the Master Database to fetch data and performs 
numerous operations. These Operations are generally concerned with one Table 
which is quite huge in size.
We replicated this Table in SQLite and following are the observations:

Number of Fields: 60
Number of Records: 1,00,000

As the data population starts, the memory of the Application, shoots up 
drastically to ~1.4 GB from 120MB. At this time our application is in idle 
state and not doing any major operations. But normally, once the Operations 
start, the Memory Utilization shoots up. Now with SQLite as in Memory DB and 
this high memory usage, we don't think we will be able to support these many 
records.

Q. Is there a way to find the size of the database when it is in memory?

When I create the DB on Disk, the DB size sums to ~40MB. But still the Memory 
Usage of the Application remains very high.
Q. Is there a reason for this high usage. All buffers have been cleared and as 
said before the DB is not in memory?

Any help would be deeply appreciated.

Thanks and Regards
Sachin

___
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] SQLite Memory Usage

[Sachin . 2 . Gupta]

Hi,

We are trying to Integrate SQLite in our Application and are trying to populate 
as a Cache. We are planning to use it as a In Memory Database. Using it for the 
first time. Our Application is C++ based.

Our Application interacts with the Master Database to fetch data and performs 
numerous operations. These Operations are generally concerned with one Table 
which is quite huge in size.
We replicated this Table in SQLite and following are the observations:

Number of Fields: 60
Number of Records: 1,00,000

As the data population starts, the memory of the Application, shoots up 
drastically to ~1.4 GB from 120MB. At this time our application is in idle 
state and not doing any major operations. But normally, once the Operations 
start, the Memory Utilization shoots up. Now with SQLite as in Memory DB and 
this high memory usage, we don't think we will be able to support these many 
records.

Q. Is there a way to find the size of the database when it is in memory?

When I create the DB on Disk, the DB size sums to ~40MB. But still the Memory 
Usage of the Application remains very high.
Q. Is there a reason for this high usage. All buffers have been cleared and as 
said before the DB is not in memory?

Any help would be deeply appreciated.

Thanks and Regards
Sachin

___
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" <a1rex2...@yahoo.com> 
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

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

[a1rex]

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

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

[sqlite] memory usage and queries

[Christopher Taylor]

I am running into issues where I am running out of memory on my embedded
app.  I have stored 10K records in a table.  There is an index on a key
field and a sort field.  An external program needs to reconcile its data
with the data in this table.  It does so 200 records at a time.  The code I
am using is:
Sprint(buffer, "SELECT * FROM tbl_Log where key > %ld and key < %ld order by
key asc", id, id + 200);

The query takes about 3 seconds to run which is acceptable.  The external
app sends an id of 0 and increments according to what is received from the
query.  It is expected to run 50 times to get the 10K records.

After each query, the memory usage jumps.  After about 10 queries I am out
of RAM.

Any ideas on how I can keep the RAM usage stable and low?  I am doing the
finalize on the statement after the looping over the records so I do not
think there is a memory leak there.  Any help is appreciated.

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

[sqlite] Memory Usage

[Marco Bambini]

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.

Please help.
Thanks.
-- 
Marco Bambini


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

[sqlite] Memory usage

[Marco Bambini]

Hello guys,

I need your help in order to solve a very annoying issue with sqlite  
3.6.11.

I have two opened db inside my application and when I insert 180,000  
rows inside a transaction I can see heap memory usage that exceeds  
100MB (data is written twice so I have 2 transactions inside two  
different db each one that write 180,000 rows).

I tried to use the sqlite3_soft_heap_limit without much luck(with  
SQLITE_ENABLE_MEMORY_MANAGEMENT defined) ... please note that  
transactions are started with BEGIN EXCLUSIVE.

I think that the new sqlite versions simply try to cache as much data  
as possible (memory usage was much lower with version 3.2.1 for  
example) ... so, how can keep memory usage low or to a limit similar  
to the old versions?

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





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

[sqlite] memory usage

[ed]

Hello,
My multi-threaded application has various sqlite db's open simultaneously,
in memory using the :memory: keyword, disk based db's and at times, tmpfs
(ram) db's. Is there a way to view each individual database's memory usage?

I found the functions sqlite3_memory_used() and
sqlite3_status(SQLITE_STATUS_MEMORY_USED, ...) but these look like they
provide memory statistics for all of sqlite, not per database.

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

[sqlite] memory usage

[#]

Hello,
My multi-threaded application has various sqlite db's open simultaneously,
in memory using the :memory: keyword, disk based db's and at times, tmpfs
(ram) db's. Is there a way to view each individual database's memory usage?

I found the functions sqlite3_memory_used() and
sqlite3_status(SQLITE_STATUS_MEMORY_USED, ...) but these look like they
provide memory statistics for all of sqlite, not per database.

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

[sqlite] Fwd: sqlite memory usage

[#]

Hello,
My multi-threaded application has various sqlite db's open simultaneously,
in memory using the :memory: keyword, disk based db's and at times, tmpfs
(ram) db's. Is there a way to view each individual database's memory usage?

I found the functions sqlite3_memory_used() and
sqlite3_status(SQLITE_STATUS_MEMORY_USED,
...) but these look like they provide stats for the entire application, not
per database.

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

[sqlite] memory usage

[#]

Hello,My application has several sqlite db's open simultaneously, in memory
using the :memory: keyword, disk based db's and at times, tmpfs based db's.
Is there a way to view each individual database's memory usage?

I found the functions sqlite3_memory_used() and
sqlite3_status(SQLITE_STATUS_MEMORY_USED,
...) but these look like they provide stats for the entire application, not
per database. Also, is there any difference between these two functions?

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

[sqlite] sqlite memory usage

[#]

Hello,My first mail attempt appears not to have gotten through yesterday due
to the spam storm.


My multi-threaded application has various sqlite db's open simultaneously,
in memory using the :memory: keyword, disk based db's and at times, tmpfs
based db's. Is there a way to view each individual database's memory usage?

I found the functions sqlite3_memory_used() and
sqlite3_status(SQLITE_STATUS_MEMORY_USED,
...) but these look like they provide stats for the entire application, not
per database.

thanks,
ed
___
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]
-

[sqlite] Memory Usage

[ScottDerrick]

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
-- 
View this message in context: 
http://www.nabble.com/Memory-Usage-tf4822840.html#a13798003
Sent from the SQLite mailing list archive at Nabble.com.


-
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]
-

[sqlite] Memory Usage

[Ben Clewett]

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]
-

[sqlite] Memory Usage

[Paul M]

Hi all,
I have a program that uses SQLite and it frequently updates and adds to the
database. However, despite my freeing the result when done memory usage
continues to increase over time. The database has currently has 940 rows and
uses sever selects and a select with limit and offset. Also only one insert,
so anyone have any suggestions on what could be the cause? Thanks in
advance.

Paul

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