Re: [sqlite] SQLITE and the memory

[Philippe RIO]

The answer:

I remember : I have 256 databases.
I have an array of 256 columns into which I store the number of record
created. When I insert a record into a table, the corresponding entry into
this array is incremented by one. When this element is equal to 1000 I
commit the transaction. Not all the the parts of the array are filled at the
same time, In that case the memory used grows. I had forgotten to commit
many transaction for which the values into the array was less than 1000. Now
rather than using 2Gb I only needs less than 1Gb wich is which is partially
released. It will be released when the tables will be closed.

hereis the code to see what I am writing :



 

My last test for inserting 10 000 000  passwords was good for me. I made it
in 43 minutes.

Thank you for your help.




-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE and the memory

[Keith Medcalf]

The Windows Task Manager shows various types of "memory usage" and that is 
different depending on how the wind blows at the moment.  In fact, even the how 
the "name descriptions" given in the Windows Task Manager correspond to actual 
memory management values is subject to how the wind blows at the moment one is 
looking.  That is, Windows is a magical mystical beast and the numbers reported 
by any of its memory management subsystems only have vague correspondence (if 
any) to what a knowledgeable professional in the area of virtual memory 
management might call them.

That said, there are the common "Memory Size" numbers available through Task 
Manager (Windows 10 Pro for Workstations v.1903) and what they mean in "normal" 
terms:

Working set (memory)
 - sum of "Working set (private)" and "Working set (shared)" (typically 
equivalent to VMSize)

Peak working set (memory)
 - high water mark of "Working set (memory)" since process started

Working set delta (memory)
 - Change in "Working set (memory)" since last time measured

Memory (active private working set)
 - Theoretically the current process private V:R size (that is, the private 
memory resident size)

Memory (private working set)
 - Memory that is allocated to the process (and cannot be shared) (may or may 
not be committed memory)

Memory (shared working set)
 - Memory that is allocated to the process (that can be shared) (may or may not 
be committed memory)

Commit size
 - The portion of the Virtual Memory allocated to the process which requires 
backing store, including discardable pages, but excluding uncommitted pages.


By default, Task Manager shows the "Memory (active private working set)" (ie, 
Resident Private Bytes) in its display.

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

>-Original Message-
>From: sqlite-users  On Behalf
>Of Jens Alfke
>Sent: Monday, 9 September, 2019 16:18
>To: SQLite mailing list 
>Subject: Re: [sqlite] SQLITE and the memory
>
>
>
>> On Sep 8, 2019, at 2:12 PM, Philippe RIO <51...@protonmail.ch> wrote:
>>
>> I use the windows task manager to see how the memory is used
>
>I don't use Windows, but I know that in any modern OS, memory usage is a
>very vague thing and is tricky to measure. There are quite a few numbers
>that mean different things, like
>- actual RAM in use by the process
>- virtual address space allocated
>- address space with backing store assigned to it
>- address space not being shared with other processes
>- address space that's writeable
>- address space used for 'malloc' heaps
>- address space actually in use in heaps
>etc.
>
>I find that when looking at memory usage of a program I'm working on, the
>stats related to heap space are the most useful because they correspond
>with memory my code is involved in allocating and managing.  The farther up
>that list you go, the more you see the effects of things like memory-mapped
>I/O, shared library sizes, filesystem caches, and other things that are
>usually out of your control.
>
>Specific to SQLite: it's usually pretty easy to manage the amount of memory
>it uses because most of it is block caches, which you can customize the
>size of yourself with pragmas.
>
>—Jens
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] SQLITE and the memory

[Jens Alfke]

> On Sep 8, 2019, at 2:12 PM, Philippe RIO <51...@protonmail.ch> wrote:
> 
> I use the windows task manager to see how the memory is used

I don't use Windows, but I know that in any modern OS, memory usage is a very 
vague thing and is tricky to measure. There are quite a few numbers that mean 
different things, like
- actual RAM in use by the process
- virtual address space allocated
- address space with backing store assigned to it
- address space not being shared with other processes
- address space that's writeable
- address space used for 'malloc' heaps
- address space actually in use in heaps
etc.

I find that when looking at memory usage of a program I'm working on, the stats 
related to heap space are the most useful because they correspond with memory 
my code is involved in allocating and managing.  The farther up that list you 
go, the more you see the effects of things like memory-mapped I/O, shared 
library sizes, filesystem caches, and other things that are usually out of your 
control.

Specific to SQLite: it's usually pretty easy to manage the amount of memory it 
uses because most of it is block caches, which you can customize the size of 
yourself with pragmas.

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

Re: [sqlite] SQLITE and the memory

[Olivier Mascia]

> Le 8 sept. 2019 à 23:12, Philippe RIO <51...@protonmail.ch> a écrit :
> 
> Opening the 256 tables :
> sqlite3_open_v2(_lpszTbl,[__iTableNumber],SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX,NULL);

Wouldn't you be confusing database tables and files?

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia

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

Re: [sqlite] SQLITE and the memory

[Keith Medcalf]

Unused memory is wasted memory.  If you have unused memory then you bought 
something that you did not need.

The question I have however is:
(a) Why do you have such a crapload of itty-bitty databases?  It kind of 
defeats the whole purpose of having a database at all.
(b) Why have you changed the page_size from the default?  There are be reasons 
for doing this, what is yours?

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

