Forum

Thread tagged as: Question, Problem, Runway

Perch Runway issues -- database size, backups

Hello,

I would like to share with you some problems I have been experiencing with Perch Runway regarding a product based site I have implemented.

The site amounts approx. 200 products.

The issues are the following:

  • The database size has reached almost 430 mb. I am not an expert in databases but this seems to be massive.

  • The backup feature in Runway does not complete the process (throws Server Error 500) and so I can not use this feature and I am collecting backups manually from the cPanel.

  • Page functions such as perch_collection() were becoming very slow early on, and in order to achieve performance I had to write direct db calls

Maybe all the above are related issues (around the db structure?) and I meant to let you know for a while.

I am puzzled about these issues and not sure what I have done wrong. Otherwise everything works fine. In any case it would be great to somehow make the backup feature usable.

Thank you for the great support,

DIAGNOSTICS REPORT

Perch Runway: 2.8.26, PHP: 5.6.18, MySQL: 5.6.29, with PDO
Server OS: Linux, cgi-fcgi
Installed apps: content (2.8.26), assets (2.8.26), categories (2.8.26), collection_2 (2.8.26), collection_4 (2.8.26), perch_forms (1.8.3), collection_1 (2.8.26), collection_5 (2.8.26), perch_members (1.3)
App runtimes: <?php $apps_list = array( 'content', 'categories', 'perch_forms', 'drops', );
PERCH_LOGINPATH: /perch
PERCH_PATH: /home/drop8445/public_html/stage/perch
PERCH_CORE: /home/drop8445/public_html/stage/perch/core
PERCH_RESFILEPATH: /home/drop8445/public_html/stage/assets/images
Image manipulation: GD
PHP limits: Max upload 2M, Max POST 8M, Memory: 128M, Total max file upload: 2M
F1: 2edba60ed1f613d6dd804feb202456a2
Resource folder writeable: Yes
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
REQUEST_URI: /perch/core/settings/diagnostics/
DOCUMENT_ROOT: /home/drop8445/public_html/stage
HTTP_HOST: stage.dropsjewellery.com
Spyros Perris

Spyros Perris 0 points

  • 5 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

430mb isn't, or shouldn't be a problem in terms of data. It's certainly not massive. If you're constrained by disk space you could do things like turn off resource logging and reduce your undo history level.

What error are you getting in your log from the backup failing?

How are you calling perch_collection()? What options are you using? You've not really given any information that enables me to help.

What is your custom app doing?
Spyros Perris

Spyros Perris 0 points

5 years ago

Database size is not a problem per se, I just mentioned it because i thought it might mean I am doing something wrong.

Regarding ~~~ perch_collection() ~~~ I think that it was slow in returning the results when I called it on a whole collection. For example when I am calling it with a limit i.e. ~~~ 'count' => 3 ~~ there is no performance problem.

But here is the log I am getting when trying to perform a backup

[29-Feb-2016 19:28:27 Europe/Athens] PHP Fatal error:  Maximum execution time of 30 seconds exceeded in /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/vendor/rah/danpu/src/Rah/Danpu/Base.php on line 252
[29-Feb-2016 19:28:27 Europe/Athens] PHP Stack trace:
[29-Feb-2016 19:28:27 Europe/Athens] PHP   1. {main}() /Users/spyros/htdocs/dropsjewellery/perch/core/settings/backup/index.php:0
[29-Feb-2016 19:28:27 Europe/Athens] PHP   2. include() /Users/spyros/htdocs/dropsjewellery/perch/core/settings/backup/index.php:29
[29-Feb-2016 19:28:27 Europe/Athens] PHP   3. PerchBackupPlan->run() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/settings/modes/backup.runs.list.pre.php:20
[29-Feb-2016 19:28:27 Europe/Athens] PHP   4. PerchBackupPlan->run_database_backup() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/PerchBackupPlan.class.php:43
[29-Feb-2016 19:28:27 Europe/Athens] PHP   5. PerchBackupPlan->dump_db() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/PerchBackupPlan.class.php:113
[29-Feb-2016 19:28:27 Europe/Athens] PHP   6. Rah\Danpu\Base->__construct() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/PerchBackupPlan.class.php:225
[29-Feb-2016 19:28:27 Europe/Athens] PHP   7. Rah\Danpu\Export->init() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/vendor/rah/danpu/src/Rah/Danpu/Base.php:105
[29-Feb-2016 19:28:27 Europe/Athens] PHP   8. Rah\Danpu\Export->dump() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/vendor/rah/danpu/src/Rah/Danpu/Export.php:65
[29-Feb-2016 19:28:27 Europe/Athens] PHP   9. Rah\Danpu\Export->dumpTables() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/vendor/rah/danpu/src/Rah/Danpu/Export.php:111
[29-Feb-2016 19:28:27 Europe/Athens] PHP  10. Rah\Danpu\Base->write() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/vendor/rah/danpu/src/Rah/Danpu/Export.php:180
[29-Feb-2016 19:28:27 Europe/Athens] PHP  11. fwrite() /Users/spyros/htdocs/dropsjewellery/perch/core/runway/lib/vendor/rah/danpu/src/Rah/Danpu/Base.php:252
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

