Help with Database Transactions

Permalink 2 users found helpful
I've been learning over the last few weeks the C5 is very greedy when it comes to loading a page object - and on a site with hundreds, if not thousadns of pages, site speeds drastically decrease.

I installed a tool on my server called New Relic, which traces out specifically what transaction are taking the most time.

I've been testing on this site:
http://www.metrosportscommission.com... - where pageload times are now up over 30 seconds.

I've attached a couple of screenshots from New Relic so you can see what I'm seeing.

Currently, the most time consuming Database Calls are PermissionsAccess - SELECT, AttributeValues - SELECT and AttributeTyles - SELECT.

On a site without Advanced Permissions turned on, the major culprit is Pages - SELECT.

You'll see all of that in Screenshot 1

In screenshot 2, it shows a graph outlining Time Spent in Database and Time Spent in PHP. Both are astronomical.

What can I do to fix this?

I sure hope it's not a problem with the c5 core that would require major re-building.

2 Attachments

leinteractive
 
Job replied on at Permalink Reply
Job
I've had this myself.
The patch (not fix) was to disable caching through Dashboard and then install APC (a PHP library).

This brought the page load times down. They're still not perfect, but better.
leinteractive replied on at Permalink Reply
leinteractive
I already did that.

Went so far as to actaully define apc as the cache libray in the site.php file:

define('CACHE_LIBRARY', 'apc');
Remo replied on at Permalink Reply
Remo
Access to PermissionAccess can be reduced with a patch that's already available but not released into a stable version.

Check this comment:http://www.concrete5.org/community/forums/installation/time-to-firs...
Remo replied on at Permalink Reply
Remo
Is the top navigation based on autonav? Can you post the settings you've used there? (Display sub pages, relevant etc. etc.)
leinteractive replied on at Permalink Reply
leinteractive
Thanks, Remo.

I'll check out the github file.

The Top Nav is hardcoded. That was the first thing I did to try to speed things up - didn't seem to help at all.
Remo replied on at Permalink Reply
Remo
What do you mean by hardcoded?
$bt = BlockType::getByHandle('autonav');
$bt->controller->displayPages = 'top';
$bt->controller->orderBy = 'display_asc';                    
$bt->controller->displaySubPages = 'all'; 
..


?

This change anything regarding the performance, but value you specify can change a lot! Please post the code if you're using something like above.
leinteractive replied on at Permalink Reply
leinteractive
By hardcoded, I mean I copied the HTML Output that the automav generated and pasted it in place of the Nav Area - there's no area or block in that place at all. Just HTML from the template file.
Remo replied on at Permalink Reply
Remo
Okay I see. On a site without APC, this has quite an impact..

Does your site have a lot of attributes? Maybe attributes you could remove? Whenever a page object is loaded, it also loads all the attributes.
leinteractive replied on at Permalink Reply
leinteractive
What do you mean it has quite an impact on a site without APC? What sort of impact?

I'm not sure how you would define "a lot of attributes."

We use Image Attributes and pull them in as thumnails for various pages. Then on each those pages themselves, the attribute is pulled in via the theme file to display the image directly on the page - that way our content editors don't have to monkey with images in the Content Block.

We use Chad Strat's ProEvents addon, and there are dozens of attributes that come packaged with that one, plus we added a few custom ones. We have about 300 or so events under or events section.
Remo replied on at Permalink Reply
Remo
Loading a page object (Page::getByID) in one of my servers takes about 20ms if APC isn't active. If you have an autonav with 100 pages, it will take 2 seconds to build the internal array of pages.

We often use a file attribute to display a thumbnail to, but I don't think we've ever built a site with 20 attributes and probably even 10 would be a lot for us. I don't think it should cause a lot of troubles if APC is active, but on sites without APC, loading 50 attributes take a lot of time. 50 pages * 50 attributes results in a pretty high number and also in a lot of MySQL queries.
leinteractive replied on at Permalink Reply
leinteractive
I'm not saying we have 20 different page attributes with images in them.

We have a single Image Attribute that we have added onto every page of the "New Item" pagetype.

So when my pagelist loads, it has to fine 10 or 20 or 30 or whatever number of pages I specify in the controller and load all of those.

It seems to me that c5 is trying to load ALL of the pages and then filtering out the ones needed as opposed to only loading the ones needed in the first place.
Remo replied on at Permalink Reply
Remo
Well, concrete5 might load all pages even if they aren't needed. That's why I've asked about autonav, if you have an autonav block with "show all sub pages" in it, it will load all pages, even if some of them are filtered in view.php...

But concrete5 definitely doesn't load all pages by default. Things like that can happen in autonav, pagelist and other blocks from the marketplace.
leinteractive replied on at Permalink Reply
leinteractive
Hmmm...well I guess I'm not sure what's taking so long then. All my pagelists are using the core Page List Block with custom view templates - but I'm not loading anything that the controller isn't otherwise giving me.
Remo replied on at Permalink Reply
Remo
any chance you can create a clone of your site and remove some things there? add-on's, pages, attributes..
leinteractive replied on at Permalink Reply
leinteractive
Yea...it would take some time. Anything in particular you think should be removed?
Remo replied on at Permalink Reply
Remo
I usually start by removing some blocks from a page which is slow and then start removing add-ons.
leinteractive replied on at Permalink Reply
leinteractive
Hi Remo -

I have a test site setup now duplicated over:

http://test.metrosportscommission.com...

Page loads are still over 30seconds, up to 47 seconds I'm seeing.

Here's what I did:

1.) Removed the ProEvents block from the homepage
2.) Removed the Page List for latest news from the homepage
3.) Removed the Page List to show Athlete of the Week from the homepage
4.) Removed "Show Files from Set" block (recent photos) from the homepage
5.) Turned Advanced Permissions OFF