>-Original Message-
>From: sqlite-users  On Behalf
>Of Philippe RIO
>Sent: Sunday, 8 September, 2019 15:13
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] SQLITE and the memory
>
>I use the windows task manager to see how the memory is used
>
>
>Opening the 256 tables :
>sqlite3_open_v2(_lpszTbl,[__iTableNumber],SQLITE_OPEN_READWRITE|SQL
>ITE_OPEN_FULLMUTEX,NULL);
>
>
>
>
>
>-
>
>Kenavo
>
>https://md5finder.blogspot.com/
>(Never be pleased, always improve)
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] SQLITE and the memory

[Philippe RIO]

I use the windows task manager to see how the memory is used


Opening the 256 tables :
sqlite3_open_v2(_lpszTbl,[__iTableNumber],SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX,NULL);





-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE and the memory

[Dominique Pellé]

Philippe RIO <51...@protonmail.ch> wrote:

> Hello,
> I have an application composed of 256 databases. Each database occupied 42Mb
> for more than 950 000 records into.
>
> [samedi 7 septembre 2019 13:11:45] : Number of passwords stored : 244 152
> 645
> [samedi 7 septembre 2019 13:19:28] : Closing Log file
> [samedi 7 septembre 2019 13:19:28] : *** Log Stoped ***
>
> These 42Mb are measured after a Vacuum.
> When I lauched my application it only opens the databases (all) and the
> memory is filled by 57mb just for SQLITE !
> When I launch a treatment to generate random passwords the memory is filled
> at 87% (>2Gb).
> I would like to reduce this used memory but I don't know how.
>
> I have the following pragmas :
>
> main.page_size=65536
> wal_autocheckpoint=0
> encoding "UTF_8"
> temp_store=FILE
> case_sensitive_like=TRUE
> foreign_keys=OFF
> legacy_file_format=OFF
> cache_size=-200
> main.cache_size=-200
> main.auto_vacuum=NONE
> main.journal_mode=OFF
> main.secure_delete=OFF
> main.synchronous=OFF
> main.locking_mode=EXCLUSIVE
> main.user_version=230714the data
> main.application_id=241259
> shrink_memory
>
> Some pragmas have a sense when creating the database and the other when
> creating the tables.
>
> I have recompiled SQLITE with the following options :
>
> SQLITE_ENABLE_COLUMN_METADATA = 1
> SQLITE_THREAD_SAFE = 2
> SQLITE_DEFAULT_MEMSTATUS = 0
> SQLITE_DEFAULT_FILE_FORMAT = 4
> SQLITE_DEFAULT_PCACHE_INITSZ = 0
> SQLITE_WIN32_MALLOC = 1
> SQLITE_TEMP_STORE = 0
> SQLITE_CASE_SENSITIVE_LIKE = 1
> SQLITE_ENABLE_API_ARMOR 1
> SQLLITE_ENABLE_FTS3 = 1
> SQLLITE_ENABLE_FTS3_PARENTHESIS = 1
> SQLLITE_ENABLE_FTS3_TOKENIZER = 1
> SQLLITE_ENABLE_FTS4 = 1
> SQLLITE_ENABLE_FTS5 = 1
> SQLLITE_ENABLE_GEOPOLY = 1
> SQLLITE_ENABLE_DESERIALIZE = 1
> SQLLITE_ENABLE_JSON1 = 1
> SQLLITE_ENABLE_MEMORY_MANAGEMENT = 1
> SQLLITE_ENABLE_RTREE = 1
> SQLLITE_ENABLE_ENABLE_SESSION = 1
> SQLLITE_ENABLE_SOUNDEX = 1
>
> The software is running under W7 Pro
> I have no problem with it, I find it very fast.
> See my blog at https://md5finder.blogspot.com/2019/08/md5finder.html
> But this memory used/consummed is really a problem.
> I have a small machine with only 3Gb of RAM and only one program needs 87% !
> How to reduce that.
>
> I would appreciate some help.
> Thank You

How about running a memory profiler to find
out when memory is used?  On Linux I'd use
massif, but I don't know what's available on Windows.

If you open many DB connections, then:
1) each connection uses memory for the schema
2) each connection uses memory for the prepared statement.
3) each connection uses paged cache

You can use sqlite3_soft_heap_limit64() to
limit the total memory use in SQLite. It's a soft limit
so SQLite may allocate more if it has no other choice.

Open DBs in read-only mode if possible, it saves
memory as constraints are then not stored in memory.

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

Re: [sqlite] SQLITE and the memory

[Simon Slavin]

On 8 Sep 2019, at 2:06pm, Philippe RIO <51...@protonmail.ch> wrote:

> These 42Mb are measured after a Vacuum.
> When I lauched my application it only opens the databases (all) and the 
> memory is filled by 57mb just for SQLITE !

How are you opening these database ?  Do you have 256 connections, each for one 
database ?

Also, how are you measuring this 57mb ?  Where does it appear ?

Please test this: open only half the 256 databases and see what it reports your 
memory usage is.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE and the memory

[Philippe RIO]

Hello,
I have an application composed of 256 databases. Each database occupied 42Mb
for more than 950 000 records into.

