perch2_content_index 585,000+ rows?
I have a site which has been upgraded many times as a Perch site, and is now a Runway site. The DB has grown to what I would consider to be a very large size considering the content. The two largest multiple item regions have been moved to collections, one with 31 items and one with 54 items.
Here are some table row stats from the DB:
perch2_collection_index: 1,620 rows
perch2_content_index: 585,149 rows
perch2_content_items: 345 rows
perch2_content_regions: 20 rows
perch2_resource_log: 1,480,855 rows
As a result of the number of rows, it is taking a very long time to save region content, with mysql hogging CPU and locking down the site during saving.
Any idea what could be wrong here? The DB doubled in size after updating to Runway.
Diagnostics:
SUMMARY INFORMATION
Perch Runway: 2.8.19, PHP: 5.6.14-1+deb.sury.org~trusty+1, MySQL: mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $, with PDO
Server OS: Linux, apache2handler
Installed apps: content (2.8.19), assets (2.8.19), categories (2.8.19), perch_blog (5.0), perch_forms (1.8.3), collection_2 (2.8.19), collection_1 (2.8.19), perch_members (1.4)
App runtimes: <?php $apps_list = array( 'content', 'categories', 'perch_members', 'perch_blog', 'perch_forms' );
PERCH_LOGINPATH: /easton-admin
PERCH_PATH: /var/www/public/easton.dev/easton-admin
PERCH_CORE: /var/www/public/easton.dev/easton-admin/core
PERCH_RESFILEPATH: /var/www/public/easton.dev/easton-admin/resources
Image manipulation: GD Imagick
PHP limits: Max upload 100M, Max POST 100M, Memory: 128M, Total max file upload: 100M
Resource folder writeable: Yes
HTTP_HOST: easton.dev
DOCUMENT_ROOT: /var/www/public/easton.dev
REQUEST_URI: /easton-admin/core/settings/diagnostics/
SCRIPT_NAME: /easton-admin/core/settings/diagnostics/index.php
What's leading you to believe it's due to the number of rows?
My very limited understanding of MySQL :) This site is considerably slower and the DB much larger than other Perch sites I've created with similar amounts of content. I don't have any other Perch sites with DBs larger than 20 MB, and this one is 150 MB+. I'm just worried that something is wrong.
I should also add that this was the site last year which was having duplicate region content and saving issues. As a result, you created the
no-index="true"option, but I just worry that there are lingering problems in the DB.OK, that makes things clearer. If you've had issues like that in the past then there's every reason to suspect something's wrong with that old data.
Did you delete the regions you imported into new collections?
Yes, as well as the pages that contained the old regions.
How heavily are you relying on resource clean-up?
Well, there are a lot of image assets and it would be nice if those could be cleaned up as they're removed from content, but I'd be willing to disable resource cleanup if that's the fix. I could test locally first. Would the fix be apparent immediately?
Turning off clean-up would be a quick fix for the million+ row table, if that's bogging your server down.
Could you run these two queries and let me know the results?
and
I'm afraid this is outside of my comfort zone...I have several Perch databases on this dev server, could these queries potentially affect all of them?
Let me ask you this: on this site, I have a page with one region containing only one item which is a simple text block. If I edit that text block and save, it's taking mysql almost 20 seconds to save. This is a pretty powerful VPS and every other Perch site on it is blazing fast. Do you think this resource table could be the problem, or am I on the wrong track here?
No, just the database you run them against. They're both
SELECTstatements - all they're doing is counting rows so I can get an idea of what's going on.A table with 1 million+ rows isn't an issue for MySQL per se. It could become an issue if the server configuration settings are optimised for smaller loads. You're talking about 20MB of data, and the database can cope with tables of 2GB on Windows and more on better file systems (up to about 64 terabytes, I believe).
The issue for me is that the amount of data is disproportionate to the amount of content on the website. The historical issues you've had with duplicate data suggest that there's probably a bunch of erroneous data in there which the system has to check and work around each time - that would cause a slow down.
That's all hypothetical without the numbers - which was what I was trying to get with those queries.
Thank you for taking the time to explain that Drew, I really appreciate it.
Here is the result of the first query:
and the second:
One more thought: the editors of this site have a bad habit of trying to upload huge images despite my warnings. I had to reduce the number of image sizes in several templates because they were getting constant out of memory warnings.
Looks like it's all collections then. Could you try this modified orphan query?
The result is
It looks like that data is all in use, then.
How many image sizes do you generate, and how many images are added per collection item?
There are around 25-40 images per item. Back when I first built the site, I was generating 5 sizes per image, but I've dropped that to 1 now, to try to combat the memory errors. I also remember in the early stages of the site, I hit some sort of problem where I was using spaces in bucket names, so the bucket name for the template changed at some point, too. I think there are a lot of unused images hanging around as a result. Also, assets may not have been a part of Perch when the site was first built.
Ok, so you should be generating something like 1600 rows per collection item. How many items do you have?
31 of this collection type, and 54 of another type which contains only 5-10 repeater images at the most.
That should account for less than 10% of what you have. Very curious.
You've not adjusted the undo buffer, have you?
Yes, back when you created the
no-index="true"option, you also advised me to adddefine('PERCH_UNDO_BUFFER', 3);to config.phpOk, so it's lower than usual, which means this makes even less sense.
I think I'm about at the limit of how I can help remotely.