Closed GianfilippoDerga closed 1 month ago
@GianfilippoDerga Thank you for reporting your observation.
I have setup a test case to reproduce this issue here: https://github.com/cap-js/cds-dbs/pull/762
When I run the test case with the default behavior I get the following memory consumption pattern on Postgres:
So I did some searching through the Postgres documentation and found the VACUUM
statement (docs). So I have added a VACUUM
call to the test in between scenarios and it produces the following memory consumption pattern on Postgres:
There doesn't seem to be a leak in the @cap-js/postgres
implementation. It seems that Postgres is caching something. Most likely it is related to the WAL
. In the test I have disabled the test that inserts 16.777.216
(1 << 24
) rows. Not because of trouble inserting, but Postgres struggles to DELETE
the rows again while keeping the possibility to rollback the transaction.
There is a feature in Postgres that will automatically call VACUUM
on that database, but you would have to look deeper into how to enable this feature.
Additionally I would like to point out that it is possible with @cap-js/postgres
to stream data into the database (code). Postgres still requires enough memory to load the full payload into memory for processing. So it might still be required to chunk the data, but this way it can insert the data over a single connection and makes it easier to prevent the Postgres from being overloaded.
I used pgAdmin to execute a VACCUM FULL
operation but didn't do the trick.
I have changed the pool config for db in package.json adding evictionRunIntervalMillis
and numTestsPerEvictionRun
and it works for my case.
"db": {
"impl": "@cap-js/postgres",
"pool": {
"max": 50,
"min": 20,
"acquireTimeoutMillis": 5000,
"idleTimeoutMillis": 7500,
"softIdleTimeoutMillis": 5000,
"evictionRunIntervalMillis": 5000,
"numTestsPerEvictionRun": 50
},
"kind": "postgres",
"credentials": {
"host": "localhost",
"port": 5432,
"database": "sfatest",
"user": "postgres",
"password": "postgres"
},
"schema_evolution": "auto"
},
In the library node-pool if not specified numTestsPerEvictionRun
are set to 3 and evictionRunIntervalMillis
are set to 0.
if evictionRunIntervalMillis
are set to 0 the eviction job will not sheculed.
As I'm understanding, numTestsPerEvictionRun
define the number of connection checked each evictionRunIntervalMillis
.
The eviction job checks and closes those connections that passed the idleTimeoutMillis
or the softIdleTimeoutMillis
.
@GianfilippoDerga good to know that the problem could be solved with the pool configurations you shared. Your explanation of the pool configurations are the same as my understanding of them.
looks like this has been resolved, feel free to ask any further questions here
Description of erroneous behaviour
After migrating to cap-js from cds-dbm as a dbs i have a problem related to the db postgres resource handling by the library.
I'm using a specific logic to procedurally generate sql queries to execute massive upsert operations (5000 rows at a time) in my postegres db and after some time the database memory gets saturated and i get multiple errors with
Connection terminated unexpectedly
This errors generate some unhandled exeptions that make my Node.js application crash
db definition in package.json:
We have an insert function for our db tables that cause the problem defined as follow:
An shortened example of the query generated by the generateUpsert function is the following:
This first screen show the usage of memory (right graph). After come time from the memory saturation the app crashes and the memory usage drops
If we increase the allocated memory the app does not crash but we can notice that the ram usage only increases and the memory usage doesn't get freed after the end of the import logic process execution.
Detailed Steps to Reproduce
Details about your project