Should I limit how many find() methods I use on a single page?

Brian Scramlin · May 17, 2017

Hey!

Quick question,

Should I limit how many find() methods I use on a single page? Is it fairly resource-intensive?

I am getting consistent

2006 MySQL server has gone away

errors and have followed all the suggestions I can find, but wonder if I am just "overloading" my server resources with database requests or something like that?

Thank you!

Sergio · May 17, 2017

Reading queries are lighter than writing ones, so it seems odd that you're having such problem, but how many find()'s has the page in question?

This seems to be a problem with the server where the database is running. Are you monitoring its ram/cpu/io?

Brian Scramlin · May 17, 2017

That is good to hear.

Let's take the home page for instance, I will only post the find() method calls:

<head>
	<?php $latestArticle = $pages->find("template=article, articleFeatured=1, sort=-created")->first(); ?>
</head>
<body>
	<?php $resultsAll = $pages->find("template=article, articleInsider!=1, limit=20, sort=-articleDate, sort=-created") ?>
	<?php $obituaries = $pages->find("template=obituary, limit=5, sort=-obitDate"); ?>
</body>

However, there are get() calls as well. In case that is relevant:

8 in the <head>
6 in the <body>
1 in the <footer>

Although, many of those get() calls have logic controllers such as <?php if ($pages->get("template=site-options")->homeShareImage): ?> and I don't know whether that takes as much resource.

As well as 11 eq() WireArray selections--I don't know if selecting out of a WireArray is accessing the database or not.

What is occurring is every so often I am getting these resource spikes that cause the site to go down. Now, I am not sure if MySQL is restarting--which is my hunch--or whether something else is going on. I also wonder if it has something to do with the ProCache module? My hosting provider can't figure it out either (I am using a cloud hosting service with 3 CPU Cores, CentOS, 5GB RAM, and 40GB SSD, not shared.

My client is getting frustrated with me!

TracyDebugger shows no errors, however it does show the exception that is thrown (see attached image).

It only lasts a small amount of time, 4 to 28 seconds. But this website has a lot of traffic and a lot of people are not getting through. This month we have had 373,000 sessions in the past 30 days--usually 12-15,000 a day so I can see why my client is upset, especially since I sold him on ProcessWire and cloud hosting.

The only other thing I can think of that might be intensive is some image processing.

I am using this function to create widescreen versions of small images (like mug shots):

<?php
//This is an amazing function to use imagick
function imagickal($imagePath, $method, array $arguments) {
    $path_parts = pathinfo($imagePath);
    $dirname = $path_parts['dirname'] . '/';
    $filename = $path_parts['filename'];
    $mod = $method . '-' . implode($arguments, '-');
    $mod = wire('sanitizer')->filename($mod, true);
    $savename = $dirname . $filename . '_' . $mod . '.jpg';
    if (!file_exists($_SERVER['DOCUMENT_ROOT'] . $savename)) {
        $image = new Imagick($_SERVER['DOCUMENT_ROOT'] . $imagePath);
        call_user_func_array([$image, $method], $arguments);
        $image->writeImage($_SERVER['DOCUMENT_ROOT'] . $savename);
    }
    return $savename;
}
?>

And I use it once on the home page and once on the article page like so:

<?php $fImage = $latestArticle->articleFeaturedImage; ?>
<?php if ($fImage->width < 730): ?>
<?php
$fImage = imagickal($fImage->url, "thumbnailImage", [730, 350,true,true]);
?>
<?php else: ?>
<?php $fImage = $fImage->size(730, 350)->url; ?>
<?php endif; ?>
<img src="<?= $fImage ?>" class="img-fluid" title="<?= $latestArticle->title ?>" alt="<?= $latestArticle->title ?>" />

If anyone has any ideas as to why the "2006 MySQL server has gone away" is occurring I would appreciate it!

NOTE: I have attempted this fix already:

max_allowed_packet=16M -> 100M
wait_timeout=60 -> 300

I do feel this has helped limit how often this is occurring, but I think it is a band-aid, not a solution.

Sergio · May 17, 2017

1 hour ago, Brian Scramlin said:

What is occurring is every so often I am getting these resource spikes that cause the site to go down. Now, I am not sure if MySQL is restarting--which is my hunch--or whether something else is going on. I also wonder if it has something to do with the ProCache module? My hosting provider can't figure it out either (I am using a cloud hosting service with 3 CPU Cores, CentOS, 5GB RAM, and 40GB SSD, not shared.

