Thread tagged as: Problem, Error, Runway

Long query times viewing assets app

Hi again,

Apologies for posting so many threads recently. This last build seems to be exposing some issues in Perch that only occur with very large amounts of data.

I have imported the assets from the previous version of a site, there is a lot of historical data here which means there are now around 157 pages in the assets app. These are all required files and can't be pruned. There are 12,000+ rows in the database. This seems to be producing a load time in the range of 20-60 seconds.

0.1059  0.0795  [7] SELECT DISTINCT resourceBucket FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !=""
0.1582  0.0524  [30] SELECT SQL_CALC_FOUND_ROWS DISTINCT r1.*, r2.resourceFile AS thumb, r2.resourceWidth AS thumbWidth, r2.resourceHeight AS thumbHeight, r2.resourceDensity AS thumbDensity FROM perch3_resources r1 LEFT OUTER JOIN perch3_resources r2 ON r2.resourceParentID=r1.resourceID AND r2.resourceKey='thumb' AND r2.resourceAWOL!=1 WHERE r1.resourceKey='orig' AND r1.resourceAWOL=0 AND r1.resourceBucket IN ('admin', 'banner', 'blog', 'charity', 'default', 'instagram', 'logo', 'meta', 'newsroom', 'portfolio', 'staff') ORDER BY r1.resourceUpdated DESC, r1.resourceID DESC LIMIT 0, 30
20.0008 19.8426 [4686] SELECT FOUND_ROWS() AS `count`

I have executed that query in isolation on the production server and the results were the same, taking 43 seconds to complete. This is happening on my local and production environments.

Here is the diagnostic:

Perch Runway: 3.0.11, PHP:, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $, with PDO
Server OS: Linux, fpm-fcgi
Installed apps: content (3.0.11), assets (3.0.11), categories (3.0.11), root_analytics (2.0.0), perch_blog (5.6.1), root_heartbeat (1.0.1)
App runtimes: <?php $apps_list = [ 'perch_blog', 'root_analytics' ];
PERCH_PATH: /home/forge/XXXXXX/public/admin
PERCH_CORE: /home/forge/XXXXXX/public/admin/core
PERCH_RESFILEPATH: /home/forge/XXXXXX/public/admin/resources
Image manipulation: GD Imagick
PHP limits: Max upload 100M, Max POST 100M, Memory: 512M, Total max file upload: 100M
F1: 3b606135b33e6a102526838f4152a807
Resource folder writeable: Yes
DOCUMENT_ROOT: /home/forge/XXXXXX public
REQUEST_URI: /admin/core/settings/diagnostics/
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php

The client is a media company I would expect them to make heavy use of the assets area so this is quite a critical issue.


Edit: It seems that SQL_CALC_FOUND_ROWS DISTINCT is the issue here. Removing it drops the query time to 150ms.

James Wigger

James Wigger 0 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

How well resourced is the server?

The site is hosted standalone on the Linode $5 plan:

  • 1 GB RAM
  • 1 CPU Core
  • 20 GB SSD Storage
  • 1 TB Transfer
  • 40 Gbps Network In
  • 1000 Mbps Network Out

My local copy is running on an iMac:

  • iMac (27-inch, Late 2013)
  • 3.2 GHz Intel Core i5
  • 24 GB 1600 MHz DDR
Drew McLellan

Drew McLellan 2638 points
Perch Support

Are you able to share an SQL dump of this?

I can send it privately, which tables would you like? Is the email address

Hi Drew,

Did you receive the SQL file I sent through a few days ago?

Drew McLellan

Drew McLellan 2638 points
Perch Support

Yes, I've got it, thanks. I'm going to run it up and take a look.

I'm running into the same problem, were you able to resolve this?

Drew McLellan

Drew McLellan 2638 points
Perch Support

I think this should be fixed by 3.0.12.

Great to hear - thanks for looking into this Drew, much appreciated :)

Just thought I'd confirm and let you know that the update has fixed the issue, loading times are now near-instant. Great job!