Forum

Thread tagged as: Discussion, Runway

Perch database question - slow perch_collection() call

I’m redesigning a fairly mature Runway site. Migrated the existing database and am redoing all presentational templates. Structure of the data (perch content, collections + those templates that define the data structure) will not change.

A perch_collection() call for one of my collections took a huge amount of time (> 40 secs) on the new site/db. I tracked down the offending SQL, and it was a group by query on the perch3_collection_index table, which contained > 200k rows (this doesn’t seem like a huge amount to me for a mature site with reasonably complex templates).

So I emptied that table + re-saved my perch content and everything is super-fast now.

I’m not sure exactly what my questions are, but they probably include:

  • why would the query run so much slower on my migrated db? Could there be some Perch indexing or caching in play on my existing site that isn’t there on my new site or migrated db.
  • any downside to emptying the perch3_collection_index table, other than losing revision history for my collections?
  • is there some built-in way to clean up or optimise Perch dbs that I should be aware of?
Simon Ansell

Simon Ansell 1 points

  • 4 years ago
Rachel Andrew

Rachel Andrew 394 points
Perch Support

4 years ago

Please post your Diagnostics Report and the relevant templates.
Simon Ansell

Simon Ansell 1 points

4 years ago

Hi Rachel,

It was a general question so I didn't see the need to include the diagnostics, but I probably should have done. Sorry. They are below.

Another question I have is: is there some config setting that can minimise the number of collection (content) revisions that are held in the db? Are/can the oldest revisions (be) removed intermittently by a scheduled task?

Diagnostics and template for the collection I am running perch_collection() on (sub-templates not included, they are not complex):

Note this is after I have emptied the perch3_collection_index table and re-saved my content, hence the diagnostics report is not showing 200k rows.

Perch Runway: 3.0.8
Production mode: Production (100)
Installed apps: content (3.0.8), assets (3.0.8), categories (3.0.8), perch_gallery (2.8.9), perch_twitter (3.7)
DB driver: PDO
DB tables: #sales_by_product (0), foxycart_transaction_items (967), foxycart_transactions (776), perch3_ansell_disqus_posts (70), perch3_ansell_disqus_settings (1), perch3_ansell_disqus_threads (89), perch3_backup_plans (1), perch3_backup_resources (976), perch3_backup_runs (590), perch3_blog_authors (5), perch3_blog_categories (17), perch3_blog_comments (1033), perch3_blog_index (6860), perch3_blog_posts (335), perch3_blog_posts_to_categories (619), perch3_blog_posts_to_tags (0), perch3_blog_sections (1), perch3_blog_tags (0), perch3_categories (16), perch3_category_counts (0), perch3_category_sets (3), perch3_collection_index (4200), perch3_collection_items (1093), perch3_collection_revisions (376), perch3_collections (4), perch3_content_index (58), perch3_content_items (27), perch3_content_locks (0), perch3_content_regions (11), perch3_gallery_albums (5), perch3_gallery_image_versions (642), perch3_gallery_images (104), perch3_kraken_jobs (58), perch3_mailchimp_history (405), perch3_mailchimp_stats (1), perch3_mailchimp_subscribers (10), perch3_members (2), perch3_members_forms (0), perch3_members_member_tags (2), perch3_members_sessions (1), perch3_members_tags (1), perch3_menu_items (10), perch3_navigation (0), perch3_navigation_pages (0), perch3_newsfeed (140), perch3_page_routes (7), perch3_page_templates (26), perch3_pages (20), perch3_resource_log (4265), perch3_resource_tags (18), perch3_resources (961), perch3_resources_to_tags (30), perch3_scheduled_tasks (40), perch3_settings (41), perch3_simona_foxycart_discounts (372), perch3_simona_foxycart_transaction_items (4711), perch3_simona_foxycart_transactions (3199), perch3_twitter_scheduled_tweets (0), perch3_twitter_settings (1), perch3_twitter_tweets (5269), perch3_user_passwords (0), perch3_user_privileges (35), perch3_user_role_privileges (16), perch3_user_roles (2), perch3_users (1), transaction_details (2346), transactions (512)
Users: 1
App runtimes:
<?php
    $apps_list = [
    'perch_gallery',
    'perch_twitter',
    ];
