Update issues (3.0.165 -> 3.0.257)

[joaomota]

I recently decided to update the ProcessWire version on my site. I went from version 3.0.165 to 3.0.257 (I hadn't updated it in several years). During the update, I also installed Page Paths (recommended for faster page indexing). This update caused major database issues, such as heavy load, constantly hovering between 90% and 100% and queries taking a very long time (something I thought PagePaths would resolve). We have approximately 2 million pages, each supporting both PT and EN.
For the update, I completely updated the WIRE folder (deleting the old one and adding the updated version), the index.php file, and the composer file.

Any suggestions on how to fix this?

Thanks.

Edited August 12 by joaomota

[Peter Knight]

Hi @joaomota
Could it be an initial indexing by PP?

PPaths might be building its understanding of your site, and with 2 million pages, that could be the culprit.
 

 

This is mentioned in the modules code

 

public static function getModuleInfo() {
    return array(
        'title' => 'Page Paths',
        'version' => 4,
        'summary' => "Enables page paths/urls to be queryable by selectors. 
                       Also offers potential for improved load performance. 
                       Builds an index at install (may take time on a large site).",
        'singular' => true,
        'autoload' => true,
    );
}

 

[joaomota]

Hi @Peter Knight.

Thanks for the reply.

I ran the PPaths indexing on the development server (it created about 6 million entries). Then, I copied the table to production. This should work, given that the pages on the dev server are the same as those in production. Another thing I forgot to mention is that we are using PHP version 7.4. Could that have an impact?

This is an example of a query that i got running into my DB, even after the PPaths if configured.

 

Quote

EXPLAIN
SELECT pages.id AS p0_id, pages.parent_id AS p0_parent_id, pages.templates_id AS p0_templates_id, pages.status AS p0_status, pages.name AS p0_name, pages.name1020 AS p0_name1020, pages.status1020 AS p0_status1020, p1.id AS p1_id, p1.parent_id AS p1_parent_id, p1.templates_id AS p1_templates_id, p1.status AS p1_status, p1.name AS p1_name, p1.name1020 AS p1_name1020, p1.status1020 AS p1_status1020, p2.id AS p2_id, p2.parent_id AS p2_parent_id, p2.templates_id AS p2_templates_id, p2.status AS p2_status, p2.name AS p2_name, p2.name1020 AS p2_name1020, p2.status1020 AS p2_status1020, p3.id AS p3_id, p3.parent_id AS p3_parent_id, p3.templates_id AS p3_templates_id, p3.status AS p3_status, p3.name AS p3_name, p3.name1020 AS p3_name1020, p3.status1020 AS p3_status1020, p4.id AS p4_id, p4.parent_id AS p4_parent_id, p4.templates_id AS p4_templates_id, p4.status AS p4_status, p4.name AS p4_name, p4.name1020 AS p4_name1020, p4.status1020 AS p4_status1020, p5.id AS p5_id, p5.parent_id AS p5_parent_id, p5.templates_id AS p5_templates_id, p5.status AS p5_status, p5.name AS p5_name, p5.name1020 AS p5_name1020, p5.status1020 AS p5_status1020, p6.id AS p6_id, p6.parent_id AS p6_parent_id, p6.templates_id AS p6_templates_id, p6.status AS p6_status, p6.name AS p6_name, p6.name1020 AS p6_name1020, p6.status1020 AS p6_status1020 
FROM pages 
LEFT JOIN pages AS p1 ON p1.parent_id=pages.id AND (p1.name='level-1' OR p1.name1020='level-1') 
LEFT JOIN pages AS p2 ON p2.parent_id=p1.id AND (p2.name='level-2' OR p2.name1020='level-2') 
LEFT JOIN pages AS p3 ON p3.parent_id=p2.id AND (p3.name='level-3' OR p3.name1020='level-3') 
LEFT JOIN pages AS p4 ON p4.parent_id=p3.id AND (p4.name='level-4' OR p4.name1020='level-4') 
LEFT JOIN pages AS p5 ON p5.parent_id=p4.id AND (p5.name='level-5' OR p5.name1020='level-5') 
LEFT JOIN pages AS p6 ON p6.parent_id=p5.id AND (p6.name='level-6' OR p6.name1020='level-6') 
WHERE (pages.parent_id=1 AND (pages.name='root-child' OR pages.name1020='root-child'));

 

Edited August 13 by joaomota

[Peter Knight]

Hi. honestly, I’m not so sure. Although 7.x is not the recommended PHP version, I don’t think this is your issue. 

[Macrura]

Not answering authoritatively here, but I would simply assess whether you need page paths. I enabled it on a site and it also led to some problems – in my case I have pages that render under custom URLS, and use a hook to change the path to those pages (so that I can for example use the View button, but also so that those "virtual pages" have a URL). In any case once page paths is installed it doesn't know that the paths are fake and being processed by a custom resolver, so they all got added to the page paths table and the PW thought they were real pages, so the went 404 and never reached the resolver; Took a while to figure that out; I only enabled it because it was required for the Verify Links module that i wanted to use.

[joaomota]

Thanks for the help @Macrura @Peter Knight.

I disabled PPaths module and the problem persist, so they shouldn't be related.
Regarding that specific query i mentioned,

Quote

EXPLAIN
SELECT pages.id AS p0_id, pages.parent_id AS p0_parent_id, pages.templates_id AS p0_templates_id, pages.status AS p0_status, pages.name AS p0_name, pages.name1020 AS p0_name1020, pages.status1020 AS p0_status1020, p1.id AS p1_id, p1.parent_id AS p1_parent_id, p1.templates_id AS p1_templates_id, p1.status AS p1_status, p1.name AS p1_name, p1.name1020 AS p1_name1020, p1.status1020 AS p1_status1020, p2.id AS p2_id, p2.parent_id AS p2_parent_id, p2.templates_id AS p2_templates_id, p2.status AS p2_status, p2.name AS p2_name, p2.name1020 AS p2_name1020, p2.status1020 AS p2_status1020, p3.id AS p3_id, p3.parent_id AS p3_parent_id, p3.templates_id AS p3_templates_id, p3.status AS p3_status, p3.name AS p3_name, p3.name1020 AS p3_name1020, p3.status1020 AS p3_status1020, p4.id AS p4_id, p4.parent_id AS p4_parent_id, p4.templates_id AS p4_templates_id, p4.status AS p4_status, p4.name AS p4_name, p4.name1020 AS p4_name1020, p4.status1020 AS p4_status1020, p5.id AS p5_id, p5.parent_id AS p5_parent_id, p5.templates_id AS p5_templates_id, p5.status AS p5_status, p5.name AS p5_name, p5.name1020 AS p5_name1020, p5.status1020 AS p5_status1020, p6.id AS p6_id, p6.parent_id AS p6_parent_id, p6.templates_id AS p6_templates_id, p6.status AS p6_status, p6.name AS p6_name, p6.name1020 AS p6_name1020, p6.status1020 AS p6_status1020 
FROM pages 
LEFT JOIN pages AS p1 ON p1.parent_id=pages.id AND (p1.name='level-1' OR p1.name1020='level-1') 
LEFT JOIN pages AS p2 ON p2.parent_id=p1.id AND (p2.name='level-2' OR p2.name1020='level-2') 
LEFT JOIN pages AS p3 ON p3.parent_id=p2.id AND (p3.name='level-3' OR p3.name1020='level-3') 
LEFT JOIN pages AS p4 ON p4.parent_id=p3.id AND (p4.name='level-4' OR p4.name1020='level-4') 
LEFT JOIN pages AS p5 ON p5.parent_id=p4.id AND (p5.name='level-5' OR p5.name1020='level-5') 
LEFT JOIN pages AS p6 ON p6.parent_id=p5.id AND (p6.name='level-6' OR p6.name1020='level-6') 
WHERE (pages.parent_id=1 AND (pages.name='root-child' OR pages.name1020='root-child'));

, it never used to show up in the database before the update, so something might have changed.

Do you have any idea what might be going on?

[Peter Knight]

I don't think simply copying the database table would work in itself. It ignore any other dependencies that the PP module might need.

 

[Macrura]

@joaomota

Debugging it on a live site could be tricky and risky. Probably the first thing i would do is download the database and hand it to Claude Code or similar to take a look at for any obvious issues.

If you updated PHP, check to see if the MySQL host version changed; You can also check the database collation, connection and that sort of thing.

You could go into something like PHPMyAdmin and check, optimize or repair tables; Check the codebase to see if there are any PHP compatibility issues. 

Take backups of the database at each step before you do anything.

[Peter Knight]

There's a Pro module available called ProfilerPro.
I'm not sure if this will solve your issue, but it might certainly provide some performance insights.  

 

[Peter Knight]

@joaomota just curious, but did you ever get this fixed?