RAM usage issue TSQLRestServerDB calling BackupBackground

isa · 2018-09-24 15:08:13

Hi,

I have a server that has multiple instances of TSQLRestServerDB running on it. For each user we have a separate databse that has his own data and do his own stuff.
We do backups on these servers once every night using the BackupBackground call. Making a local copies of the database files.

This works great, everything is fine.

However, lately the users increased, reaching approximately 200 and some of them are intensive users who have lots of data, which have databases up to 200MB. Problem is, because I created a 32bit server application (due to certain libraries I use), I am limited to roughly 4GB of RAM.
Normally I have enough resources to handle all of this on the system this server is running, but the BackupBackground significantly increases the memory usage and is not released. And the amount of memory used up depends on the actual size of the database. It's not perfectly linear, but there is a clearly visible correlation.

If this was only for the moment when the backup was created, no problem, but because this is done for roughly 200 databases. It increases the memory usage A LOT. And can only solve this by stopping the servers. I initially thought this had to do with the cache of the database, but that wasn't the case. That was limited to the default 10MB , and clearing it didn't have any effect. Using different parameters on BackupBackground didn't change anything either, read through the comments for that method and similar methods in the code and couldn't find much.

So my question is, what is using this memory? Why is this persistent? And how can I avoid this?

Thanks upfront.

Last edited by isa (2018-09-24 15:08:39)

ab · 2018-09-24 15:14:28

200*10MB = 2GB so you reach your memory limit I guess.

isa · 2018-09-24 16:36:00

Thank you very much for the fast reply,

But no, I don't think the cache is the issue. As you did state that they are cleared on every write operation on the server, and they barely get full after some monitoring. The backup on the other hand seemed like a major issue after monitoring what was using so much ram.
I tested manually on 6 servers with varying Database sizes, ranging from 20MB up to over 200MB. After calling BackupBackground one by one on each of these databases, I noticed a growth in RAM usage with at least 9MB, up to 90MB of ram usage with each separately single call of BackupBackground. The average was around 35MB increase in Ram usage after every BackupBackground call.
And like I said, that increase in RAM persists, even after manually flushing the cache, or even completely disabling the cache. Tried other stuff with page sizes etc, but no luck either.

Looked for other stuff, and am still searching to prevent or clear this somehow, as I do feel like those spaces are used up unnecessarily, but am not sure of course.

Hoping to find an answer soon.

Thanks again.

pvn0 · 2018-09-25 11:59:39

I did a quick test on my setup , doing BackupBackground on a 50KB db every 10 seconds does not increase memory over time, I am however running 64bit and using stock sqlite.org 64bit precompiled dll, so maybe that's something you could look into. If you still have issues then create a reproducable example that I can run and test.

isa · 2018-09-25 12:30:23

pvn0 wrote:

I did a quick test on my setup , doing BackupBackground on a 50KB db every 10 seconds does not increase memory over time, I am however running 64bit and using stock sqlite.org 64bit precompiled dll, so maybe that's something you could look into. If you still have issues then create a reproducable example that I can run and test.

Hi thank you for your reply, but no, that's not the issue.
The memory usage doesn't increase with every BackupBackground call.
It only increased ram usage, the initial time you use BackupBackground. After that, no matter how many times you call BackupBackground, it does NOT increase the RAM usage. However this only goes for that specific instance of TSQLRestServerDB.

Now if you look at my case, let's say that I only had 1 instance of TSQLRestServerDB running, and it does a backup every night. That would increase the RAM usage only on the first call, so the first night it calls that backup. After that, it doesn't increase anymore the nights after. So It's a single time increase only.
Now my problem is, that I don't have a single instance of TSQLRestServerDB, but 200 running at the same time.

So each instance separately increases the ram usage, for the first BackupBackground that was done for each separate server.

So this is my situation:
I have a thread that runs in the background that calls once every night at 3:00 BackupBackground once for every running instance of TSQLRestServerDB I have.

So my server uses up roughly 1.5GB of RAM.
After the first night that becomes roughly 3.7GB, Which leaves a VERY little headroom for other actions on that server.
But after that first night, it remains more or less around 3.7, since only the first BackupBackground call uses up ram, after that it's fine.

After roughly 1 week of uptime I start to see out of memory exceptions here and there, so I restart my server once a week.

I did also write a small fix for this that is temporarily avoiding the issue, which is doing a quick Free and recreate of the running TSQLRestServerDB instance after the backup for it was done. This clears the used up memory from the backup, and seems to work fine. But would rather avoid that if possible, as I want to know the reason why it keeps that RAM space allocated.

pvn0 · 2018-09-25 14:14:13

I understand now, my bad. So it's a single time increase only per server object instance that accumulates because you have so many instances running. Have you tried to step through code in BackupBackground (and associated background thread ) to see which call allocates this memory? I have my suspicions but want to see if you come to the same conclusion because my dataset is so small it's hard to pinpoint.

ab · 2018-09-25 14:37:54

I was talking about the SQLite3 internal cache, not the SynSQLite3 JSON cache.

The SQlite3 internal cache is just filled up to its size, never flushed.
The SynSQLite3 JSON cache is flushed on write.

isa · 2018-09-25 14:58:34

