-
Notifications
You must be signed in to change notification settings - Fork 13
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Server connection pool getting filled by idle "wait: ClientRead" connections when sending queries rapidly #21
Comments
Hello, INSERT INTO "public"."metrics" ("name", "metric", "units", "type")
VALUES
('Sensor 1', 39.46, 'ºF', 'temperature'),
('Sensor 2', 39.46, 'ºF', 'temperature'),
('Sensor 3', 39.46, 'ºF', 'temperature'),
('Sensor 4', 39.46, 'ºF', 'temperature'),
('Sensor 5', 39.46, 'ºF', 'temperature') |
@Alkarex thanks for looking into it. In some circumstances I'm able to batch inserts, but not always. In the flow I attached, each sensor sends data whenever the temperature has changed. Sometimes they will change at the same moment but most often they will change at unrelated moments. I started noticing this hung connection problem because sensors would correctly log for a day or two and then suddenly all logging would stop and the node-red console would fill up with |
I'm having a similar issue to @robacarp, except mine seems to be getting caught up with |
If you have a scenario during which you have a burst of many requests during a short time period, you can increase the Connection Timeout parameter (in milliseconds): It of course depends on the number of requests and the time the SQL requests take to be executed by PostgreSQL. So for safely processing a large number of requests at maximum speed, I advise using the built-in backpressure mechanism whenever possible. |
@robacarp I started having the same issue with INSERTS. What I did is use the |
Similar issue over here. Under some conditions node-red-contrib-postgresql gets stuck in dangling queries and any further request is rejected with "Error: timeout exceeded when trying to connect". In my case the connection pool settings are the default 10, idle timeout 1000ms, connection timeout 10000ms, and the backlog when stuck is e.g.:
It seems as if the connection pool does not get cleaned up properly under certain conditions ... |
My bad. For exactly those nodes firing the queries listed above as dangling I activated "Split results in multiple messages" to ease my post-processing of the single row returned, not recognising that this also activates backpressure management. So seemingly the node was waiting to receive a tick such that it would only then return the connection to the pool with no further rows available. Sorry for the noise ... Kind regards, |
@wangnick Thanks for the update 👍🏻 Let me know if you spot another issue |
A performance issue has been fixed by #25 , which might also positively impact the problems discussed here |
Hey, thanks for this node-red node. I was able to get it up and running pretty quick, but I'm running into a small issue. Simple insert queries are hanging when fired off too fast. It causes the connection pool on the server to fill up, which causes later queries to fail due to
"Error: timeout exceeded when trying to connect"
.A workaround is to add a rate-limit delay node, which tells me that something is getting forgotten when more than one query is being run at a time. I believe this would be insufficient if I had many query nodes in my flows because any collision would leave a connection hanging and eventually lead to pool exhaustion.
I'm not doing anything too clever. Postgres is installed and connected over 127.0.0.1, and it's a clean install of Postgres 14. No other applications are connecting at this time -- I have shut down grafana, which is the only other thing connected to the server.
My query is this:
It gets expanded correctly and is received by postgres like this, which I pulled from the query log:
The first batch of events query node execute fine and data ends up in the table. But the n-1 queries never finishes and the connection is left hanging open:
These connections listed here will never close -- triggering my flow again fires 5 queries and 4 of them will never correctly finish.
Here are some screenshots:
Node-Red flow:
A working node-red flow:
The text was updated successfully, but these errors were encountered: