Time to First Byte (TTFB) Problems - Database Issues???2 users found helpful
I've been using C5 for quite awhile with no problems, but recently I've experienced significant lags in loading my pages. First, here's my set-up:
Host: Hostpapa (dedicated IP)
C5 Version: 126.96.36.199
PHP Version: 5.3.16
To illustrate the problem, here are some links to tests run on WebPagetest.org:
Besides the TTFB lags, I'm also occasionally getting "508 Resource Limit Reached" errors.
I talked to Hostpapa and they say that their Apache server is functioning properly. I have checked all my databases (in CPanel) and they're all okay. I implemented the Cloudflare CDN which has improved loading content after the TTFB, but hasn't done anything to help TTFB. I have wandered all over the C5 forums looking for a fix and have found much tips regarding speeding up loading content (e.g. installing Miser, etc.) and have tried many of these tips but, again, the TTFB remains very high.
Can anyone give me any pointers on how to get the TTFB down? Is this a database issue (i.e. slow queries), problems in C5, or what?
Thanks SO much for any help/advice offered!
Thanks, again, for any help/advice offered!
I would also check to see how many page versions you have. Running the remove old page versions jobs could help you out as well.
Caching is off. I couldn't find the remove old versions in the dashboard (even though the php file is in the /concrete/jobs directory!) so I used the site map to remove all old versions. I also deleted all files/directories in the /files/cache directory. However, the site is still unacceptably slow. Here's another test run:
Any other thoughts?
Thanks, again, for any help you can provide.
I have done a bit of experimenting. First, I created a static html page (test.html) with only "This is a test page" for content. No images, css, nothing. This page loaded *very* fast (395 ms) as seen by this test report:
Then, I installed a new C5 site with only the sample data so now we're doing MySQL queries. This site loaded reasonably quickly (TTFB of 8,467 ms for first view and 5,396 ms for repeat view). Here's the test report on that one:
Finally, I re-ran the test on the main page for my main site. The load was *terribly* slow (TTFB of 23,818 ms for first view and 20,772 for repeat view). Here's that report:
Obviously, what's slowing everything down appears to be the MySQL queries. My first question is whether the TTFB for the test site (8,467 ms) is "reasonable" given it only contains the sample data? If not, then where should I start looking for bottlenecks?
If the TTFB for the test site *are* reasonable, then is my problem with MySQL (e.g. a trashed index or something) or perhaps one (or more) of my C5 plug-ins?
Finally, are there any "tools" I can use to watch what's happening in-between the first connection and TTFB to maybe diagnose what's going on?
Thanks, again, for any help/advice provided.
395ms for a static HTML page is very slow I'd say! A PHP CMS always does several times as much as a static HTML page.
Of course a simple, raw html page is going to load super-fast compared to a full-blown CMS running under PHP and MySQL! The reason I ran that little test was to see if anyone felt the server is running slow even *without* the back-end overhead of PHY and MySQL.
You seem to have answered that question by saying 395 ms is "very slow for a static html page." If that's the case, then perhaps I need to *first* worry about diagnosing and fixing a slow server *before* I start worrying about the databases and CMS applications?
If 395 ms is "very slow" for a simple static html page, then what specifically could be causing that slowness? What should I be asking my hosting company's tech support people to look for?
Second, do you think the TTFB for the test site (fresh C5 install with only the sample date) is "reasonable" or is that slower than should be expected too?
Thanks for any help you can provide.
The home page contains several blocks and even fetches data form another server with every request!
My site's server is located in Toronto, Canada, and the tests were run from Chantilly, Virginia, so the everything is on the same continent. Those results for your site are pretty impressive. Also, I loaded your site from here in Canada it it came up quickly.
MyPHPAdmin shows this for MySQL:
Server: Localhost via UNIX socket Server version: 5.5.27-cll Protocol version: 10 User: xxxxxxx@localhost MySQL charset: UTF-8 Unicode (utf8)
It appears that the databases are on localhost, or should I still ask?
I added "define('CACHE_FRONTEND_OPTIONS', serialize(array('automatic_cleaning_factor' => 0)));" to the site.php file yesterday and it made some difference in load times *after* TTFB but didn't seem to effect TTFB itself.
The lines for compressing output were also added to .htaccess yesterday. Again, this seemed to make some difference in loading content after TTFB but didn't seem to effect TTFB itself.
I don't see any "Optimize Website" option in CPanel. (SIGH!) Is there a way to do this from myPHPAdmin (or some other way?)
Thanks, again, for any suggestions/advice provided!
Hostpapa tech support confirms that C5 and the databases are on the same server. As well, they INSIST there are no issues with the server in terms of load or speed. They keep telling me, "You should optimize the databases... that should fix your problems." I have created a cron job to run mysqlcheck -Ao twice daily and the reports coming back from that are saying everything is optimized.
To recap, here's what I have already done:
1. Optimized the databases (see above)
2. Caching is as follows: Basic Cache = OFF, Overrides Cache = OFF, Full Page Caching = OFF
3. define('CACHE_FRONTEND_OPTIONS', serialize(array('automatic_cleaning_factor' => 0))); has been added to root/config/site.php
4. AddOutputFilterByType DEFLATE to enable compression has been added to .htaccess in the site's root directory
5. CDN (CloudFlare) has been enabled and is working
Does anyone have ANY further suggestions on how I can get this puppy functioning properly? I'd move to another host, but I've prepaid Hostpapa to 2014 (BIG mistake!) and personal finances prevent me from switching right now (after all, it IS a recession folks!).
Again, a HUGE THANKS to all who have provided tips and suggestions! And THANKS for any further help provided!
Pretty awful, eh?!?! (SIGH!)
I'd bet that I could improve the performance of your site if they'd give me full root access. I'd do this by either killing all other customers on that server or by changing the configuration. You can ask them if they are willing to take the bet, after all it is a recession ;-)
concrete5 certainly needs a lot of resources, and just saying that you should optimize your database sounds a bit blunt to me.
But anyways.. If you check this discussion, you'll see a number of patches I created that reduce the number of SQL queries which in case the database is the bottleneck, should improve things:http://www.concrete5.org/developers/bugs/5-6-0-2/page-load-time-inc... But always make a backup before you apply such changes, they aren't well tested yet! Bleeding edge!
If possible, can you please try to install an add-on? I've explained it here:http://www.concrete5.org/developers/bugs/5-6-0-2/page-load-time-inc... Post the two screenshots like in the other discussion and we'll see how well the database responds.
That said, I'll bet you COULD improve things if you were given root access to the server. However, I won't bother asking for that as I'm pretty sure I know what the answer will be. :-))) Anyway...
Before I get into mucking with C5 by installing patches, I downloaded the add-on to document database response. Attached are 3 screenshots of the add-on's output. Hopefully, that will give you a better clue of what's going on. If, after viewing the screenshots, you think I should go ahead and install the patches, let me know.
Btw, Remo, I ***REALLY*** appreciate all the help you've given me! Although I'm fairly technically savvy, I'm *not* a programmer, so much of this has my head spinning! THANKS again!
In the second screenshot you can see that a single query is executed 1679 and consumes a total of 5.54 seconds. You can also see that the runtime for a single execution varies between 0.000091 and 0.097039. While those numbers are still very small, it also shows you that it's not a very constant number. Unfortunately the add-on of mkly which I extended doesn't show the standard deviation, I might add that one day. It might be a buffer issue on the MySQL server. If there's not enough memory available for the database, it has get remove data from the memory rather quickly and reload it again.
The first query has been improved a lot by a change Andrew made a while ago. Unfortunately it's not in the official stable version yet. But if you don't mind to fiddle around with some files, check this page:https://github.com/concrete5/concrete5/commit/14d03d48f2a5c3a8bd1571... and try to make sure concrete/core/models/permission/access/model.php looks like shown on github.
The patches I've created aren't related to the attributes, only to the collection, pages and collectionversions. I doubt that they will change a lot on your site. I'd start with the link above and see if it changes anything..
select cPointerID from Pages where cID = ? LIMIT 1
tells me, that your site has to load 115 page object for a single page. In concrete5 a page is represented as an object which has to be loaded using several methods. Loading a page always take a bit of time. It's usually not an issue but if you're server isn't the fasted, you should probably try to avoid loading that many page objects.
How can that be done? Basically check the number of links you have in your page. A drop down navigation is pretty bad for this as concrete5 has to print a lot of sub items when the page is rendered. The same goes for the date nav block in the right sidebar. Most links are hidden but they are still rendered in the HTML.
If possible, try to use a navigation where you have to display less pages in autonav.. I'm aware that this isn't always an option, I'm just stating a general rule which could cause a bad performance..
However, the webpagetest.org results are no better than before:
So, from an "internal" viewpoint it seems things improved, but from an "external" viewpoint it doesn't seem to be much better.
There's still a few suspicious and expensive queries (see attached screenshots) but the times have improved greatly too.
I can easily get rid of the date nav and gallery in the right sidebar. Unfortunately, I *do* need some sort of navigation on the site (DUH!). But as soon as I add the autonav performance degrades again:
Hmmm.... I'm going to have to give this some thought!
I got myself a root server after many switches to different virtual hosts without very often no possibility to fiddle around the server.
But still, I am bloody jealous of what Remo did to that swiss travel site. :-)
btw. Chris: If this was my site, I would try setting it up new with another version of C5, I am using 188.8.131.52 usually, as I have the best experience with it and all the bug reports of 5.6 keep me away from it until it is really fixed.
All the best!
Replacing the auto nav with a hardcoded html nav kind of defeats the purpose of a CMS, doesn't it? I mean, then I'd have to manually edit the html nav every time I added a page or changed something else.
Just as a test, I rolled back C5 to version 184.108.40.206 (by changing config/site.php) and it didn't make any difference in performance, at least as far as I could see.
well, depends. I found, that I rareley add new pages to the "top hierarchy", from my point of view. the most useful aspect of a CMS is content-editing and easy integration of add-ons.
Or try to establish the nav with a Stack that is using Textlinks - just to see if this is helping performance.
Hope you can fix this :(
I'm fairly confident that I can improve the loading process of a page object (on a site without APC) by at least 30% but the change I've started to make is a bit complicated and I'm not finished yet and will fly to Sweden tomorrow.
What does everyone think about trying to increase the memory_limit in the php.ini. Has that helped anyone in the past? I know 5.6 generally needs more memory than 5.5 but I'm not sure it solves TTFB issues.
I've also heard that installing a lot of packages & blocks can slow down a site down irregardless of whether those blocks are actually instantiated on pages.
Just more to think about.
I don't see anything in the C5 dashboard about override caching. Where is that?
Also, I haven't been able to find php.ini anywhere in my site's file structure, nor is there anywhere in CPanel to edit that (at least that I could find). Any suggestions where I might look for that?
Finally, I *do* have a lot of add-ons installed that I'm not using so tomorrow I'm going to do a clean-up and see if that has any effect.
Thanks for the further suggestions! Btw, I'd die and go to heaven if my TTFB times were the same as yours! :)
Prior to 5.6, every time concrete5 opened any file, it had to search through all the folders to see if the developer has overridden any of the files. This created a lot of unnecessary overhead when 99% of the time there is no override file to be found. Version 5.6 added the ability to remember which files have been overridden and so it doesn't have to perform this fruitless search all the time.
This overrides cache does cause problems for developers though because when we do override a file, C5 will not see it unless we remember to turn off the overrides cache.
On the php issue... the host has a default php.ini file that it's using but sometimes they let you override that configuration by adding your own php.ini file to your root folder. There is a free add-on in the marketplace that shows you what php.ini file is being used along with a gazzillion other settings. You can get the add-on here:
You can also find out the basic php settings from 'Dashboard > System and Settings > Environment'. Scroll down and see how much memory is being allocated for your site to run php scripts. It needs to be 64M or higher for 5.6.
I installed that add-on (Thanks!) and found php.ini and memory is 64M. There's a comment directly above that parameter saying Hostpapa will cancel your service if you increase memory above that, so I guess I won't touch it. :) Btw, are there any other php.ini parameters that are critical to C5 that I should check?
Today, I'm going to go through all of the add-ons and themes and get rid of those that I'm not using. Will post later the results of that on performance.
Thanks for the additional comments & tips!
The MySQL queries have improved quite a bit (see attachments) but, unfortunately, TTFB is still terrible:
Comments? Other suggestions?
Thanks, again, for any help!
Not a very classy name but the final test it does is to put a load on the server and measure how fast it can respond. Your site is so slow that this test times out.
I have attached a quick 'single page' that I just put together to try to test the capacity of the server. It ain't pretty but it spits out some numbers that might be helpful. To install it, download it and rename it 'mySQL_benchmark.php' and upload it to your '[root]/single_pages' folder. Then visit 'Dashboard > Single Pages' and add 'mySQL_benchmark' (without the .php). This will add this page to the main navigation so you will want to move it somewhere more obscure.
The numbers it spits out are for comparison purposes only. These tests are just grabbed from other sources on the net and stuffed onto this page. If you can run this page and try to cut'n paste the results here, I can compare them to my numbers.
Thanks for that benchmark file. I installed it. (Btw, you forgot to mention having to edit the MySQL login parameters! LOL!) Attached are 2 screenshots of the results. I haven't a *clue* what any of that means, so I'm looking forward to hearing y our interpretation and (hopefully!) suggestions.
I then asked 3 questions:
1. Is my account being "throttled" in any way because of resource utilization, particularly memory usage?
2. Is it possible to increase my memory allocation to MySQL higher than what it currently is?
3. Is it possible to move my site to another server that has less traffic, better performance, or more resources?
I closed by stating that a reply of "it's not the server" would be unacceptable, and that I'd *really* like some answers. I then thanked them for their time and effort.
Just now, I received a one line reply from Hostpapa tech support:
"We have adjusted the server. Your issue should now be resolved."
I have NO idea what they did, but the TTFB ratings HAVE improved significantly:
As well, the MySQL query times have also significantly improved (see attached screenshots).
My question, now, is whether the above results are "good enough" or is there more I can do to speed things up? I already see that I need to convert my JPG files to PNG format and some other things, but I'm wondering if there's some more things I can do with "tweaking" the MySQL queries to optimize them a bit more?
Finally, let me give another HUGE thank-you to everyone who's helped me out these past few days! I have learned a lot about how C5 works, and I'm VERY grateful for all of the suggestions, pointers to helpful add-ons, and other assistance!
If anyone has any further suggestions I'm all ears!
P.S. It would have been REALLY nice if they had "adjusted the server" when I first submitted the ticket! (WINK!)
I noticed that your server is 'nginx' rather than Apache. Nginx is a relative newcomer to the server world and although it is promising, I'm not sure it's the best choice right now to host concrete5. Can you ask your host if you can be moved to an Apache server?
I'm "somewhat pleased" with whatever they did last night as, now, we're at least "in the ballpark" of what the performance should be. People won't "stay on a slow site" but it's even *worse* if the site isn't even loading, eh?!? That's been reflected in my site's "hit" stats... they've been pretty much down to zero since this mess started!
However, as you said, these TTFB and render times are still "slow" by today's standards. That's why I asked for further suggestions... IMHO, There still work to be done! I'm not sure what I can get the TTFB and render times down to, but I'm pretty sure they could be a bit better than this.
Uhhh... where did you see the info that I'm not on an Apache server??? CPanel is showing that I'm on an Apache 2.2.22 server (see attached screenshot). I've never heard of nginx before!
Thanks for pointing that out! I'll look into it!
EDIT: Never mind! I see, now, where it's saying nginx... that's CloudFlare, the CDN I'm using. I'll turn-off CloudFlare and see if that makes a difference. Again, thanks for pointing that out!
Just as a test, I'm going to temporarily disable CloudFlare and see if that makes any difference.
Also, per your suggestions about reducing the number of queries, for those pages that have a drop-down menu both at the top and in the left sidebar, I'm going to get rid of one or the other (probably the header nav?) but I've got to decide which is the best way to go.
I'm also probably going to drop the date nav on the right sidebar of the main page, as you suggested. If possible, though, I'd like to replace it with something that at least displays the most recent blog post (or a summary of it and link to it). Would you (or anyone) know of an add-on that does that? Or, would doing that merely replace one query with another one? Any suggestions on this?
Finally, you also pointed out the gallery pages as requiring a lot of queries. I'm using Deluxe Image Gallery (1.1.6) because of some of its features (drag-and-drop arranging of photos, html in captions and pop-up descriptions, etc.). I'm hesitant to get rid of that add-on but think I might be able to speed things up by reducing the number of images being displayed, right? I'm going to contact the add-on's developer and ask him if there are ways to optimize the add-on. He's been *fantastic* in providing quick and helpful support, so maybe he has some ideas on how to speed things up?
In short, I definitely going to try to implement the suggestions you made. It's just a matter of figuring out the best way of doing it.
Thanks, again, for all your help!
I removed CloudFlare and TTFB stayed about the same (around 4-5 seconds) but render times went way up (from our 8-9 seconds to 14+ seconds). So, I put CloudFlare back, at least for now. I may try this again later, if I'm able to further speed-up the site.
Thanks for the suggestion, mhawke!
That page you tested has very little content, so I have been testing a page that has more content to get a truer picture of site performance. What's interesting is that if I run several back-to-back tests, the first test has fairly high results but the second test is much better. Here's an example of back-to-back tests I just ran:
As you can see, the TTFB/render times in the first test are around 7.9 and 11.8 seconds, respectively. However, a second test ran just 1 minute later gives TTFB/render times of 3.5 and 5.2 seconds, respectively.
Why??? And, perhaps even more important, are these results what I should be expecting?
I went back to Hostpapa and asked them if they could do anything else to make things a bit faster and got a somewhat snarky reply saying if I want to "have server resources all to myself" I should move to a dedicated server. I'm *not* asking to "hog" server resources... all I'd like is to squeeze a much speed out of the server as possible, given that I'm on a shared server.
So, again, I'm wondering if the results I'm getting are about as good as it gets, or can more be done? Again, I'm still working on optimizing C5 and the content as much as possible, but I'm wondering if more can be done on the host side too?
Thanks, again, to anyone who has any suggestions/tips to offer.
I do have one more idea that might be causing some issues.
As for your learning a lot from this thread... me too! :) If you've learned stuff from me then, "You're welcome!" :)
I, too, have learned a lot from you, so "Thanks again!" from me too!
Btw, I have attached a screenshot of the queries in case you want to take a look.
Or, so our testserver has a lookup time of 1.6sec, with a TTFB of 0.409sec, time to play around :)
I think it's common for the first level of tech support to be instructed to give us the brush-off because in all likelihood the casual user will accept their BS and put up with it. They may even be paid a bonus for faster customer 'throughput' so it's no wonder they don't want to spend the time required to fix the problem. I no longer put up with it and demand I be escalated immediately by telling the first person to pass me to 'Dave' or 'Frank' or whoever it was that took my problem seriously and had the power and knowledge to actually fix things.
http://thecaen.ca/index.php - This week I have had to shut my site down with a page maintenance index.html page because it became too slow.http://thecaen.ca
I was one stormweb with C5 simple script, TOFB and speed resulted in conflict with the host, did a full reinstall on a new shared server that only has 56 sites on it and run by a friend of a friend (he boosted all my bandwidth memory limits above normal). The TOFB was an issue, but much improved from the previous, but as the amount of content grew TOFB (10 problog posts a day x 3months ) became crippling.
So if it is not other sites slowing me down, what can be done?
yes, i have unused add-ons and themes, too many apis (two are lazy load), but is that 10 stories a day and 2xday homepage updates just too much over time for a shared host environment? Should I just run it on a VPS - which i cannot afford? Should dynamic sites with lots of updates run a VPS?
Ultimately, the problog posts with limited addons on the story pages had a worse TOFB than the arguably heavy loaded homepage?
a few days ago, oUtside support built the site on a his hostgator shared server (no Images) and the TOFB is much less of an issue -http://codesolvent.com/projects/thecaen/index.php...
so if this a server configuration issue, than what is the magique?
CDNs, by default, will not have a positive effect on TTFB - unless they are able to cache HTML (which is often dynamic and thus marked max-age:0 in headers). In fact, 9 out of 10, will actually add latency by adding another "hop" between the browser and origin server.
This limitation can be overcome with an intelligent caching algorithms that will override the default header directives, which ensuring caching.