ab wrote:

I was talking about the SQLite3 internal cache, not the SynSQLite3 JSON cache.
The SQlite3 internal cache is just filled up to its size, never flushed.
The SynSQLite3 JSON cache is flushed on write.

I see, thank you very much, than that might be very well be the cause of the issue.
Do you happen to know by heart how I can clear that/flush that? Is it even possible?
And is that cache significant in terms of performance?

Thanks again.

pvn0 wrote:

I understand now, my bad. So it's a single time increase only per server object instance that accumulates because you have so many instances running. Have you tried to step through code in BackupBackground (and associated background thread ) to see which call allocates this memory? I have my suspicions but want to see if you come to the same conclusion because my dataset is so small it's hard to pinpoint.

No I haven't tried that, perhaps I should, but what ab mentioned does make sense, nonetheless I will look into this further tomorrow morning. Thank you

Last edited by isa (2018-09-25 15:00:35)

ab · 2018-09-25 15:17:49

I don't know anything which disable SQlite3 internal cache.
So in your case, I would stick e.g. to 2MB or 4MB cache size for each DB, and rely on the OS global disk cache.

In practice, a 2MB cache size is big enough.
It is the default Sqlite3 value https://www.sqlite.org/pragma.html#pragma_cache_size
The OS will do proper caching for you.

isa · 2018-09-26 07:38:40

ab wrote:

I don't know anything which disable SQlite3 internal cache.
So in your case, I would stick e.g. to 2MB or 4MB cache size for each DB, and rely on the OS global disk cache.
In practice, a 2MB cache size is big enough.
It is the default Sqlite3 value https://www.sqlite.org/pragma.html#pragma_cache_size
The OS will do proper caching for you.

Thank you very much Arnaud. This ended up being it.
I didn't realize that CacheFlush didn't clear that cache in the beginning, later on I was still puzzled because the cache size was set to 10000 Default by SynSQLite3 and my ram usage was significantly larger than 10MB.
But I ended up being wrong.

When combined with the PRAGMA page_size which normally seems to have a default of 1024, instead it seems like SynSQLite3 sets this to 4096 by default. Which means a total cache size of 40MB if I'm correct, for each instance I have. Which seems to be the reason of my issue And Backup was my culprit because it was by far the easiest and fastest way to fill the cache.

Once again thank you!

EDIT: Though I notice now, somehow my math was wrong? setting cache size to 2000 and page size to 1024 still utilizes 20MB of ram, only after setting it to 200 and 1024 the ram usage was lowered to 2MB. I suppose I'm missing something.
EDIT 2: I also can sort of Simulate a Cache flush by setting these values to their minimum (cache 1, and page size 512), and then setting it back to their original sizes, but don't know if that's a safe thing to do. Or if it has some side effects.

Last edited by isa (2018-09-26 08:24:16)

ab · 2018-09-26 09:39:13

Page_size can't be changed once a file is created.

And cache_size only at file opening IIRC (to be verified).

isa · 2018-09-26 09:54:39

ab wrote:

Page_size can't be changed once a file is created.
And cache_size only at file opening IIRC (to be verified).

Weird,
I must have seen things, testing now again indeed changing page size doesn't seem to do much, and I should leave it be then. Guess I'll only adjust the Cache size.
However like I said resetting the cache size does seem to drop the ram usage. So e.g. my example:

I start a single server. Has 17MB of ram usage.
I do a backup with the standard cache size of 10000, and end up with ram usage of roughly 100MB (the DB in quesiton is 89MB)
I now tried this.

BackupServer
temp := CacheSize
CacheSize := 1;
CacheSize := temp;

And this leaves me with 19Mb of ram usage after calling it.
But nonetheless, you have been a great help to me. Once again thank you

ab · 2018-09-26 13:59:31

Downsizing the CacheSize seems a good idea, then!

But you may try do to it before BackupServer:

temp := CacheSize
CacheSize := 1024;
BackupServer
CacheSize := temp;

since the backup process doesn't really need the cache IIRC.
So the memory won't grow up at all.

isa · 2018-09-26 14:43:49

ab wrote:

Downsizing the CacheSize seems a good idea, then!
But you may try do to it before BackupServer:
temp := CacheSize
CacheSize := 1024;
BackupServer
CacheSize := temp;
since the backup process doesn't really need the cache IIRC.
So the memory won't grow up at all.

Nice tip, thank you once again, and with this my issue should be solved indeed.
Thanks again!

mORMot Open Source

#1 2018-09-24 15:08:13

RAM usage issue TSQLRestServerDB calling BackupBackground

#2 2018-09-24 15:14:28

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#3 2018-09-24 16:36:00

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#4 2018-09-25 11:59:39

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#5 2018-09-25 12:30:23

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#6 2018-09-25 14:14:13

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#7 2018-09-25 14:37:54

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#8 2018-09-25 14:58:34

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#9 2018-09-25 15:17:49

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#10 2018-09-26 07:38:40

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#11 2018-09-26 09:39:13

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#12 2018-09-26 09:54:39

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#13 2018-09-26 13:59:31

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

#14 2018-09-26 14:43:49

Re: RAM usage issue TSQLRestServerDB calling BackupBackground

Board footer