Current time: 11-15-2019, 10:27 PM Hello There, Guest! (LoginRegister)

Post Reply 
 
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Inconsistent results? Testing MySQL?
03-25-2011, 09:42 AM
Post: #1
Inconsistent results? Testing MySQL?
Hi guys,

Brand new to this forum, hello! One of the most important pages on my site is also one of the slowest loading:

http://www.datesphere.com/reviews/

I ran it through webpagetest and failed almost everything (I'm working on fixes! Will probably have more posts on that soon). I theorized that the biggest performance boost I could get would be from reducing MySQL queries on the page, so I created a second versions that uses static files (from an hourly cron job that does the necessary queries) to populate the page instead of MySQL queries:

http://www.datesphere.com/reviews/index2.php

I've re-run the test several times on both pages and it's about 50/50 whether the new versions beats the old one. Load times vary greatly on both (from about 7s to 12s) which I assume is a function of how busy the network is, but I would expect one version of the page to consistently outperform the other.

I can only conclude that reducing MySQL queries from 3 to 0 had no real effect on load speed (which seems really weird to me). Does anyone know of a way to get webpagetest (or another tool) to measure php / MySQL execution time? I just want to know if I'm helping anything at all by caching MySQL queries, and if so how to measure it.

Sorry for the long post. Thanks!

Chris
Find all posts by this user
Quote this message in a reply
03-25-2011, 10:32 AM
Post: #2
RE: Inconsistent results? Testing MySQL?
Adding a ink to your test results so others can chime in as well: http://www.webpagetest.org/result/110324_WR_7ZY2/

Like you discovered, the multiple queries aren't the main driver for your woes. The queries (and the caching of static html) that you were doing all impact the time to generate the base page (first request in the waterfall) and are usually what we cal "back-end time". The bulk of the time spent loading your page comes from the "front end" which are all of the things that your html references.

It could be worse, you could have had keep-alives broken as well which would have doubled the time to load the page :-)

The easiest thing you can do for starters would be to enable gzip compression for your text resources. It's usually just a config setting on the server and doesn't require any changes to the page. It looks like you're on Apache so hopefully mod_deflate is already instaled and you just need to enable it.

Next up would be to merge the 3 javascript files into a single file (curvycorners, jquery and jquery.rating). It should be as easy as copy/pasting the contents of each file into a single file in the same order that they are loaded.

Those two changes should cut your start render time in half or so and should be the easiest to implement (if you can move the javascript to the end of the page that would be even better but you have to be careful because that might break the page).

Next up would be to merge all of those individual images into a smaller number of image sprites. spriteme can help with the task but from the looks of it you should be able to merge almost all of the images into a single sprite which would almost completely eliminate the time after start render and pull your whole page load into the 2-3 second range.

Finally, you should make the static pieces of the page cachable by giving them a far-futures expires time (the css, javascript and images). That way the repeat visits will be able to load the images directly from their disk cache and not ping your server to see if each one of them needs to be updated. This is a little more complicated because it requires you to change the file names if you change the contents (add a logo, change the css, whatever) but it is well worth the effort.
Visit this user's website Find all posts by this user
Quote this message in a reply
03-25-2011, 10:35 AM
Post: #3
RE: Inconsistent results? Testing MySQL?
btw, I just realized I forgot to answer your original question. Your php execution time is usually the main part of the "first byte time". It also includes the DNS lookup and socket connect time but that is the point at which your code has done it's work and started spitting back the page.

Technically you probably need to include the full time for the first request but I'm guessing you're not flushing the page early which means that the code has completed it's work at the first byte time and the rest of the time is literally downloading the html (uncompressed until you enable gzip).
Visit this user's website Find all posts by this user
Quote this message in a reply
03-25-2011, 10:55 AM (This post was last modified: 03-25-2011 11:19 AM by ckelly06.)
Post: #4
RE: Inconsistent results? Testing MySQL?
Pat, thank you so much for this AWESOME reply. I'm going to get working on these fixes right now.

Quote:Finally, you should make the static pieces of the page cachable by giving them a far-futures expires time (the css, javascript and images). That way the repeat visits will be able to load the images directly from their disk cache and not ping your server to see if each one of them needs to be updated. This is a little more complicated because it requires you to change the file names if you change the contents (add a logo, change the css, whatever) but it is well worth the effort.

One thing that's been challenging since I learned I should be caching static content is that there are NO good tutorials on this on the internet (or so it seems, I certainly can't find one). Actually the most helpful thing I've found so far is another thread on this forum, where I finally learned that implementing this requires changes to .htaccess (none of the other tutorials I found bothered to mention this). Also, looking at an .htaccess file my friend sent me, it looks like he's using the following code for this:

Code:
<FilesMatch "\.(xml|txt|ico|pdf|flv|jpg|jpeg|png|gif|js|css|swf)$">
Header set Cache-Control "max-age=2592000, public, must-revalidate"
</FilesMatch>

Do you think this code is sufficient, or should I do something more robust like in the thread (code from that is pasted at the bottom of this reply)?

Also, another newbie question. I signed up for a CloudFlare account today (not showing up in results yet, but I think they said it takes a few hours for DNS servers to update). Even if I'm using CloudFlare, it still makes sense to cache static content, right? It sort of seemed redundant if I'm using CF, but I assume that since you break it out as a separate item on the report card, I should do both.

Thanks again!

Chris


