Forum

Thread tagged as: Problem

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_DEVELOPMENT mode
  • 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.cnf file and changed the value of query_cache_size to 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 collection is on the page
  • The perch collection has 13 items, three of them are in the category new-homes
  • The html template is only using 5 IDs from 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.

Perch Screenshot

Questions

  1. Apart from the slow load time, is there anything in the debug that raises an eyebrow?
  2. Is there a faster way to use data from a perch_collection? I'm only listing 3 items from a single category.
  3. 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.
  4. I've checked the php and apache logs, there aren't any errors.
  5. Is there anything else I can provide you with to help figure this out?
Stephen Meehan

Stephen Meehan 4 points

  • 11 months ago
Hussein Al Hammad

Hussein Al Hammad 103 points
Registered Developer

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.

Is there anything else I can provide you with to help figure this out?

I think you need to share your diagnostic report for official support.

Drew McLellan

Drew McLellan 2636 points
Perch 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,

If you turn off query caching, you're deliberately hurting performance without any benefit, so I don't recommend doing that.

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 collection either way.

How many items are in your collection index?

In Sequel Pro I can see the perch2_collection index table. It displays rows 1 - 3000 of 205,383. Is that a lot?

Which MySQL table type are you using?

I'm not sure what you mean? perch2?

Perch information

Perch Runway: 3.0.14
Production mode: Development (10)
Installed apps: content (3.0.14), assets (3.0.14), categories (3.0.14), perch_forms (1.10)
DB driver: PDO
DB tables: perch2_backup_plans (1), perch2_backup_resources (1), perch2_backup_runs (5656), perch2_blog_authors (2), perch2_blog_comments (0), perch2_blog_index (69), perch2_blog_posts (3), perch2_blog_posts_to_tags (0), perch2_blog_sections (1), perch2_blog_tags (0), perch2_blog_webmention_queue (0), perch2_blogs (2), perch2_categories (9), perch2_category_counts (0), perch2_category_sets (5), perch2_collection_index (205383), perch2_collection_items (1192), perch2_collection_revisions (186), perch2_collections (6), perch2_content_index (1803), perch2_content_items (166), perch2_content_locks (0), perch2_content_regions (23), perch2_forms (8), perch2_forms_responses (1431), perch2_jw_activity_log_actions (1266), perch2_menu_items (18), perch2_navigation (2), perch2_navigation_pages (4), perch2_page_routes (29), perch2_page_templates (27), perch2_pages (29), perch2_resource_log (18335), perch2_resource_tags (5), perch2_resources (3043), perch2_resources_to_tags (5), perch2_scheduled_tasks (26), perch2_settings (38), perch2_user_passwords (0), perch2_user_privileges (44), perch2_user_role_buckets (0), perch2_user_role_privileges (27), perch2_user_roles (2), perch2_users (8)
Users: 8
App runtimes:
<?php
    $apps_list = array(
        'perch_forms',

    );
Scheduled tasks for Backup: plan_2 (10 mins)
Editor plug-ins:
H1: ba167287ed9b818e6008ad2765e5121e
L1: 3fabca5af968c99f35bc9d5a1464a963
F1: 3b606135b33e6a102526838f4152a807
headerColour: #000000
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 1
content_collapseList: 1
lang: en-gb
update_2.8.13: done
update_runway_2.8.15: done
headerScheme: dark
update_runway_2.8.13: done
latest_version:
on_sale_version:
dashboard: 0
hide_pwd_reset: 1
content_hideNonEditableRegions: 0
content_frontend_edit: 1
logoPath: /perch/resources/logo.png
perch_blog_update: 5.6
perch_blog_post_url: /blog/post.php?s={postSlug}
update_runway_2.8.16: done
update_runway_2.8.17: done
update_runway_2.8.18: done
update_runway_2.8.24: done
update_runway_2.8.25: done
update_runway_2.8.26: done
update_runway_2.8.27: done
perch_blog_site_name:
perch_blog_slug_format: %Y-%m-%d-{postTitle}
perch_blog_akismet_key:
perch_blog_max_spam_days: 0
perch_blog_comment_notify: 0
update_runway_2.8.30: done
jw_activity_log_prune_time: 30
update_runway_2.8.31: done
update_runway_2.8.32: done
update_runway_3.0.8: done
update_runway_3.0.14: done
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /Users/stephen/Repositories/my-site
PERCH_DB_USERNAME: root
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: XXXXXX_
PERCH_DB_PREFIX: perch2_
PERCH_EMAIL_FROM: XXXXXXX
PERCH_EMAIL_FROM_NAME: XXXXXXXXX
PERCH_LOGINPATH: /perch
PERCH_PATH: /Users/stephen/Repositories/my-site/perch
PERCH_CORE: /Users/stephen/Repositories/my-site/perch/core
PERCH_RESFILEPATH: /Users/stephen/Repositories/my-site/perch/resources
PERCH_RESPATH: /perch/resources
PERCH_HTML5: 1
PERCH_TZ: Europe/London
PERCH_RWD: 1
PERCH_SCHEDULE_SECRET: XXXXXXXXXXXXX
PERCH_CLEAN_RESOURCES:
PERCH_EMAIL_METHOD: smtp
PERCH_EMAIL_HOST: smtp.mailtrap.io
PERCH_EMAIL_SECURE: tls
PERCH_EMAIL_AUTH: 1
PERCH_EMAIL_PORT: 2525
PERCH_EMAIL_USERNAME: XXXXXXXXXXXXX
PERCH_GMAPS_API_KEY: XXXXXXXXXXXXX
PERCH_PRODUCTION_MODE: 10
PERCH_DEBUG: 1
PERCH_RUNWAY: 1
PERCH_ERROR_MODE: DIE
PERCH_DATE_LONG: %d %B %Y
PERCH_DATE_SHORT: %d %b %Y
PERCH_TIME_SHORT: %H:%M
PERCH_TIME_LONG: %H:%M:%S
PERCH_RUNWAY_ROUTED:
PERCH_STRONG_PASSWORDS:
PERCH_ASSET_VERSION: f704e7442d11292c99b5
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /Users/stephen/Repositories/my-site/perch/templates
PERCH_TEMPLATE_FILTERS:
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_XHTML_MARKUP:
PERCH_HTML_ENTITIES:
PERCH_SSL:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_PARANOID:
PERCH_FORCE_SECURE_COOKIES:
PERCH_DEFAULT_BUCKET: default
PERCH_TRANSLATION_ASSIST:
PERCH_PASSWORD_MIN_LENGTH: 6
PERCH_MAX_FAILED_LOGINS: 10
PERCH_AUTH_LOCKOUT_DURATION: 1 HOUR
PERCH_VERIFY_UPLOADS:
PERCH_PRIV_ASSIST:
PERCH_CUSTOM_EDITOR_CONFIGS:
PERCH_ENABLE_EXIF: 1
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: markdown

