Forum

Thread tagged as: Problem, Shop

Slow request and process times

Hello,

I'm using Runway and the Shop app. On my development environment things are running fine. However, on a live server pages that display a product or a list of products are taking longer than expected to load; similar to Mike Harrison's issue.

On the product page, I am getting up to 7s request time on the live server and under a second on my development environment. It is not a light page by any means, I'm using (apart from global layouts and a shared region) perch_shop_product, perch_shop_products, perch_shop_variants, perch_shop_brand and perch_collection.

I tired removing everything except perch_shop_product to display the product, the global layouts and the shared region. I'm getting 1-2s request and process times (0.1s on development environment).

Is there anything I could do concerning Runway's setup?

Diagnostics report:

Perch Runway: 2.8.34
Production mode: Production (100)
Installed apps: content (2.8.34), assets (2.8.34), categories (2.8.34), perch_gallery (2.8.6), perch_shop_orders (1.0.12), perch_shop_products (1.0.12), perch_shop (1.0.12), perch_members (1.5), perch_mailchimp (3.0.1)
DB driver: PDO
DB tables: perch2_backup_plans (0), perch2_backup_resources (0), perch2_backup_runs (0), perch2_categories (1), perch2_category_counts (0), perch2_category_sets (1), perch2_collection_index (104), perch2_collection_items (19), perch2_collection_revisions (6), perch2_collections (1), perch2_content_index (125), perch2_content_items (25), perch2_content_regions (8), perch2_gallery_albums (1), perch2_gallery_image_versions (48), perch2_gallery_images (8), perch2_mailchimp_campaigns (0), perch2_mailchimp_imports (0), perch2_mailchimp_lists (1), perch2_mailchimp_subscribers (2), perch2_mailchimp_subscriptions (2), perch2_mailchimp_webhooks (1), perch2_members (2), perch2_members_forms (1), perch2_members_member_tags (0), perch2_members_sessions (0), perch2_members_tags (0), perch2_navigation (1), perch2_navigation_pages (3), perch2_page_routes (3), perch2_page_templates (12), perch2_pages (11), perch2_resource_log (72), perch2_resource_tags (0), perch2_resources (79), perch2_resources_to_tags (0), perch2_settings (34), perch2_shop_addresses (4), perch2_shop_admin_index (32), perch2_shop_brands (2), perch2_shop_cart (50), perch2_shop_cart_data (12), perch2_shop_cart_items (9), perch2_shop_countries (250), perch2_shop_currencies (152), perch2_shop_customers (2), perch2_shop_emails (1), perch2_shop_index (2196), perch2_shop_option_values (9), perch2_shop_options (2), perch2_shop_order_items (4), perch2_shop_order_promotions (0), perch2_shop_order_statuses (9), perch2_shop_orders (2), perch2_shop_orders_meta (1), perch2_shop_product_files (0), perch2_shop_product_option_values (29), perch2_shop_product_options (12), perch2_shop_product_tags (0), perch2_shop_products (39), perch2_shop_promotions (0), perch2_shop_search (7), perch2_shop_shipping_zone_countries (1), perch2_shop_shipping_zones (2), perch2_shop_shippings (1), perch2_shop_tax_exhibits (8), perch2_shop_tax_group_rates (4), perch2_shop_tax_groups (2), perch2_shop_tax_locations (2), perch2_shop_tax_rates (3), perch2_shop_variants (66), perch2_user_passwords (1), perch2_user_privileges (71), perch2_user_role_privileges (16), perch2_user_roles (2), perch2_users (2)
Users: 2
PHPMailer: 5.2.21
App runtimes:
<?php
    $apps_list = array(
        'content', 
        'categories',
        'perch_members',
        'perch_shop',
        'perch_gallery',
        'perch_mailchimp',
    );
