Perch Reordering

ryan · Oct 6th 2019

Hi,

I have an issue with reordering items in a collection, where basically the site returns 500 error and a connection timeout upon save of a reorder. The site has over about 10 collections and there's about 1000 items, so it's a larger dataset.

When running a reorder on a smaller collection, there seems to be a specific slow query both on production server and local testing environment:

SQL

SELECT * FROM perch3_collection_revisions r WHERE itemID=3410 LIMIT 1

It takes "2.8132" to execute and is called for each item in the collection I believe? The collection_revisions table has 2928 rows in.

The php function is:

Code

PerchContent_CollectionItems.class.php: PerchContent_CollectionItems->find_item_revision():

Diagnostics:

PHP

Perch Runway: 3.1.5, PHP: 7.3.5, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $, with PDO
Server OS: Darwin, fpm-fcgi
Installed apps: content (3.1.5), assets (3.1.5), categories (3.1.5), perch_blog (5.6.1), perch_forms (1.12), perch_members (1.6.4), perch_mailchimp (3.1)
App runtimes: <?php $apps_list = [ 'perch_mailchimp', 'perch_blog', 'perch_forms', 'mbk_util', 'perch_members', ];
PERCH_LOGINPATH: /admin
PERCH_PATH: /Users/ryangittings/Builds/mysite/admin
PERCH_CORE: /Users/ryangittings/Builds/mysite/admin/core
PERCH_RESFILEPATH: /Users/ryangittings/Builds/mysite/admin/resources
Image manipulation: GD
PHP limits: Max upload 128M, Max POST 128M, Memory: 128M, Total max file upload: 128M
F1: 3b606135b33e6a102526838f4152a807
Resource folder writeable: Yes
HTTP_HOST: mysite.test
DOCUMENT_ROOT: /Users/ryangittings/Builds/mysite
REQUEST_URI: /admin/core/settings/diagnostics/
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php

Display More

Cheers as always,

Ryan

Byron Fitzgerald · Oct 7th 2019

You could try reduce the PERCH_UNDO_BUFFER to something lower (Defaults to 30). This should hopefully reduce the number of revisions stored, however I'm not sure if this removes older revision retroactively.

ryan · Oct 7th 2019

That was my first port of call - I've reduced to two for now to see if I can get the index down. I'm hoping Perch 4 can bring some improvements and optimisations in this area!

drewm · Oct 8th 2019

Is there an error message in the logs associated with the 500 status?

ryan · Oct 8th 2019

I did check the logs, but it's just a pure timeout based on max execution time of 30 seconds, can't see any errors in logs