Trim Rollback

Squiz Matrix Support
1

Hey Squiz Guys…


Just wondering what is the best way to trim the rollback data in the databases?



Do not want to purge all records, but want to trim the tables as things are starting to slow down again…

2

You use the rollback_management.php script in the /scripts folder. Check the Server Administration manual for more details.

3

[quote]You use the rollback_management.php script in the /scripts folder. Check the Server Administration manual for more details.
[right][post=“13255”]<{POST_SNAPBACK}>[/post][/right][/quote]

Just wanted to confirm that it will actually trim the rollback tables if run using say 1 week option etc.

4

[quote]Just wanted to confirm that it will actually trim the rollback tables if run using say 1 week option etc.
[right][post=“13256”]<{POST_SNAPBACK}>[/post][/right][/quote]



Oh, yeah. :slight_smile: You should run it twice: Once for the database and again to remove old file versions. Though, a week is pretty short – we usually recommend a 6 month rollback period (or on major version upgrade essentially).

5

[quote]Oh, yeah. :slight_smile: You should run it twice: Once for the database and again to remove old file versions. Though, a week is pretty short – we usually recommend a 6 month rollback period (or on major version upgrade essentially).
[right][post=“13258”]<{POST_SNAPBACK}>[/post][/right][/quote]

Would love to have more than 6 months of data in the Database before it starts slowing down hehehehe :smiley:

6

[quote]Would love to have more than 6 months of data in the Database before it starts slowing down hehehehe  :smiley:
[right][post=“13259”]<{POST_SNAPBACK}>[/post][/right][/quote]

Just off the topic a little, when the view cron details screen times out, stopping you from being able to remove once off cron items… is there another way to remove them??

7

[quote]Would love to have more than 6 months of data in the Database before it starts slowing down hehehehe  :smiley:
[right][post=“13259”]<{POST_SNAPBACK}>[/post][/right][/quote]



A standard system should be able to handle at least a year’s worth of rollback without significant performance issues (assuming standard editing patterns, of course). I have some systems with over 2 years of rollback data. How many rollback entries do you have (sq_rb_ast and sq_rb_ast_attr_val in particular)?


[quote]Just off the topic a little, when the view cron details screen times out, stopping you from being able to remove once off cron items… is there another way to remove them??

[right][post=“13260”]<{POST_SNAPBACK}>[/post][/right][/quote]



You can remove them from the asset’s details screen (future status cron jobs for a particular asset are listed on that asset’s Details screen). We have also paginated the Cron Manager’s screen in the development version, so this shouldn’t be an issue from v3.12.

8

Excellent about the pagination in the cron details screen, that would defaintely be of use…


as far as rolback entries

    sq_rb_ast 	 37479
    sq_rb_ast_attr_uniq_val  40
    sq_rb_ast_attr_val  58277
    sq_rb_ast_edit_access  2
    sq_rb_ast_lnk 	 58593
    sq_rb_ast_lnk_tree  24520
    sq_rb_ast_lookup  68100
    sq_rb_ast_lookup_remap  1173
    sq_rb_ast_lookup_value  3818
    sq_rb_ast_mdata 	 2237
    sq_rb_ast_mdata_dflt_val	347
    sq_rb_ast_mdata_val  1199
    sq_rb_ast_path 	 6551
    sq_rb_ast_perm 	 63669
    sq_rb_ast_role 	 0
    sq_rb_ast_url 	 86
    sq_rb_ast_wflow 	 213
    sq_rb_cal_date_val  3
    sq_rb_shdw_ast_lnk  2
    sq_rb_trig 	 40


System is combination of live sites, and sites being migrated built...

but because live sites are there, rollback needs to be on
9

[quote]as far as rolback entries
[right][post=“13264”]<{POST_SNAPBACK}>[/post][/right][/quote]



Hmm… Those are pretty standard rollback numbers. I have seen systems with hundreds of thousands of rows in sq_rb_ast_attr_val, though – but I’m not sure how their server hardware compares to the stuff running your system. Could you contact me via email so we can look into this further and see if we’re comparing apples with apples?

10

Is there any way rollback can be configured to keep certain major and not minor versions - everytime a page is made live rather than the stages of workflow i.e.
keep:

0.1.0

0.2.0

0.3.0

1.1.0

1.2.0



but discard:

0.1.1

0.1.2

0.2.2



etc.?



Thanks,

Sarah

11

No, rollback works as database triggers, so there is no way to filter which versions it stores.
12

Thanks Avi,

Is it possible to remove only so many days/weeks worth of versions?
13

Maybe the script that trims stuff could trim everything but major versions?

14

The rollback_management.php script can trim rollback based on time periods, yes. We usually recommend keeping 3 to 6 months in the live database. The script can also purge old file versions.

[quote]Maybe the script that trims stuff could trim everything but major versions?[/quote]

This would leave the system in an inconsistent state -- actually going into rollback would likely make the whole thing fall into a miserable heap.