Scheduled tasks for perch_mailchimp: import_data (1 mins)
Editor plug-ins: markitup
H1: 9ab69e06985262d9b7bd94ec13724bf3
L1: a3483b8220ff33d6f5fe1e3f26bc293a
F1: 6a33f95eca3667f9e0c39bf5ca2980fe
headerColour: #000000
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 0
content_collapseList: 1
lang: en-gb
update_2.8.34: done
perch_shop_default_currency: 8
headerScheme: dark
update_runway_2.8.34: done
latest_version: 2.8.15
on_sale_version: 2.8.34
perch_gallery_update: 2.8.5
perch_shop_update: 1.0.12
perch_gallery_bucket_mode: single
perch_gallery_bucket: default
perch_members_login_page: /members/login.php?r={returnURL}
perch_mailchimp_api_key: ######
perch_mailchimp_campaign_url: /mailchimp/campaign/{campaignSlug}
perch_shop_price_tax_mode: exc
perch_shop_trade_price_tax_mode: exc
perch_shop_site_url: https://
perch_shop_product_url: /products/{slug}
perch_shop_invoice_number_format: Invoice%d
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
perch_gallery_basicUpload: 0
perch_shop_reporting_currency: 8
perch_mailchimp_secret: 7020639
perch_members_update: 1.4
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /home/hvnhellc/public_html
PERCH_SCHEDULE_SECRET: MFYXGU6idyPf8t4U
PERCH_DB_USERNAME: hvnhellc_dev
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: hvnhellc_cms
PERCH_DB_PREFIX: perch2_
PERCH_EMAIL_FROM: info@hvn-hell.com
PERCH_EMAIL_FROM_NAME: Heaven and Hell
PERCH_LOGINPATH: /perch
PERCH_PATH: /home/hvnhellc/public_html/perch
PERCH_CORE: /home/hvnhellc/public_html/perch/core
PERCH_RESFILEPATH: /home/hvnhellc/public_html/perch/resources
PERCH_RESPATH: /perch/resources
PERCH_HTML5: 1
PERCH_TZ: Australia/Adelaide
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_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /home/hvnhellc/public_html/perch/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_PRODUCTION_MODE: 100
PERCH_RWD:
PERCH_HTML_ENTITIES:
PERCH_SSL:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_PARANOID:
PERCH_FORCE_SECURE_COOKIES:
PERCH_PASSWORD_MIN_LENGTH: 6
PERCH_MAX_FAILED_LOGINS: 10
PERCH_AUTH_LOCKOUT_DURATION: 1 HOUR
PERCH_VERIFY_UPLOADS:
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
PERCH_SESSION_TIMEOUT_MINS: 20
PERCH_SHOP_VERSION: 1.0.12
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: textile
HOSTING SETTINGS

PHP: 5.5.38
Zend: 2.5.0
OS: Linux
SAPI: cgi-fcgi
Safe mode: not detected
MySQL client: 5.6.23
MySQL server: 5.6.23-cll-lve
Free disk space: 248.14 GB
Extensions: Core, date, ereg, libxml, openssl, pcre, sqlite3, zlib, bz2, calendar, ctype, curl, hash, filter, ftp, gettext, gmp, SPL, iconv, pcntl, readline, Reflection, session, standard, shmop, SimpleXML, mbstring, tokenizer, xml, cgi-fcgi, bcmath, dom, fileinfo, gd, imap, intl, json, exif, mcrypt, mysql, mysqli, PDO, pdo_mysql, pdo_pgsql, pdo_sqlite, pgsql, Phar, posix, soap, sockets, tidy, xmlreader, xmlrpc, xmlwriter, xsl, zip, mhash, ionCube Loader, Zend OPcache, Zend Guard Loader
GD: Yes
ImageMagick: No
PHP max upload size: 128M
PHP max form post size: 128M
PHP memory limit: 768M
Total max uploadable file size: 128M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
DOCUMENT_ROOT: /home/hvnhellc/public_html
GATEWAY_INTERFACE: CGI/1.1
HTTPS: on
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
HTTP_ACCEPT_ENCODING: gzip, deflate, sdch, br
HTTP_ACCEPT_LANGUAGE: en-GB,en-US;q=0.8,en;q=0.6,ar;q=0.4
HTTP_CONNECTION: keep-alive
HTTP_COOKIE: p_m=; geo=United+Arab+Emirates; cmsa=1; PHPSESSID=4ftlgpuat34haopklm7s0obkn4
HTTP_HOST: hvn-hell.com
HTTP_REFERER: https://hvn-hell.com/perch/core/settings/diagnostics/
HTTP_UPGRADE_INSECURE_REQUESTS: 1
HTTP_USER_AGENT: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36
PATH: /bin:/usr/bin
QUERY_STRING: extended
REDIRECT_STATUS: 200
REMOTE_ADDR: 92.98.242.41
REMOTE_PORT: 63369
REQUEST_METHOD: GET
REQUEST_URI: /perch/core/settings/diagnostics/?extended
SCRIPT_FILENAME: /home/hvnhellc/public_html/perch/core/settings/diagnostics/index.php
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
SERVER_ADDR: 66.198.253.174
SERVER_ADMIN: webmaster@hvn-hell.com
SERVER_NAME: hvn-hell.com
SERVER_PORT: 443
SERVER_PROTOCOL: HTTP/1.1
SERVER_SOFTWARE: Apache
SSL_TLS_SNI: hvn-hell.com
UNIQUE_ID: WKl0XELG8BUAB43m8sYAAAIX
PHP_SELF: /perch/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1487500380.8503
REQUEST_TIME: 1487500380

