[Resolved] Whole Layouts module not working after update (really slow / timeouts)

[Author]

Posts

[michaelB-31]

Hi support,

Important: I have a strong feeling, that this issue is related to my other ticket: https://toolset.com/forums/topic/performance-problem-with-large-tables-legacy-post-relationships/

As in the other ticket explained, I wanted to run the relationships migration. But I can't start the update process, because the "Custom legacy code" check keeps loading forever (see screenshot 1). But I think, we have to fix this issue first.

Some further context and information: The live website runs on a really old version: WordPress 4.9.18 with Toolset Layouts 2.2, Toolset Types 2.2.21, Toolset Views 2.5.2. But everything runs fine here.

Now I am finally upgrading and testing everything on a new staging server. And now after updating to the newest Toolset versions, the whole Layouts module is not working anymore. That means:
1. If I edit a page and then click on "Edit with Layouts", I will most of the time get a 504 Gateway Timeout error. On some pages the edit page itself can't load and gets the 504 error. Sometimes when it does load, there is one SQL query which takes a lot of time and probably causes the timeout (see screenshot 2).
2. If I go to "Toolset - Layouts", most of the time I only get an empty page. And when it does load, it created an abnormally large SQL "IN" query (see screenshot 3). So quite a similar problem.

I have 2 guesses:
1. After the update the Toolset Layouts plugin generates some really performance intensive queries, which are too much for my big database (see my other ticket) and makes it unusable.
2. My layouts are somehow not correctly updated or missing some information.

But basically I have no idea and really need help.

I also assume the "Custom legacy code" check for the relationships update process has a similar problem that simply some query has a time out while checking the layouts. I don't know, one problem at a time, but at least I wanted to mention it 🙂

Thanks in advance and best regards,
Michael

[Nigel]

Hi Michael

To be able to help here I think I'll likely need to get my hands dirty (or dirty someone else's hands) with a copy of your site that we can install locally for testing.

Would that be okay?

Rather than have you upload a duplicate somewhere (it is going to be fairly large), I could create the duplicate from your staging site myself and download the duplicate directly from there.

Let me mark your next reply as private so that I can get log-in credentials from you—you may want to create a temporary admin user for me to use that you can later delete. And be sure to have a current backup of your site.

[michaelB-31]

Hi Nigel,

It is a staging site, so I could give you access (and I will, if you can't reproduce it locally 😉 ). But I thought it would be easier, if I directly give you the backup, because it is an unusual setup. Just some notes:

1. I didn't include the "uploads" folder, because that would be about 40GB. Also: I changed the default "uploads" folder to "belege", and this folder is in the main folder, not within "wp-contents" (see wp-config.php: "define('UPLOADS', 'belege');").

2. If you test an SQL query make sure that the query cache in your MySQL database is deactivated. If the query is cached it obviously runs very fast the second time. But caching is not realistic in this case. First, the database cache is not enough for hundreds and thousands of users. And second, the view/SQL query changes basically every time the user reloads the page, because he enters new entries on the same page.

Here now the link to the backup (files and database, not a Duplicator package, because the Duplicator plugin has some issues with databases this big): hidden link

And of course, if you have any questions, let me know.

Thanks a lot and best regards,
Michael

[Nigel]

I get an "Incorrect format parameter" error every time I try to import the SQL file using phpMyAdmin.

Perhaps you could try the All in One WP Migration plugin and backup only the database. I've found that to be reliable even with large sites, plus it will automatically handle replacing the URLs.

[michaelB-31]

I'm pretty sure the problem is not the SQL file, even though the error message kind of implies that. If you google for this error, it is always because of upload limits or timeouts (that's also the reason why the backup/export via plugins take forever to export this 685MB database via PHP).

You should be able to import it directly via the command line with this command (see for example here: hidden link ):
mysql -u database_username -p database_name < file.sql

Anyway, I also created a database only backup with All in One WP Migration plugin. You can download it here: hidden link

And I forgot: Could you please after you restored the site go to "MemberPress - Settings - Emails" and disable all Member Notices emails? So nobody accidently receives an email from your testing site.

[Nigel]

I'd already deleted the SQL file, so I downloaded the All in One WP Migration backup, and although it took quite a long time I'm pleased to say that it has imported.

No need to worry about unintended emails, I'll get to testing the issue reported with just the Toolset plugins installed.

[Nigel]

Firstly, regarding the post relationships migration.

On the locally installed copy of your site, I was unable to complete the migration.

I was able to bypass the problem of the custom code check using a filter, by adding this to my theme's functions.php:

add_filter( 'types_skip_m2m_migration_legacy_code_scan', '__return_true' );

