A personal repository of random information in compensation for a fatigued biological computer
Breaded IT » Web Development » SilverStripe CMS pre V3.0 » HouseKeeping » Silverstripe 2.2.x DB Cleanup
I had been concerned about this issue as some of my pages are large (tables etc) and if I edit 1 typo it must start to impact on DB size.
Using the GODADDY Database manager, I can see that the only tables >= 10kb are:
Table |
Records |
Size kbytes (20090302) |
File |
56 |
19.1 |
PageView |
6,783 | 779.4 |
SiteTree | 157 | 712 |
SiteTree_Live | 154 | 711.3 |
SiteTree_Versions | 2,392 | 15.4MB |
So, it is SiteTree_Versions that is the culprit, and fair enough since the current SiteTree & SiteTree_live is 711 kBytes with just the active tree.
So, we backup the db using Godaddys backup tool (Hosting Control Center -> Databases -> Pencil icon next to target DB -> Backup, wow that produced a 14mb sql file (ENORMOUS)
Browsing SiteTree shows that it contains each pages content as well, hence the large size.
So what is PageView then: Oh, its a visitors log, cool.
From: http://silverstripe.org/general-questions/show/251293#post251293
SiteTree_Versions: |
DELETE FROM SiteTree_versions |
Pages: |
DELETE FROM `PageView` WHERE ID < 225745 |
Hmm I'm intuitively concerned that the SiteTree delete above doesn't check sitetree live as well as sitetree? I have no pages/changes that are not published yet I have a different number of records between SiteTree & SiteTree_Live which is probably cause for concern as well, we investigate this issue here.
Ok so we are happy with the above script and after running we now have 154 records in each of:
We then do an 'Optimize' on SiteTree_Versions and it is now from 15MB down to 681 KBytes. Huzzah.