Forum
Perch Collection loading speed issue
Hi,
I'm having problems with the loading speed when using a collection
Here's my setup
- I'm running Perch Runway 3.0.14 in
PERCH_DEVELOPMENTmode - Using MAMP Pro
- I noticed when refreshing the page the performance indicators didn't display the same value
- I was informed this was related to MySQL query caching
- I've edited the
my.cnffile and changed the value ofquery_cache_sizeto 0, this turns query caching off - Restarted MAMP Pro, the performance indicators now remain roughly the same on each page reload
Test
- I've removed everything else from the page template
- Only the
perch collectionis on the page - The
perch collectionhas 13 items, three of them are in the categorynew-homes - The
htmltemplate is only using 5IDsfrom the master collection template
perch_collection('Property developments', [
'category' => 'developments/new-homes',
'template' =>'_property-developments/property-developments-footer-list.html',
property-developments-footer-list.html
<perch:before>
<ul>
</perch:before>
<li><a title="<perch:content id="developmentName" />, new homes in <perch:content id="developmentAddress2"/>" href="/find-a-new-home/<perch:categories id="developmentStatus" set="developments"><perch:if exists="perch_item_first"><perch:category id="catSlug"/>/</perch:if></perch:categories><perch:content id="slug" type="slug" />"><i></i><perch:content id="developmentName" />, <perch:content id="developmentAddress2"/></a></li>
<perch:after>
</ul>
</perch:after>
Here's a screenshot showing the output of debug.
Questions
- Apart from the slow load time, is there anything in the debug that raises an eyebrow?
- Is there a faster way to use data from a
perch_collection? I'm only listing 3itemsfrom a single category. - What could be causing the slow load time? As a test, I've deleted most of the items in the collection, leaving only 3 items from the category
new-homes. Didn't have any affect on the loading speed. - I've checked the
phpandapachelogs, there aren't any errors. - Is there anything else I can provide you with to help figure this out?
Hello Stephen,
If you have a staging environment set up, it might be a good idea to test on there too. After all the website won't be served from your local machine.
I think you need to share your diagnostic report for official support.
If you turn off query caching, you're deliberately hurting performance without any benefit, so I don't recommend doing that.
How many items are in your collection index? Which MySQL table type are you using?
Hi,
I've only turned off query caching while trying to debug this problem. I have no intention of leaving it turned off.
Is it not a good way to emulate how someone might experience the website the first time they visit? Or does it only count for the first time the site is visited by anyone. As once it's visited it's in the cache at the server level? Genuinely curious.
There's still a noticeable delay when accessing the
collectioneither way.In Sequel Pro I can see the
perch2_collection indextable. It displays rows 1 - 3000 of 205,383. Is that a lot?I'm not sure what you mean?
perch2?Perch information
Hosting settings
Hi Hussein,
Thanks for showing an interest in this :)
I have a staging site setup, that's how I noticed the slow loading in the first place. Everything is nice and fast locally. But I noticed lag on the staging sever.
To try and debug the slowness, I ran
PERCH_PRODUCTION_MODE', PERCH_DEVELOPMENTI spotted a few slow areas of the page template.The query cache runs across all connected MySQL clients, so it's not specific to a single website visitor. MySQL has no concept of your website visitors. You can read about how it works for yourself here: https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
Generally you only need to care about it when writing queries that will cache well. Turning it off just makes this worse.
It's not necessarily a lot. It worthwhile making sure you're only indexing the fields you need to sort or filter by, and adding
no-indexto the rest, if you'd like to optimise that. The less you index, the faster things will go.In Sequel Pro, select the
perch2_collection_indextable, then go to the Table Info tab. What's listed in the Type select field?Thanks for the great answers.
MySQL table type is MyISAM - is that good, bad?
Is this something I'd do in Perch? Or at the database level?
You'd add that to your template tag
Ah, ok.
So, I'll add
no-index="true"to all the fields I don't intend to filter or sort by.I read in this post you recommended to:
Will I need to reindex the collection? If so, how?
Where can I check the size of the index? It'd be nice to be able to see a before and after.
Thanks for your support with this.
It's right there in your screenshot: "Number of rows".
The template changes will take effect when each collection item is re-saved, so you should see the index size drop over time for the same volume of content. Obviously if you're adding more items it'll grow.
Perfect. I'll give it go. Thanks for your help.