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
WHERE CONCAT_WS('_', RecordID, Version) NOT IN
(SELECT CONCAT_WS('_', id, Version) AS combined_id FROM SiteTree)

 

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:

  • SiteTree
  • SiteTree_Live
  • SiteTree_Versions

We then do an 'Optimize' on SiteTree_Versions and it is now from 15MB down to 681 KBytes.  Huzzah.