Adobe Campaign provides a built-in workflow which lets you schedule certain database maintenance tasks: the database cleanup workflow. This workflow carries out the following tasks:
Please note that the cleanup task deals mostly with application level maintenance, not with RDBMS level maintenance (with the exception of statistics update). However, maintenance operations will be required on the database. Even if the database cleanup workflow runs successfully, this does not mean that the database is optimally tuned.
The database cleanup workflow does not include any database maintenance tool: it is up to you to organize maintenance. To do this, you can either:
These maintenance procedures must be carried out on a regular basis and should include the following:
You need to find the appropriate slots for performing these maintenance activities. They can heavily impact the database performance while running or even block the application (due to locking).
These tasks are typically run once a week during a period of low activity that does not collide with backups, data reloading or aggregate calculation. Some systems which are highly solicited require more frequent maintenance.
More in-depth maintenance, such as full table rebuilds, can be performed once a month, preferably with applications fully stopped since the system is unusable anyway.
Several strategies are available:
| Online defragmentation
|| Most database engines provide defragmentation methods.
|| Simply use the database defragmentation method. These methods typically take care of integrity issues by locking the data during defragmentation.
|| Depending on the database, these defragmentation methods can be provided as an RDBMS option (Oracle) and aren't always the most efficient way of dealing with larger tables.
| Dump and restore
|| Dump the table to a file, delete the table in the database and restore from the dump.
|| This is the easiest way to defragment a table. Also the only solution when the database is almost full.
|| Since the table is deleted and recreated, the application cannot be left online, even in read only mode (the table is not available during the restore phase).
| Duplicate, rename and drop
|| This creates a copy of a table and its indexes, then drops the existing one and renames the copy to take its place.
|| This method is faster than the first approach since it generates less IOs (no copy as a file and read from this file).
|| Requires twice the amount of space.
All active processes writing to the table during the process must be stopped. However, reading processes will not be affected, since the table is swapped at the last moment once rebuilt.