Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Simon Dingley 1470 posts 3427 karma points c-trib
    Jun 19, 2017 @ 14:05
    Simon Dingley
    0

    Performance Benefits of Reducing Version History in Umbraco

    I have a large multi-site load balanced install that I maintain and it has been an ongoing project for around 5 years now. It started out life as a v4 site (v4.8.1) and has been upgraded all the way through to v7.5.8.

    The full database backups are currently coming in at around 5GB which to me is fairly sizable but to a DBA is likely very small.

    I want to do an audit of the content revision history and try out the Unversion package as a means of keeping the history to a minimum going forward. What I would like to know is what exactly are the performance benefits of doing this given that the overall database size will not shrink as a result, in fact quite the opposite, and performing a shrink operation on the database from what I am reading is not really recommended. So, will the database and the site perform better with a more limited revision history for its content or is it just general housekeeping?

    Thanks, Simon

  • Sotiris Filippidis 286 posts 1501 karma points
    Jun 20, 2017 @ 13:41
    Sotiris Filippidis
    0

    Simon, just a question out of curiosity: Did you check whether those large sizes come from the logs and not the actual DB? If you have a Full recovery plan it's very likely that your db logs will be significantly bigger than your data file.

  • Simon Dingley 1470 posts 3427 karma points c-trib
    Jun 20, 2017 @ 13:55
    Simon Dingley
    0

    Unfortunately, in this case I do not have direct access to the SQL Server instance so would need to check with the host but I can confirm that a restored version on my local development environment has an MDF file of ~5.6GB and an LDF file of ~470MB if that answers your question?

  • Sotiris Filippidis 286 posts 1501 karma points
    Jun 20, 2017 @ 14:03
    Sotiris Filippidis
    0

    Yep, it does - I don't have an answer to your original question but it's evident that your data file occupies the most space. If it was otherwise, it could be corrected with a log file backup from your DBA, but now your original question still stands.

  • Simon Dingley 1470 posts 3427 karma points c-trib
    Jun 20, 2017 @ 14:07
    Simon Dingley
    0

    Thanks. From a conversation I had on one of the slack channels, it seems that pruning the version history might do little or nothing in terms of performance gain. Therefore, at the moment I'm still undecided if there is any benefit to it at all.

  • Jason Prothero 422 posts 1243 karma points c-trib
    Jun 20, 2017 @ 16:16
    Jason Prothero
    0

    One option is to use the F.A.L.M. package to remove old versions and see if that has an impact. You can do it via a dashboard in the back-office.

    If it does help, then adding unversion is the next step.

    Thanks, Jason

  • Nicholas Westby 2054 posts 7100 karma points c-trib
    Jun 20, 2017 @ 17:00
    Nicholas Westby
    0

    From a conversation I had on one of the slack channels, it seems that pruning the version history might do little or nothing in terms of performance gain.

    I find it's useful to keep the database size to a minimum, even if only to speed up the transfer of the database from one environment to another (e.g., if you need to test new functionality against production data). The savings can be significant. On one website, this brought the size from 5GB to 500MB (roughly... can't remember the exact numbers).

    I believe there is a ticket submitted for a feature to allow for the version history to be automatically pruned after a configurable number of revisions (say, 10).

  • Marc Goodson 2124 posts 14215 karma points MVP 8x c-trib
    Jun 20, 2017 @ 18:27
    Marc Goodson
    3

    Hi Simon

    Unversion will allow you to set rules on which doctypes need to be allowed to store how many versions, or optionally set a date eg six months after which they can be removed:

    https://our.umbraco.org/projects/website-utilities/unversion/

    In terms of assessing impact on performance, the frontend of your site will largely be served from the umbraco xml cache file, so unversioning won't improve matters here, but in my experience it will improve performance of the backoffice, largely by relieving stress on database and indexing of key tables in the Umbraco structure.

    Run the following on your db:

    Select count(*) from cmsPropertyData

    There is a row in this table for every property of every document for every version...

    Depending on the size of your database server this can choke and slow down the backoffice, when writing to and to a lesser extent reading from this table

    15million is the most I've seen...

    it's not a competition though...

    Secondly run

    SELECT count(*)
      FROM [atgmedia-paywall-dev].[dbo].[umbracoLog]
    

    The umbracoLog table contains a row for everything that ever happened in terms of publishing, saving etc, in your Umbraco site forever, and is where the 'Audit Trail' functionality of the cms is drawn from.

    Some sites, the Audit Trail is unimportant, and some sites it's a legal requirement to know who published that controversial report back in September fo last year!

    But if you have an Umbraco site that has been upgraded through the versions, then you'll also have in here 'trace log' information, in the old days, instead of logging to trace log files on disk, Umbraco would log issues to this table, so if you haven't already, you can probably safely clear out, Error, Debug, Open, Custom, System, NotFound and Notify logs...

    delete from umbracoLog where logHeader in ('Error', 'Debug', 'Open', 'Custom', 'System', 'NotFound', 'Notify')
    

    There was a release of Media Protect, (latest version is fixed, and great) that accidentally spammed this log table everytime an image was requested on the front end of the site) this caused the record rows I've seen in the log table 0f 292,572,030.

    But yes if you are experiencing slow site on the front end there are other avenues to explore first.

    regards

    Marc

  • Simon Dingley 1470 posts 3427 karma points c-trib
    Jun 21, 2017 @ 15:41
    Simon Dingley
    0

    Marc, that's brilliant - thank you. Exactly the sort of information I was looking for. Thankfully we are not experiencing significant performance issues in the front end or the back end but there is room for improvement.

Please Sign in or register to post replies

Write your reply to:

Draft