MySQL inexplicably pegs with Coldfusion

SYMPTOMS
Using Coldfusion, MySQL uses 100% CPU on consistant intervals. Most likely around 1 hour and 7 minutes.
Not caused by too many connections or memory limits.

THE PROBLEM
Coldfusion’s administrator runs automatic processes via a method called CFHTTP. It is used to invoke Scheduled Tasks and other things like the client stores purging. In this case, client stores purge settings are by default set to start removing client variables older than 90 days. Once it detects that it has been 90 days, every hour and seven minutes it will execute a call via CFHTTP to purge data from MySQL (or your DBMS).

This process works great unless the URL being called by CFHTTP is a secure address (https) and the server owning the URL has a self signed certificate or the certificate of authority does not exist in the cacerts file in CFMX. In order to connect to SSL enabled resources via tags like CFHTTP, CFINVOKE, and CFLDAP, you must import SSL certificates into the JVM keystore used by the JVM under ColdFusion MX.

Because my server has a self signed SSL Cert, CFADMIN's call to purge client stores via CFHTTP was failing. This is where I was seeing MySQL run up the CPU. CFADMIN was executing the CFHTTP method that ran a MySQL data purge routine on the CLIENT VAR tables CDATA and CGLOBAL. Because my local server's SSL cert was not identified to Coldfusion, it was erroring out but not timing out the connections to MySQL.

PROPOSED SOLUTIONS
There are four known ways to fix this problem.

1. Configure SSL for CFADMIN.
Click here to see how
2. Remove CFADMIN from under https.
3. Turn off all automatic processing. That includes turning off clients stores. (See below)
4. Check for SSL Certificate install errors on the server being called. (Most common problem)

Stopping Client Variable Purging
Do:
1. Client Variables
2. Click on the datasource you are using to store client vars in
3. Uncheck the box that says "Purge data for clients that remain unvisited for [box] days."

As I get more information, I will post it or modify what I have previously written.

Good luck!

Steve H.

www.fusecast.com
Web hosting, design and development services.