Tuesday, April 3, 2007

MySQL inexplicably pegs with Coldfusion

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.

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.

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
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.

Web hosting, design and development services.


Yemi said...

Hey man,

Any idea which mysql table type is best for storing CF client variables. Our client variables tables have now grown to about 6 million rows within 2 months months and is are using MYISAM tables.

Unfortunately, the SQL statement used by CF to purge the DB is too inefficient and ends locking up, which obviously affects the site.

Just wondering if INNODB tables are better suited for this kind of job as supposed to MyISAM tables.

Thanking you in advance for your time.


Steve H. said...

I'm using INNODB tables for the CDATA and CGLOBAL tables that handle our client vars. They seem to be doing great.

Now I also tuned the Database for INNODB so I could get as much performance from those tables as possible. You can do this in the my.cnf file in the etc dir on apache.

Be sure you have good index setting per table as this allows for the CF process that purges the tables to run much faster. When I set up CF's client stores, I got the MySQL script from Adobe that sets up the tables appropriately for me. (Whew!)

That's about all I have. Good luck!

Yemi said...

Thanx for getting back to me.

Out of curiousity, which INNODB settings did you set in my.cnf.

Also, do you have any links to the Adobe MySQL scripts, best practices or any other resources on this subject that you think would be useful.

Thanks again for your time.


Anonymous said...

This is a very important issue and this slide is very helpful. I would mention that many of us readers actually are definitely blessed to live in a fantastic website with very many special  professionals with beneficial points.Web Hosting Solution