The migration then commenced, and progressed well for some time (I wasn't keeping an eye on the clock, it was more than 30 mins but less than an hour).

The migration occurs in batches of 500 at a time, to avoid server timeouts, and it got up to close to 300 batches when the process stopped, with a message saying to share details with support. But there weren't any errors to account for why it stopped. I tried twice and got similar results.

I discussed with the Types developer, and they said to escalate so that they can try and get to the bottom of the problem themselves (they will look this week). So I've done that, and I'll share their feedback when I have it.

I think migrating the relationships should help your other ticket, about slow Views queries. The Types 2 relationships store the relationship data in wp_postmeta, which is notoriously slow when used to filter posts, and which was one of the rationales for switching to custom database tables in Types 3.

Regarding the Layouts problem.

Here I'm not so sure I can help very much.

On my local site, with just Types, Views and Layouts active (current versions), I was able to list the Layouts at Toolset > Layouts and edit individual Layouts, but it was very slow, because of the slow queries stemming from Layouts identified by Query Monitor on those pages.

When I reverted to the older plugin versions you have in production, I was able to edit the same pages, and while it was still fairly slow, it was certainly not as slow as with the current plugin versions.

The slowness appears to arise from the use of the IN clause in SQL queries on those pages which include a very long list of post IDs, and queries with the IN clause can be relatively inefficient.

I think the changes were probably introduced in Layouts 2.4, back in 2018 (see the changelog at https://toolset.com/download/toolset-layouts/#changelog).

In any case, the changes themselves are problematic in the context of very large sites such as yours.

The relationship migration won't help with this problem, it is specifically a Layouts issue.

And the difficulty here is that Layouts is a legacy plugin that is not under active development, solely receiving bug fixes.

We could argue about whether this is a bug or something else (e.g. a performance issue), but it won't help you with your site update, given that even if the developers agreed to look into this issue it would be a low priority and probably would not receive any attention for some time.

You could avoid the issue by not updating your plugins ("if it ain't broke, don't fix it"), except you have the problem with the time taken for Views queries, which should benefit from updating the plugins and migrating the relationship data.

I think you have two options. One is to update the other plugins, but not Layouts (not as far as version 2.4 anyway). But until you do, I can't say whether that will introduce some internal compatibility issues between Layouts and the other plugins or not.

The second and best option—though it will involve some more work—would be to take the opportunity to drop the requirement for Layouts, and use something else (e.g. Toolset Blocks) for your templating needs.

Anyway, I'm escalating this while I await feedback about the Types migration process.

[michaelB-31]

"You could avoid the issue by not updating your plugins ("if it ain't broke, don't fix it"), except you have the problem with the time taken for Views queries, which should benefit from updating the plugins and migrating the relationship data."

A classic catch-22 I think 😉

But I totally understand it. At first I surely try it with an older Layouts version. But you're right, I think it makes sense to do the work and switch to the new Toolset Blocks plugin.

So yes, I'm waiting for the feedback regarding the relationships upgrade.

[Nigel]

Hello again Michael

Just to keep you up-to-date.

When the developer installed the copy of your site locally they were able to run and complete the migration, so it seems somewhat server dependent.

I installed a copy of your site on an online server—ostensibly for testing by the developer—but when I ran the migration it also completed successfully.

However, there are actually 2 migrations that should complete, the first from Types 2 to Types 3 relationships, and the second from Types 3 to Types 3.3 relationships.

It didn't complete the second migration (stuck on 81%), so I'm installing a copy of that partially migrated site back to my localhost to see if I can complete the migration there. (They are taking hours, so it will be a while before I know.)

What I'm aiming for is to be able to complete the migrations for you, and then pass an export of the migrated database back to you to install.

It seems to be something of an edge case (still no error reports) that is server dependent, so any solution in the plugin code is likely to be a long way off, and performing the migrations for you seems like the most effective way to proceed.

[Nigel]

Hi Michael

I managed to complete the double migration after some time.

I've exported the affected tables (wp_options plus the Toolset relationship tables), and you can download a zip of that here: hidden link

Can you try importing that into a staging site to test and report back on whether you are successful?

[michaelB-31]

Hi Nigel,

Thanks again for your extensive help. I have to do the migration by myself, because the live site is still getting new entries. And I will do several test runs, until I finally upgrade the live site (as it will require a longer down time, I'm also moving to a different server at the same time).

But fortunately with the provided filter that bypasses the code checking, I could do both migrations (the staging site is running on a Linode virtual server, so server limitations shouldn't be a problem). The first one did take about under an hour, the second one I'm not exactly sure, at least over 2 hours, maybe even up to 3. But anyway, if it worked once, it should work again.

And for the second problem regarding the layouts module you gave me a workaround. So yes, this ticket can be closed.

Thanks again and best regards,
Michael