Forum

Thread tagged as: Question, Problem, Configuration

Very Slow Database Updates when Adding new Collection Items

Hi

I recently noticed that when we try to update content in the admin area the site on the front end is not accessible. We are trying to update collection items.

The database seems to update veerrry slowly but will eventually save. It results in pingdom sending us a "site down" alert.

Any ideas what could be the issue?

Diagnostics report

Perch Runway: 2.8.34, PHP: 5.6.30, MySQL: mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $, with PDO
Server OS: Linux, cgi-fcgi
Installed apps: content (2.8.34), assets (2.8.34), categories (2.8.34), collection_5 (2.8.34), collection_2 (2.8.34), collection_1 (2.8.34), collection_9 (2.8.34), collection_6 (2.8.34), collection_14 (2.8.34), collection_11 (2.8.34), perch_forms (1.8.3), collection_12 (2.8.34), collection_4 (2.8.34), collection_10 (2.8.34), collection_13 (2.8.34), collection_19 (2.8.34), collection_8 (2.8.34), collection_3 (2.8.34), collection_7 (2.8.34), perch_members (1.5), perch_mailchimp (3.0.1)
App runtimes: <?php $apps_list = array( 'content', 'categories', 'perch_members', 'perch_forms', 'perch_mailchimp', );
PERCH_LOGINPATH: /login
PERCH_PATH: /home/admin/public_html/login
PERCH_CORE: /home/admin/public_html/login/core
PERCH_RESFILEPATH: /home/admin/public_html/login/resources
Image manipulation: GD
PHP limits: Max upload 200M, Max POST 1024M, Memory: -1M, Total max file upload: -1M
F1: 6a33f95eca3667f9e0c39bf5ca2980fe
Resource folder writeable: Yes
HTTP_HOST: www.uk-cpi.com
DOCUMENT_ROOT: /home/admin/public_html
REQUEST_URI: /login/core/settings/diagnostics/
SCRIPT_NAME: /login/core/settings/diagnostics/index.php
Dan Lee

Dan Lee 1 points

  • 1 year ago
Drew McLellan

Drew McLellan 2633 points
Perch Support

Have you checked your logs?

Dan Lee

Dan Lee 1 points

When updating the database this is what we see in the logs:

[Mon Jun 05 11:16:26.669936 2017] [cgi:error] [pid 17545] [client 66.249.76.159:38563] AH01215: PHP Warning:  strlen() expects parameter 1 to be string, array given in /home/admin/public_html/login/core/lib/PerchTemplate.class.php on line 348: /usr/local/cpanel/cgi-sys/ea-php56
Drew McLellan

Drew McLellan 2633 points
Perch Support

Ok. If you think it's a Runway issue rather than a hosting issue, my first suggestion is to make sure you're running the current version.

Dan Lee

Dan Lee 1 points

Thanks Drew - we are due to upgrade so I'll let you know if this makes a difference.

Dan Lee

Dan Lee 1 points

Hi Drew,

We have upgraded to the latest version of Perch 3. We are still finding that updating content is causing delays in accessing the site. There is no problem when updating normal page content but the delay does occur when editing the Collection Items. The site gets taken down for 4mins and results in a pingdom "site down alert".

The size of our Collection Index Table is quite sizable at 1.6 GiB - could this be the source of the issue?

Perch Runway: 3.0.8, PHP: 5.6.30, MySQL: mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $, with PDO
Server OS: Linux, cgi-fcgi
Installed apps: content (3.0.8), assets (3.0.8), categories (3.0.8), perch_forms (1.9.1), perch_members (1.6.1), perch_mailchimp (3.1)
App runtimes: <?php $apps_list = [ 'perch_members', 'perch_forms', ];
PERCH_LOGINPATH: /login
PERCH_PATH: /home/admin/public_html/login
PERCH_CORE: /home/admin/public_html/login/core
PERCH_RESFILEPATH: /home/admin/public_html/login/resources
Image manipulation: GD
PHP limits: Max upload 200M, Max POST 1024M, Memory: 128M, Total max file upload: 128M
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
Resource folder writeable: Yes
HTTP_HOST: www.uk-cpi.com
DOCUMENT_ROOT: /home/admin/public_html
REQUEST_URI: /login/core/settings/diagnostics/
SCRIPT_NAME: /login/core/settings/diagnostics/index.php
Drew McLellan

Drew McLellan 2633 points
Perch Support

Are you using relationships? If so, have you checked that you've not created any circular relationships?

Dan Lee

Dan Lee 1 points

Yes, I am using relationships, and yes there are circular relationships in operation. Example

Technologies related to Case Study Case Study is related to Technology

Drew McLellan

Drew McLellan 2633 points
Perch Support

That's the error then. Why do you need a relationship in both directions?

Dan Lee

Dan Lee 1 points

For building and displaying related content. Here is a real world example:

The Technology - containing related Case Study in sidebar: https://tinyurl.com/yam9s7vk
The Case Study - containing the technology tag "Biologics" under the article. https://tinyurl.com/ybdva59g

What would be a better way of doing this?

Drew McLellan

Drew McLellan 2633 points
Perch Support

You only need the relationship one way. I would create a relationship in Case Studies to Technology.

Then when you output the technology, filter the case studies using something like:

'filter' => 'tech.slug',
'value' => 'slug-for-this-item',

where tech is the ID of the relationship and slug is your slug field, and slug-for-this-item is the slug of the technology item you're currently displaying.

Dan Lee

Dan Lee 1 points

Ok thanks Drew - we will amend this.

What are your thoughts on a database table being 1.6GB? Is this a potential risk due to the size?

Drew McLellan

Drew McLellan 2633 points
Perch Support

It should dramatically drop after each edit once you've fixed your architecture.

Dan Lee

Dan Lee 1 points

Thanks. Last question. What about relationships set aginst their own collection item?

Example: Display Related Case Study in Case Study - is this bad practice too?

Drew McLellan

Drew McLellan 2633 points
Perch Support

It depends how fond you are of infinite loops! It's generally not going to end well.

Dan Lee

Dan Lee 1 points

Ok so just to confirm the following is bad practice?

In my Case Study Collection template I have the following...

    <perch:related id="case-study" suppress="true" collection="Case Study" label="Case Studies" divider-before="Promoted Content" help="Choose a alternative Case Study you want to promote in the side bar.">
    </perch:related>

<!-- later on down the page show one related case study -->

<perch:related id="case-study" collection="Case Study">

          <perch:if id="perch_item_index" match="eq" value="1">

              <div class="card featured-card">
                <a href="/case-studies/<perch:content id="titleSlug" />">
                  <div class="card__case-study">
                      <div class="card__case-study-img" style="background-image:url(<perch:content id="postImageCard" type="image" width="620" height="349" crop="true" />);">
                      </div>
                      <div class="card__case-study-content">
                        <div class="card__date">Related Case Study</div>
                        <h3><perch:content id="heading" /></h3>
                        <div class="cat__icon"><svg class="icon icon-chevron-right"><use xlink:href="#icon-chevron-right"></use></svg></span> <span class="cat__icon_text">Read More</div>
                      </div>
                      <div class="card__case-study-bg">
                      </div>
                  </div>
                </a>
              </div>

          </perch:if>

      </perch:related>
Drew McLellan

Drew McLellan 2633 points
Perch Support

It's not so much bad practise as it's just not going to work. You're sending Runway into a loop trying to resolve a relationship that never ends. That's why it keeps going for 4 minutes and fills up your database table.

Dan Lee

Dan Lee 1 points

Doing a test my end it appears that relating a Collection Item to other items in the same Collection is adding at least 1mb per relationship created.

How would you then go about creating a Related News feature when displaying news articles? Ideally, we want to be able to select the news item specifically.

Drew McLellan

Drew McLellan 2633 points
Perch Support

I'm not sure how I'd go about that, but creating a circular relationship isn't the way.

Dan Lee

Dan Lee 1 points

Ok - Thanks for your help Drew.

Dan Lee

Dan Lee 1 points

I have been manually saving my collection items after removing the relationship field from Case Studies.

I noticed however that the index table for collections in the database keeps on adding rows on save and instead of getting smaller in size gets bigger. Current size for the index table is 3.4GB - Am I looking in the right place? What should I be checking for in the database?