osDate and mysql Performance Optimization Tips

Well, one of my websites has been successful to the point where I have to start looking into performance issues.  It's based on osDate, so most of what I find may be helpful to others using that system.  Since osDate uses image processing, mysql and smarty templates, these are the main areas for optimization.

(The following graph from my Google webmaster tools console was added Nov 30, 2009, three weeks after I began working on the optimizations, so you can see that even as my database grows - as it does slowly over time with a larger userbase - my average page load time is vastly improved due to these performance tips)

download time after optimization (graph from google webmaster tools console)

Continued Improvements From Optimization

(Here's another graph from December 12th showing even more improvements - despite significant increases in user base and page views - it pays off to optimize!:)



I'll continue adding to this page, so you may want to bookmark it and come back later as I add more to it.

  1. Make sure you are using the newest versions of the simple search and advanced search queries (osDate 2.5) rather than anything osDate 2.2 or older, because there are some subtle but major improvements in efficiency that reduce search page speed from 2-3 seconds to 1-1.5 seconds (before including any of the other optimizations).  Additionally, you may want to reduce the number of values that are returned from the sql queries, if (like me) you are not using all of them - zip code, for instance, no reason to waste CPU transmitting that, as I don't keep any zip code data in my site.
  2. You should set up your mysql database so that slow queries are added to the slow query log.  You can then read the log directly or with a tool like maatkit's mk-query-digest
  3. Here's a good tip that I came across from using the slow query log.  This will reduce database load and quicken response time (on my dedicated server, it reduces the query for a user's inbox from .25 seconds to 0 seconds -- and this query happens dozens of times per minute on my system):  create index mail_owner_index on mailbox(owner) ; [ And here's one that shaved 40% of the execution time off showing a typical user profile, and also improved other page performance: create index user_regdate on user(regdate) ;]
  4. optimize your tables regularly.  I think there's no osDate cron job to do this, but you can do it by clicking Optimize Tables from the admin panel.  Once a week is a lot better than once a year!  Your hosting company may do it automatically, but since I'm on a dedicated server I'll have to modify the script to run from a cronjob.
  5. Set Smarty so that it does not check for newer versions of its source templates, by setting compile_check = false in Smarty.class.php.  You'll have to remember that if you change your code after this, that you'll have to delete the files from templates_c, which is where Smarty stores precompiled templates, in order to force them to be recreated.  This reduces excess disk accesses, and I found in general gave me 100ms better performance when logged-in on pages like the home page.
  6. Install APC - this is a php-compiler that does some parsing of php files and caches it so that subsequent calls to the same php file (within about 2 hours) do not need to have the same preprocessing.  It sometimes can speed up your php by 2x or more.  In my installation, most of the time is usually spent in the database, so I found minimal benefit from this.  But if I make more progress optimizing the database interactions or get a more powerful server, it might make a bigger difference.  Here are two pages with tips on the installation and another page with additional apc configuration instructions.
  7. Here's one I tried that didn't help at all: I switched the adodb layer from the standard, huge adodb library to adodb-lite.  But while it's smaller, and supposed to be faster, in my setup, it was a hair slower.  I didn't investigate this fully, but I can tell you that the query timing debug library of adodb-lite was handy even if the overall library wasn't a performance improvement.
  8. Put your piccache, cache, templates_c, and session directories in RAM disk (in linux, it's as easy as locating those directories in /dev/shm).
  9. Surprisingly, the code base supplied with osDate does not have its own anonymous-page-caching setup properly.  The cache functions work fine, but they're only enabled for profile pages, which are far from the most important.  On my site, the home page, search results pages, signup, and newest members pages, are all more important than the profile pages.  On average, measured using Yslow in Firebug (Run, then Components, and click doc for the html file only) - the typical response times for these pages went from 1000-2000 ms (with a few even as high as 6000 ms ) to 300-500ms, including the 100ms ping to my server.  Of course this is only for the html, and only for freshly-cached pages (I set my timeout at 15 minutes).   I'd like to make it even faster (there's no way I could possibly beat google's download for its landing page of 175ms, but I'm already faster than Amazon's landing page, which was surprisingly slow at 1500ms for the html only).  In order to cache other types of pages, what you need to do is a three-part process:
    1. The easy part is just to make sure you have set your permission on your cache/ directory to 777.
    2. Add the other page URLs around line 35 of osdate_check_cache.php (ie $_SERVER['SCRIPT_NAME'] == DOC_ROOT.'showprofile.php' ||  $_SERVER['SCRIPT_NAME'] == DOC_ROOT.'newmemberslist.php' || ....)
    3. You also need to change the call at the end of the corresponding php files from $t->display() to instead save the data to a variable, and (if it is anonymous -- to make sure you're not showing a logged-in user's page with username to an anoymous user)  like this:
      1. $cached_data = $t->fetch('index.tpl') ; 
      2.   echo($cached_data);
      3. if ( (isset($_SESSION['UserId']) && $_SESSION['UserId'] == '') || !isset($_SESSION['UserId'])   ) {
      4. require_once FULL_PATH.'includes/internal/osdate_save_cache.php'; }
  10. The newest version of osDate (2.5) has image caching, but I haven't looked into it yet.  I implemented a very simple and very efficient cache (hack) for thumbnails only - which on my site are 90% of the image views.  It resulted in a noticeable CPU load reduction, and noticeable page load time reduction across the board of about 15% in my google webmaster tools console.  You'll have to customize based on your site URLs, but the idea is simple to store a preformatted jpg image in a cache directory for these thumbnails.  Here are the basic elements you must add to getsnap.php:
    • $snap_cache = dirname(__FILE__).'/'. 'piccache/';
    • if ($_GET['id'] && ( $_GET['width'] == 100 ) && ( $_GET['height'] == 100 ) && ! $_GET['picid'] )  { $file_contents_from_cache = imagecreatefromjpeg ($snap_cache.$_GET['id']) ;}
    • Then include all the image processing (most of the file) in an if:
      • if (!$file_contents_from_cache) {}
      • At the end of the IF (inside) then store your new snaps in the cache dir:
        •  if (($_GET['width'] == 100 )&& ($_GET['height'] == 100)&& ! $_GET['picid']) { imagejpeg($img2, $snap_cache . $_GET['id'] ); }
    • Finally, at the end of the file, if your file came from the cache, display it from cache; if from image processing, display from the respective variable, something like:
      • if (!$file_contents_from_cache) { // we just created the image now... imagejpeg($img2); // send to browser imagedestroy($img2); } else { // we already have the thumbnail from the cache stored in our variable... send to browser and that's it imagejpeg($file_contents_from_cache); imagedestroy($file_contents_from_cache);
    • Don't forget to create your piccache directory and set permissions to 777 - also you should delete all the files every few days, in case any users change their pictures.  I also delete all cached pictures for any user, anytime they upload a picture, which is even better.