I have some coldfusion servers that are connected to an oracle database and have been having some issues sending queries to the DB from the CF servers. I must also state that both the server and db are quite busy. The issue I am having is that the same query will get processed and shortly after I would receive the aforementioned error. I've done some troubleshooting with the DBA an confirmed the issue is not coming from the DB. Further research into issue led me to make some changes on the CF servers but I am still experiencing this issue. Two actions I normally execute provides a temporary solution to the problem:
1. disable all the db connections and then re-enable them.
2. Restart the nodes with the datasource connections.
The server settings I change are as follows:
1. Increase the "Max Pooled Statements" from 100 to 500.
2. Enable "Limit Connections"
3. "Restrict connections to" 48 (Simultaneous Requests 16 * 4 nodes) not sure if this is the right approach.
I am not sure what else to do at this moment. I have attached some to photos of the CF server node stats and db connection settings. The settings are mirrored on the other servers.
10
Replies
Thanks for reaching BKBK and my apologies for the delayed response!
Here's the info you requested:
ColdFusion version and update level? Current Update Level is 18
What is the Oracle driver version?
I'm thinking this is the version? |Oracle|6.0.0.1408
CF Ora Package:
number of unique cfquery tags that use cfqueryparam: 318
SQL QUERY:
select to_char(evtime, 'mm/dd/yyyy hh24:mi') AS evtime, SUM(count_) AS tcnt
from traffic_stats
WHERE evtime >= (param 1)
and evtime >= (param 2)
and evtime >= (param 3)
and node = (param 4)
and type = (param 5)
group by evtime
order by evtime
params for the query:
(param 1) = [type='IN', class='java.sql.Timestamp', value='2025-05-12 05:16:00.0', sqltype='cf_sql_timestamp'] ,
(param 2) = [type='IN', class='java.sql.Timestamp', value='2025-04-08 03:21:52.0', sqltype='cf_sql_timestamp'] ,
(param 3) = [type='IN', class='java.sql.Timestamp', value='2025-05-28 12:21:56.189', sqltype='cf_sql_timestamp'] ,
(param 4) = [type='IN', class='java.lang.String', value='908086', sqltype='cf_sql_varchar'] ,
(param 5) = [type='IN', class='java.lang.String', value='LTE', sqltype='cf_sql_varchar']
STACKTRACE:
I'm about to uncheck the "Limit Connections" checkbox and delete the 48 from "Restrict connections to"; Then restart the nodes.
For the "Enable connection validation", will this query run each time the coldfusion sends a query or each "Interval (min)"? which is currently 7 on my server settings.
By @teno_chin
No, the validation query does not run each time ColdFusion sends a query to the database. The validation query is a test for the database connections in the connection pool. It checks if a pooled database connection ColdFusion is about to use is still alive.
Using the validation query is optional. I suggest it because I think it is necessary in your situation.
The issues in your application seem to be related to the database connection pool. The validation query ensures the connection to be used is still valid and alive, not stale or broken.
In any case, the validation query is very lightweight. It should normally run in a few milliseconds. With a maximum of 64 connections in the pool, the total performance cost of the validation query should be well below 1 second. Check it:
<cfquery name="q" datasource="your_datasource_name" result="QResult">
select 1 from dual
</cfquery>
The query ran in <cfoutput>#QResult.executionTime#</cfoutput> milliseconds
I strongly recommend an upgrade to ColdFusion 2021 Update 20. After you do, you should indeed delete the contents of felix-cacheI, and then restart ColdFusion.
Thank you! To provide an update, after I implemented the suggested changes my app ran with low errors (usually happens when I restart the server) for a few hours then the errors increase over time as per usual behavior. The only thing I did different, which was strongly against my will, is to uncheck "Maintain Connections". After doing that and restarting the server, the errors went away but subsequently increased the number of my DB's session states "ON CPU" which was expected. It went up from an avg 70% to 80%. The good thing is that I am getting a new soon so I will continute to monitor the performance. If this holds for another 24hrs. I will consider the issue resolved. Here's what the error trends look like now.
Thanks again for your support! I really appreciate it. I definitely have to upgrade to 20 now that I have IT sec advising me of my server vulnerabilities.
AdChoices