Scheduled tasks for perch_twitter: post_tweets (1 mins), update_tweets (30 mins)
Scheduled tasks for Backup: plan_1 (10 mins)
Editor plug-ins:
H1: 421aa90e079fa326b6494f812ad13e79
L1: 32b579456d63de14a46ddae442d8bbcc
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
headerColour: #000000
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 0
content_collapseList: 1
lang: en-gb
update_2.8.21: done
perch_gallery_bucket_mode: single
headerScheme: dark
update_runway_2.8.21: done
latest_version:
on_sale_version:
perch_shop_update: g9
perch_members_login_page: /
perch_shop_price_tax_mode: exc
perch_shop_site_url: https://
perch_shop_default_currency: 47
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
perch_twitter_update: 3.5
update_runway_2.8.24: done
update_runway_2.8.25: done
perch_gallery_update: 2.8.5
update_runway_2.8.26: done
perch_members_update: 1.4
perch_gallery_bucket: default
perch_gallery_basicUpload: 0
update_runway_2.8.27: done
update_runway_2.8.30: done
perch_kraken_api_key: 6470f5b90e4c35cc8b33ede447ca06ab
perch_kraken_api_secret: 068a45a6c572aba4c31e4988ba8e055be7006fe0
perch_kraken_url: https://www.gingergm.com
perch_kraken_dev_mode:
update_runway_2.8.31: done
perch_backup_mysqldump_path: /usr/bin/mysqldump
simona_gingergm_sale_description: Most Amazing Moves webinar sale! 12 hours only - 50% off the Ginger GM Collection and all iChess bundles! Ends Sunday 21:00 BST
simona_gingergm_saleNowOn: 0
update_runway_3.0.8: done
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /var/www/html
PERCH_SCHEDULE_SECRET: Iha5YTdHqmQTcT8L
PERCH_DB_USERNAME: root
PERCH_DB_SERVER: db
PERCH_DB_DATABASE: db_gingergm
PERCH_DB_PREFIX: perch3_
PERCH_DEBUG: 1
PERCH_EMAIL_FROM: siansell@gmail.com
PERCH_EMAIL_FROM_NAME: Simon Ansell
PERCH_LOGINPATH: /perch
PERCH_PATH: /var/www/html/perch
PERCH_CORE: /var/www/html/perch/core
PERCH_RESFILEPATH: /var/www/html/perch/resources
PERCH_RESPATH: /perch/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_ASSET_VERSION: 5163d57ff611b3cf853b
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /var/www/html/perch/templates
PERCH_TEMPLATE_FILTERS:
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_PRODUCTION_MODE: 100
PERCH_XHTML_MARKUP:
PERCH_RWD: 1
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_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.1.6
Zend: 3.1.0
OS: Linux
SAPI: apache2handler
Safe mode: not detected
MySQL client: mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
MySQL server: 5.7.18
Free disk space: 365.04 GB
Extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, ctype, curl, dom, fileinfo, filter, ftp, hash, iconv, json, mbstring, SPL, PDO, session, posix, Reflection, standard, SimpleXML, pdo_sqlite, Phar, tokenizer, xml, xmlreader, xmlwriter, mysqlnd, apache2handler, gd, pdo_mysql
GD: Yes
ImageMagick: No
PHP max upload size: 64M
PHP max form post size: 64M
PHP memory limit: 64M
Total max uploadable file size: 64M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: No
HTTP_HOST: localhost
HTTP_CONNECTION: keep-alive
HTTP_UPGRADE_INSECURE_REQUESTS: 1
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
HTTP_REFERER: https://localhost/perch/core/settings/diagnostics/
HTTP_ACCEPT_ENCODING: gzip, deflate, sdch, br
HTTP_ACCEPT_LANGUAGE: en-US,en;q=0.8,de;q=0.6,nb;q=0.4
HTTP_COOKIE: __distillery=3ecd6ba_cda1f8d8-5985-4526-ace6-43794566dc90-04ec86b15-26c138ed7d51-1f11; muxData=mux_viewer_id=d617fe53-125f-43d1-a59f-9ec1da3dfd54&msn=0.915952676681898&sid=0d87ade1-13fa-4268-90cb-6cd9f3fc8f58&sst=1497611747248&sex=1497613408767; _drip_client_4574203=vid%253Da0b42cc034c10135725f0eaa90c5513c%2526pageViews%253D190%2526sessionPageCount%253D36%2526lastVisitedAt%253D1497647077286%2526weeklySessionCount%253D3%2526lastSessionAt%253D1497641651899%2526form%255B14888%255D%255Bmanual_open%255D%253D1497619203%2526form%255B14888%255D%255Bmanual_close%255D%253D1497619205; PHPSESSID=b772cc65ca86f44a5086e7cf3b0b1903; cmsa=1
PATH: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
SERVER_SIGNATURE: <address>Apache/2.4.10 (Debian) Server at localhost Port 80</address>
SERVER_SOFTWARE: Apache/2.4.10 (Debian)
SERVER_NAME: localhost
SERVER_ADDR: 172.20.0.3
SERVER_PORT: 80
REMOTE_ADDR: 172.20.0.1
DOCUMENT_ROOT: /var/www/html
REQUEST_SCHEME: http
CONTEXT_DOCUMENT_ROOT: /var/www/html
SERVER_ADMIN: webmaster@localhost
SCRIPT_FILENAME: /var/www/html/perch/core/settings/diagnostics/index.php
REMOTE_PORT: 59232
GATEWAY_INTERFACE: CGI/1.1
SERVER_PROTOCOL: HTTP/1.1
REQUEST_METHOD: GET
QUERY_STRING: extended
REQUEST_URI: /perch/core/settings/diagnostics/?extended
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
PHP_SELF: /perch/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1498859086.494
REQUEST_TIME: 1498859086
argc: 1

