search

silverstripe / silverstripe-versioned

Versioning for Silverstripe model
BSD 3-Clause "New" or "Revised" License
8 stars 35 forks source link

Large changeset tables cause website stability issues #334

Open chillu opened 3 years ago

chillu commented 3 years ago

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 commented 3 years ago

Publish recursive uses change set internally. https://github.com/silverstripe/silverstripe-versioned/blob/165913a80a72961bf964e3916a1b77349c7a1d11/src/RecursivePublishable.php#L76-L88

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". https://github.com/silverstripe/silverstripe-versioned/blob/165913a80a72961bf964e3916a1b77349c7a1d11/src/ChangeSet.php#L431-L443

kinglozzer commented 3 years ago

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: https://github.com/silverstripe/silverstripe-framework/issues/8401#issuecomment-442508310. 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 commented 3 years ago

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: https://github.com/silverstripe/silverstripe-framework/issues/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".