Right now, the homepage has nothing on it except for two Content Blocks. The nav is hardcoded HTML.

Navigating through the dashboard pages is lightning quick...so something on the front end is causing major issues.
Remo replied on at Permalink Reply
Remo
Maybe there's a package which uses events? Can you try to uninstall package by package?
leinteractive replied on at Permalink Reply
leinteractive
Removing ProEvents didn't seem to help.

Page Loads are down from 30 seconds to 20ish - but on a page with nearly nothing on it, the culprit has to be elsewhere.
leinteractive replied on at Permalink Reply
leinteractive
Okay, I so it turns out that AutoNav was the culprit - for some reason I thought I copied the HTML output, but apparently not.

That said - My "fix" still isn't ideal.

I just told the AutoNav to only display pages at the top level.

This doesn't work because there are a couple top-level items that I need to display dropdowns for. However, the top-level item for "Schedules" has 300+ pages beneath it. I have the "Schedules" page set to "Exclude Subpages from Nav", but apparently c5 still has to load all those pages objects so it knows to exclude them.

Is there a better solution here?
Remo replied on at Permalink Reply
Remo
I'm afraid but with this structure, it's a bit tricky.

Some people use "display all sub pages" when creating a drop down navigation. This could be improved if you specify a custom level of pages to display.

If you use this "custom level" setting, the autonav block will not build an array with every page of your site.

However, filtering by attribute doesn't really help as this happens in view.php, but the array is built in the controller.

Any chance you can update your page structure to avoid having too many pages on the first two levels?
leinteractive replied on at Permalink Reply
leinteractive
I had Custom Level set to 1, but since 1 level deep under "Schedules" includes 500+ subpages, that won't work.

I guess I'll have to move the schedules to a deeper level so I can keep my top level dropdowns in tact.

Why does C5 have to load every page object in a sub-menu if I have the parent item set to "exclud_subpages_from_nav"?

You'd think that if the controller runs into that attribute, it shouldn't even load the sub pages for that item, right?

Instead, it's loading everything and then excluding what it doesn't need. That's just crazy.
Remo replied on at Permalink Best Answer Reply
Remo
Well, it doesn't have to, but I think it does.

Looking through the code makes me wonder if even what I wrote about custom levels is wrong. Check this code:

//Retrieve the raw "pre-processed" list of all nav items (before any custom attributes are considered)
         $allNavItems = $this->generateNav();
         //Remove excluded pages from the list (do this first because some of the data prep code needs to "look ahead" in the list)
         $includedNavItems = array();
         $excluded_parent_level = 9999; //Arbitrarily high number denotes that we're NOT currently excluding a parent (because all actual page levels will be lower than this)
         $exclude_children_below_level = 9999; //Same deal as above. Note that in this case "below" means a HIGHER number (because a lower number indicates higher placement in the sitemp -- e.g. 0 is top-level)
         foreach ($allNavItems as $ni) {
            $_c = $ni->getCollectionObject();
            $current_level = $ni->getLevel();
            if ($_c->getAttribute('exclude_nav') && ($current_level <= $excluded_parent_level)) {
               $excluded_parent_level = $current_level;
               $exclude_page = true;
            } else if (($current_level > $excluded_parent_level) || ($current_level > $exclude_children_below_level)) {
               $exclude_page = true;
            } else {


As I've mentioned before, I do believe that I could improve the autonav block a lot but it's not an easy nor quick task. It probably happens someday, but I can't tell you when. As long as we have work arounds for our own customers projects, there's no urgent need to rewrite this code.
leinteractive replied on at Permalink Reply
leinteractive
I hear ya. Thanks for the help.

At least I know to keep this in mind when building out navigations moving forward.

Still - for a site with a ton of subpages that need to be displayed, the autonav is going to wreak havoc.
aghouseh replied on at Permalink Reply
aghouseh
Yeah, I'm running into this identical issue. I have 3000 pages on a site I'm developing locally. When I comment out the $area->display($c) with the nav in it, my page load is ~300ms versus around 17.5-18 seconds with autonav running. All those pages are contained within a hidden subpage while I develop, so I'm going to try moving them to a level beyond that specified in my autonav to see if they will be hidden.

I'm a little miffed that there isnt a join on the "exclude_from_nav" attribute specifically, as that would solve a lot of the problem in loading unnecessary pages.
mhawke replied on at Permalink Reply
mhawke
I have to agree that the AutoNav seems to load the ENTIRE site every time it's called which is insane.

The other day I saw this odd behavior. I have all the caches turned off and I deleted everything in my 'files/cache' folder. I then chose a page from my navigation that contained no images. Much to my surprise, my 'files/cache' folder fills up with thumbnails from all the pages that contain re-sized images such as sliders. Why? I would expect that to happen only when I rendered the page that actually contained the slider (because the image helper creates thumbnails in the cache regardless of whether the caching is on or off).

The number one issue with C5 has been sluggish performance. This must change soon because it's hampering the growth of C5 because it can't be hosted on economy servers when other CMS's run fine.
leinteractive replied on at Permalink Reply
leinteractive
If you have the auto nav set to load all pages, you'll run into this problem. In only pulls from the database what you tell it to.
Remo replied on at Permalink Reply
Remo
No, he's right, some filtering happens after the object are already loaded. However, the main bottleneck imho is Page::getByID. Every single object is loaded while we could fetch all the necessary page information for one level and create the objects from that.

I tried to do something very similar to the internal PageList class. You can see my preliminary code here:https://github.com/Remo/concrete5/commit/c7d335a49b96204a3a6b8b60cd1...

I'm pretty confident that if we'd do the same for autonav, we'd get a much better performance.
malthoff replied on at Permalink Reply
malthoff
Is this still valid for the last version before 5.7, say 5.6.3.2?