Product page after removing everything except perch_shop_product and global stuff:

<?php if (!defined('PERCH_RUNWAY')) include($_SERVER['DOCUMENT_ROOT'].'/perch/runtime.php');
    //$title = perch_shop_product(perch_get('s'), [ 'template' => 'products/title.html' ], true);
    perch_layout('global/head', [
        'siteName'=>' - Heaven & Hell',
        //'title'=>$title,
    ]);
?>

<body>
    <div class="page-wrapper">
        <?php 
            perch_layout('global/header', ['nav-level' => 1,]);

            perch_shop_product(perch_get('s'), [
                'template' => 'products/product_page.html',
            ]);

            perch_content('Subscription Form');
        ?>
    </div>
<?php perch_layout('global/footer'); ?>

Debug message:


Debug Message [11] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC, p.pagePath ASC Matched route: products/[slug:cat]/[slug:s] Using master page: /templates/pages/product.php Page arguments: Array ( [0] => /products/heaven-hell-vol-1/103 [cat] => heaven-hell-vol-1 [1] => heaven-hell-vol-1 [s] => 103 [2] => 103 ) [1] SELECT * FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1 [1] SELECT * FROM perch2_shop_cart WHERE cartID=53 [1] SELECT * FROM perch2_shop_cart WHERE cartID=53 Using template: /templates/pages/attributes/default.html Using sub-template: /templates/pages/attributes/seo.html [3] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC [1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1 [2] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-002-001', '000-002', '000') ORDER BY pageTreePosition DESC [3] Using template: /templates/navigation/item.html [1] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='_id' WHERE 1=1 AND ((idx.indexKey='slug' AND idx.indexValue=103)) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC [33] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0 [1] Using template: /templates/shop/products/product_page.html [6] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=12 AND productDeleted IS NULL ORDER BY productOrder ASC [2] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=12 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC [3] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC [2] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='12' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC [6] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=2 AND valueDeleted IS NULL ORDER BY valueOrder ASC [2] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='12' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='2' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=8 LIMIT 1 [1] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/products/product' OR regionPage='*' ORDER BY regionPage DESC [1] SELECT groupID FROM perch2_navigation WHERE groupSlug='footer' LIMIT 1 [3] SELECT np.pageID, np.pageParentID, p.pagePath, p.pageTitle, p.pageNavText, p.pageNew, p.pageOrder, np.pageDepth, p.pageSortPath, np.pageTreePosition, p.pageAccessTags, p.pageAttributes FROM perch2_navigation_pages np, perch2_pages p WHERE p.pageID=np.pageID AND np.groupID=1 AND p.pageNew=0 ORDER BY np.pageTreePosition ASC [0] SELECT np.pageTreePosition FROM perch2_pages p, perch2_navigation_pages np WHERE np.pageID=p.pageID AND np.groupID=1 AND p.pagePath='/products/product' LIMIT 1 [3] Using template: /templates/navigation/item-footer.html Request time: 1.5327 Process time: 1.5319 Memory: 2.2256
Hussein Al Hammad

Hussein Al Hammad 41 points

  • 1 year ago
Drew McLellan

Drew McLellan 2517 points
Perch Support

Is the data the same between the two environments?

Nothing majorly different. Number of products is different, but low in both environments. Slightly different text and some products have more pictures on the live server.

There's one thing that is set up differently. The products are sequentially named 101, 102, 103, etc (manually entered). SKU and Title have the same values (e.g. both are 102). Locally I used the automatically generated slugs SKU-title (e.g. 102-102), but manually changed them to only include the title (e.g. 102) for cleaner URLs.

Do you think this might be the source of the problem?

Drew McLellan

Drew McLellan 2517 points
Perch Support

That shouldn't make a difference at all.

Are you able to switch in to dev mode? If you do that the debug will include various timing points which might point to the cause of the slowness.

define('PERCH_PRODUCTION_MODE', PERCH_DEVELOPMENT);

https://docs.grabaperch.com/perch/configuration/perch/

I was wondering how to display the timing points!

Here is the debug messages for a couple of products on the live server:

1


Time Δ Debug Message 0.1188 0 [11] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC, p.pagePath ASC 0.1233 0.0045 Matched route: products/[slug:cat]/[slug:s] 0.1236 0.0003 Using master page: /templates/pages/product.php 0.1237 0 Page arguments: Array ( [0] => /products/heaven-hell-vol-1/101 [cat] => heaven-hell-vol-1 [1] => heaven-hell-vol-1 [s] => 101 [2] => 101 ) 0.1361 0.0124 [1] SELECT * FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1 0.1518 0.0157 [1] SELECT * FROM perch2_shop_cart WHERE cartID=57 0.1524 0.0006 [1] SELECT * FROM perch2_shop_cart WHERE cartID=57 0.1542 0.0018 Using template: /templates/pages/attributes/default.html 0.1546 0.0003 Using sub-template: /templates/pages/attributes/seo.html 0.159 0.0044 [3] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC 0.1597 0.0007 [1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1 0.1601 0.0004 [2] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-002-001', '000-002', '000') ORDER BY pageTreePosition DESC 0.1606 0.0005 [3] Using template: /templates/navigation/item.html 0.1647 0.0041 [1] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='_id' WHERE 1=1 AND ((idx.indexKey='slug' AND idx.indexValue=101)) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC 0.8266 0.6619 [33] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0 0.8646 0.038 [1] Using template: /templates/shop/products/product_page.html 0.8656 0.0011 [6] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=1 AND productDeleted IS NULL ORDER BY productOrder ASC 0.8684 0.0028 [2] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=1 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC 0.8712 0.0028 [3] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC 0.8717 0.0005 [2] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='1' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC 0.8931 0.0214 [6] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=2 AND valueDeleted IS NULL ORDER BY valueOrder ASC 0.8938 0.0007 [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='1' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='2' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC 0.9317 0.0379 [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=8 LIMIT 1 0.9388 0.007 [1] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/products/product' OR regionPage='*' ORDER BY regionPage DESC 0.9411 0.0023 [1] SELECT groupID FROM perch2_navigation WHERE groupSlug='footer' LIMIT 1 0.9414 0.0003 [3] SELECT np.pageID, np.pageParentID, p.pagePath, p.pageTitle, p.pageNavText, p.pageNew, p.pageOrder, np.pageDepth, p.pageSortPath, np.pageTreePosition, p.pageAccessTags, p.pageAttributes FROM perch2_navigation_pages np, perch2_pages p WHERE p.pageID=np.pageID AND np.groupID=1 AND p.pageNew=0 ORDER BY np.pageTreePosition ASC 0.942 0.0006 [0] SELECT np.pageTreePosition FROM perch2_pages p, perch2_navigation_pages np WHERE np.pageID=p.pageID AND np.groupID=1 AND p.pagePath='/products/product' LIMIT 1 0.9426 0.0006 [3] Using template: /templates/navigation/item-footer.html 0.9439 0.0014 Request time: 0.9439 0.944 0 Process time: 0.9428 0.944 0 Memory: 2.2956

2

Time    Δ   Debug Message
0.066   0   [11] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC, p.pagePath ASC
0.0686  0.0026  Matched route: products/[slug:cat]/[slug:s]
0.0687  0.0001  Using master page: /templates/pages/product.php
0.0688  0   Page arguments:
Array
(
    [0] => /products/heaven-hell-vol-1/105
    [cat] => heaven-hell-vol-1
    [1] => heaven-hell-vol-1
    [s] => 105
    [2] => 105
)
0.075   0.0063  [1] SELECT * FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1
0.0829  0.0079  [1] SELECT * FROM perch2_shop_cart WHERE cartID=57
0.0835  0.0006  [1] SELECT * FROM perch2_shop_cart WHERE cartID=57
0.0842  0.0007  Using template: /templates/pages/attributes/default.html
0.0844  0.0002  Using sub-template: /templates/pages/attributes/seo.html
0.0868  0.0024  [3] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
0.0872  0.0004  [1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1
0.0874  0.0002  [2] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-002-001', '000-002', '000') ORDER BY pageTreePosition DESC
0.0877  0.0003  [3] Using template: /templates/navigation/item.html
0.0897  0.002   [1] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='_id' WHERE 1=1 AND ((idx.indexKey='slug' AND idx.indexValue=105)) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC
0.365   0.2753  [33] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
0.6625  0.2975  [1] Using template: /templates/shop/products/product_page.html
0.663   0.0006  [6] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=26 AND productDeleted IS NULL ORDER BY productOrder ASC
0.6643  0.0013  [2] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=26 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
0.6663  0.002   [3] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
0.6668  0.0005  [2] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='26' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
1.0182  0.3514  [6] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=2 AND valueDeleted IS NULL ORDER BY valueOrder ASC
1.0188  0.0006  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='26' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='2' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
1.3576  0.3387  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=8 LIMIT 1
1.3643  0.0067  [1] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/products/product' OR regionPage='*' ORDER BY regionPage DESC
1.3659  0.0016  [1] SELECT groupID FROM perch2_navigation WHERE groupSlug='footer' LIMIT 1
1.3662  0.0003  [3] SELECT np.pageID, np.pageParentID, p.pagePath, p.pageTitle, p.pageNavText, p.pageNew, p.pageOrder, np.pageDepth, p.pageSortPath, np.pageTreePosition, p.pageAccessTags, p.pageAttributes FROM perch2_navigation_pages np, perch2_pages p WHERE p.pageID=np.pageID AND np.groupID=1 AND p.pageNew=0 ORDER BY np.pageTreePosition ASC
1.3667  0.0005  [0] SELECT np.pageTreePosition FROM perch2_pages p, perch2_navigation_pages np WHERE np.pageID=p.pageID AND np.groupID=1 AND p.pagePath='/products/product' LIMIT 1
1.3671  0.0004  [3] Using template: /templates/navigation/item-footer.html
1.3679  0.0008  Request time: 1.3679
1.3679  0   Process time: 1.3673
1.368   0   Memory: 2.219

And here's one from the development environment for comparison:


Time Δ Debug Message 0.0366 0 [15] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC, p.pagePath ASC 0.0476 0.0111 Matched route: products/[slug:cat]/[slug:s] 0.0477 0.0001 Using master page: \templates\pages\product.php 0.0477 0 Page arguments: Array ( [0] => /products/collection-i/101-101 [cat] => collection-i [1] => collection-i [s] => 101-101 [2] => 101-101 ) 0.0509 0.0031 [1] SELECT * FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1 0.0554 0.0046 [1] SELECT * FROM perch2_shop_cart WHERE cartID=279 0.0557 0.0003 [1] SELECT * FROM perch2_shop_cart WHERE cartID=279 0.0569 0.0012 Using template: \templates\pages\attributes\default.html 0.0578 0.0009 Using sub-template: \templates\pages/attributes/seo.html 0.0602 0.0024 [3] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC 0.0606 0.0004 [1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1 0.0608 0.0002 [2] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-004-001', '000-004', '000') ORDER BY pageTreePosition DESC 0.0613 0.0004 [3] Using template: \templates\navigation\item.html 0.0635 0.0022 [1] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='_id' WHERE 1=1 AND ((idx.indexKey='slug' AND idx.indexValue='101-101')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC 0.0744 0.011 [34] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0 0.0796 0.0051 [1] Using template: \templates\shop\products\product_page.html 0.0805 0.0009 [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=1 AND productDeleted IS NULL ORDER BY productOrder ASC 0.0821 0.0016 [2] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=1 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC 0.0836 0.0015 [3] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=2 AND valueDeleted IS NULL ORDER BY valueOrder ASC 0.084 0.0004 [2] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='1' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='2' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC 0.0869 0.0029 [3] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC 0.0872 0.0003 [2] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='1' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC 0.0927 0.0055 [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=8 LIMIT 1 0.0956 0.0028 [4] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/products/product' OR regionPage='*' ORDER BY regionPage DESC 0.0972 0.0016 [1] SELECT groupID FROM perch2_navigation WHERE groupSlug='footer' LIMIT 1 0.0973 0.0002 [4] SELECT np.pageID, np.pageParentID, p.pagePath, p.pageTitle, p.pageNavText, p.pageNew, p.pageOrder, np.pageDepth, p.pageSortPath, np.pageTreePosition, p.pageAccessTags, p.pageAttributes FROM perch2_navigation_pages np, perch2_pages p WHERE p.pageID=np.pageID AND np.groupID=1 AND p.pageNew=0 ORDER BY np.pageTreePosition ASC 0.0976 0.0003 [0] SELECT np.pageTreePosition FROM perch2_pages p, perch2_navigation_pages np WHERE np.pageID=p.pageID AND np.groupID=1 AND p.pagePath='/products/product' LIMIT 1 0.0979 0.0003 [4] Using template: \templates\navigation\item-footer.html 0.1 0.002 Request time: 0.1 0.1 0 Process time: 0.0981 0.1 0 Memory: 4.5886
Drew McLellan

Drew McLellan 2517 points
Perch Support

Is there any impact if you add this to your options?

'filter-mode' => 'legacy-group',

Adding the filter-mode to the options doesn't seem to have changed much:

Time    Δ   Debug Message
0.083   0   [11] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC, p.pagePath ASC
0.0865  0.0035  Matched route: products/[slug:cat]/[slug:s]
0.0867  0.0001  Using master page: /templates/pages/product.php
0.0867  0   Page arguments:
Array
(
    [0] => /products/heaven-hell-vol-1/102
    [cat] => heaven-hell-vol-1
    [1] => heaven-hell-vol-1
    [s] => 102
    [2] => 102
)
0.0939  0.0072  [1] SELECT * FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1
0.1022  0.0083  [1] SELECT * FROM perch2_shop_cart WHERE cartID=58
0.1029  0.0007  [1] SELECT * FROM perch2_shop_cart WHERE cartID=58
0.1037  0.0008  Using template: /templates/pages/attributes/default.html
0.1039  0.0002  Using sub-template: /templates/pages/attributes/seo.html
0.1063  0.0024  [3] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
0.1073  0.001   [1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/products/product' LIMIT 1
0.1076  0.0003  [2] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-002-001', '000-002', '000') ORDER BY pageTreePosition DESC
0.108   0.0004  [3] Using template: /templates/navigation/item.html
0.1102  0.0022  [1] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='_id' WHERE 1=1 AND ((idx.indexKey='slug' AND idx.indexValue=102)) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, productID ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC
0.6568  0.5466  [33] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
0.9628  0.306   [1] Using template: /templates/shop/products/product_page.html
0.9636  0.0009  [3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=8 AND productDeleted IS NULL ORDER BY productOrder ASC
0.9653  0.0017  [2] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=8 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
0.9683  0.003   [3] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
0.9692  0.0009  [1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='8' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
1.2808  0.3116  [6] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=2 AND valueDeleted IS NULL ORDER BY valueOrder ASC
1.2817  0.0009  [1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='8' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='2' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
1.6174  0.3357  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=8 LIMIT 1
1.6218  0.0044  [1] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/products/product' OR regionPage='*' ORDER BY regionPage DESC
1.6235  0.0017  [1] SELECT groupID FROM perch2_navigation WHERE groupSlug='footer' LIMIT 1
1.6238  0.0003  [3] SELECT np.pageID, np.pageParentID, p.pagePath, p.pageTitle, p.pageNavText, p.pageNew, p.pageOrder, np.pageDepth, p.pageSortPath, np.pageTreePosition, p.pageAccessTags, p.pageAttributes FROM perch2_navigation_pages np, perch2_pages p WHERE p.pageID=np.pageID AND np.groupID=1 AND p.pageNew=0 ORDER BY np.pageTreePosition ASC
1.6243  0.0005  [0] SELECT np.pageTreePosition FROM perch2_pages p, perch2_navigation_pages np WHERE np.pageID=p.pageID AND np.groupID=1 AND p.pagePath='/products/product' LIMIT 1
1.6246  0.0004  [3] Using template: /templates/navigation/item-footer.html
1.6254  0.0008  Request time: 1.6254
1.6255  0   Process time: 1.6245
1.6255  0   Memory: 2.2203
Drew McLellan

Drew McLellan 2517 points
Perch Support

Is the production server shared with other sites? I'm wondering what might impact its performance. Anything in the MySQL logs?

Yes, it is a shared server. Since it's a shared server I don't have access to the MySQL logs. I've contacted the hosting company's support team and they're looking into it. I'll let you know how things go.

By the way, I just found out my client's web host is recommended by Perch. I realise the recommendation does not include the use of Runway or the Shop app on there, so not sure if this piece of information is even helpful.

Drew McLellan

Drew McLellan 2517 points
Perch Support

It would be interesting to hear what they suggest.

Prior to giving them access I switched back to the full product page that takes ~7s to load. They confirmed how slow it is (took ~9s TTFB for them).

They checked the load on the server:

"The load on the server is consistently below normal which indicates your memory and CPU consumption are within reasonable limits for the server."

They also used GTmetrix to test the product page's performance, gave me a link to the test's results and advised me to follow the suggestions on there. The suggestions included:

  • Avoid scaling images in HTML or CSS
  • Leverage browser caching for cacheable resources (CSS, JS and images)
  • Use CDN
  • Reduce number of DOM elements

The remainder of their response:

"We also identify some wait time for your account. Wait time is the time between the moment the server acknowledges the request, and the time that the browser receives the first byte which contains the start of the response headers from the server. This step includes script processing and database calls.

Some scripts return the response header before any processing or database calls; this is generally indicated by an extremely short wait time of less than 10 milliseconds. When headers are sent before content, the time that would normally be in the wait time is added to the receiving or download time.

You can make most improvements in wait time at the application level. How the page is rendered in the code and how the site runs database queries are the largest factors in improving performance."

Drew McLellan

Drew McLellan 2517 points
Perch Support

Right, basically that's just saying what we already know, which is that the page is taking a while to compile.

Yep exactly. That's why I got in touch with them again and they were happy to look into it again. They ended up tweaking the MySQL settings which has drastically improved the performance.

I'm now getting between 0.5s and 2s request time for the full product page that used to get over 7s.

Drew McLellan

Drew McLellan 2517 points
Perch Support

That sounds promising!

Could you ask your host to share the configuration changes? Had the opposite problem. Very slow on local and fast on shared hosting.