Extensive .htaccess code for caching:
Code:
<ifModule mod_expires.c>
ExpiresActive On
ExpiresDefault "access plus 1 seconds"
ExpiresByType text/html "access plus 1 seconds"
ExpiresByType text/css "access plus 31536000 seconds"
ExpiresByType text/javascript "access plus 31536000 seconds"
ExpiresByType application/javascript "access plus 31536000 seconds"
ExpiresByType application/x-javascript "access plus 31536000 seconds"
ExpiresByType image/ico "access plus 31536000 seconds"
ExpiresByType image/gif "access plus 31536000 seconds"
ExpiresByType image/jpg "access plus 31536000 seconds"
ExpiresByType image/jpeg "access plus 31536000 seconds"
ExpiresByType image/png "access plus 31536000 seconds"
ExpiresByType image/bmp "access plus 31536000 seconds"
</ifModule>
<ifModule mod_headers.c>
<filesMatch "\\.(ico|pdf|flv|jpg|jpeg|png|gif)$">
Header set Cache-Control "max-age=31536000, public"
</filesMatch>
<filesMatch "\\.(css)$">
Header set Cache-Control "max-age=31536000, public"
</filesMatch>
<filesMatch "\\.(js)$">
Header set Cache-Control "max-age=31536000, private"
</filesMatch>
<filesMatch "\\.(xml|txt)$">
Header set Cache-Control "max-age=31536000, public, must-revalidate"
</filesMatch>
<filesMatch "\\.(html|htm|php)$">
Header set Cache-Control "max-age=1, private, must-revalidate"
</filesMatch>
</ifModule>
<ifModule mod_headers.c>
Header unset ETag
</ifModule>
FileETag None
<ifModule mod_headers.c>
Header unset Last-Modified

On a sad side note, HostGator just informed me that they don't allow mod_deflate on business shared hosting accounts like mine. Bummer. I don't think I'm ready to upgrade to VPS yet, so I guess gzip is something I'll have to put on hold for now.
Find all posts by this user
Quote this message in a reply
03-26-2011, 02:51 AM
Post: #5
RE: Inconsistent results? Testing MySQL?
oooohhhh, HostGator - yes, they have been mentioned before for their absurd policies about Gzip compression Sad - At a minimum you can gzip your php output directly by doing this: http://php.net/manual/en/function.ob-gzhandler.php but you should express your strong displeasure to them as well.

For the expires, I'd steer away from the .htaccess from your friend. I'm pretty sure the must-revalidate will pretty much defeat the purpose, and it does the matching based on file extension. If file extensions are good enough for you then removing the must-revalidate would get you there (though I generally prefer to use Expires in place of cache-control, either is fine).

Turning on caching is the easier part though - make sure you're prepared for the consequences before you do because once you turn it on the files will be stored on the user's computers and the only way to update them (change the css for example) is to change the file name and the references to it. The way I do this is to have a constant in one of my global files for the current version of the css (or javascript) files (like $CSS_VER='3'). Then everywhere in the code where the files are referenced I add a ?v=$CSS_VER query parameter to the files. The web server ignores the query parameter and serves up the file but the browsers include it as part of the file name so when they see a new version they download it from the server. People will frown a bit because SOME intermediary proxies will refuse to cache requests with query parameters and they recommend you put the version in the actual path but for me the tradeoff is worth it since it's easier to deal with (no rewrite rules, it just works).

Yes, even if you are using a CDN you should enable caching of the files. It's actually even more important that you do, otherwise the CDN itself will not be able to store a copy of your file and they will have to go back to your web server for every request anyway (which will actually make it slower).
Visit this user's website Find all posts by this user
Quote this message in a reply
03-26-2011, 04:18 AM
Post: #6
RE: Inconsistent results? Testing MySQL?
Quote:For the expires, I'd steer away from the .htaccess from your friend. I'm pretty sure the must-revalidate will pretty much defeat the purpose, and it does the matching based on file extension. If file extensions are good enough for you then removing the must-revalidate would get you there (though I generally prefer to use Expires in place of cache-control, either is fine).

Thanks, Pat. So would you recommend I use the more "extensive" code I quoted above? It looks like that code uses both Expires and cache-control.

Quote:Turning on caching is the easier part though - make sure you're prepared for the consequences before you do because once you turn it on the files will be stored on the user's computers and the only way to update them (change the css for example) is to change the file name and the references to it. The way I do this is to have a constant in one of my global files for the current version of the css (or javascript) files (like $CSS_VER='3'). Then everywhere in the code where the files are referenced I add a ?v=$CSS_VER query parameter to the files. The web server ignores the query parameter and serves up the file but the browsers include it as part of the file name so when they see a new version they download it from the server. People will frown a bit because SOME intermediary proxies will refuse to cache requests with query parameters and they recommend you put the version in the actual path but for me the tradeoff is worth it since it's easier to deal with (no rewrite rules, it just works).

Good tip, thanks Smile
Find all posts by this user
Quote this message in a reply
03-26-2011, 04:21 AM
Post: #7
RE: Inconsistent results? Testing MySQL?
I'd use just the mod_expires section from the code you have. Here is what mine looks like:

Code:
ExpiresActive On
ExpiresByType image/gif A31536000
ExpiresByType image/jpg A31536000
ExpiresByType image/jpeg A31536000
ExpiresByType image/png A31536000
ExpiresByType image/bmp A31536000
ExpiresByType text/css A31536000
ExpiresByType text/javascript A31536000
ExpiresByType application/javascript A31536000
ExpiresByType application/x-javascript A31536000
Visit this user's website Find all posts by this user
Quote this message in a reply
03-26-2011, 04:33 AM
Post: #8
RE: Inconsistent results? Testing MySQL?
THANKS!
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)