Large changeset tables cause website stability issues

[chillu]

dev/build can run into issues on active sites where implicit ChangeSet and ChangeSetItem tables can run into tens of millions of rows. Notably this is all background data, very few customers use the "campaigns" feature directly (with ChangeSet.IsInferred=0).

Option 1: Allow disabling of changeset writing (unclear if this is possible without rearchitecting, and whether we want to support that variation)

Option 2: Garbage collection of those records

Option 3: Fix dev/build failure cases (maybe around indexes?)

[maxime-rainville]

Publish recursive uses change set internally.

silverstripe-versioned/src/RecursivePublishable.php

Lines 76 to 88 in 165913a

	$changeset = ChangeSet::create();
	$changeset->IsInferred = true;
	$changeset->Name = _t(
	__CLASS__ . '.INFERRED_TITLE',
	"Generated by publish of '{title}' at {created}",
	[
	'title' => $owner->Title,
	'created' => DBDatetime::now()->Nice()
	]
	);
	$changeset->write();
	$changeset->addObject($owner);

I tried truncating the ChangeSet table to see what happens. The CMS "Publish" action starting throwing errors in my face. After a couple "failed publish", the ChangeSet table put itself back in a state where pages could be published again. This would imply that at least the latest ChangeSet is required for doing a recursive publish ... although I can't tell you why.

Beyond that, it's not clear what the value of keeping those ChangeSet record in the database is. It looks like once upon on time, someone intended ChangeSet to be "revertable".

silverstripe-versioned/src/ChangeSet.php

Lines 431 to 443 in 165913a

	public function canRevert($member = null)
	{
	// All changes must be publishable
	foreach ($this->Changes() as $change) {
	/** @var ChangeSetItem $change */
	if (!$change->canRevert($member)) {
	return false;
	}
	}
	// Default permission
	return $this->can(__FUNCTION__, $member);
	}

[kinglozzer]

Are the dev/build issues triggered when models are added/removed? I’ve had performance issues in the past when the enum values were updated for ChangeSetItem.ObjectClass: silverstripe/silverstripe-framework#8401 (comment). Unfortunately I can’t remember which project I was referring to, or what MySQL version it was running, so I may be barking up the wrong tree here 😄

[brettt89]

From an Operations perspective
dev/build issues relating to this table that I have observed have been mostly related to Framework performing a CHECK TABLE call on these large datasets causing the majority of identified pain during dev/build (E.g. Long build times).

Reference: silverstripe/silverstripe-framework#9966

However there are other issues related to large tables / datasets (e.g. Snapshots / Backups / Database Maintenance / etc) which could also be improved through approaches such as "Garbage Collection".