Tuesday, April 3, 2007

MySQL and Coldfusion Isolation Types

I was working a locking and speed problem with our MySQL Servers today and found something interesting you may want to have a look at.

MySQL defaults its tables to MyISAM. In order to create Foreign Key relationships, the tables must be changed to InnoDB. The problem with InnoDB is that by default the isolation level for the tables are "REPEATABLE-READ".

In reading the documentation for CF on cftransaction tags, adobe says that "REPEATABLE-READ" is the same as read-committed, except that rows in the recordset are exclusively locked until the transaction completes. Due to high overhead, Macromedia does not recommend this isolation level for normal database access..

The recommended isolation level is "READ-COMMITTED".

Now you can go through ever query statement and encase each with a cftransaction tag specifying the isolation type OR you can set it as the general isolation type for MySQL.

This is how to change the default isolation level for MySQL. It's simple and fast and requires a quick MySQL restart.

1. Run the following SQL to see what the default Isolation level was for the DB.
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

If you got back anything but "READ-COMMITTED", continue on to step 2.

2. I created a text file called my.cnf.
File contents
transaction-isolation = READ-COMMITTED
Note: Do not use the dashes or "File contents". Just the portion between the "=" signs. If you have a my.cnf, just add "transaction-isolation = READ-COMMITTED" to it.

3. Uploaded the file (my.cnf) to /etc/ on the server (Linux)
4. Restart MySQL /etc/init.d/mysql restart
5. Re-run the following SQL
SELECT @@global.tx_isolation ;
SELECT @@tx_isolation;

You should get back "READ-COMMITTED"

Boom! Game on! Now your servers will be running what Adobe recommends for CF.

When I did this, immediately I saw huge performance gains from my production box!

Hope this helps!

Steve H.
Web hosting, design and development services.

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.