List of pages by template timing out

[creativejay]

I'm displaying a list of products which are found by their templates, but the pages are taking a very long time to load. At first, I blamed it on my image rendering (using PIM2), but even with all those images now stored in the file tree, the page is taking abysmally long to load. ProCache seems to help but I don't feel as though what I'm trying to do should be gnawing the bones of my resources quite so long.

The variable for the selector is defined in my header include:

$productCatList="prod_series|prod_series_ethernet|prod_series_access|prod_series_accessories|prod_series_fiber|prod_series_pwr_supplies|prod_series_pwr_systems|prod_series_wireless";
$getCurrentProdOptions="template=$productCatList, prod_status_pages!=1554|1559|1560|4242";

Then in the template for the page upon which the directory loads:

$products = $pages->find("$getCurrentProdOptions"); 
include_once("./prod-list-row.inc");
echo $out;

And the prod-list-row.inc foreach (which is on every page that's exhibiting the slowdown):

<?php 
	$sum = 0;
	$out ="";
	$out .= "<div class='span_12_of_12'>\n"; 
	foreach($products as $p){
		$sum += 1; 
		 	if ($sum % 2 == 0) {
		 		$bgcolor = '#fff';
			} else { $bgcolor = '#e4e4e4';}
		$par = $p->parent;
		$out .="<div class='section group' style='background: $bgcolor ; min-height: 110px'>\n";
		$img = $p->prod_image;
		$thumb = $img->pim2Load('squarethumb100')->canvas(100,100,array(0,0,0,0),'north',0)->pimSave()->url;
   		
		$out .="<div data-match-height='{$p->title}' class='col span_2_of_12 hide'>";
		
		$out .="<a href='{$p->url}'><span class='product-image-box'><img src='{$thumb}' alt='{$p->title}' title='{$p->title}'></span></a>";
		$out .= "</div>";
		$out .= "<div data-match-height='{$p->title}' class='col span_6_of_12'>";
		$out .= "<div class='prod-list-name-label'><a href='{$p->url}'>{$p->title}</a></div>";
		if($page!=$par) {
			$out .= "<div class='prod-list-category-label' style='font-size: .7em;'>Category: <a href='{$par->url}'>{$par->title}</a></div>";
			}		
		
		$out .=	"<div class='list-headline' style='font-size: .8em;'>{$p->headline}</div>";
		$out .="<div class='learn-more-buttons-sm'>";
        $out .="<a href='{$p->url}' title='Product Specs and Documentation'><span class='find-out-more-button' style='font-size: .8em;'><i style='font-size: .8em;' class='fa fa-lightbulb-o' ></i> &nbsp; Learn More</span></a>";
		$out .="</div>";
		$out .="</div> \n";
		$out .= "	<div data-match-height='{$p->title}' class='col span_4_of_12'>";
		if(count($p->prod_feat_imgs) >0 ){
			$out .= "<div class='featured-icons-list' margin: 2em .5em;'>";
			foreach($p->prod_feat_imgs as $feat){
				$icon = $pages->get("$feat->prod_featicon_pages");
				if($icon->image) {
					if($feat->prod_feat_textlang) { $icontitle = $feat->prod_feat_textlang;} else {$icontitle = $icon->title;}
				
				$out .= "<img src='".$icon->image->size(35,35,$imgOptions)->url . "' alt='" . $icontitle . "' title='" . $icontitle . "' class='listing-feat-icon' style='margin-right: .5em;' />";
				}
			}
			$out .= "</div>";
			if($p->prod_product_line){
				foreach($p->prod_product_line as $pline)
					if($pline->image) {
						$out .= "<div style='height: 35px;'>\n";
						$out .= "<img src='{$pline->image->size(75,35,$imgOptions)->url}' alt='{$pline->title}' />"; 
						$out .= "</div>";
					}
			}
			
		}
		
			$out .= "</div>";
		$out .="</div>";
	}
	$out .= "</div>";

 

Is there a clear culprit here of what I'm doing that's so stressing the system?

I turned off TracyDebugger because I saw another thread about that causing slowdown (even though I'm using the latest), but that had no effect. Every time I thought I found the culprit and commented it out, nothing changed.

Would appreciate some more eyes on this. Thank you!

ETA: prod_feat_imgs is a repeater field which contains a Page reference field (from which I pull the image and title) and a multilanguage textfield (to override the page reference title if it exists). Could that be the problem?

Edited October 14, 2017 by creativejay
more info

[abdus]

Well, your code runs slow because:

• You have multiple nested loops, each of which is querying pages, which would be slow by definition.
• Inside every loop you're getting, resizing images, which is even slower than querying pages (on the first request, at least)
• You seem to use repeater fields to store metadata for images, and each repeater item has page reference fields, which slows down your code even more

Points for improvement:

• Use Image Extra module instead of repeaters for saving extra data with images
• Use WireCache a lot
• Check out this thread
• Not a speed improvement, but escape long spreads of HTML from PHP, you don't have to write all of your HTML into strings
  http://php.net/manual/en/language.basic-syntax.phpmode.php
• Look up ternary operators and elvis operator
• Instead of writing CSS into HTML elements, use CSS classes and separate your CSS from HTML. You can refactor $bgcolor as .bg-white and .bg-gray, for example

That said, here's an reformatted/rewritten version of your code:

<?php namespace ProcessWire; ?>
<style>
    .bg-white {
        background-color: #fff;
    }
    .bg-gray {
        background-color: #e4e4e4;
    }
    .group {
        min-height: 110px;
    }
    
    .prod-list-category-label {
        font-size: .7em;
    }
    
    .list-headline {
        font-size: .8em;
    }
    .featured-icons-list {
        margin: 2em .5em;
    }
</style>
<div class='span_12_of_12'>
<?php foreach ($products as $p): ?>
    <?php
    // $bgcolor = $sum % 2 == 0 ? '#fff' : '#e4e4e4';
    $bgClass = $sum % 2 == 0 ? 'bg-white' : 'bg-gray';

    $par = $p->parent;
    $img = $p->prod_image;
    $thumb = $img->pim2Load('squarethumb100')->canvas(100,100,array(0,0,0,0),'north',0)->pimSave()->url;

    ?>
    <?php // Replace inline styles with classes ?>
    <div class="section group <?= $bgClass ?>">
        <div class="col span_2_of_12 hide" data-match-height="<?= $p->title ?>">
            <a href="<?= $p->url ?>"><span class="product-image-box"><img src="<?= $thumb ?>" alt="<?= $p->title ?>" title="<?= $p->title ?>"></span></a>
        </div>
        <div class="col span_6_of_12" data-match-height="<?= $p->title ?>">
            <div class="prod-list-name-label"><a href="<?= $p->url ?>"><?= $p->title ?></a></div>

            <?php if ($page != $par): ?>
                <?php // Replace inline styles with classes ?>
                <div class='prod-list-category-label'>Category: <a href='<?= $par->url ?>'><?= $par->title ?></a></div>
            <?php endif; ?>
            <?php // Replace inline styles with classes ?>
            <div class="list-headline"><?= $p->headline ?></div>
            <div class="learn-more-buttons-sm">
                <a href='<?= $p->url ?>' title='Product Specs and Documentation'><span class='find-out-more-button' style='font-size: .8em;'><i style='font-size: .8em;' class='fa fa-lightbulb-o' ></i>   Learn More</span></a>
            </div>
        </div>
        <div data-match-height='<?= $p->title ?>' class='col span_4_of_12'>
            <?php if ($p->prod_feat_imgs->count > 0): ?>
            <div class='featured-icons-list'>
                <?php
                foreach ($p->prod_feat_imgs as $feat) {
                    $icon = $pages->get("$feat->prod_featicon_pages");
                    if ($icon->image) {
                        // Loop up Elvis operator
                        $icontitle = $feat->prod_feat_textlang ?: $icon->title;
                        $out .= "<img src='" . $icon->image->size(35, 35, $imgOptions)->url . "' alt='" . $icontitle . "' title='" . $icontitle . "' class='listing-feat-icon' style='margin-right: .5em;' />";
                    }
                }
                ?>
            </div>



            <?php if($p->prod_product_line){
                foreach($p->prod_product_line as $pline)
                    if($pline->image) {
                        $out .= "<div style='height: 35px;'>\n";
                        $out .= "<img src='{$pline->image->size(75,35,$imgOptions)->url}' alt='{$pline->title}' />";
                        $out .= "</div>";
                    }
            } ?>

            <?php endif; ?>
        </div>
    </div>
<?php endforeach; ?>
</div>

 

[Robin S]

Limiting and paginating would be sensible too.

Even if the list is currently not very long, any time you list pages that a client may add to (I assume the client can add new products) you should limit and paginate because you don't know how many pages this may ultimately grow to.

[creativejay]

Thank you both very much for taking the time to reply and help me out. abdus, thank you very much for the rewritten code. I begin to see what you mean.

13 hours ago, Robin S said:

Even if the list is currently not very long, any time you list pages that a client may add to (I assume the client can add new products) you should limit and paginate because you don't know how many pages this may ultimately grow to.

Thankfully, in this case, I am the client. I am building this site for my day job, and I know a bit about the number of products that typically are added in any given year (generally no more than 5). 

On the individual product categories, (organized by the product templates' placement in the page tree), right now the maximum number is 37. While that section will grow, my main concern is the speed of the pages upon which every product is listed for the convenience of someone (most often, myself) who needs to reference something quickly.

The site needs to launch last year (of course!), so while I figure this out I will likely keep such bloated pages unpubbed so I can work to improve their performance before I make them public.

 

I'm self-taught, and this is the most complicated (in terms of interlinking pages) that I've ever created a site. I had no idea it was going to so greatly impact performance to loop it the way I have until the information was already populated and I saw the results of the foreach. 

 

15 hours ago, abdus said:

Use Image Extra module instead of repeaters for saving extra data with images

The reason that I have the prod_feat_imgs in a Repeater field (as a page reference) is that I've got ~45 icons that appear both in the category list and on the product pages themselves. These have default value captions (in the referenced page's title field) which occasionally need to be overridden per product (for example one boasts of operating temperature range and in 85% of cases is one value (set in the 'page-select-option' page along with the icon) but in other cases will need to be overridden.

So prod_feat_imgs is a repeater which includes a page reference (prod_feat_ and a textfield to override the referenced page's title.

I have set up the product templates with a large number of getPage fields (and a whole hidden section of the page tree dedicated to universal options to choose from, for the same reason as above: future proofing).

Thankfully not that many are built into repeaters (I started to realize what a wicked web I'd woven as I tried to get the repeater information into the markup but didn't realize it taxed PW to run as much as it did me to code). Many, however, are multi-select.

In the case of prod_feat_imgs, I will periodically need to go in an update the entire list of 45 images if we redesign them. It seemed future-proof to update them on 45 pages rather than track them to separate image fields across potentially 200-300 product pages.

So, all that was to explain my thought process in why I'm not putting the Image field directly in the product template fieldset. It doesn't seem that Image Extra would help me in this use case. If you have another suggestion to how to approach this, I am open to hearing it!

Right now the best alternative I can come up with is to take the page reference (prod_feat_iconpages) out of the repeater and just add additional pages to that section of my page tree for the variations. This will become ungainly in the future, though, as cases like the temperature example would require a new entry for a minor variation in spec.

I'm planning further updates to the site (the full improvement list I intended to be ready for launch, but there was too much data entry required for the time allotted), and this image's caption could be overridden by the actual specification for temperature, for which I was planning to use a table field on prod-series' children pages, prod-model (this promises another headache of markup looping. I'm almost glad I didn't have time to include this in the current launch).

 

15 hours ago, abdus said:

Use WireCache a lot

I am using ProCache currently. I thought ProCache handled the basic functions of WireCache and extended them. Am I mistaken?

15 hours ago, abdus said:

Not a speed improvement, but escape long spreads of HTML from PHP, you don't have to write all of your HTML into strings

Thank you, I will adopt this practice going forward.

15 hours ago, abdus said:

ternary operators and elvis operator

Thank you. I did want to look into these (having seen them in sample code) but didn't have the vocabulary to search for them! (The worst part about being self-taught).

15 hours ago, abdus said:

Instead of writing CSS into HTML elements, use CSS classes and separate your CSS from HTML. You can refactor $bgcolor as .bg-white and .bg-gray, for example

Yes, thank you. I write it into the HTML elements while I'm fussing about with the templates, then migrate them over into my referenced css file.  But it would make my life easier to at least structure it the way you did in the rewritten code. 

 

15 hours ago, abdus said:

Check out this thread

Thank you. I'm not sure on the direct takeaway of that thread (I did find it before I posted this one). Are you suggesting switching to AJAX/JSON or using the MySQL monitor?

15 hours ago, abdus said:

You have multiple nested loops, each of which is querying pages, which would be slow by definition.

This is the most complicated site I've ever designed, and I was under the impression that, as long as I refined my queries down as much as I could (using get instead of find wherever possible), that the query was not a terrible drain on the process, and that this was the PW advantage over other CMS. Obviously, I need to reapproach my thinking on the matter. 

15 hours ago, abdus said:

Inside every loop you're getting, resizing images, which is even slower than querying pages (on the first request, at least)

Yes, and this was my red herring. As I adjusted image sizes in the template markup, I fully expected the delay. It was only when I finished and the page was still slow that I realized there was another, probably more fundamental, problem with my coding.

 

 

 

[BitPoet]

Adding to what @abdus said: make sure you haven't got a name resolution issue that slows down database access (it often helps setting 127.0.0.1 instead of localhost for $config->dbHost to work around IPv6 issues). If you still want an additional boost, consider enabling opcode caching (e.g. with PHP's opcache module).

[Robin S]

My gut feeling is that the Page Reference fields, repeaters, foreach nesting is not what is causing this slowness. For a listing of only 37 product pages there should not be that much of a delay. (How long is "abysmally slow", by the way?) I'm doing similar things for with page-per-image setups (although with PageTable rather than repeater) with much larger numbers of pages and there isn't a significant impact on speed. And for pages in the ProCache cache there should be no impact whatsoever.

So I think you have some other issue going on. Not sure what though, sorry. If you're developing locally, try testing the site on a remote server and see if you experience the same slowness. Or vice-versa if you are developing remotely.

Also: start commenting/cutting out blocks of your template code to try and isolate if one area is responsible.

[creativejay]

40 minutes ago, Robin S said:

My gut feeling is that the Page Reference fields, repeaters, foreach nesting is not what is causing this slowness. For a listing of only 37 product pages there should not be that much of a delay. (How long is "abysmally slow", by the way?) I'm doing similar things for with page-per-image setups (although with PageTable rather than repeater) with much larger numbers of pages and there isn't a significant impact on speed. And for pages in the ProCache cache there should be no impact whatsoever.

So I think you have some other issue going on. Not sure what though, sorry. If you're developing locally, try testing the site on a remote server and see if you experience the same slowness. Or vice-versa if you are developing remotely.

Also: start commenting/cutting out blocks of your template code to try and isolate if one area is responsible.

 

Eliminating the prod_feat_imgs call from the foreach did seem to speed things up, but not to the near-instant page loads I've come to expect. I really hope I can improve performance from the scripting and that I don't need to find a new host. I'm developing on a managed VPS account, in a subdirectory.

To answer your question, abysmally slow was 26 seconds or more per page (and if the images were being rendered afresh, up to four minutes, including a time-out and a reload to finish processing them).

[fbg13]

You can use

$start = microtime(true);
// code to test
echo microtime(true) - $start;
// or log the time
$log->error("code xyz took: " . microtime(true) - $start);

to find which part takes the most time to execute.

http://php.net/microtime

[adrian]

2 hours ago, fbg13 said:

You can use

$start = microtime(true);
// code to test
echo microtime(true) - $start;
// or log the time
$log->error("code xyz took: " . microtime(true) - $start);

to find which part takes the most time to execute.

http://php.net/microtime

Also, check out the Performance Panel in Tracy to show the time between named breakpoints.
https://processwire.com/blog/posts/introducing-tracy-debugger/#performance-panel

[szabesz]

8 hours ago, creativejay said:

I'm developing on a managed VPS account, in a subdirectory.

I always put all my sites in a subdirectory so I do not think it should matter regarding the performance of the site. I also have a site on a managed VPS (WHM) and originally Tracy reported about 500ms execution time for its 10 product listing page. I asked the support to optimize the VPS – originally it was in its default setup state – and they managed to speed it up so right now the same page renders in about 130ms. They changed php settings, etc... We did not discuss in detail what they had done, I was just happy to see the result  

I'm not saying fine tuning your VPS will surely solve your problem but it might help too.

Edited October 16, 2017 by szabesz
typo

[creativejay]

8 hours ago, szabesz said:

I always put all my sites in a subdirectory so I do not think it should matter regarding the performance of the site. I also have a site on a managed VPS (WHM) and originally Tracy reported about 500ms execution time for its 10 product listing page. I asked the support to optimize the VPS – originally it was in its default setup state – and they managed to speed it up so right now the same page renders in about 130ms. They changed php settings, etc... We did not discuss in detail what they had done, I was just happy to see the result  

I'm not saying fine tuning your VPS will surely solve your problem but it might help too.

Thank you szabesz, I asked my host about it and the response was "what did you have in mind?"

They did offer to install litespeed and provided this information on what it offers: https://www.a2hosting.com/kb/a2-hosting-products/turbo-web-hosting/differences-between-turbo-and-swift-servers

To those of you who understand this better than I, would this be worthwhile for a monthly fee, or are these largely improvements I can handle with PWire modules?

I will play around with the timestamps this morning now that my files are in their final resting place, before I must throw the switch later today.

[abdus]

14 minutes ago, creativejay said:

They did offer to install litespeed and provided this information on what it offers: https://www.a2hosting.com/kb/a2-hosting-products/turbo-web-hosting/differences-between-turbo-and-swift-servers

To those of you who understand this better than I, would this be worthwhile for a monthly fee, or are these largely improvements I can handle with PWire modules?

ProCache should cover almost all of what they offer (mostly caching). I'd say it's a cash grab.

You said you were hosting your websites on a managed VPS, how is your linux skills? Try switching to another hoster. I am a happy user of Vultr and DigitalOcean, and never had problems with them. They bill (IIRC) hourly, so you can try it for a few days for a few bucks and see if it works out for you.

Managing your own VPS isn't too difficult. As long as you dont keep many ports open (80, 443 for web and 22 or a different port for SSH), set up a fail2ban and you'd be mostly OK in terms of security, and you get to extend your skillset along the way.

[creativejay]

Oh, okay a new reply without my prompting (I do love this host's support response):

Quote

You are currently running PHP 5 with dso | Ruid2. I would suggest that we install PHP 7 or multiple versions 5-7 if needed so you can take advantage of performance increases. 

Anything I need to be aware of if I have them install PHP 7?

[szabesz]

Well

12 minutes ago, creativejay said:

"what did you have in mind?"

Well, no comment....

I looked up the conversation with my VPS team and that's all the "depth" we talked about the matter: "I did however make some performance optimizations to your server to improve overall performance, such as switching the site to use PHP-FPM as the PHP handler, which is much faster than the default handler."

It's a managed VPS and as long as it is speedy and works without issues, I am happy.

5 minutes ago, creativejay said:

Anything I need to be aware of if I have them install PHP 7?

Switching to PHP 7.0.x should be okay even if you have 3rd party modules installed which work with ProcessWire 3.x. I'm using it too for all my sites.

Edited October 16, 2017 by szabesz
typo

[creativejay]

5 minutes ago, abdus said:

how is your linux skills?

That's the cat with the short tail, right?  XD

 

(In other words, I have zero linux skills)

[creativejay]

Checking times in Tracy, I see (not that surprising) that my selector for locating the products on the site is a bit of a hog:

Quote

template=prod_series|prod_series_ethernet|prod_series_access|prod_series_accessories|prod_series_fiber|prod_series_pwr_supplies|prod_series_pwr_systems|prod_series_wireless, prod_status_pages!=1554|1559|1560|4242

1294.8

Not surprising, since a lot of the pages that hit this slowdown contain a similar $pages->find() call.

Which is the largest offender here? That it's returning 245 results, or that it would technically be checking every page in the tree for that template and status?

I added a has_parent=1048 to confine the search to the Products section, but that only improved the timestamp to 1240.6

[abdus]

Try caching the result of that

$selector = 'template=prod_series|prod_series_ethernet|prod_series_access|prod_series_accessories|prod_series_fiber|prod_series_pwr_supplies|prod_series_pwr_systems|prod_series_wireless, prod_status_pages!=1554|1559|1560|4242';
if (!($list = $cache->get('products-main-view', WireCache::expireHourly))) {
    $list = $pages->find($selector);
    $cache->save('products-main-view', $list);
}

// do sth with the $list

PW will save a list of ids (such as 3545|2556|1456|7889|3456) to caches table in DB and it wont have to manually build that list at every non-cached request (I keep forgetting that you're using ProCache). Once it's cached, it'll retrieve pages back using IDs and you can't get faster than that.

Procache is disabled when you're logged in. Are your results from an incognito window?

[creativejay]

6 minutes ago, abdus said:

Are your results from an incognito window?

No, I am using the TracyDebugger bar that loads for the superuser.

I implemented that code and the first instance took the expected 1240 ms, while the second instance (the original call on the page) then took .6 ms. The pages still load somewhat slowly in the second browser I have open (where I am not logged in).

8 minutes ago, abdus said:

I keep forgetting that you're using ProCache.

I currently have ProCache disabled while I try to suss this out. Does this mean ProCache will provide similar caching to the code you offered, or should I still go through and force cache the common id lists?

[adrian]

1 minute ago, creativejay said:

No, I am using the TracyDebugger bar that loads for the superuser.

Just a quick FYI - you can get Tracy's debug bar when logged out two different ways. If you are on a local dev machine, check the "Force Guest Users into Development Mode on Localhost" option. The other way if you are on a live server is to use the User Switcher to logout.

Hope that helps you get data you may want while logged out.

[creativejay]

Okay, my host is prepared to enable PHP-FPM on my say-so. PHP has been upgraded though the active version is currently 5.6.

Is there an ideal PHP version, for the current release of PW3.x?

And should I be aware of anything before requesting PHP-FPM be enabled? I've been asked "if I'm sure" and that's giving me heart palpitations (that and all the coffee that kept me going through this mess all weekend).

[abdus]

38 minutes ago, creativejay said:

PHP has been upgraded though the active version is currently 5.6.

Definitely make the switch to PHP 7.0 or 7.1

[szabesz]

17 minutes ago, creativejay said:

And should I be aware of anything before requesting PHP-FPM be enabled? I've been asked "if I'm sure" and that's giving me heart palpitations (that and all the coffee that kept me going through this mess all weekend).

I'm no expert at all but they should be able to switch back should anything go awry. I do not have any issue with it...

5 minutes ago, abdus said:

PHP 7.0 of 7.1

I guess there is a typo: 7.0 or 7.1
I would start with 7.0 and you should see improvement. Afterwards you might want to try 7.1 too, of course,

[creativejay]

Thanks everyone! I wouldn't have gotten this far without you.

I have asked them to switch to 7.0. I will test and then ask them to enable PHP-FPM, test again.

Then switch TracyDebugger into Production Mode, then enable ProCache. Then give the site a last lookie-loo, and then throw the switch.

My eyes hurt!

ETA: Without any of the above being completed, the site is loading faster for a guest. A very good sign!

Edited October 16, 2017 by creativejay
noticed speed increase while waiting!

[creativejay]

Even faster in 7.0, but now we've got some PHP errors getting thrown.

Does anyone happen to know what modules, aside from pdo/mysql, might be required to be installed on the server for ProcessWire?

[szabesz]

5 minutes ago, creativejay said:

PHP errors getting thrown.

I recommend showing us the errors to track things down more easily. If you have long error output strings, you might want to use the forum's "spoiler" feature to make your post shorter.

Edited October 16, 2017 by szabesz
typo