Forum
Shop Performance
Hi,
I have been developing a new online shop for a client and I have noticed that as the products have been added the saving, updating, generating variants and editing variants have slowed to a crawl. At times taking up to 30 seconds to save, though averaging around 17-20 seconds.
Not only this but while this action is completing the front of the site is locking up too, it would seem there is some serious blocking when it comes to the database. I am not sure it is the server as the site is hosted on a reasonable powerful VPS with few other sites to contest resources with. Additionally, saving standard content for pages is pretty much instantaneous.
The shop contains around 180 products, with variants resulting in 688 rows on the shop_products table. The shop_index table contains 23,150 rows - would this be considered normal for these numbers? The same locking action happens when saving Brands as well, there are only 6 of those stored.
The response times for reading data from the shop is fast, and seems faster since the 1.0.10 update, this is only occuring when saving a product / brand.
What would you suggest is the reason for the locking up? Do we need to provide additional resources to the shop or is there an error with the shop indexing process to cause this?
Diagnostics:
Diagnostics report
PERCH INFORMATION
Perch Runway: 2.8.32
Production mode: Production (100)
Installed apps: content (2.8.32), assets (2.8.32), categories (2.8.32), root_analytics (1.0.1), perch_forms (1.8.3), perch_shop_orders (1.0.10), perch_shop_products (1.0.10), perch_shop (1.0.10), perch_members (1.5), root_artithmetic (1), root_breadcrumbs (1.0), perch_mailchimp (3.0.1)
DB driver: PDO
DB tables: perch2_backup_plans (0), perch2_backup_resources (0), perch2_backup_runs (0), perch2_categories (29), perch2_category_counts (0), perch2_category_sets (4), perch2_collection_index (0), perch2_collection_items (0), perch2_collection_revisions (0), perch2_collections (0), perch2_content_index (2013), perch2_content_items (252), perch2_content_regions (45), perch2_forms (2), perch2_forms_responses (1), perch2_mailchimp_campaigns (0), perch2_mailchimp_imports (0), perch2_mailchimp_lists (1), perch2_mailchimp_subscribers (1), perch2_mailchimp_subscriptions (1), perch2_mailchimp_webhooks (1), perch2_members (1), perch2_members_forms (1), perch2_members_member_tags (0), perch2_members_sessions (1), perch2_members_tags (0), perch2_navigation (4), perch2_navigation_pages (19), perch2_page_routes (6), perch2_page_templates (28), perch2_pages (32), perch2_resource_log (2011), perch2_resource_tags (8), perch2_resources (1750), perch2_resources_to_tags (64), perch2_root_analytics_cache (3), perch2_scheduled_tasks (11), perch2_settings (37), perch2_shop_addresses (8), perch2_shop_admin_index (126), perch2_shop_brands (6), perch2_shop_cart (2577), perch2_shop_cart_data (36), perch2_shop_cart_items (11), perch2_shop_countries (250), perch2_shop_currencies (152), perch2_shop_customers (1), perch2_shop_emails (2), perch2_shop_index (23150), perch2_shop_option_values (20), perch2_shop_options (1), perch2_shop_order_items (3), perch2_shop_order_promotions (0), perch2_shop_order_statuses (9), perch2_shop_orders (3), perch2_shop_orders_meta (0), perch2_shop_product_files (0), perch2_shop_product_option_values (541), perch2_shop_product_options (125), perch2_shop_product_tags (0), perch2_shop_products (688), perch2_shop_promotions (0), perch2_shop_search (285), perch2_shop_shipping_zone_countries (1), perch2_shop_shipping_zones (2), perch2_shop_shippings (4), perch2_shop_tax_exhibits (6), perch2_shop_tax_group_rates (1), perch2_shop_tax_groups (1), perch2_shop_tax_locations (1), perch2_shop_tax_rates (1), perch2_shop_variants (541), perch2_user_passwords (1), perch2_user_privileges (74), perch2_user_role_privileges (16), perch2_user_roles (2), perch2_users (1)
Users: 1
App runtimes:
<?php
$apps_list = [
'content',
'categories',
'perch_forms',
'perch_members',
'perch_shop',
'perch_mailchimp',
'root_breadcrumbs',
'root_arithmetic',
];
Scheduled tasks for perch_mailchimp: import_data (1 mins)
Scheduled tasks for root_analytics: root_analytics_fetch_comparisons (1440 mins)
Editor plug-ins: markitup, redactor
H1: 08bfd0c44a6a60ae38bf35a9268e0a07
L1: 48729459af5dd8f62aba38d76e6c3aa1
F1: 2edba60ed1f613d6dd804feb202456a2
headerColour: #23AE9F
content_singlePageEdit: 1
helpURL: mailto:design@rootstudio.co.uk
siteURL: /
hideBranding: 1
content_collapseList: 1
lang: en-gb
update_2.8.31: done
perch_shop_update: 1.0.10
headerScheme: dark
update_runway_2.8.31: done
latest_version: 2.8.15
on_sale_version: 2.8.32
dashboard: 1
hide_pwd_reset: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
logoPath: /admin/resources/little-puds-and-co-logo-with-stars.png
perch_members_login_page: /account?r={returnURL}
perch_shop_price_tax_mode: exc
perch_shop_trade_price_tax_mode: exc
perch_shop_site_url:
perch_shop_product_url: /shop/product/{slug}
perch_shop_default_currency: 47
perch_shop_reporting_currency: 47
perch_shop_invoice_number_format: Order #%d
perch_members_update: 1.4
update_runway_2.8.32: done
perch_mailchimp_api_key:
perch_mailchimp_campaign_url: /mailchimp/campaign/{campaignSlug}
perch_mailchimp_secret: 20e37ed
root_analytics_namespace: Little Puds & Co
root_analytics_view_id:
root_analytics_credentials_json:
root_analytics_time_period: 90
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /home/suzanne/public_html
PERCH_DB_USERNAME: suzanne_perchu
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: suzanne_perch_shop
PERCH_DB_PREFIX: perch2_
PERCH_PRODUCTION_MODE: 100
PERCH_DEBUG:
PERCH_EMAIL_FROM: james@rootstudio.co.uk
PERCH_EMAIL_FROM_NAME: Root Studio
PERCH_LOGINPATH: /admin
PERCH_PATH: /home/suzanne/public_html/admin
PERCH_CORE: /home/suzanne/public_html/admin/core
PERCH_RESFILEPATH: /home/suzanne/public_html/admin/resources
PERCH_RESPATH: /admin/resources
PERCH_HTML5: 1
PERCH_TZ: UTC
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/suzanne/public_html/admin/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
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.10
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: textile
HOSTING SETTINGS
PHP: 5.6.26
Zend: 2.6.0
OS: Linux
SAPI: cgi-fcgi
Safe mode: not detected
MySQL client: mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $
MySQL server: 5.6.34
Free disk space: 80.76 GB
Extensions: Core, date, ereg, libxml, openssl, pcre, sqlite3, zlib, bcmath, calendar, ctype, curl, dom, fileinfo, filter, ftp, gd, hash, iconv, SPL, intl, json, mbstring, mcrypt, session, standard, mysqlnd, Phar, posix, Reflection, mysql, SimpleXML, sockets, imap, tokenizer, xml, xmlreader, xmlwriter, cgi-fcgi, PDO, pdo_sqlite, imagick, pdo_mysql
GD: Yes
ImageMagick: Yes
PHP max upload size: 200M
PHP max form post size: 12M
PHP memory limit: 256M
Total max uploadable file size: 12M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
CONTEXT_DOCUMENT_ROOT: /home/suzanne/public_html
DOCUMENT_ROOT: /home/suzanne/public_html
GATEWAY_INTERFACE: CGI/1.1
HTTPS: on
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
HTTP_ACCEPT_ENCODING: gzip, deflate
HTTP_ACCEPT_LANGUAGE: en-gb
HTTP_CONNECTION: keep-alive
HTTP_COOKIE: _ga=GA1.3.875184230.1479730398; PHPSESSID=64381520a7e050bd454cbf14252dff6d; cpsession=suzanne%3aFd4M1qUYgGPv7tJr%2ccd6028ead345fbfbf92e531688a7fa02; timezone=Europe/London; cmsa=1; cmssb=0; p_m=856c543c74da4490b1c4c0441724ca47c9395c7d; cookieconsent_dismissed=yes
HTTP_HOST:
HTTP_REFERER:
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12) AppleWebKit/602.1.50 (KHTML, like Gecko) Version/10.0 Safari/602.1.50
PATH: /bin:/usr/bin
QUERY_STRING: extended
REDIRECT_STATUS: 200
REMOTE_ADDR: 81.130.131.129
REMOTE_PORT: 51476
REQUEST_METHOD: GET
REQUEST_SCHEME: https
REQUEST_URI: /admin/core/settings/diagnostics/?extended
SCRIPT_FILENAME: /home/suzanne/public_html/admin/core/settings/diagnostics/index.php
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
SERVER_ADDR: 162.251.83.56
SERVER_ADMIN:
SERVER_NAME:
SERVER_PORT: 443
SERVER_PROTOCOL: HTTP/1.1
SERVER_SOFTWARE: Apache
SSL_TLS_SNI:
UNIQUE_ID:
PHP_SELF: /admin/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1479745901.66
REQUEST_TIME: 1479745901
argc: 1
Additionally, here are some debug logs of saving brands:
SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='a4b630e3a7be45f4fd83235ef083966b' LIMIT 1
UPDATE perch2_users SET userHash='a096f684a671220f6bf7632adb558a67' WHERE userID='1'
[74] SELECT p.privKey FROM perch2_user_privileges p
[37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
Adding: Arithmetic
Adding: Breadcrumbs
[nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
[1] SELECT * FROM perch2_shop_brands WHERE brandID='6' AND brandDeleted IS NULL LIMIT 1
Using template: /templates/shop/brands/brand.html
[1] SELECT * FROM perch2_resources WHERE resourceID='98' LIMIT 1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='98' AND resourceKey='thumb'
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='98' AND resourceTargetWidth=361 AND resourceTargetHeight=361 AND resourceCrop=0 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='98' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=0 AND resourceDensity=1
Logging resources for perch_shop
DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='brandID' AND itemRowID=6
INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','brandID',6,98),('perch_shop','brandID',6,99),('perch_shop','brandID',6,100),('perch_shop','brandID',6,101)
UPDATE perch2_shop_brands SET brandDynamicFields='{\"title\":\"Powell Craft\",\"_title\":\"Powell Craft\",\"slug\":\"powell-craft\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>Powell Craft Ltd has been a family-run business since 1962. The product range has grown and diversified over the years. The last decade has seen the launch of a wide selection of childrenswear & babywear inspired by using vintage prints and fabrics. Just adorable and excellent quality.<\\/p>\",\"processed\":\"<p>Powell Craft Ltd has been a family-run business since 1962. The product range has grown and diversified over the years. The last decade has seen the launch of a wide selection of childrenswear & babywear inspired by using vintage prints and fabrics. Just adorable and excellent quality.<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"\",\"processed\":\"\"},\"url\":null,\"image\":{\"assetID\":\"98\",\"title\":\"Powell craft\",\"_default\":\"\\/admin\\/resources\\/brands\\/powell-craft.png\",\"bucket\":\"brands\",\"path\":\"powell-craft.png\",\"size\":47991,\"w\":512,\"h\":512,\"mime\":\"image\\/png\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"powell-craft-thumb@2x.png\",\"size\":51669,\"mime\":\"\",\"assetID\":\"99\"},\"w361h361c0\":{\"w\":\"361\",\"h\":\"361\",\"target_w\":\"361\",\"target_h\":\"361\",\"crop\":false,\"density\":\"1\",\"path\":\"powell-craft-w361h361.png\",\"size\":72333,\"mime\":\"\",\"assetID\":\"100\"},\"w512h512c0\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":false,\"density\":\"1\",\"path\":\"powell-craft-w512h512.png\",\"size\":47991,\"mime\":\"\",\"assetID\":\"101\"}}},\"alt_text\":\"Powell craft\",\"status\":\"1\"}', brandTitle='Powell Craft', brandUpdated='2016-11-21 16:12:41' WHERE brandID='6'
No ids to log.
DELETE FROM perch2_shop_index WHERE itemKey='brandID' AND itemID='6'
INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('brandID',6,'title','Powell Craft'),('brandID',6,'_title','Powell Craft'),('brandID',6,'slug','powell-craft'),('brandID',6,'description','Powell Craft Ltd has been a family-run business since 1962. The product range has grown and diversified over the years. The last decade has seen the launch of a wide selection of childrenswear & babywear inspired by using vintage prints and fabrics. Just '),('brandID',6,'meta_description',''),('brandID',6,'url',''),('brandID',6,'image','/admin/resources/brands/powell-craft.png'),('brandID',6,'alt_text','Powell craft'),('brandID',6,'status','1'),('brandID',6,'brandID','6'),('brandID',6,'brandTitle','Powell Craft'),('brandID',6,'brandCreated','2016-09-23 09:39:14'),('brandID',6,'brandUpdated','2016-11-21 16:12:41'),('brandID',6,'brandDeleted',''),('brandID',6,'itemID',''),('brandID',6,'itemRowID',''),('brandID',6,'_id',6)
[1] OPTIMIZE TABLE perch2_shop_index
Queries: 20
Memory: 5.3238
and saving a product
SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='9487bab867a687a180b8e54a3f34f46a' LIMIT 1
UPDATE perch2_users SET userHash='20ddcdfe5c4126e6a6ed69dba31b2447' WHERE userID='1'
[74] SELECT p.privKey FROM perch2_user_privileges p
[37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
Adding: Arithmetic
Adding: Breadcrumbs
[nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
[1] SELECT * FROM perch2_shop_products WHERE productID='167' AND productDeleted IS NULL LIMIT 1
Using template: /templates/shop/products/product.html
[3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=167 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[19] 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
[3] 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='167' 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] SELECT * FROM perch2_resources WHERE resourceID='535' LIMIT 1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceKey='thumb'
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=200 AND resourceTargetHeight=200 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=58 AND resourceTargetHeight=58 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceID='535' LIMIT 1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceKey='thumb'
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=415 AND resourceTargetHeight=415 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=1024 AND resourceTargetHeight=1024 AND resourceCrop=0 AND resourceDensity=1
[1] SELECT * FROM perch2_categories WHERE catID=6 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=55 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=25 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
Logging resources for perch_shop
DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='productID' AND itemRowID=167
INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','productID',167,535),('perch_shop','productID',167,536),('perch_shop','productID',167,537),('perch_shop','productID',167,538),('perch_shop','productID',167,539),('perch_shop','productID',167,535),('perch_shop','productID',167,536),('perch_shop','productID',167,540),('perch_shop','productID',167,541)
[3] SELECT * FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL
UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\"}', productStockOnParent='0', productUpdated='2016-11-21 16:14:40' WHERE productID='168'
No ids to log.
UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\"}', productStockOnParent='0', productUpdated='2016-11-21 16:14:40' WHERE productID='169'
No ids to log.
UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\"}', productStockOnParent='0', productUpdated='2016-11-21 16:14:40' WHERE productID='170'
No ids to log.
UPDATE perch2_shop_products SET title='Racing Car Leggings', sku='BR0026', stock_level=NULL, productDynamicFields='{\"_title\":\"Racing Car Leggings\",\"slug\":\"racing-car-leggings-br0026\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom which is perfect for showing off when little ones are zooming about crawling!<\\/p>\",\"processed\":\"<p>The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom which is perfect for showing off when little ones are zooming about crawling!<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers!\",\"processed\":\"Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers!\"},\"favourite\":null,\"product_image_thumb\":{\"assetID\":\"535\",\"title\":\"Racing Car Leggings\",\"_default\":\"\\/admin\\/resources\\/shop\\/racing-car-leggings.jpg\",\"bucket\":\"shop\",\"path\":\"racing-car-leggings.jpg\",\"size\":2729552,\"w\":2000,\"h\":1333,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"99\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"racing-car-leggings-thumb@2x.jpg\",\"size\":18484,\"mime\":\"\",\"assetID\":\"536\"},\"w200h200c1\":{\"w\":\"200\",\"h\":\"200\",\"target_w\":\"200\",\"target_h\":\"200\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w200h200.jpg\",\"size\":13991,\"mime\":\"\",\"assetID\":\"537\"},\"w512h512c1\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w512h512.jpg\",\"size\":75543,\"mime\":\"\",\"assetID\":\"538\"},\"w58h58c1\":{\"w\":\"58\",\"h\":\"58\",\"target_w\":\"58\",\"target_h\":\"58\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w58h58.jpg\",\"size\":2111,\"mime\":\"\",\"assetID\":\"539\"}}},\"product_image_thumb_alt\":null,\"product_images\":[{\"product_image_main\":{\"assetID\":\"535\",\"title\":\"Racing Car Leggings\",\"_default\":\"\\/admin\\/resources\\/shop\\/racing-car-leggings.jpg\",\"bucket\":\"shop\",\"path\":\"racing-car-leggings.jpg\",\"size\":2729552,\"w\":2000,\"h\":1333,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"99\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"racing-car-leggings-thumb@2x.jpg\",\"size\":18484,\"mime\":\"\",\"assetID\":\"536\"},\"w415h415c1\":{\"w\":\"415\",\"h\":\"415\",\"target_w\":\"415\",\"target_h\":\"415\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w415h415.jpg\",\"size\":51831,\"mime\":\"\",\"assetID\":\"540\"},\"w1024h1024c0\":{\"w\":\"1024\",\"h\":\"682\",\"target_w\":\"1024\",\"target_h\":\"1024\",\"crop\":false,\"density\":\"1\",\"path\":\"racing-car-leggings-w1024h1024.jpg\",\"size\":185760,\"mime\":\"\",\"assetID\":\"541\"}}},\"product_image_caption\":null}],\"brand\":\"2\",\"range\":[\"6\"],\"age_size\":[\"55\",\"56\"],\"category\":[\"25\"],\"special\":null,\"price\":{\"47\":\"10.00\",\"_default\":\"10.00\"},\"sale_price\":{\"47\":\"\",\"_default\":\"\"},\"trade_price\":{\"47\":\"\",\"_default\":\"\"},\"on_sale\":null,\"tax_group\":\"1\",\"stock_status\":\"0\",\"stock_location\":\"0\",\"max_in_cart\":null,\"requires_shipping\":\"1\",\"weight\":null,\"width\":null,\"height\":null,\"depth\":null,\"catalog_only\":\"0\",\"status\":\"1\"}', productStockOnParent='0', productSlug='racing-car-leggings-br0026', productUpdated='2016-11-21 16:14:40' WHERE productID='167'
No ids to log.
DELETE FROM perch2_shop_index WHERE itemKey='productID' AND itemID='167'
[3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=167 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[19] 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
[3] 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='167' 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] SELECT * FROM perch2_shop_brands WHERE brandID='2' AND brandDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=6 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=55 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=25 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
[1] SELECT * FROM perch2_shop_tax_groups WHERE groupID='1' AND groupDeleted IS NULL LIMIT 1
INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('productID',167,'_title','Racing Car Leggings'),('productID',167,'slug','racing-car-leggings-br0026'),('productID',167,'description','The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool'),('productID',167,'meta_description','Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers!'),('productID',167,'favourite',''),('productID',167,'product_image_thumb','/admin/resources/shop/racing-car-leggings.jpg'),('productID',167,'product_image_thumb_alt',''),('productID',167,'product_images.product_image_main','/admin/resources/shop/racing-car-leggings.jpg'),('productID',167,'product_images.product_image_main','/admin/resources/shop/racing-car-leggings.jpg'),('productID',167,'product_images.product_image_caption',''),('productID',167,'brand','2'),('productID',167,'brand.title','Blade & Rose'),('productID',167,'brand._title','Blade & Rose'),('productID',167,'brand.slug','blade-rose'),('productID',167,'brand.url',''),('productID',167,'brand.alt_text','Blade & Rose logo'),('productID',167,'brand.status','1'),('productID',167,'brand.brandID','2'),('productID',167,'brand.brandTitle','Blade & Rose'),('productID',167,'brand.brandCreated','2016-09-23 09:38:01'),('productID',167,'brand.brandUpdated','2016-11-21 16:09:45'),('productID',167,'brand.brandDeleted',''),('productID',167,'_category','ranges/boys/'),('productID',167,'_category','age-size/6-12-months/'),('productID',167,'_category','age-size/1-2-years/'),('productID',167,'_category','products/leggings/'),('productID',167,'price','10.00'),('productID',167,'price.gbp','10.00'),('productID',167,'sale_price',''),('productID',167,'sale_price.gbp',''),('productID',167,'trade_price',''),('productID',167,'trade_price.gbp',''),('productID',167,'on_sale',''),('productID',167,'tax_group','1'),('productID',167,'tax_group.groupID','1'),('productID',167,'tax_group.groupTitle','Default'),('productID',167,'tax_group.groupSlug','default'),('productID',167,'tax_group.groupTaxRate','buyer'),('productID',167,'tax_group.groupCreated','2016-09-23 09:34:09'),('productID',167,'tax_group.groupUpdated',''),('productID',167,'tax_group.groupDeleted',''),('productID',167,'stock_status','0'),('productID',167,'stock_location','0'),('productID',167,'max_in_cart',''),('productID',167,'requires_shipping','1'),('productID',167,'weight',''),('productID',167,'width',''),('productID',167,'height',''),('productID',167,'depth',''),('productID',167,'catalog_only','0'),('productID',167,'status','1'),('productID',167,'productID','167'),('productID',167,'title','Racing Car Leggings'),('productID',167,'sku','BR0026'),('productID',167,'productSlug','racing-car-leggings-br0026'),('productID',167,'stock_level',''),('productID',167,'parentID',''),('productID',167,'productVariantDesc',''),('productID',167,'productOrder','1'),('productID',167,'productHasVariants','1'),('productID',167,'productStockOnParent','0'),('productID',167,'productTemplate','product.html'),('productID',167,'productCreated','2016-10-10 10:34:58'),('productID',167,'productUpdated','2016-11-21 16:14:40'),('productID',167,'productDeleted',''),('productID',167,'itemID',''),('productID',167,'itemRowID',''),('productID',167,'has_variants','1'),('productID',167,'_variant_opts','0-6 Months|168,6-12 Months|169,1-2 Years|170'),('productID',167,'_id',167)
[1] OPTIMIZE TABLE perch2_shop_index
DELETE FROM perch2_shop_search WHERE itemKey=167 AND itemType='product'
INSERT INTO perch2_shop_search(itemKey,itemType,searchBody) VALUES(167,'product',' BR0026 Racing Car Leggings racing car leggings br0026 The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom which is perfect for showing off when little ones are zooming about crawling! Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers! 2 Boys 6-12 Months, 1-2 Years Leggings 1 0 0 1 0 1 ')
[3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=167 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[19] 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
[3] 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='167' 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 * FROM perch2_shop_brands WHERE 1=1 AND brandDeleted IS NULL ORDER BY brandTitle ASC
[4] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='ranges' ORDER BY catTreePosition ASC
[6] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='age-size' ORDER BY catTreePosition ASC
[17] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='products' ORDER BY catTreePosition ASC
[2] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='specials' ORDER BY catTreePosition ASC
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_tax_groups WHERE 1=1 AND groupDeleted IS NULL ORDER BY groupTitle ASC
Do you see the same characteristics in your dev environment? If so, could you switch into dev mode so that the debug has timing output?
Hi Drew,
In my local environment the length of time spent waiting for the save actions to complete is less, but there is still a noticeable wait for it to complete. Here are the timed logs for local:
Saving a brand:
Saving a product:
This query is flagging up as the longest:
DELETE FROM perch2_shop_search WHERE itemKey=11 AND itemType='product'In the production environment the same pattern, though with longer times:
Brand:
Product:
The same query again is taking 16 seconds to complete:
DELETE FROM perch2_shop_search WHERE itemKey=11 AND itemType='product'Edit: Another thing, testing in sequel pro the
OPTIMIZE TABLE perch2_shop_indextakes around 3 seconds and outputs this:Edit 2: Yes, messing about and changing the table type of
perch_shop_indexto MyISAM to matchperch_content_indexcompletely removes any locking and saving is now instantIt should be fine as MyISAM, so you can stick with that.
Ok, thanks Drew that seems to have sorted out the problems on the production server too. I have also spotted a minor bug where updating an order for a deleted product throws a hard error with a white page:
I can expand this into a new thread if that is preferred?
Yes, please.