You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I'm debugging a memory issue in my application, and noticed an interesting pattern in how pg-pool works, that leads to an increased memory consumption. This may be not the fault of the library, but more of how I use it, therefore any insights or advice would be helpful.
I've got a NodeJS application that uses Postgres integrated via node-postgres with a big traffic, therefore a significant amount of reads from the database. The consumption from the database happens via typeorm.
The configuration is quite simple:
connectionsLimit = 100
idleTimeout = 10 000 ms
replication with one master and one read replica
The fun begins when application is working in production - with the increase of load the amount of consumed memory significantly increases. When the load is stopped, memory gets released over a span of ~25 minutes.
I've taken a memory snapshot and uploaded it into the visualizing tool, which showed me that a big chunk of memory is occupied by the Postgres connection pool.
I've surfed around the pg-pool internals, and figured out the following:
when the database query is executed, the available connection tries to be obtained from the pool
if there is a free idle connection or the room to create a new one - query gets executed
otherwise the query gets added to _pendingQueue and is executed once the next occupied connection becomes idle
So in my case:
the application load is higher than the amount of available connections can handle
when the amount of pending queries grows bigger than {N} connections, _pendingQueue starts to grow
it keeps growing under the constant load, because the query arrival rate is higher than the release rate
on a long run it "eats" a significant amount of memory
The main questions at this point are:
Am I doing something dramatically wrong here? What is the best pattern for handling huge database load?
Is there a way to reduce the memory footprint used by pending queries?
I suspect the best way out of this situation is scale the application when the pending queue grows too large. Is consuming waitingCount on an instance of the pool and making a decision based on it is a right thing to do?
The text was updated successfully, but these errors were encountered:
Am I doing something dramatically wrong here? What is the best pattern for handling huge database load?
Nope! You're doing things right....you just need to scale your app servers more if you're running out of memory.
What is the best pattern for handling huge database load?
More read replicas, with one pg-pool instance per replica will allow more read throughput from postgres (horizontal scaling the db readers)
2. Is there a way to reduce the memory footprint used by pending queries?
Outside of the text & params to the query there isn't much overhead besides that. You could use something like promise-queue or something else higher up in your app to queue a request before submitting to postgres...but YMMV on the amount of memory saved
3. I suspect the best way out of this situation is scale the application when the pending queue grows too large. Is consuming waitingCount on an instance of the pool and making a decision based on it is a right thing to do?
Yup that's a public API you can rely on being there "forever" - there are tests written against it existing. As far as that being the absolute #1 with a bullet way to scale from a decision making perspective...I'd probably go with something more holisitic like number of requests per second or something but that's very application/business specific.
These are great questions - thank you for them! Please lmk if you have any more. ❤️
I'm debugging a memory issue in my application, and noticed an interesting pattern in how
pg-pool
works, that leads to an increased memory consumption. This may be not the fault of the library, but more of how I use it, therefore any insights or advice would be helpful.I've got a NodeJS application that uses Postgres integrated via
node-postgres
with a big traffic, therefore a significant amount of reads from the database. The consumption from the database happens viatypeorm
.The configuration is quite simple:
The fun begins when application is working in production - with the increase of load the amount of consumed memory significantly increases. When the load is stopped, memory gets released over a span of ~25 minutes.
I've taken a memory snapshot and uploaded it into the visualizing tool, which showed me that a big chunk of memory is occupied by the Postgres connection pool.
I've surfed around the
pg-pool
internals, and figured out the following:_pendingQueue
and is executed once the next occupied connection becomes idleSo in my case:
{N}
connections,_pendingQueue
starts to growThe main questions at this point are:
waitingCount
on an instance of the pool and making a decision based on it is a right thing to do?The text was updated successfully, but these errors were encountered: