How to reduce database size?

Question

I'm getting alarmed by the size of the database backups and the database in general. The site has Craft Commerce installed, which contributes I think, but if you look at the following list of backups there seems to have been a sharp increase in database size that has nothing to do with Craft Commerce (which has been installed since July).

Does anyone have an tips or articles to share about getting control of this? I feel it's getting ridiculous but that it has probably happened since we went live on October 17.

EDIT:

Looking at the database tables, there are definitely several tables that seem to larger than they should be:

1. craft_searchindex 31MiB
2. craft_commerce_orders 30MiB
3. craft_elements_i18n 20MiB
4. craft_content 15MiB craft_elements 13MiB
5. craft_commerce_customers 8MiB
6. craft_entryversions 3MiB
7. craft_deprecationerrors 1.5MiB

I'm wondering if there is a spam issue here. Could that be it?

Answer 1

So two primary things can contribute to database bloat:

• If you have versioning turned on for your Sections, it will store every change in the db. The Delete Entry Versions plugin can help with this
• If your backup isn't dropping temporary cache tables like templatecaches, templatecachecriteria, etc. that can contribute to the bloat as well

Given that you're seeing the backups get so much larger in a one week period makes me think it's probably the temporary cache tables causing the bloat, unless you're doing an awful lot of editing.

It's unclear how you are doing your backups, but check out the Mitigating Disaster via Website Backups; the Craft-Scripts used therein will not include the various temporary tables in the database backups.

It's also possible that too many cache tables are being created due to the way you're using the {% cache %} tags on the site; check out The Craft {% cache %} Tag In-Depth for details on what might be happening.

An easy way to check what's going on would be to connect to the database with something like Sequel Pro or what have you, and look at the number and size of the db rows in the various tables.

Answer 2

Well, I started by removing unwanted orders over a month old and that's got the commerce_orders table down to 10 MB. Whether it's a coincidence I'm not sure, but now the searchindex and elements tables are down to 10 MB each also.