Reordering pages is still slow

[evenreven]

We were reordering a lot of pages on our production server recently (Ruby 2.4.7, Rails 5.1.7, Refinery 4-0-stable branch), and the production server crashed outright and needed to be rebooted. I can't really prove reordering was what did it, but going from the logs, it seems likely. (The reorder ended in a 500, and after that the log was full of Active Record errors until we rebooted. I couldn't even reach the server for a puma restart, had to go full reboot.)

I'm trying various ways of reordering on development now to test. My simplest test case: With a dump of my entire production db (approx 700 pages), I move one root-level page one position up. None of the pages that are affected by reordering have child pages.

Still, this takes 12603ms and writes more than 8500 lines of log entries. That's from moving one page without children.

If we can't make this less taxing on the SQL queries, I fear I need to decouple our news and staff pages into different models (probably using an engine or extension), and then use a different frontend ordering scheme for them. Which is doable, but a lot of work for what's basically a workaround - I actually prefer that the pages belong to the standard refinery page model (e.g. I reuse the view templates for non-news pages), and just reordering when needed. But if the CMS can't handle it, then it's back to the drawing board.

This is not a new issue. When we were still on Refinery 2.1.5 and using Unicorn, I had to manually raise the Unicorn timeout just for page reordering, and even then it was error-prone. I frequently had the server giving up midway through the reordering, with extremely unpredictable results. (Back then we probably had 400 pages or less.) I'm aware of this PR #2747, and I haven't had the incorrect order after failed job/timeout since then, but it's still extremely taxing and I can see a huge disk usage write spike (30x more than a normal session) on our server logs from the time it happened.

Any ideas how/if this can be made more efficient somehow?

[bricesanchez]

Hi @evenreven,

Are you aware of this changes: https://github.com/refinery/refinerycms/pull/3390/files and a1b6e8a ?

Are you using this PR on your project?

[evenreven]

Thanks for the tip. No, I don't think so. I'm using the 4-0-stable branch because I can't update to master (I have Globalize-specific code and also the page-images extension doesn't work with Mobility (or Rails 5.2 for that matter)). Right now I'm using an up to date fork of the main repo because we're overriding the page preview feature, but if you think this commit fixes the slow (not to mention taxing on the db) reordering, I'm happy to backport it to the 4-0-stable branch in my fork.

[bricesanchez]

but if you think this commit fixes the slow (not to mention taxing on the db) reordering, I'm happy to backport it to the 4-0-stable branch in my fork.

It will really helps. Also in dev env, you should activate the cache when your perform reordering : rails dev:cache