1. Processwire default installation uses MyISAM as the engine which has the disadvantage of locking the entire table during a write operation. This can cause bottleneck issues in some cases. But if you are running a up-to-date MySQL version, you can change it to InnoDB to have better performance. BUT I cannot say that it will solve the problem just by doing this. Which version are you using?

1.1. And which PW version?

2. It can help to use MariaDB instead of MySQL as they say it uses less RAM, but moving the database to a second VPS will improve a lot.

3. Procache should help A LOT on the performance, as it bypasses PHP and MySQL altogether unless you're not using it right. Are the users logging in for instance?

4. Just of curiosity, why are you using a custom image processing and not PW built-in methods?

Sergio · May 17, 2017

Also, look for modules that you have installed. Have any suspects there?

Brian Scramlin · May 17, 2017

Quote

2 hours ago, Sérgio Jardim said:

1. Processwire default installation uses MyISAM as the engine which has the disadvantage of locking the entire table during a write operation. This can cause bottleneck issues in some cases. But if you are running a up-to-date MySQL version, you can change it to InnoDB to have better performance. BUT I cannot say that it will solve the problem just by doing this. Which version are you using?

1.1. And which PW version?

2. It can help to use MariaDB instead of MySQL as they say it uses less RAM, but moving the database to a second VPS will improve a lot.

3. Procache should help A LOT on the performance, as it bypasses PHP and MySQL altogether unless you're not using it right. Are the users logging in for instance?

4. Just of curiosity, why are you using a custom image processing and not PW built-in methods?

1. Interesting, I did not know about different engines in MySQL. I am using MyISAM currently, although I see ProCache is using InnoDB.

2. I am not familiar with MariaDB. I would have to connect with my host to see if that is an option for me. Also, can you elaborate on what moving the database to a second Virtual Private Server would mean and how that would help?

3. I am glad to hear that. This was my understanding as it would serve static HTML with zero server-side processing. But, I didn't know if when the cache was reset if that could cause a spike in resources. I do believe everything is being cached correctly, but I can certainly explain the details more if that is a clue.

4. The reason is because processwire does not have the capability to add padding to a small image and make it a specific dimension. To maintain the correct formatting on the home page, I require a specific dimension size and some of the images the client is posting are so small they look awful when sized upward. That led me to utilize the PHP iMagick library already available on my server. Although, I wonder if this function is being executed every time a page is loaded, or only on the first load of the page as is the case with ProcessWire image resizing?

I am prepared to take your suggestion regarding switching to InnoDB, as I understanding locking a row instead of the entire table may be more desirable in my situation. I will update you after I do that.

Sergio · May 17, 2017

1. Be careful, the reason why PW uses MyISAM by default is that PW requires it for full-text search and this is only available for InnoDB if you're using MySQL version 5.6 (or 5.7, I think) and most servers don't have that version yet. So check it!

1.1. OK!

2. MariaDB was created as an open source alternative, after Oracle bought MySQL years ago. It's fully compatible as far as I know. The suggestion to move the database to its own VPS is to have more RAM available to it if that's the case. A good write-up about it: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql

3. Are you saving a field on a page when a user access it, for instance, like a page-views counter? If not, Procache should not reset the page's cache status in most scenarios.

4. I guess it's executed only one time, per page, BUT, check your server RAM usage. 5GB is a good amount, but maybe MySQL is crazy on it and making the server closing some connections to it (I'm guessing).

Robin S · May 17, 2017

8 hours ago, Brian Scramlin said:

<?php $latestArticle = $pages->find("template=article, articleFeatured=1, sort=-created")->first(); ?>

This finds all 'articleFeatured' articles, but you only need one. So better to do this:

$latestArticle = $pages->findOne("template=article, articleFeatured=1, sort=-created");

3 hours ago, Brian Scramlin said:

Although, I wonder if this function is being executed every time a page is loaded, or only on the first load of the page as is the case with ProcessWire image resizing?

I wrote that imagickal() function and it doesn't recreate the image on every page load if the processed image already exists. But it was really intended for applying ImageMagick effects - you don't need it for simple image resizing. Rather than add padding in the resized image you should use CSS to keep the image within its container while maintaining aspect ratio. A couple of ways you can do this below - CSS is inline in the examples but you would move it to an external stylesheet.