How many items are there in your collection? Pulling back all your data is always going to be much slower than pulling back 3 items.

Can you post your extended diagnostics? That will give me a sense of where the data is.
Spyros Perris

Spyros Perris 0 points

5 years ago

Good day,

The largest collection holds about 200 items. We have other smaller collections and there are relationships between those (this could be important too?).

Posting extended info...

PERCH INFORMATION

Perch Runway: 2.8.26
Production mode: Production (100)
Installed apps: content (2.8.26), assets (2.8.26), categories (2.8.26), collection_2 (2.8.26), collection_4 (2.8.26), perch_forms (1.8.3), collection_1 (2.8.26), collection_5 (2.8.26), perch_members (1.3)
DB driver: PDO
DB tables: perch2_backup_plans (0), perch2_backup_resources (0), perch2_backup_runs (0), perch2_categories (101), perch2_category_counts (0), perch2_category_sets (6), perch2_collection_index (2496575), perch2_collection_items (1500), perch2_collection_revisions (248), perch2_collections (4), perch2_content_index (464992), perch2_content_items (582), perch2_content_regions (87), perch2_forms (1), perch2_forms_responses (6), perch2_members (1), perch2_members_forms (0), perch2_members_member_tags (0), perch2_members_sessions (0), perch2_members_tags (0), perch2_my_sample_things (0), perch2_navigation (4), perch2_navigation_pages (8), perch2_page_routes (18), perch2_page_templates (10), perch2_pages (21), perch2_resource_log (13176), perch2_resource_tags (11), perch2_resources (2286), perch2_resources_to_tags (106), perch2_scheduled_tasks (14), perch2_settings (25), perch2_user_passwords (0), perch2_user_privileges (32), perch2_user_role_privileges (20), perch2_user_roles (2), perch2_users (4)
Users: 4
App runtimes:
<?php
    $apps_list = array(
        'content',
        'categories',
        'perch_forms',
        'drops',
    );
Scheduled tasks for drops: sitemap (1 mins), swiftype_items (1 mins)
Editor plug-ins: markitup
H1: eb891e4435bbff318dfad40c37ac394e
L1: 927e64b579b420296bef724be68e7ca7
F1: 2edba60ed1f613d6dd804feb202456a2
headerColour: #ffffff
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 0
content_collapseList: 1
lang: en-gb
update_2.8.10: done
latest_version: 2.8.15
on_sale_version: 2.8.26
update_runway_2.8.10: done
headerScheme: light
perch_members_login_page: /members/login.php?r={returnURL}
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
my_sample_example_setting: 0
logoPath: /assets/images/logo-main.png
update_runway_2.8.15: done
update_runway_2.8.21: done
update_runway_2.8.26: done
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_DB_USERNAME: drop8445_stage
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: drop8445_db_stage
PERCH_DB_PREFIX: perch2_
PERCH_TZ: Europe/Athens
PERCH_EMAIL_FROM: spyros.perris@gmail.com
PERCH_EMAIL_FROM_NAME: Spyros Perris
PERCH_LOGINPATH: /perch
PERCH_PATH: /home/drop8445/public_html/stage/perch
PERCH_CORE: /home/drop8445/public_html/stage/perch/core
PERCH_RESFILEPATH: /home/drop8445/public_html/stage/assets/images
PERCH_RESPATH: /assets/images
PERCH_HTML5: 1
PERCH_SITEPATH: /
PERCH_RWD: 1
PERCH_SCHEDULE_SECRET: dropstasks
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_DEBUG:
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /home/drop8445/public_html/stage/perch/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_PRODUCTION_MODE: 100
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_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: textile
HOSTING SETTINGS