[samedi 7 septembre 2019 13:11:45] : Number of passwords stored : 244 152
645
[samedi 7 septembre 2019 13:19:28] : Closing Log file
[samedi 7 septembre 2019 13:19:28] : *** Log Stoped ***

These 42Mb are measured after a Vacuum.
When I lauched my application it only opens the databases (all) and the
memory is filled by 57mb just for SQLITE !
When I launch a treatment to generate random passwords the memory is filled
at 87% (>2Gb).
I would like to reduce this used memory but I don't know how.

I have the following pragmas :

main.page_size=65536
wal_autocheckpoint=0
encoding "UTF_8"
temp_store=FILE
case_sensitive_like=TRUE
foreign_keys=OFF
legacy_file_format=OFF
cache_size=-200
main.cache_size=-200
main.auto_vacuum=NONE
main.journal_mode=OFF
main.secure_delete=OFF
main.synchronous=OFF
main.locking_mode=EXCLUSIVE
main.user_version=230714the data
main.application_id=241259
shrink_memory

Some pragmas have a sense when creating the database and the other when
creating the tables.

I have recompiled SQLITE with the following options :

SQLITE_ENABLE_COLUMN_METADATA = 1
SQLITE_THREAD_SAFE = 2
SQLITE_DEFAULT_MEMSTATUS = 0
SQLITE_DEFAULT_FILE_FORMAT = 4
SQLITE_DEFAULT_PCACHE_INITSZ = 0
SQLITE_WIN32_MALLOC = 1
SQLITE_TEMP_STORE = 0
SQLITE_CASE_SENSITIVE_LIKE = 1
SQLITE_ENABLE_API_ARMOR 1
SQLLITE_ENABLE_FTS3 = 1
SQLLITE_ENABLE_FTS3_PARENTHESIS = 1
SQLLITE_ENABLE_FTS3_TOKENIZER = 1
SQLLITE_ENABLE_FTS4 = 1
SQLLITE_ENABLE_FTS5 = 1
SQLLITE_ENABLE_GEOPOLY = 1
SQLLITE_ENABLE_DESERIALIZE = 1
SQLLITE_ENABLE_JSON1 = 1
SQLLITE_ENABLE_MEMORY_MANAGEMENT = 1
SQLLITE_ENABLE_RTREE = 1
SQLLITE_ENABLE_ENABLE_SESSION = 1
SQLLITE_ENABLE_SOUNDEX = 1

The software is running under W7 Pro
I have no problem with it, I find it very fast.
See my blog at https://md5finder.blogspot.com/2019/08/md5finder.html
But this memory used/consummed is really a problem.
I have a small machine with only 3Gb of RAM and only one program needs 87% !
How to reduce that.

I would appreciate some help.
Thank You

Philippe RIO



-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite in low memory devices

[Israel Lins Albuquerque]

Well I have a port to latest version (3.6.23.1) of sqlite to palmos, and in 
some devices with this S.O. 
had only 2MB of Dynamic Heap. On this environments sqlite doesn't works pretty 
well, then to work I had 
to set the cache size (PRAGMA cache_size) for all attached database, 
calculating the possible value based 
on the amouth of free memory and the page size. 

Hanging this on mind, on vacuum I had to do the same thing, but on vacuum the 
sqlite make attach of a 
memory database and that need use the same size of cache used by main database, 
then, I modified the 
sqlite code to do this, and now vacuum will work. I'm sending attached the path 
for this. 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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

[sqlite] SQLite version 3.6.10: Memory savings with large pages

[Brown, Daniel]

Good morning list,

Has anyone else noticed significant memory savings when using larger
page sizes since upgrading to 3.6.9 or 3.6.10 (we were on 3.6.1
previously)?  We use about 7.32 MB (Peak 9.64 MB) when loading our
database into RAM using default settings and using larger page sizes
(PRAGMA page_size = 4096) used to not affect memory usage much.  Since
the upgrade we are now using only 5.65 MB (Peak 8.00 MB) loading the
same database using larger pages, very impressive!

Cheers,

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

RE: [sqlite] SQLite 3.5.2 - Unix - Memory issue?

[Mark Brown]

Ah, yes.  My mistake.  Thanks.

> 
> The locking style has absolutely nothing to do with
> threads.  Locking style is what kind of file locks
> are used to prevent separate processes from updating
> the same database as the same time.  SQLite uses
> posix advisory locks by default.  Not all systems
> support posix advisory locks correctly, which is the
> reason for providing alternatives.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>



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

Re: [sqlite] SQLite 3.5.2 - Unix - Memory issue?

[drh]

"Mark Brown" <[EMAIL PROTECTED]> wrote:
> I was using the noLocking style.  Is that essentially what you get with
> SQLITE_THREADSAFE = 0?
> 

The locking style has absolutely nothing to do with
threads.  Locking style is what kind of file locks
are used to prevent separate processes from updating
the same database as the same time.  SQLite uses
posix advisory locks by default.  Not all systems
support posix advisory locks correctly, which is the
reason for providing alternatives.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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

RE: [sqlite] SQLite 3.5.2 - Unix - Memory issue?

[Mark Brown]

I was using the noLocking style.  Is that essentially what you get with
SQLITE_THREADSAFE = 0?

Thanks,
Mark