If you don't mind using background images the technique is dead simple. Just adjust the padding-top for the desired aspect ratio (in the example I used your 730:350 ratio).

<div style="padding-top:47.945%; background:#000 url('my-image.jpg') no-repeat center center / contain;"></div>

If you want to use an <img> tag there's a bit more to it:

<div style="padding-top:47.945%; position:relative;">
    <div style="position:absolute; width:100%; height:100%; top:0; left:0; font-size:0; line-height:0; text-align:center; background-color:#000">
        <img src="my-image.jpg" style="max-width:100%; max-height:100%;">
    </div>
</div>

If you are using a CSS pre-processor you can use a helper mixin, e.g. https://css-tricks.com/snippets/sass/maintain-aspect-ratio-mixin/

But none of this should make that much of a difference when you are using ProCache - your server load should be very low when ProCache is used considering you have no front-end users logging in. You could check to make sure you are not clearing the entire cache unnecessarily - for best performance you shouldn't use the "Reset cache for entire site" option for when a page is saved but instead use the settings on the "Cache" tab of Edit Template to only clear the cache of related pages. But the other thing to consider is if the problem might be due to the host. You could copy the files and DB to a different host and trial that for a bit to see if the problem resolves. A hassle for sure but if you are getting desperate...

Brian Scramlin · May 19, 2017

On 5/17/2017 at 7:29 PM, Robin S said:

This finds all 'articleFeatured' articles, but you only need one.

I am speechless I didn't know of the findOne() method! Thank you!

Thank you for writing the imagickal function, it has worked very well for our situation. Originally, I just put minimum-size requirements on the image field, but the client complained right away because of the issue with mug shots. The reason CSS is not able to fully solve our issue is social media sharing. The resolution size is perfectly formatted for sharing on Facebook, which drives about 80% of the traffic. So, server-side processing is preferred. I only wish the client didn't need to upscale any images!

Regarding ProCache, I have attached screenshot of what it is being used for. I am not resetting any child pages, parent pages, the home page, or entire site after a page save. I have everything checking out on the cache-side, apart from getting into a CDN, which I have considered.

Yes, I am considering a different hosting solution :/ I have really enjoyed the company I am with (siteground.com) as their technicians are very quick to help, but perhaps digitalocean.com or something similar's server configurations would be better suited.

Brian Scramlin · May 19, 2017

On 5/17/2017 at 1:06 PM, Sérgio Jardim said:

Also, look for modules that you have installed. Have any suspects there?

I did go through the modules.

I don't think they could be causing the resource-spikes.

ProCache
Sitemap XML
Hanna Code
Template Editor
Video Embed for YouTube/Vimeo
Tracy Debugger
And just recently Diagnostics

Brian Scramlin · May 19, 2017

On 5/17/2017 at 6:20 PM, Sérgio Jardim said:

1. Be careful, the reason why PW uses MyISAM by default is that PW requires it for full-text search and this is only available for InnoDB if you're using MySQL version 5.6 (or 5.7, I think) and most servers don't have that version yet. So check it!

1.1. OK!

2. MariaDB was created as an open source alternative, after Oracle bought MySQL years ago. It's fully compatible as far as I know. The suggestion to move the database to its own VPS is to have more RAM available to it if that's the case. A good write-up about it: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql

3. Are you saving a field on a page when a user access it, for instance, like a page-views counter? If not, Procache should not reset the page's cache status in most scenarios.

4. I guess it's executed only one time, per page, BUT, check your server RAM usage. 5GB is a good amount, but maybe MySQL is crazy on it and making the server closing some connections to it (I'm guessing).

1. After reading some documentation, there are fears I have about switching over to InnoDB. I will continue to read, but I decided not to delve into it without feeling more confident I can properly configure everything. Right now there is just as much chance as making things worse, than better!

