Slooooow C220.127.116.11 site needs help
I have a new installation of C18.104.22.168 that uses Bootstrap Theme and few add-ons:
- Bootstrap Carousel
- Remo Expand
- Spacer (travisn_spacer)
- Simple PHP (nd_php_package)
- global_areas nd_php_package
I have loaded this site with about 4500 imported pages (using the API) in a structured tree, divided in about 10 page types, each having a different sidebar (i.e. one stack for each page type).
After having everything imported, the speed decreased horribly, right now one page loads in approx 7-12 seconds with no other users/visitors but me, (7-12 seconds depending on database optimization settings). Eg:http://v1.maya-control.ro/virusbulletin/2016/01/throwback-thursday-...
Is true that I have caching disabled completely as I am still in development. With caching on, page loading is under 1 second but this is not the point as when editing a page (i.e. when logged in) it takes 7-12 seconds to display the page, plus the same amount for putting it in edit mode, and then once more to save changes, which is really counter-productive.
I had the site installed in Digital Ocean on a 2GB/2 cores server initially and I have tried to scale it up to 4GB/2cores and even 8GB/4 cores but no performance improvement at all; which is quite understandable since at no point the memory/cpu in the smallest 2GB/2cores virtual machine were fully used with only me as user.
Then I have tried upgrading MySQL from the 5.1 version (that came default on CentOS 6.5) to 5.5, 5.7 and even MariaDB 10.1 and tune it as much as possible (and as much as I know how to). The performance is not influenced by MySQL version, it is however increased by MySQL tunning (10-12 seconds per page vs. 7s when tunned). Still, 7s is a lot.
I've installed Extreme Clean Add-on; run it without any effect on speed.
Today I have enabled query logging for MySQL and I have discovered that for only one page request, C5 is issuing no less than 18,000 SQL requests which I find quite extreme for a page that has basically 3 main content blocks, a sidebar with 1 block and 2 stacks and one stack and header/footer. Attached is such a log for only one page request spanning over 11 seconds of sql statements.
I think here it is the problem. Anyone able to help? I am considering a payment for a positive solution to the problem, however it should be a reasonable/decent sum.
I can be emailed in yahoo com as "csebe" for more details/discussions.
EDIT (2 days later):
Thank everyone for their great support and ideas. I reduced the loading time greatly with only one change:
I had my site main navigation using an auto-nav that had:
"Display pages: at top level"
"Sub pages to display: Display all"
"Sub-page Levels: Display all"
Then, to display only the first level (which is what I really wanted) I had a page attribute that came with the bootstrap theme named: "Hide children from navigation" that was enabled for each of the top pages.
This was wrong, as for every page, there were queries to dig in the whole site tree each time, then only those not having the "hide children" attribute were displayed (the others filtered out).
That's why the loading time increased dramatically with the increase in the number of pages and that's why it manifested on every page, no matter if it had page list blocks or stacks in them or not.
The rest is fine tuning and I will try to apply every suggested answer from this thread.
11s ttfb for the html
3.6 mins stalled on ccm.app.css
I suspect the 3.6 mins was the server clogged up waiting for all the logging to complete after serving the html.
Long waits for a page and massive numbers of database queries are often the result of poorly considered autonavs or page lists - loading massive numbers of pages to look at their ancestors and attributes, then filtering them down to just a few to show. That does not appear to be the case with the page you reference, but all the same, have a think about those and what they could be doing behind the scene and don't forget to look at the footer nav..
Also check your MySQL server is co-resident with the web server. If its on a different host, then database queries will be further slowed by network requests.
If the above doesn't help. Swap it back to the greek yogurt theme and see if there is a difference (you won't loose anything).
Then start removing nav and page list blocks and all other blocks one at a time until you find the one that makes a big difference.
You have a lot of js loaded in the header that could be in the footer. That is unlikely to be the immediate cause of your problem, but may provide futrther gain once the immediate problem is resolved.
Starting from your pointers I have found that what's delaying is the fact that each of the 4500 pages has a stack in it, and that stack has a some sort of "show latest 5 pages from this category" page list block.
Also, somehow, the bootstrap theme uses also stacks for each of the areas in header and footer (!). As soon as I took all these out (including my legitimate ones) it started to move faster and comparable with plain yoghurt: plain yoghurt with all the articles installed retrieves a page in about 0.87 secs, whilst bootstrap in ~1.5 secs (there is obviously more css and js in the bootstrap theme).
The import was done having the page types pre-prepared with stacks included in their page defaults, so after importing all pages got the stacks in. Perfect in theory but killing everything.
Now, I have modified all page defaults and removed all stacks from them, so the problem is to add the same functionality back, but in a different way that is not impeding the speed. For the bootstrap header and footer areas, I can go as deep as writing directly into the php files and not even using a content block, as they are quite static.
However, not sure what to do for my sidebars that need to present most recent 5 articles from that category they're in; there are 6 categories (each having 3 page types, like article, monthly article index, yearly article index) and until now each used one separate stack.
I still need to do this separation at this granularity, however I'll try to add things directly in page defaults instead of adding them in stacks.
here, it is another problem: I have tested by trying to add one page list block in the page defaults of a test page type (that had 4500 pages spawn out of it) and when I tried "Setup on child pages" it crashes after a while not finishing all of the 4500, no matter how many times I try.
Any idea on how to avoid the stacks problem but still have different sidebars (i.e. dynamic, that present latest 5 articles in that category of articles) per page type ?
You should look at the way the page list in the stack is filtering. It will be the page list that is slow, not the stack that is used to load it.
Page lists are not good in page defaults because they evaluate w.r.t the default page, not the instance of the page that is shown from the defaults. Hence they often need to search wider/deeper than a more specific list. You will probably need the stack for the list, or perhaps that is why a stack was used in the first place.
You have jquery.fancybox.pack.js taking 4.97 seconds and is not even used.
Is query logging still happening?
Having had an initial look at the queries, you should turn it off as it will be obscuring any measurement.
In my defence, at the end of the initial post there is a request for quotation if someone is interested to take it over. However, it looks like the C5 community is exceeding expectations, again :)
EDIT: And actually I have received now an offer, so other understood it as job posting, as it was intended :)
<FilesMatch "\.(js|css|html|htm|php|xml)$"> SetOutputFilter DEFLATE </FilesMatch> <IfModule mod_headers.c> BrowserMatch MSIE ie Header set X-UA-Compatible "IE=Edge,chrome=1" env=ie </IfModule> <IfModule mod_expires.c> # Enable expirations ExpiresActive On ExpiresDefault "access plus 1 month" ExpiresByType image/x-icon "access plus 1 year" ExpiresByType image/gif "access plus 1 month" ExpiresByType image/png "access plus 1 month" ExpiresByType image/jpg "access plus 1 month"