Database connections stuck

specklesystems / speckle-server

The Speckle Server, Frontend, 3D Viewer, & other JS utilities.

https://speckle.systems

Other

655 stars 164 forks source link

Database connections stuck #748

Open gjedlicska opened 2 years ago

gjedlicska commented 2 years ago

After some time, some of the available database connections get stuck and they are never freed. This causes the preview service to stop functioning (a stopgap measure to revert to node14 was put in place) But its also causing performance degradation on the server too.

jsdbroughton commented 2 years ago

I encountered this a few times recently and put it down to user error with making funky commits. Both things may be true.

gjedlicska commented 2 years ago

Which symptom have you noticed @jsdbroughton ?

jsdbroughton commented 2 years ago

This causes the preview service to stop functioning

Which symptom have you noticed @jsdbroughton ?

Specifically, the previews are generated for some commits and not others. I only notice as I am using the embedded viewer. Whether the viewer has a preview or not is not a huge issue, but it has been inconsistent.

gjedlicska commented 2 years ago

fixed in #805

gjedlicska commented 2 years ago

Lets try with rewriting the SQL query without WHERE IN

gjedlicska commented 1 year ago

next steps to try:

[ ] not use WHERE IN for sql query in get object and children
[ ] sdk-s are sending the requests with get these child objects for me, that are in this list. There is possibly a better solution with cursors or smthing
[ ] properly close node pg query streams, in here, the added extra streams are probably not closed, when a connection is terminated https://github.com/specklesystems/speckle-server/blob/main/packages/server/modules/core/rest/diffDownload.js#L66