> 
> I recommend that you *not* use LOCKING_STYLE.  It is
> disabled by default.  Dan and I have a note to go back
> and get the LOCKING_STYLE stuff working, but we have not
> gotten around to that yet, being distracted by more
> pressing issues.



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

Re: [sqlite] SQLite 3.5.2 - Unix - Memory issue?

[drh]

"Mark Brown" <[EMAIL PROTECTED]> wrote:
> Hi-
> 
> I am currently migrating over from 3.4 to 3.5.2.  I am creating a custom
> VFS, but it is essentially the same as unix.
> 
> As I was going through the code, there was a particular section that =
> made me
> think that it may not have been migrated correctly.
> 
> In the fillInUnixFile() method (and assuming that
> SQLITE_ENABLE_LOCKING_STYLE is defined), 

I recommend that you *not* use LOCKING_STYLE.  It is
disabled by default.  Dan and I have a note to go back
and get the LOCKING_STYLE stuff working, but we have not
gotten around to that yet, being distracted by more
pressing issues.

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


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

[sqlite] SQLite 3.5.2 - Unix - Memory issue?

[Mark Brown]

Hi-

I am currently migrating over from 3.4 to 3.5.2.  I am creating a custom
VFS, but it is essentially the same as unix.

As I was going through the code, there was a particular section that made me
think that it may not have been migrated correctly.

In the fillInUnixFile() method (and assuming that
SQLITE_ENABLE_LOCKING_STYLE is defined), my concern is at line 2196:

  pNew = sqlite3_malloc( sizeof(unixFile) );

(in context, the surrounding code is:)

  pNew->dirfd = -1;
  pNew->h = h;
  SET_THREADID(pNew);
  pNew = sqlite3_malloc( sizeof(unixFile) );  /* <--- This line */
  if( pNew==0 ){
close(h);
enterMutex();
releaseLockInfo(pNew->pLock);
releaseOpenCnt(pNew->pOpen);
leaveMutex();
return SQLITE_NOMEM;
  }else{
switch(lockingStyle) {

It seems like the allocation will wipe out previous set values and cause a
memory leak.  Could someone verify?

Thanks,
Mark



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

Re: [sqlite] Sqlite in Shared Memory

[Ben Combee]

On 7/12/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:

Hi,

I want to use Sqlite as in-mem db and want this Mem to be alloacted on SHM.
Has anyone tried it?


Since sqlite3 uses file locking as an IPC mechanism to prevent
multiple modifications, you're probably best using it with a file in
/tmp or some other shared-memory based file system.  Then, the file
locking would control access to the shared memory data.

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

[sqlite] Sqlite in Shared Memory

[RaghavendraK 70574]

Hi,

I want to use Sqlite as in-mem db and want this Mem to be alloacted on SHM.
Has anyone tried it?

regards
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

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

Re: [sqlite] sqlite and in memory databases

[Jay Sprenkle]

Yes, several others on this forum have done exactly that.
I believe the forum is archived somewhere, but I don't know where.
It might provide some leads.

On 6/26/06, james osburn <[EMAIL PROTECTED]> wrote:

i am looking for a embeddable sql database that i can use to manipulate
memory stored in battery back sram. would sqllite be cable of doing this?

thanks
j osburn






--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

[sqlite] sqlite and in memory databases

[james osburn]

i am looking for a embeddable sql database that i can use to manipulate 
memory stored in battery back sram. would sqllite be cable of doing this?


thanks
j osburn

Re: [sqlite] SQLite 3.3.6 - possibly memory leak

[Brannon King]

I've seen a few issues myself, but they all appeared to be related to 
the memory management thing. If you're using VC, try putting this into 
your code:

#define CRTDBG_MAP_ALLOC
#include 
#include 

// and then in your main function:

_CrtSetDbgFlag ( _CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF | 
_CRTDBG_CHECK_ALWAYS_DF);

Put a copy of the above line in sqlite3_open command as well. I think that it 
would be good to run the standard tests with that enabled. It provides a lot of 
useful information and checks for out of bounds or memory leaks on every 
allocation. It is similar to Borland's Codeguard. I have no idea how to enable 
malloc_dbg automatically in GCC, though.

Here's a few links:
http://www.amanjit-gill.de/CPP_VCtips.html
http://www.cprogramming.com/debugging/valgrind.html
http://msdn2.microsoft.com/en-us/library/sc65sadd(VS.80).aspx

Sasa Zeman wrote:

I working with my own SQLite wrapper for Delphi, with the statically linked
SQLite 3.3.6.

File variant works fine:
  SQLite3_Open('Test.sqb',db);
  SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
  ...

However memory variant rise a memory leak report:
  SQLite3_Open(':memory:',db);
  SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
  ...

I'm aware that above is a nonsence script. However, scripts with created
table which is later deleted twice by mistake, can also produce mentioned
problem. Please confirme memory leak existanace.

Sasa
--
www.szutils.net


  

Re: [sqlite] SQLite 3.3.6 - possibly memory leak

[Christian Smith]

Sasa Zeman uttered:


I working with my own SQLite wrapper for Delphi, with the statically linked
SQLite 3.3.6.

File variant works fine:
 SQLite3_Open('Test.sqb',db);
 SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
 ...

However memory variant rise a memory leak report:
 SQLite3_Open(':memory:',db);
 SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
 ...

I'm aware that above is a nonsence script. However, scripts with created
table which is later deleted twice by mistake, can also produce mentioned
problem. Please confirme memory leak existanace.



Speculating, as I'm not familiar with your Delphi wrapper, but are you 
sqlite3_free()'ing the error message string when reporting any errors?





Sasa



Christian

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

[sqlite] SQLite 3.3.6 - possibly memory leak

[Sasa Zeman]

I working with my own SQLite wrapper for Delphi, with the statically linked
SQLite 3.3.6.

File variant works fine:
  SQLite3_Open('Test.sqb',db);
  SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
  ...

However memory variant rise a memory leak report:
  SQLite3_Open(':memory:',db);
  SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
  ...

I'm aware that above is a nonsence script. However, scripts with created
table which is later deleted twice by mistake, can also produce mentioned
problem. Please confirme memory leak existanace.

Sasa
--
www.szutils.net

Re: [sqlite] SQLite on limited Memory

[drh]

"Tony Fenleish" <[EMAIL PROTECTED]> wrote:
> You meantion avoid using sqlite3_get_table.  I've been using sqlite 2.8.15 
> and have postponed upgrading just because "it's been working".  Are there 
> better memory handling and speed improvements for embedded devices in sqlite 
> 3, or are most of the improvements for machines with lots of ram and large 
> processors?
> 

SQLite version 3 contains a number of enhancements
designed specifically for embedded devices with
limited memory.  The database files for version 3
are about 33% smaller.  Version 3 uses less stack
and heap space.  And the code footprint is about 
the same size.  When malloc() fails in version 3.3.x,
just just get an SQLITE_NOMEM error for that one
function call.  When malloc() fails in version 2, 
you get the SQLITE_NOMEM forever more.  SQLite
version 3 supports AUTOVACUUM.

There are probably other embedded-device specific
enhancments in version 3, but those are the ones
that jump to mind.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] SQLite on limited Memory

[Tony Fenleish]

You meantion avoid using sqlite3_get_table.  I've been using sqlite 2.8.15 
and have postponed upgrading just because "it's been working".  Are there 
better memory handling and speed improvements for embedded devices in sqlite 
3, or are most of the improvements for machines with lots of ram and large 
processors?




From: Christian Smith <[EMAIL PROTECTED]>
Subject: Re: [sqlite] SQLite on limited Memory
Date: Tue, 25 Apr 2006 15:06:34 +0100 (BST)

Otherwise, avoid loading the full result set into memory as you process
it. Ie. Avoid sqlite3_get_table().

Christian

Re: [sqlite] SQLite on limited Memory

[Christian Smith]

On Mon, 24 Apr 2006, Tony Fenleish wrote:

>I've been developing an app on a linux embedded device with 128 M of RAM,
>although the available RAM for queries is less than that.  Now that the
>databases are getting a little larger (120 MB), I'm having problems with
>some queries that have large results.  I watch my memory drop completely,
>and then my sqlite client is killed.  Now, as I understand it, the entire
>database is not opened into memory, just the amount of data that is needed.
>
>According to documentation, 256 Bytes of memory is needed for every 1 MB of
>data.  Does anyone have insights as to the memory limitations of SQLite?
>

IIRC, the 256 bytes of memory per 1MB of data is to track dirty pages for
rollback.

The memory footprint of SQLite is controlled primarily by:
- Page cache. Defaults to up to 2000 pages. At 1K pages, that's up to 2MB
  of cached page data. This number can be reduced as needed.
- Temporary tables in memory or disk based tables. If memory based
  temporary store is used, then any temporary tables used for sorting, for
  example, will be memory resident. Temporary tables, however, can be
  moved to temporary disk files to compete with the page cache just like
  the main database. This is set using "PRAGMA temp_store=". See
  http://www.sqlite.org/pragma.html for details.

Otherwise, avoid loading the full result set into memory as you process
it. Ie. Avoid sqlite3_get_table().

If you are sorting large queries, switch to using disk based temporary
store.

Christian


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

[sqlite] SQLite on limited Memory

[Tony Fenleish]

I've been developing an app on a linux embedded device with 128 M of RAM, 
although the available RAM for queries is less than that.  Now that the 
databases are getting a little larger (120 MB), I'm having problems with 
some queries that have large results.  I watch my memory drop completely, 
and then my sqlite client is killed.  Now, as I understand it, the entire 
database is not opened into memory, just the amount of data that is needed.


According to documentation, 256 Bytes of memory is needed for every 1 MB of 
data.  Does anyone have insights as to the memory limitations of SQLite?

Re: [sqlite] SQLite and Flash memory

[Paul Bohme]

[EMAIL PROTECTED] wrote:


Has anyone experimented with putting SQLite directly on the
Linux MTD flash interface?  I.e. by-passing JFSS2.  Would this
offer any kind of significant efficiency benefit?  
 



JFFS2 is designed to wear the flash evenly, preventing you from 
'drilling holes' by writing to any single block more than others and 
wearing it out faster.  If there is a performance gain to be had by 
going directly to flash, you lose the robustness and leveling properties 
of the JFFS2.  Unless of course the 'direct to flash' implementation 
accounts for power loss, bad blocks and wear leveling I'd be loathe to 
give up JFFS2.


 -P

[sqlite] SQLite and Flash memory

[d . webb]

Has anyone experimented with putting SQLite directly on the
Linux MTD flash interface?  I.e. by-passing JFSS2.  Would this
offer any kind of significant efficiency benefit?  

-dw

Re: RE: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[Clifford Wolf]

Hi,

On Mon, Oct 03, 2005 at 11:16:12AM -0400, [EMAIL PROTECTED] wrote:
> I also added code to deallocate the hash tables when their size
> reaches zero. [...]

Thanks a lot.

I am sure this is worth the overhead and makes sqlite an even better piece
of software.

yours,
 - clifford

--
 _  _   _  Nerds on Air - Radio for Geeks  _  __ ___
| \| |___  /_\   On 1st and 3rd Friday of the month   / |/ /__  / _ |
| .` / _ \/ _ \21:00-22:00 CET on Radio Orange   // _ \/ __ |
|_|\_\___/_/ \_\ http://www.clifford.at/noa//_/|_/\___/_/ |_|
 
perl -le '$_=1;(1x$_)!~/^(11+)\1+$/&${_}while$_++<1000'|fmt
 

Re: RE: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[drh]

Clifford Wolf <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> On Mon, Oct 03, 2005 at 09:02:38AM -0400, [EMAIL PROTECTED] wrote:
> > You are right: this is not a real memory leak.
> > [..]
> 
> in fact, for a program which is eg. continously using mktemp() (or a
> simmilar but not unsecure api) for creating temporary databases it is a
> real memory leak, because the hash table will grow one entry for every
> temporary database created.
> 

I added a new regression test named manydb.test to prove that the
above is not a problem.

I also added code to deallocate the hash tables when their size
reaches zero.  This is pointless code that is there only to make
valgrind happy.  But I get complaints about valgrind frequently
enough that I've grown weary of reading them.  So now SQLite
is a little bigger and a little slower, but at least valgrind
doesn't complain anymore.
--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: RE: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[Thomas Briggs]

   While I can understand your general sentiment, allowing minor
problems like this to clutter the output from valgrind makes spotting
the real errors amidst the noise more difficult.  Eventually, when
enough of these types of problems exist, valgrind stops being used
altogether, because it's too time consuming to inspect the output.

   In short, I guess my point is that this kind of thing is another
example of "broken window syndrome" - keeping the small things tidy
makes everyone more likely to keep the big things tidy too.

   -Tom

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Monday, October 03, 2005 9:03 AM
> To: sqlite-users@sqlite.org
> Subject: Re: RE: [sqlite] SQLite kind-of memory leak (PATCH) 
> - bug reports
> 
> "Griggs, Donald" <[EMAIL PROTECTED]> wrote:
> > > 
> > > I'm using valgrind for checking for memory leaks in SPL. When 
> > > profiling scripts which do access SQLite databases, I've 
> found that 
> > > the lockHash and openHash data structures in os_unix.c 
> don't get freed.
> > > 
> > > I wouldn't consider that a real memory leak, but it 
> doesn't look nice 
> > > in memory profilers such as valgrind. That's why I recommend the 
> > > attached patch. Please let me know how you think about it..
> > > 
> >
> 
> You are right: this is not a real memory leak.
> 
> I am disinclined to add code to SQLite that serves no purpose
> other than to make the output of valgrind look better.  valgrind
> is a nice tool for tracking down memory allocation problems.
> (SQLite uses a different mechanism, but that should not be
> taken as a slight by valgrind.)  But I do not believe that
> valgrind should become an end in itself.  I will certainly make
> whatever changes to SQLite are necessary to fix *real* memory
> leaks.  I would even be willing to modify existing code to
> better suit valgrind as long as it doesn't add complexity
> or have a run-time cost.  But the changes submitted do have
> a run-time cost, and while that cost is very small (perhaps
> even unmeasurable) it is finite.  We have worked *very* hard
> to remove such minor costs from SQLite and it would be a shame
> to add them back, just so that the output of a diagnostic
> tool could look nicer.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

Re: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[drh]

Clifford Wolf <[EMAIL PROTECTED]> wrote:
> 
> > but instead start a ticket at:
> > http://www.sqlite.org/cvstrac/tktnew
> 
> what shall I do with the patch? simply copy it to the 'bug description'
> text field? i haven't seen a file upload function in the interface.
> 

There is an "[attach]" hyperlink on the upper right of the ticket
viewer page.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: RE: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[Clifford Wolf]

Hi,

On Mon, Oct 03, 2005 at 09:02:38AM -0400, [EMAIL PROTECTED] wrote:
> You are right: this is not a real memory leak.
> [..]

in fact, for a program which is eg. continously using mktemp() (or a
simmilar but not unsecure api) for creating temporary databases it is a
real memory leak, because the hash table will grow one entry for every
temporary database created.

> [..] just so that the output of a diagnostic tool could look nicer.

what about an sqlite3_cleanup() function which does such cleanups? then it
would add no overhead at all to the library. Right now it is impossible for
a host application to do such cleanups because the allocated memory is only
referenced by static variables in os_unix.c. I could provide a patch which
adds an sqlite3_cleanup() api.

it would even possible to put that code path into a seperate elf section so
it is not paged into the physical memory unless it actually is used by the
application, if the incrased code size would be your next argument. but I
think that this would be overkill.

yours,
 - clifford

--
    ___    _  __  _ _ www.rocklinux.org
|  _ \ / _ \ / ___| |/ / | |   (_)_ __  _   ___  __
| |_) | | | | |   | ' /  | |   | | '_ \| | | \ \/ /
|  _ <| |_| | |___| . \  | |___| | | | | |_| |>  <
|_| \_\\___/ \|_|\_\ |_|_|_| |_|\__,_/_/\_\

Re: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[Clifford Wolf]

Hi,

thanks for the quick reply,

On Mon, Oct 03, 2005 at 08:35:10AM -0400, Griggs, Donald wrote:
> Page http://www.sqlite.org/support.html Suggests that you *not* directly
> email the (actually pretty responsive) author,

the page says:

Please do not send email directly to the author of SQLite
unless [..] You are working on an open source project.

and there are plenty of open source projects with my name on them, so I
thought it would be ok.

> but instead start a ticket at:
> http://www.sqlite.org/cvstrac/tktnew

what shall I do with the patch? simply copy it to the 'bug description'
text field? i haven't seen a file upload function in the interface.

yours,
 - clifford

--
L I N : B I T   ___
 ___ __ _  |_  |  The OSS cluster synchronization tool for
/ __(_-http://oss.linbit.com/csync2/ ] ---
   /___/
 
"This 'telephone' has too many shortcomings to be seriously considered as a
means of communication." - Western Union internal memo, 1876.
 

Re: RE: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[drh]

"Griggs, Donald" <[EMAIL PROTECTED]> wrote:
> > 
> > I'm using valgrind for checking for memory leaks in SPL. When 
> > profiling scripts which do access SQLite databases, I've found that 
> > the lockHash and openHash data structures in os_unix.c don't get freed.
> > 
> > I wouldn't consider that a real memory leak, but it doesn't look nice 
> > in memory profilers such as valgrind. That's why I recommend the 
> > attached patch. Please let me know how you think about it..
> > 
>

You are right: this is not a real memory leak.

I am disinclined to add code to SQLite that serves no purpose
other than to make the output of valgrind look better.  valgrind
is a nice tool for tracking down memory allocation problems.
(SQLite uses a different mechanism, but that should not be
taken as a slight by valgrind.)  But I do not believe that
valgrind should become an end in itself.  I will certainly make
whatever changes to SQLite are necessary to fix *real* memory
leaks.  I would even be willing to modify existing code to
better suit valgrind as long as it doesn't add complexity
or have a run-time cost.  But the changes submitted do have
a run-time cost, and while that cost is very small (perhaps
even unmeasurable) it is finite.  We have worked *very* hard
to remove such minor costs from SQLite and it would be a shame
to add them back, just so that the output of a diagnostic
tool could look nicer.

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

RE: [sqlite] SQLite kind-of memory leak (PATCH) - bug reports

[Griggs, Donald]

 Hi Clifford,

Page http://www.sqlite.org/support.html
Suggests that you *not* directly email the (actually pretty responsive)
author, but instead start a ticket at:

http://www.sqlite.org/cvstrac/tktnew


Donald Griggs


Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.


-Original Message-
From: Clifford Wolf [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 03, 2005 8:00 AM
To: SQLite Users
Cc: [EMAIL PROTECTED]
Subject: [sqlite] SQLite kind-of memory leak (PATCH)

Hi,

I have sent the following mail to [EMAIL PROTECTED] about a month ago. But I got
no reply and as far as I can see my patch has not been applied in the sqlite
cvs so far.

If [EMAIL PROTECTED] is the wrong address to send sqlite development issues,
what is the right one? I can only find a pointer to this 'sqlite users'
list on the webpage.

yours,
 - clifford

On Mon, Sep 12, 2005 at 01:04:13PM +0200, Clifford Wolf wrote:
> Hi,
> 
> I'm the developer of a scripting language called SPL. This scripting 
> language also has a module for accessing sqlite databases.
> 
> I'm using valgrind for checking for memory leaks in SPL. When 
> profiling scripts which do access SQLite databases, I've found that 
> the lockHash and openHash data structures in os_unix.c don't get freed.
> 
> I wouldn't consider that a real memory leak, but it doesn't look nice 
> in memory profilers such as valgrind. That's why I recommend the 
> attached patch. Please let me know how you think about it..
> 
> yours,
>  - clifford

--snip--
Clifford Wolf:
Fixed kind-of-memory-leak in sqlite-3.2.0 for unix platforms
(This is not really a memory leak - just unfreed memory which
is confusing valgrind and other leak checkers..)

--- sqlite-3.2.0/src/os_unix.c  2005-09-09 23:21:57.0 +0200
+++ sqlite-3.2.0/src/os_unix.c  2005-09-10 22:38:29.0 +0200
@@ -231,6 +231,9 @@
 static Hash lockHash = { SQLITE_HASH_BINARY, 0, 0, 0, 0, 0 };  static Hash
openHash = { SQLITE_HASH_BINARY, 0, 0, 0, 0, 0 };
 
+static int lockHashCounter = 0;
+static int openHashCounter = 0;
+
 
 #ifdef SQLITE_UNIX_THREADS
 /*
@@ -302,6 +305,8 @@
   pLock->nRef--;
   if( pLock->nRef==0 ){
 sqlite3HashInsert(, >key, sizeof(pLock->key), 0);
+if (--lockHashCounter == 0)
+  sqlite3HashClear();
 sqliteFree(pLock);
   }
 }
@@ -313,6 +318,8 @@
   pOpen->nRef--;
   if( pOpen->nRef==0 ){
 sqlite3HashInsert(, >key, sizeof(pOpen->key), 0);
+if (--openHashCounter == 0)
+  sqlite3HashClear();
 sqliteFree(pOpen->aPending);
 sqliteFree(pOpen);
   }
@@ -360,6 +367,7 @@
 pLock->cnt = 0;
 pLock->locktype = 0;
 pOld = sqlite3HashInsert(, >key, sizeof(key1), pLock);
+lockHashCounter++;
 if( pOld!=0 ){
   assert( pOld==pLock );
   sqliteFree(pLock);
@@ -383,6 +391,7 @@
 pOpen->nPending = 0;
 pOpen->aPending = 0;
 pOld = sqlite3HashInsert(, >key, sizeof(key2), pOpen);
+openHashCounter++;
 if( pOld!=0 ){
   assert( pOld==pOpen );
   sqliteFree(pOpen);
--snap--

--
  L The SPL Programming Language
SP  P L http://www.clifford.at/spl/
  L 
   S PL An object oriented, stateful, simple, small, c-like,
 P  embeddable, feature rich, dynamic scripting language
 
Qrpelcgvat ebg13 ivbyngrf gur QZPN! Cercner gb or fhrq!!
 

[sqlite] SQLite kind-of memory leak (PATCH)

[Clifford Wolf]

Hi,

I have sent the following mail to [EMAIL PROTECTED] about a month ago. But I
got no reply and as far as I can see my patch has not been applied in the
sqlite cvs so far.

If [EMAIL PROTECTED] is the wrong address to send sqlite development issues,
what is the right one? I can only find a pointer to this 'sqlite users'
list on the webpage.

yours,
 - clifford

On Mon, Sep 12, 2005 at 01:04:13PM +0200, Clifford Wolf wrote:
> Hi,
> 
> I'm the developer of a scripting language called SPL. This scripting
> language also has a module for accessing sqlite databases.
> 
> I'm using valgrind for checking for memory leaks in SPL. When profiling
> scripts which do access SQLite databases, I've found that the lockHash and
> openHash data structures in os_unix.c don't get freed.
> 
> I wouldn't consider that a real memory leak, but it doesn't look nice in
> memory profilers such as valgrind. That's why I recommend the attached
> patch. Please let me know how you think about it..
> 
> yours,
>  - clifford

--snip--
Clifford Wolf:
Fixed kind-of-memory-leak in sqlite-3.2.0 for unix platforms
(This is not really a memory leak - just unfreed memory which
is confusing valgrind and other leak checkers..)

--- sqlite-3.2.0/src/os_unix.c  2005-09-09 23:21:57.0 +0200
+++ sqlite-3.2.0/src/os_unix.c  2005-09-10 22:38:29.0 +0200
@@ -231,6 +231,9 @@
 static Hash lockHash = { SQLITE_HASH_BINARY, 0, 0, 0, 0, 0 };
 static Hash openHash = { SQLITE_HASH_BINARY, 0, 0, 0, 0, 0 };
 
+static int lockHashCounter = 0;
+static int openHashCounter = 0;
+
 
 #ifdef SQLITE_UNIX_THREADS
 /*
@@ -302,6 +305,8 @@
   pLock->nRef--;
   if( pLock->nRef==0 ){
 sqlite3HashInsert(, >key, sizeof(pLock->key), 0);
+if (--lockHashCounter == 0)
+  sqlite3HashClear();
 sqliteFree(pLock);
   }
 }
@@ -313,6 +318,8 @@
   pOpen->nRef--;
   if( pOpen->nRef==0 ){
 sqlite3HashInsert(, >key, sizeof(pOpen->key), 0);
+if (--openHashCounter == 0)
+  sqlite3HashClear();
 sqliteFree(pOpen->aPending);
 sqliteFree(pOpen);
   }
@@ -360,6 +367,7 @@
 pLock->cnt = 0;
 pLock->locktype = 0;
 pOld = sqlite3HashInsert(, >key, sizeof(key1), pLock);
+lockHashCounter++;
 if( pOld!=0 ){
   assert( pOld==pLock );
   sqliteFree(pLock);
@@ -383,6 +391,7 @@
 pOpen->nPending = 0;
 pOpen->aPending = 0;
 pOld = sqlite3HashInsert(, >key, sizeof(key2), pOpen);
+openHashCounter++;
 if( pOld!=0 ){
   assert( pOld==pOpen );
   sqliteFree(pOpen);
--snap--

--
  L The SPL Programming Language
SP  P L http://www.clifford.at/spl/
  L 
   S PL An object oriented, stateful, simple, small, c-like,
 P  embeddable, feature rich, dynamic scripting language
 
Qrpelcgvat ebg13 ivbyngrf gur QZPN! Cercner gb or fhrq!!