Misc Matt | 16 Apr 2009 11:16 am
Manually Purge ColdFusion Client Sessions in Database
ColdFusion will purge your client sessions for you on a schedule. This schedule is dependent on when the CF engine starts. If your purge schedule is OK now, a CF reboot will set a new schedule and cause problems. if you use a database and your client variables table has hundred of thousands or millions of records, this can really kill performance.
I have disabled purge of client sessions in the CF Admin and do my purge during low load hours using a separate schedule.
I run 4 queries in a bash script. You can write a CF script to run these in windows or any other scripting language that let’s you connect to your client variable database.
Here’s the MySQL versions of the queries:
delete from CDATA where CFID in (select CFID from CGLOBAL where CGLOBAL.lvisit < date_sub(now(), interval 60 day));
delete from CGLOBAL where lvisit < date_sub(now(), interval 60 day);
optimize table CDATA;
optimize table CGLOBAL;
Optimizing your tables after the purge will clean up the empty space in your db file left now that those records are gone. This is helpful if you delete thousands of records at a time.
on 12 Jun 2009 at %I:%M%p 1.Chad said …
Great tip!
How were you able to disable the purge? I was on the phone with an adobe tech and they couldn’t even tell me how to do it. According to this, you can’t set it to 0 or CF will try to purge every millisecond. I tried to up it to just once a month, but I looked in the logs and CF read the value as 0 and went back to the default of 1 hour, 7 minutes.
on 12 Jun 2009 at %I:%M%p 2.Matt said …
Hey Chad,
First of all, I should have mentioned I was doing this in CF8. I think CF7 was setup similarly but can’t remember for sure.
On the Server Settings, Client Variables page just click on the same of your selected default storage mechanism for client sessions. In the screen that comes up with setting for that storage mechanism, there should be a checkbox set by default that says “Purge data for clients that remain unvisited for 90 days”. Just uncheck that box.
As far as I know, the only way to turn this off is to uncheck that box for your default selection. Even if you never actually use that default and have different storage mechanisms per application you still need to uncheck that default one in cf admin.
Also I have seen cf magically turn this back on after a hotfix so keep that in mind.
If that’s not working for you let me know we can dig into some raw config files.
-Matt
on 12 Jun 2009 at %I:%M%p 3.Chad said …
Thanks Matt. We’re on CF8. Didn’t dawn on me to click on the datasource for further options.