PHP: 5.6.18
Zend: 2.6.0
OS: Linux
SAPI: cgi-fcgi
Safe mode: not detected
MySQL client: 5.6.29
MySQL server: 5.6.29
Free disk space: 419.17 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, PDO, pdo_mysql, mysqlnd, sockets, xmlrpc, dom, mysqli, pdo_sqlite, bcmath, Phar, xmlwriter, json, gd, posix, exif, mysql, mcrypt, xmlreader, imap, apcu, mhash, apc, ionCube Loader
GD: Yes
ImageMagick: No
PHP max upload size: 2M
PHP max form post size: 8M
PHP memory limit: 128M
Total max uploadable file size: 2M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: No
PATH: /sbin:/usr/sbin:/bin:/usr/bin
PWD: /usr/local/cpanel/cgi-sys
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
AUTH_TYPE: Basic
REMOTE_USER: drops
REMOTE_PORT: 62630
SCRIPT_FILENAME: /home/drop8445/public_html/stage/perch/core/settings/diagnostics/index.php
SERVER_ADMIN: webmaster@stage.dropsjewellery.com
DOCUMENT_ROOT: /home/drop8445/public_html/stage
REMOTE_ADDR: 85.72.168.197
SERVER_PORT: 80
SERVER_ADDR: 185.88.20.29
SERVER_NAME: stage.dropsjewellery.com
SERVER_SOFTWARE: Apache
HTTP_COOKIE: optimizelyEndUserId=oeu1453816861802r0.24310580780729651; _ga=GA1.2.552254767.1453816863; optimizelySegments=%7B%224534285351%22%3A%22search%22%2C%224533405408%22%3A%22false%22%2C%224538815056%22%3A%22gc%22%7D; optimizelyBuckets=%7B%224518488953%22%3A%224545373156%22%7D; cmsa=1; PHPSESSID=610de7f1o5983h8jig8g3bmgo2
HTTP_ACCEPT_LANGUAGE: en-US,en;q=0.8,el;q=0.6,fr;q=0.4,it;q=0.2
HTTP_ACCEPT_ENCODING: gzip, deflate, sdch
HTTP_REFERER: https://stage.dropsjewellery.com/perch/core/settings/diagnostics/
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.116 Safari/537.36
HTTP_UPGRADE_INSECURE_REQUESTS: 1
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
HTTP_CONNECTION: close
HTTP_HOST: stage.dropsjewellery.com
UNIQUE_ID: VtVWzLlYFB0ABGBbuE8AAAAI
FCGI_ROLE: RESPONDER
PHP_SELF: /perch/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1456821964.4372
REQUEST_TIME: 1456821964
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

You have 2.5 million rows in your collection index, which is surprising for just 1500 items. That suggests 1500 indexed values per collection item, which has to be wrong.

What's the history of this site?
Spyros Perris

Spyros Perris 0 points

5 years ago

Yes I have noticed this about the index table. I am collecting maybe 15-20 pieces of metadata for each item max. Would the template I am using help to understand? The site is new, built on perch from scratch.
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

Built on Perch or on Runway? Always version 2.8.26?
Spyros Perris

Spyros Perris 0 points

5 years ago

On Runway. Version was maybe 2.8.15 or lower at the time.
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

Ok, so it's possible you hit an old bug.

In your dev environment, make a backup and then run this:

DELETE FROM perch2_collection_index WHERE itemID NOT IN (SELECT itemID FROM perch2_collection_items)

see if that reduces the row count.
Spyros Perris

Spyros Perris 0 points

5 years ago

I did this but no rows were affected.
Spyros Perris

Spyros Perris 0 points

5 years ago

Querying a single item and single revision like:

SELECT * from perch2_collection_index
WHERE itemID = 7
AND itemRev = 61

returns 758 records.

I have many records which look something like this:

featured_product.artist.featured_product.artist.featured_product

It looks like the index is going through all related items trying to index them...?
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

Do you have circular relationships?
Spyros Perris

Spyros Perris 0 points

5 years ago

It looks like I do...

I have used relationships in 3 collections:

  • Products,

  • Artists,

  • Collections (as in jewellery collections)

The relationships are:

  • product <is related to> artist (with id="artist")

  • artist <is related to> product (with id="featured_product")

  • collection <is related to> artist

  • collection <is related to> product
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

That's the issue then. I'll have a look and see if we can't build in some protection in case circular references are created.
Spyros Perris

Spyros Perris 0 points

5 years ago

Thanks Drew for looking into this and I am wondering if there was a better way to deal with these relationships.