Hosting settings

PHP: 7.2.1
Zend: 3.2.0
OS: Darwin
SAPI: cgi-fcgi
Safe mode: not detected
MySQL client: mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
MySQL server: 5.6.38
Free disk space: 28.98 GB
Extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bcmath, bz2, calendar, ctype, curl, dom, hash, fileinfo, filter, ftp, gd, SPL, iconv, intl, json, ldap, mbstring, session, standard, mysqlnd, PDO, pdo_mysql, pdo_sqlite, Phar, posix, readline, Reflection, mysqli, SimpleXML, soap, sockets, sodium, exif, tokenizer, wddx, xml, xmlreader, xmlwriter, xsl, zip, cgi-fcgi, imap, gettext, pgsql, pdo_pgsql, igbinary, memcached
GD: Yes
ImageMagick: No
PHP max upload size: 32M
PHP max form post size: 8M
PHP memory limit: 128M
Total max uploadable file size: 8M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
PHP_FCGI_CHILDREN: 4
PWD: /Applications/MAMP/fcgi-bin
PHP_FCGI_MAX_REQUESTS: 200
__CF_USER_TEXT_ENCODING: 0x1F5:0x0:0x2
ORIG_SCRIPT_NAME: /fcgi-bin/php7.2.1.fcgi
ORIG_PATH_TRANSLATED: /Users/stephen/Repositories/my-site/perch/core/settings/diagnostics/index.php
ORIG_PATH_INFO: /perch/core/settings/diagnostics/index.php
ORIG_SCRIPT_FILENAME: /Applications/MAMP/fcgi-bin/php7.2.1.fcgi
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
REQUEST_URI: /perch/core/settings/diagnostics/?extended
QUERY_STRING: extended
REQUEST_METHOD: GET
SERVER_PROTOCOL: HTTP/1.1
GATEWAY_INTERFACE: CGI/1.1
REDIRECT_URL: /perch/core/settings/diagnostics/index.php
REDIRECT_QUERY_STRING: extended
REMOTE_PORT: 63565
SCRIPT_FILENAME: /Users/stephen/Repositories/my-site/perch/core/settings/diagnostics/index.php
SERVER_ADMIN: you@example.com
DOCUMENT_ROOT: /Users/stephen/Repositories/my-site
REMOTE_ADDR: ::1
SERVER_PORT: 80
SERVER_ADDR: ::1
SERVER_NAME: my-site.test
SERVER_SOFTWARE: Apache
PATH: /usr/bin:/bin:/usr/sbin:/sbin
HTTP_COOKIE: _ga=GA1.2.28092986.1521123071; _gid=GA1.2.545873990.1521123071; cmsa=1; PHPSESSID=3eonbs8191nl2cdms15357k2ie
HTTP_ACCEPT_LANGUAGE: en-US,en;q=0.9
HTTP_ACCEPT_ENCODING: gzip, deflate
HTTP_REFERER: http://my-site.test/perch/core/settings/diagnostics/
HTTP_DNT: 1
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Safari/537.36
HTTP_UPGRADE_INSECURE_REQUESTS: 1
HTTP_CONNECTION: keep-alive
HTTP_HOST: my-site.test
REDIRECT_STATUS: 200
REDIRECT_HANDLER: php-fastcgi
FCGI_ROLE: RESPONDER
PHP_SELF: /perch/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1521196003.8488
REQUEST_TIME: 1521196003
argc: 1

Hi Hussein,

Thanks for showing an interest in this :)

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 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_DEVELOPMENT I spotted a few slow areas of the page template.

Drew McLellan

Drew McLellan 2636 points
Perch Support

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.

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.

I can see the perch2_collection index table. It displays rows 1 - 3000 of 205,383. Is that a lot?

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-index to the rest, if you'd like to optimise that. The less you index, the faster things will go.

I'm not sure what you mean?

In Sequel Pro, select the perch2_collection_index table, 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? Perch Screenshot

It worthwhile making sure you're only indexing the fields you need to sort or filter by, and adding no-index to the rest

Is this something I'd do in Perch? Or at the database level?

Drew McLellan

Drew McLellan 2636 points
Perch Support

You'd add that to your template tag

<perch:content id="title" type="text" no-index="true" />

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:

... /perch_blog/update/ and run the updater, which will reindex the posts...

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.

Drew McLellan

Drew McLellan 2636 points
Perch Support

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.