and the template:

<perch:content id="active" type="checkbox" label="Product is active?" />
<perch:content id="is_new" type="checkbox" label="Product is new?" help="Product will have a *NEW* label shown in listings." />
<perch:content id="title" type="text" label="Title" required="true" title="true" />

<perch:related id="author" collection="Authors" label="Author">
  <perch:content id="chess_title" />
  <perch:content id="first_name" />
  <perch:content id="surname" />
</perch:related>

<perch:content id="date_published" type="date" label="Date published" format="Y" help="Only the year field is used"></perch:content>

<perch:categories id="category" label="Category" set="products" />
<perch:categories id="brand" label="Brand" set="brands">
    <perch:category id="catTitle" />
</perch:categories>

<perch:repeater id="images" label="Images">
  <perch:content type="image" id="image" label="Image" bucket="products"  help="The first image will be the main image on the product page." />
  <perch:content type="image" id="image" label="Image" width="284" height="284" crop="true" />
  <perch:content type="text" id="alt" label="Description" help="e.g. \"Chess (Book) Cover\"." />
</perch:repeater>

<perch:related id="related_products" collection="Products" label="Related products"></perch:related>

<perch:repeater id="purchase_options" label="Purchase Options" divider-before="Purchase Options">
  <perch:content type="text" id="product_code" label="Product Code (SKU)" />
  <perch:content type="text" id="description" label="Description" help="e.g. \"Killer d4: Download - Disc 2 Only\"." />
  <perch:content type="text" id="price" label="Price (GBP)" help="To two decimal places."/>
  <perch:content type="text" id="sale_price" label="Sale Price (GBP)" help="Effective if Perch setting Sale now on is checked. To two decimal places."/>
  <perch:content type="text" id="weight" label="Weight (kg)" help="Guidance: set to 0 for downloadable products, 0.4 per physical DVD, books custom but generally greater than 1kg." />
  <perch:content type="select" id="category" label="FoxyCart Category" options="Default (should NOT be used)|default, Ginger GM - DVD (download)|gingergm-dvd-download, Ginger GM - DVD (physical - requires shipping)|gingergm-dvd, Ginger GM Specials DVD Download|gingergm-specials-dvd-download, Ginger GM - Book|gingergm-book, Ginger GM - eBook|gingergm-ebook, Ginger GM - PGN file|gingergm-pgn, iChess - DVD (download)|ichess-dvd-download, ChessBase DVD (physical - requires shipping)|chessbase-dvd" help="Not to be confused with the product category set above. This controls shipping, coupons, notification emails etc. See FoxyCart admin for configuration." />
</perch:repeater>

<perch:content id="product_info" type="textarea" markdown="true" label="Product Info" size="s" editor="markitup" divider-before="Product Info & Description" html="true" />

<perch:blocks notes-before="DESCRIPTION: Add fields below to build the product description.">
  <perch:block type="textarea" label="Text">
    <perch:content id="text" type="textarea" markdown="true" label="Description" size="l" editor="markitup" html="true" />
  </perch:block>
  <perch:block type="quote" label="Pull quote">
    <perch:template path="../blockquote.html" />
  </perch:block>
  <perch:block type="video" label="Video">
    <perch:template path="../flex_video.html" />
  </perch:block>
</perch:blocks>

<perch:repeater id="quotes" label="Quotes & Reviews">
  <perch:template path="content/blockquote.html" />
</perch:repeater>
<perch:content type="slug" id="slug" for="title" />
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

Is this a template for your Products collection? If so, it looks like you have a circular relationship, which will cause index bloat. We've been working on some changes for the next release that should help with that.
Simon Ansell

Simon Ansell 1 points

4 years ago

Ah ok, thanks Drew, hadn't considered that might be a problem. I guess my questions still stand, but I'll write a custom fieldtype to remove the circular reference .
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

I'd wait for the next update and see if it helps - could save you some work.
Simon Ansell

Simon Ansell 1 points

4 years ago

Great, thanks.
Raymond Wiggins

Raymond Wiggins 0 points

4 years ago

I'm running into a similar problem with my perch3_collection_index getting very large and slow to update. I'm intrigued by the custom field "fix" - how exactly does that work? I'm certain it's the circular references in my related content.

In general, I never try to edit the database directly, but if clearing/cleaning the collection_index would help I might consider it.