So I've had a great deal of experience watching Coldfusion MX servers cripple themselves during the "client data storage purge" which happens on a defined interval via the CFAdmin. I've seen this only be a problem with using a MySQL 4.1 database with MX 6.X through 7.X.

After shelling out the $500 single support incident with numerous thread dumps via the kill 3 on the root cfmx pid and no real resolution - I decided to do the purge manually.

I came up with the following query to resolve this issue being run via a crontab on a set interval to destroy client data that is at least 45 min old:

New version for MySQL5+

LOCK TABLES CGLOBAL AS g WRITE, CDATA AS d WRITE;
delete g, d from CGLOBAL g LEFT JOIN CDATA d ON d.cfid=g.cfid
	where DATE_ADD(g.lvisit, INTERVAL 45 MINUTE) < now();
UNLOCK TABLES;

Old version for MySQL4

LOCK TABLES CGLOBAL AS g WRITE, CDATA AS d WRITE;
delete g, d from CGLOBAL g, CDATA d
	where g.cfid=d.cfid and DATE_ADD(g.lvisit, INTERVAL 45 MINUTE) < now();
UNLOCK TABLES;