Database Size Management Tips
Overview
The overall size of your Cascade CMS database is dependent on a number of factors. Listed below are some of the more common culprits that can greatly affect the size of your database over time.
Large file uploads
Since everything managed in the CMS is stored in the database itself, it is fairly common for a large percentage of storage to be used up by file content. To get a better sense as to whether files may be requiring large amounts of your database storage, check out this article for a SQL query which will give you an overview all file assets in your instance.
Ultimately, if you find that there are some extremely large files that would best be handled externally (on a media server, for example), the query results will allow for you to track them down in the application and potentially remove them as needed. It is also possible to delete old versions of files from within the interface which can also help to free up storage.
Default Max Upload setting
The Default Max Upload setting is located within the Content Preferences. This setting controls the maximum size of files that users can upload into the application. Since everything managed in the CMS is stored in the database itself, it is important to consider what should be a good limit for file uploads based on how your organization uses the application.
For example, a reasonable setting might be around 20 megabytes for most organizations. This would allow for users to upload small media files (like hi-res images, videos and PDFs), but would prevent them from unknowingly (or knowingly) uploading file assets over 50+ megabytes in size.
Reducing the value for this setting will not retroactively affect any files that were uploaded prior to that modification.
Max Asset Versions setting
The Max Asset Versions setting is also located within the Content Preferences. This particular setting is important to consider especially in conjunction with the Default Max Upload setting described above. Consider an environment with the following settings:
- Default Max Upload: 25000 KB
- Max Asset Versions: 50
With a max upload set to 25000 KB as seen above, a user is able to upload a file that is approximately 25 MB in size. This will immediately consume 25 MB of storage at the database level.
Now we need to take into account what happens each time this particular file is edited. With any edit/submit of a File asset in the CMS, a version of that File is stored in the database in a version chain that is connected to the current version of the File. Therefore, any version of this File stored in the database will also require approximately 25 MB of additional storage.
With a Max Asset Versions setting of 50, users in the system could potentially end up editing this particular file up to 49 times before the system begins to trim older versions (to keep the version chain at a maximum of 50). Since each edit of this file stores roughly 25 MB of content for the resulting version, the end result is that what was originally a 25 MB file now requires 1,250 MB (or 1.25 GB!) in order to store all of the related versions.
We typically recommend that this particular setting be configured to somewhere between 20-30, although certain organizations may require that many more past versions be retained in the system.
If you plan on reducing the number of versions available for assets in your environment, be sure to see this article for details on how changing this setting affects existing version chains in the system.
High numbers of read/unread messages
Over time, users across the system tend to amass a lot of notifications (both messages that have been read along with messages that have gone unread). Many of these notifications come from automated tasks such as publish jobs and workflows, so it is easy for users to overlook ever discarding them manually.
It is possible to remove read, unread, and broadcast messages from the system by utilizing the option to Remove notifications and expired broadcast messages from within the Optimize Database tool. Depending on how large your cxml_mail table is to begin with, this operation can help to clear those items and potentially free up some additional storage space in your database.
Database vendor configuration
The scenarios and settings listed above are specific to Cascade CMS and how data is stored in the database. While the tips mentioned can potentially help to alleviate database storage issues (immediately or over time), keep in mind that there are settings specific to certain database vendors that can affect whether or not (and how much) space can be reclaimed after any of these steps are taken. Those settings are outside the scope of the application and should be handled by your organization's database administrator.