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
Have you checked your logs?
When updating the database this is what we see in the logs:
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.
Thanks Drew - we are due to upgrade so I'll let you know if this makes a difference.
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?
Are you using relationships? If so, have you checked that you've not created any circular relationships?
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
That's the error then. Why do you need a relationship in both directions?
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?
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:
where
techis the ID of the relationship andslugis your slug field, andslug-for-this-itemis the slug of the technology item you're currently displaying.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?
It should dramatically drop after each edit once you've fixed your architecture.
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?
It depends how fond you are of infinite loops! It's generally not going to end well.
Ok so just to confirm the following is bad practice?
In my Case Study Collection template I have the following...
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.
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.
I'm not sure how I'd go about that, but creating a circular relationship isn't the way.
Ok - Thanks for your help Drew.
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?