Forum
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
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?
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
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.
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...
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?
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.
Built on Perch or on Runway? Always version 2.8.26?
On Runway. Version was maybe 2.8.15 or lower at the time.
Ok, so it's possible you hit an old bug.
In your dev environment, make a backup and then run this:
see if that reduces the row count.
I did this but no rows were affected.
Querying a single item and single revision like:
returns 758 records.
I have many records which look something like this:
It looks like the index is going through all related items trying to index them...?
Do you have circular relationships?
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
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.
Thanks Drew for looking into this and I am wondering if there was a better way to deal with these relationships.