search

craftcms / cms

Build bespoke content experiences with Craft.
https://craftcms.com
Other
3.28k stars 635 forks source link

Prune-revisions and structureelements #9977

Closed Geestig closed 3 years ago

Geestig commented 3 years ago

Description

On a website with around 700 entries with a lot of data there seems to be an issue when I try to prune the revisions. It shows to be pruning exactly as it should however while the process is running the structureelements table starts to increase in size. At one point this table alone was over 1.5GB which caused the DB to crash.

Even when I only prune 1 single revision and try to do it gradually it still increases too much in size. This is quite a big problem since we rely on cronjobs with feed-me feeds. Which automatically prune revisions after running the feed.

This problem seems to have started to become worse when the structure section that is being filled by feed me was changed into a channel. Perhaps there is still some remnant in de database that causes an issue with this?

EDIT: Have been able to complete a pruning. Pruned 1 revision of each entry - structureelements is now 450MB in size. And after completion did not seem to get cleaned. So those 450MB are now stuck in that table.

EDIT: Another thing I noticed is that row count in structureelements actually lowers whilst the size of the table grows. Very weird.

EDIT: Disabling Blitz does not help, running gc afterwards does not work. There are no errors in any log files. Structureelements just keeps on getting bigger.

EDIT: SELECT RELEASE_ALL_LOCKS(); does not help with the "Unable to acquire a lock for the structure 2" error i'm getting when trying to save an entry that no longer is a structure but now a channel.

Steps to reproduce

  1. Create section as structure.
  2. Populate section with 600+ entries.
  3. Change from structure to channel.
  4. Trigger prune-revisions (through CLI or Dashboard is the same result)
  5. Watch the structureelements database grow in size.

EDIT: When placing back a backup where the section is still a structure the same issue takes place. However it's only when deploying that it seems to increase the database. If i prune locally there do not seem to be any issues.

Additional info

Geestig commented 3 years ago

Locally did a correct pruning of revisions. After this I pushed the db live which seemed to have reduced the problem drastically. So my guess is that there is something wrong with entries having more revisions than the max revision count. The first feed-me run (+ search indexing, slug updating, pruning, blitz caching) added about 60MB to structureelements.

The second run added 10MB to structureelements. In total there was an increase of 20MB size in the entire DB after the second run.

brandonkelly commented 3 years ago

Not really sure what to make of this. Are you still seeing that the row count is decreasing?

Geestig commented 3 years ago

Yes, whenever I prune, the row count decreases whilst the size of the actual database increases. It's really odd. However It's much more manageable right now.

My guess is that the entries had 50 revisions (standard) and when I set the max revisions to 5 it somehow had trouble with the extra 45 revisions.

I've just checked the database. And the structureelements table has decreased in size again. I am not really sure what is triggering the decrease in size? And if there is a way to trigger it more often / manually through cronjob or anything.

angrybrad commented 3 years ago

@Geestig try running OPTIMIZE TABLE

i.e.

OPTIMIZE TABLE `structureelements`;
Geestig commented 3 years ago

Next time I run into this issue I'll definitely try. But pruning locally and overriding the remote database seems to have solved the issue for now. Thanks for the help.

Geestig commented 3 years ago

Update: Out of nowhere prune revisions came to a halt again. Running the optimize table command seems to crash the DB server.

When I prune the revisions I see certain entries have more than the max amount of revisions. So it seems the feed-me ignores the maxRevisions config.

brandonkelly commented 3 years ago

Likely related to #9999.

Geestig commented 3 years ago

I might have a theory. First the site ran fine. But I think as soon as revisions passed the maximum amount the pruning became an issue.

So if saving an entry while pruning (as stated in #9999) causes the amount of revisions to go over the maximum amount of revisions the system probably has trouble finding out what has to happen first. Does it need to prune first? Or does it need to save first making the amount of revisions higher than the maximum and then prune the one thats to much?

brandonkelly commented 3 years ago

The culprit is the sheer volume of structure operations, which can conflict with each other.

I have PR’d a fix, which should fix this by drastically reducing the amount of data that needs to be stored in the structureelements table, by removing (most) drafts and revisions from it.

https://github.com/craftcms/cms/pull/10040

If you want to help test, first make a database backup, then change your craftcms/cms requirement in composer.json to "dev-feature/destructure as 3.7.18.2", and run composer update.

Once updated, you should see an immediate improvement, however you can fully prune the unnecessary draft/revision structure data by running the following command for each of your Structure sections:

> php craft utils/repair/section-structure <sectionHandle>
Geestig commented 3 years ago

Thanks for solving the issue!

brandonkelly commented 3 years ago

Initial results are very promising, so we’ve gone ahead and merged that change in, and released Craft 3.7.19 with it 🚀

Remember, after updating you should run the following command for each of your Structure sections, to prune out any structure data that doesn’t need to be there anymore:

> php craft utils/resave/section-structure <sectionHandle>