I am using Lucee 5.4.7.3 with PostgreSQL Extension 42.7.5. Our Postgres database is on version 17.5. We’ve recently started having issues with UPDATE statements (and sometimes INSERT statements) getting stuck in the database with a “ClientRead” wait event (“Waiting to read data from the client”). This usually indicates that it’s waiting on the client to send more data to complete the query.
These queries have been in use and unchanged in my application for years, and they actually do not have any input parameters. They’re mostly used to aggregate data or process business logic using data from other tables. Also, there’s about 30 of these queries that are forced to run single-threaded across the whole app, but it almost always gets stuck on the same query that does a lot of string manipulation. The batch of 30 queries runs about 1400 times per day against no more than a few hundred records at a time, but it only gets stuck once or twice per day if at all.
At first I thought this was due to connection pooling being recently implemented with PGBouncer. The Lucee datasource is set to use infinite (-1) connections and has been that way since long before these issues. However, over the past week I have set the application to connect to the database on a port that circumvents PGBouncer, and the issue still persists.
Inspecting the database shows that it’s not starved for CPU or memory. However, once a query gets stuck we see the CPU spike until the session is killed.
If anyone has any ideas what might be causing this problem or how to fix it, I would be greatly appreciative.
From my quick research, clientRead can also occur when a connection is being held open for a transaction?
Is that the database or Lucee CPU spiking?
Have your tried looking at the state of Lucee running threads using jcmd #PID# Thread.print when things hang, specifically the thread doing this batch ?
You can try my performance analyzer extension for the admin, which has a the same thread report , if you have the admin enabled etc
You don’t have Lucee debugging enabled do you? What is the Lucee memory usage like?
Yes, but the problem began prior to updating. I updated the extension in hopes it would fix this problem.
Indeed, I’ve read that, too. It’s as if the database driver is not finalizing the transaction, though that seems unlikely. Each query is executed in it’s own cfquery tag within a loop in a cfthread.
The database CPU spikes once the query gets stuck.
No, debugging is not enabled.
For performance analysis, we monitor the DB and application with zabbix and datadog. I will dig up some of the relevant graphs from there.
We keep the lucee admin turned off in production for security purposes, but I might turn it on long enough to gather some metrics from your performance analyzer.
