SQLITE and the memory

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-------- Kenavo https://md5finder.blogspot.com/ (Never be pleased, always improve)
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE and the memory

Simon Slavin-3
On 8 Sep 2019, at 2:06pm, Philippe RIO <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE and the memory

Dominique Pellé
In reply to this post by Philippe RIO
Philippe RIO <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE and the memory

Philippe RIO
In reply to this post by Simon Slavin-3
I use the windows task manager to see how the memory is used


Opening the 256 tables :
sqlite3_open_v2(_lpszTbl,&hSqlite[__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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-------- Kenavo https://md5finder.blogspot.com/ (Never be pleased, always improve)
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> On Behalf
>Of Philippe RIO
>Sent: Sunday, 8 September, 2019 15:13
>To: [hidden email]
>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,&hSqlite[__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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE and the memory

Olivier Mascia
In reply to this post by Philippe RIO
> Le 8 sept. 2019 à 23:12, Philippe RIO <[hidden email]> a écrit :
>
> Opening the 256 tables :
> sqlite3_open_v2(_lpszTbl,&hSqlite[__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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE and the memory

Jens Alfke-2
In reply to this post by Philippe RIO


> On Sep 8, 2019, at 2:12 PM, Philippe RIO <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> On Behalf
>Of Jens Alfke
>Sent: Monday, 9 September, 2019 16:18
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] SQLITE and the memory
>
>
>
>> On Sep 8, 2019, at 2:12 PM, Philippe RIO <[hidden email]> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE and the memory

Philippe RIO
In reply to this post by 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 :



<http://sqlite.1065341.n5.nabble.com/file/t9046/t2ylpbjbwo27yes6g.jpg>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-------- Kenavo https://md5finder.blogspot.com/ (Never be pleased, always improve)