Huge RAM usage when sqlite is started from another thread

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

Huge RAM usage when sqlite is started from another thread

Martin Vystrčil
Hello everyone,

I have a problem using sqlite in one of my project. When I create instance
of sqlite (sqlite_open) from main thread, memory consumption is in normal
(a few megabytes). But when I start sqlite from another thread, immediately
around 70 - 80 MB of memory is allocated.

Here is the smallest example, which can reproduce this problem. Link to
pastebin where source code is: https://pastebin.com/BkU3uMCb.

There is also some more info about memory usage.

There are results of memory usage (from proc/pid/status) when database is
opened from main thread.

VmPeak:    19780 kB
VmSize:    19636 kB
VmLck:         0 kB
VmPin:         0 kB
VmHWM:      1756 kB
VmRSS:      1756 kB
VmData:      308 kB
VmStk:       132 kB
VmExe:         8 kB
VmLib:      4744 kB
VmPTE:        60 kB
VmSwap:        0 kB


And there are results when sqlite is opened from other thread

VmPeak:   158904 kB
VmSize:    93368 kB
VmLck:         0 kB
VmPin:         0 kB
VmHWM:      1636 kB
VmRSS:      1636 kB
VmData:    74040 kB
VmStk:       132 kB
VmExe:         8 kB
VmLib:      4744 kB
VmPTE:        60 kB
VmSwap:        0 kB

Most notable difference is VmData, which is like 80MB in case when the
other thread is started. Is there any reason, why sqlite consumes so much
memory when started from other thread ? Or do I have any error in my
application ?

In parallel with this e-mail I also asked on stackoverflow.

https://stackoverflow.com/questions/51317823/sqlite-high-ram-usage-when-connected-from-other-thread?noredirect=1#comment89612124_51317823

Every help is highly appreciated.
Best Regards,
Martin
_______________________________________________
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: Huge RAM usage when sqlite is started from another thread

Bob Friesenhahn
On Fri, 13 Jul 2018, Martin Vystrčil wrote:

> Hello everyone,
>
> I have a problem using sqlite in one of my project. When I create instance
> of sqlite (sqlite_open) from main thread, memory consumption is in normal
> (a few megabytes). But when I start sqlite from another thread, immediately
> around 70 - 80 MB of memory is allocated.
>
> Here is the smallest example, which can reproduce this problem. Link to
> pastebin where source code is: https://pastebin.com/BkU3uMCb.

I can not be bothered to visit such a site.

What is the size of the sqlite database file?  Is memory-mapping
enabled on the file?  Is WAL-mode enabled?

> There is also some more info about memory usage.

The growth seems to be almost entirely virtual memory, which could be
due to the size of the database file and the options used (e.g. mmap
of the file leads to more virtual memory used).  The VmRSS value (in
conjunction with VmSwap=0) is more interesting since it indicates how
much memory is actually being used.

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

Re: Huge RAM usage when sqlite is started from another thread

Richard Hipp-3
On 7/13/18, Bob Friesenhahn <[hidden email]> wrote:

> On Fri, 13 Jul 2018, Martin Vystrčil wrote:
>
>> Hello everyone,
>>
>> I have a problem using sqlite in one of my project. When I create instance
>> of sqlite (sqlite_open) from main thread, memory consumption is in normal
>> (a few megabytes). But when I start sqlite from another thread,
>> immediately
>> around 70 - 80 MB of memory is allocated.
>>
>> Here is the smallest example, which can reproduce this problem. Link to
>> pastebin where source code is: https://pastebin.com/BkU3uMCb.
>
> I can not be bothered to visit such a site.

The OP's test program (with a bug fix, various whitespace changes, and
the addition of a call to sqlite3_memory_used()) is show below.
sqlite3_memory_used() reports no difference in memory allocation.

---------------------------------------------------------------------------------
#include <iostream>
#include <pthread.h>
#include <unistd.h>
#include <sqlite3.h>

using namespace std;

void *poll(void *data){
  int rc = 0;
  char *errMsg = 0;
  sqlite3 *database;
  int i;

  rc = sqlite3_open(":memory:", &database);
  if(rc != SQLITE_OK){
    std::cout << "Cannot open memory database" << std::endl;
  }
  rc = sqlite3_exec(database, "create table if not exists xyz(id int,
descr text)",0,0,&errMsg);
  if(rc != SQLITE_OK){
    std::cout << "Cannot create table in SQLiteDB: " << errMsg << std::endl;
  }
  sqlite3_free(errMsg);

  for(i=0; i<10; i++){
    std::cout << "Thread safe: " << sqlite3_threadsafe() << std::endl;
    rc = sqlite3_exec(database, "pragma page_count",0,0,&errMsg);
    if(rc != SQLITE_OK){
      std::cout << "Select page count from DB failed " << errMsg << std::endl;
      sqlite3_free(errMsg);
    }
    std::cout << "memory used: " << sqlite3_memory_used() << std::endl;
    usleep(100000);
    std::cout << "Select from DBSQLite" << std::endl;
  }
}

int main(int argc, char *argv[]){
  if(argc > 1){
    poll(NULL);
  }else{
    cout << "Starting of sqlite DB threaded mode ..." << endl;
    pthread_t p;
    if(pthread_create(&p, NULL, poll, NULL)){
      cerr << "Error creating thread" << endl;
    }
    if(pthread_join(p, NULL)){
      cerr << "Cannot join thread" << endl;
    }
  }
}
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Huge RAM usage when sqlite is started from another thread