2. That makes sense. The thing is, we are almost always under 3GB of memory usage (see attached image), but the "spikes" reach higher (I actually can't find out through any logs how high it reaches). But, I am not opposed to suggesting this if I run out of ideas.

3. No, everything is very basic. So, it sounds like what you are saying should be true: the pages are cached.

4. The below image is the only information on the memory usage I could find. In the past two days, it appears that we broke 5GB. I don't know what is "normal" for ProcessWire. I do find it interesting though, that the per/second memory was not increased during the above-5GB mark... so maybe it is just legitimate traffic overloading the server? The client already doesn't like spending $80-100 a month on server cost.

Thanks everyone for your help!

rick · May 19, 2017

18 minutes ago, Brian Scramlin said:

The client already doesn't like spending $80-100 a month on server cost.

In addition to a server's basic specs, another consideration is bandwidth/memory surges. Does your hosting company limit you to only x-GB per month (without burst) or do they offer bursting coverage as well? That price seems a little high to me regardless, ie, You can co-lo for that price.

Also, (and I know you are aware of this) the way the code (and queries) is written can play a big part in memory usage. On a related note, I have a co-lo machine that is a dinosaur compared to today's systems -- It has 256M or ram, single processor, and runs all the pre-processwire applications I have written (hundreds of user connections) without a single hiccup. The code written utilizing ProcessWire can be done inefficiently, for example, using $page->find when $pages->count is the more efficient call.

szabesz · May 19, 2017

@Brian Scramlin I have not completely read this thread, but do you have ProDevTools? Considering this:

"VIP Support

When you purchase ProDevTools, you are purchasing full access to the VIP ProDevTools member area. This is where downloads and upgrades for products are made available, and where VIP support is provided directly by ProcessWire's lead developer, Ryan.

This is particularly useful with the ProfilerPro service, and Ryan is there to help you optimize your site and resolve bottlenecks discovered with ProfilerPro. It's also useful with the API Explorer module, as it will likely open up a new world of things you can do with ProcessWire, and we'll be there to help you learn and answer questions as they come up."

This offer might be the one you need now.

Sergio · May 19, 2017

2 hours ago, Brian Scramlin said:

I did go through the modules.

I don't think they could be causing the resource-spikes.

ProCache

Sitemap XML

Hanna Code

Template Editor

Video Embed for YouTube/Vimeo

Tracy Debugger

And just recently Diagnostics

I asked you to check because you could have developed your own modules and maybe there was a culprit there. Those you listed are ok!

The memory usage log you have doesn't show which all the processes names running, so it won't help much. Are you using Apache and is it serving a lot of static resources (CSS/images/videos etc)? Apache can use a lot of RAM in some cases compared to Nginx. But your ProCache settings seems correct.

I agreed with @szabesz, ProfilePro and Ryan could help you a lot!

The other possibility is to ask your helpdesk to install a better monitoring tool like NewRelic. It helped me a lot on a Wordpress site in the past.

Robin S · May 20, 2017

22 hours ago, Brian Scramlin said:

Thank you for writing the imagickal function, it has worked very well for our situation. Originally, I just put minimum-size requirements on the image field, but the client complained right away because of the issue with mug shots. The reason CSS is not able to fully solve our issue is social media sharing. The resolution size is perfectly formatted for sharing on Facebook, which drives about 80% of the traffic. So, server-side processing is preferred. I only wish the client didn't need to upscale any images!

Consider switching to the Pageimage Manipulator module, which has a canvas() method that supports the image resizing you are doing. I don't see any reason why the imagickal() function would be the cause of the problems you are having, but horst knows a lot more than I do and his module is likely to be better optimised.

Sign In

Should I limit how many find() methods I use on a single page?

Recommended Posts

Brian Scramlin 9

Share this post

Link to post

Share on other sites

Sergio 749

Share this post

Link to post

Share on other sites

Brian Scramlin 9

Share this post

Link to post

Share on other sites

Sergio 749

Share this post

Link to post

Share on other sites

Sergio 749

Share this post

Link to post

Share on other sites

Brian Scramlin 9

Share this post

Link to post

Share on other sites

Sergio 749

Share this post

Link to post

Share on other sites

Robin S 7,314

Share this post

Link to post

Share on other sites

Brian Scramlin 9

Share this post

Link to post

Share on other sites

Brian Scramlin 9

Share this post

Link to post

Share on other sites

Brian Scramlin 9

Share this post

Link to post

Share on other sites

rick 592

Share this post

Link to post

Share on other sites

szabesz 2,837

Share this post

Link to post

Share on other sites

Sergio 749

Share this post

Link to post

Share on other sites

Robin S 7,314

Share this post

Link to post

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content

Browse

Activity

My Activity Streams

Store

My Details

Support