Answers.Body: <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-01-19T10:58:25.177Z
Answers.LastActivityDate: 2018-01-19T10:58:25.177Z
Answers.UserDisplayName: Clive Portman
Answers.Body: <p>So two primary things can contribute to database bloat:</p>
  
  <ul>
  <li>If you have versioning turned on for your Sections, it will store <em>every</em> change in the db. The <a href="https://github.com/carlcs/craft-deleteentryversions" rel="nofollow noreferrer">Delete Entry Versions</a> plugin can help with this</li>
  <li>If your backup isn't dropping temporary cache tables like <code>templatecaches</code>, <code>templatecachecriteria</code>, etc. that can contribute to the bloat as well</li>
  </ul>
  
  <p>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.</p>
  
  <p>It's unclear how you are doing your backups, but check out the <a href="https://nystudio107.com/blog/mitigating-disaster-via-website-backups" rel="nofollow noreferrer">Mitigating Disaster via Website Backups</a>; the <a href="https://github.com/nystudio107/craft-scripts" rel="nofollow noreferrer">Craft-Scripts</a> used therein will not include the various temporary tables in the database backups.</p>
  
  <p>It's also possible that too many cache tables are being created due to the way you're using the <code>{% cache %}</code> tags on the site; check out <a href="https://nystudio107.com/blog/the-craft-cache-tag-in-depth" rel="nofollow noreferrer">The Craft {% cache %} Tag In-Depth</a> for details on what might be happening.</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-13T15:59:43.070Z
Answers.Text: Thanks. I've actually done very little performance-based work on the site so far as we went over budget. I'm hoping to carry that out in the New Year. That means we have very few {% cache %} tags - will template caches tables still be being populated? I'm checking the tables now.
Answers.UserDisplayName: Clive Portman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-13T16:13:40.670Z
Answers.Text: Having looked at the tables, there's something not right as there are several that are getting quite large. I've updated my question to reflect that.
Answers.UserDisplayName: Clive Portman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-13T12:42:50.717Z
Answers.LastActivityDate: 2017-12-13T12:42:50.717Z
Answers.UserDisplayName: andrew.welch
Body: <p>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).</p>
  <p>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.</p>
  <p>EDIT:</p>
  <p>Looking at the database tables, there are definitely several tables that seem to larger than they should be:</p>
  <ol>
  <li>craft_searchindex 31MiB</li>
  <li>craft_commerce_orders 30MiB</li>
  <li>craft_elements_i18n 20MiB</li>
  <li>craft_content 15MiB craft_elements 13MiB</li>
  <li>craft_commerce_customers 8MiB</li>
  <li>craft_entryversions 3MiB</li>
  <li>craft_deprecationerrors 1.5MiB</li>
  </ol>
  <p>I'm wondering if there is a spam issue here. Could that be it?</p>
  <p><a href="https://i.sstatic.net/BEJpm.gif" rel="nofollow noreferrer"><img src="https://i.sstatic.net/BEJpm.gif" alt="Contents of backups" /></a></p>
  
Tags: <database>
Title: How to reduce database size?
ThreadText: # How to reduce database size?
  
  (Asked by: username_0 on 2017-12-13)
  
  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?
  
  [![Contents of backups](https://i.sstatic.net/BEJpm.gif)](https://i.sstatic.net/BEJpm.gif)
  
  **Tags:** database
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2018-01-19. Score: 1 | Accepted answer
  
  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.
  
  
  ---
  
  ### Answer by username_1 on 2017-12-13. Score: 2
  
  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](https://github.com/carlcs/craft-deleteentryversions) 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](https://nystudio107.com/blog/mitigating-disaster-via-website-backups); the [Craft-Scripts](https://github.com/nystudio107/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](https://nystudio107.com/blog/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.
  
  > **Comments:**
  > **username_0**: Thanks. I've actually done very little performance-based work on the site so far as we went over budget. I'm hoping to carry that out in the New Year. That means we have very few {% cache %} tags - will template caches tables still be being populated? I'm checking the tables now.  
  > **username_0**: Having looked at the tables, there's something not right as there are several that are getting quite large. I've updated my question to reflect that.  
  
  ---
  
  