Bob Friesenhahn
On Fri, 13 Jul 2018, Richard Hipp wrote:
>
> The OP's test program (with a bug fix, various whitespace changes, and
> the addition of a call to sqlite3_memory_used()) is show below.
> sqlite3_memory_used() reports no difference in memory allocation.

The usage is uninitialized/unmodified virtual memory which could be
from a heap allocation (with no subsequent writes to it) or due to
memory mapping something such as a file.  The cause of the the usage
may be deduced by inspecting the /proc/[pid]/smaps content on a Linux
system where '[pid]' is the process id of the program.

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

Re: Huge RAM usage when sqlite is started from another thread

Martin Vystrčil
Thank you both for help.
What Richar Hipp wrote is truth and something what I also saw using
valgrind - memory allocation (overall consumption) of around 300 - 500 kB.

To answer at least some one Bob's question from first e-mail, I'm trying to
open in memory database (:memory:), so size of database should be almost
nothing.

By checking smaps, I can see big allocation there. It seems that all your
assumptions are correct, memory doesn't seem to be used.

7fdd28021000-7fdd2c000000 ---p 00000000 00:00 0
Size:              65404 kB
Rss:                   0 kB
Pss:                   0 kB
Shared_Clean:          0 kB
Shared_Dirty:          0 kB
Private_Clean:         0 kB
Private_Dirty:         0 kB
Referenced:            0 kB
Anonymous:             0 kB
AnonHugePages:         0 kB
Shared_Hugetlb:        0 kB
Private_Hugetlb:       0 kB
Swap:                  0 kB
SwapPss:               0 kB
KernelPageSize:        4 kB
MMUPageSize:           4 kB
Locked:                0 kB
VmFlags: mr mw me nr sd

But still, I would like to solve this somehow, could you please suggest me
some point, where to start ? Are there any possible compilation options, to
limit this allocation ? It would be completely fine for me, to allocate
like 1MB or so, but this is way too much, although not used.

Thank you for your help,
Best Regards,
Martin



2018-07-13 17:31 GMT+02:00 Bob Friesenhahn <[hidden email]>:

> On Fri, 13 Jul 2018, Richard Hipp wrote:
>
>>
>> The OP's test program (with a bug fix, various whitespace changes, and
>> the addition of a call to sqlite3_memory_used()) is show below.
>> sqlite3_memory_used() reports no difference in memory allocation.
>>
>
> The usage is uninitialized/unmodified virtual memory which could be from a
> heap allocation (with no subsequent writes to it) or due to memory mapping
> something such as a file.  The cause of the the usage may be deduced by
> inspecting the /proc/[pid]/smaps content on a Linux system where '[pid]' is
> the process id of the program.
>
> Bob
> --
> Bob Friesenhahn
> [hidden email], http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: Huge RAM usage when sqlite is started from another thread

Richard Hipp-3
On 7/13/18, Martin Vystrčil <[hidden email]> wrote:
>
> But still, I would like to solve this somehow, could you please suggest me
> some point, where to start ? Are there any possible compilation options, to
> limit this allocation ?

It is not SQLite that is doing this allocation.  I suspect it is
something happening inside of the malloc() in your libc.

You can see the SQLite is never doing a huge allocation by calling
sqlite3_memory_highwater(0) in place of sqlite3_memory_used().

You are probably better off letting malloc() do whatever allocations
it wants.  However, if you really want to work around this, there are
ways of getting SQLite to use a memory allocator other than the
default system malloc().  See https://www.sqlite.org/malloc.html for
further information.  These are sharp techniques, so be careful.  But
if you do it right, you can limit the memory usage of SQLite to
whatever you want.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Huge RAM usage when sqlite is started from another thread

Martin Vystrčil
Yes, I can see that SQLite is doing just small allocations using memory
methods.

I also tried to limit the size of database using following two methods

     rc = sqlite3_config(SQLITE_CONFIG_HEAP, buffer, 5000000, 64);
    if(rc != SQLITE_OK)
    {
        printf("Failed to set custom heap memory\n");
    }

    rc = sqlite3_soft_heap_limit64(1000000);
    printf("Prev: %d\n", rc);

When I use both of them as in this example, I get usage of DB of around
22MB, what is not so bad.
Using of small heap (array in my case) should be fine for my application,
as it is going to use just small databases (1 - 3MB).

What do you think about this technique ? I think, it could be very
dangerous when size of database grows, but that should not happen.
Thank you and Regards,
Martin

2018-07-13 20:58 GMT+02:00 Richard Hipp <[hidden email]>:

> On 7/13/18, Martin Vystrčil <[hidden email]> wrote:
> >
> > But still, I would like to solve this somehow, could you please suggest
> me
> > some point, where to start ? Are there any possible compilation options,
> to
> > limit this allocation ?
>
> It is not SQLite that is doing this allocation.  I suspect it is
> something happening inside of the malloc() in your libc.
>
> You can see the SQLite is never doing a huge allocation by calling
> sqlite3_memory_highwater(0) in place of sqlite3_memory_used().
>
> You are probably better off letting malloc() do whatever allocations
> it wants.  However, if you really want to work around this, there are
> ways of getting SQLite to use a memory allocator other than the
> default system malloc().  See https://www.sqlite.org/malloc.html for
> further information.  These are sharp techniques, so be careful.  But
> if you do it right, you can limit the memory usage of SQLite to
> whatever you want.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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