Tuesday, October 5, 2010

DB Clean | performance of overall site

DB Clean:
In my opinion next to Dynacache\EdgeCaching strategies for a site. I would consider DB Clean a very important aspect of improving site performance for WCS. It would also prevent any junk car issues.

Out of the box, commerce provides a table for adding these queries CLEANCONF table, which is good enough for most basic queries for complex cleanup operations, it is very important to use store procedures.

High Level commerce deletes required for
1. Address
2. CtxMgmt
3. Guest users
4. Orders
5. Schedulers
6. Staglog
7. CustomData

e.g.
insert into cleanconf (OBJECTNAME, TYPE, STATEMENT, NAMEARG, SEQUENCE, DAYSARG)
values (
'activity', 'remove-obsolete-ctxmgmt',
'delete from ctxmgmt where lastaccesstime < (sysdate - ?)',
'no', 1, 'yes');

Crontab utility in unix/solaris can be used to schedule these jobs. Usually it is very important to wrap this in a shell script and use some logging and also capture the PID, if you need to kill a process.

2 comments:

  1. Hi Raj,
    I am looking for a way to schedule the db clean utility using the WebSphere scheduler. Can you tell me is there any way to do that or do I have to stick with the shell script and crontab method.

    ReplyDelete