Weblog Home Web site's Home Clip art Search Clker.com – Available downloads RSS

Recent Posts:

arrow image github a valuable opensource too...
arrow image Easy chrome extension to convert...
arrow image Updated svg edit - much better p...
arrow image Professional graphics designers:...
arrow image Refresh when drawing
arrow image That's impressive
arrow image Another quick feature added
arrow image Image editor now works on Intern...
arrow image Manually tracing cliparts out of...
arrow image Draw edit the SVG images online
arrow image Where to find good old public do...
arrow image Developing clip arts using old p...
arrow image Expected downtime tonight
arrow image Automatic vectorization is up an...
arrow image Ubuntu lucid lynx is extremely s...
arrow image Integrating the tracer
arrow image Automatic vectorization, coming ...
arrow image Updated clker interface
arrow image using ubuntu as my main desktop ...
arrow image Full time on clker
arrow image Clker will be down tonight
arrow image Tracing local and lineart images
arrow image Bad HD - clker will be down on S...
arrow image Updates to profiles
arrow image Crayon video - Marathon runner
arrow image A detailed tutorial on Crayon
arrow image Crayon image resources & tutoria...
arrow image Example tracing president obama
arrow image More on Crayon
arrow image First version of the online trac...
arrow image Now you can edit your uploads
arrow image Clipart tracer: New feature comi...
arrow image Apache hanging once in a while -...
arrow image Injuries from working on compute...
arrow image Restarting the server once in a ...
arrow image Writing my own webserver
arrow image Flex - Zooming in on large bitma...
arrow image Linux average load
arrow image Caching SQL with PHP
arrow image Wordpress plugin version 1.2 rel...
arrow image Fixing vote up/vote down icons
arrow image Google native client
arrow image New wordpress, looks great
arrow image Bug reports and feature requests...
arrow image Further flex testing
arrow image Some flex ideas
arrow image actionscript mode for emacs
arrow image Linux spirits crawling in chrome
arrow image Wordpress clip art plugin
arrow image Adding Clker.com to opera search...

Sites I like:
Arabic movie database

Caching SQL results with PHP

I’ve been looking around lately on the best way to cache SQL results in PHP. I found some interesting articles posted in lots of places, but I didn’t find any that exactly matches my needs. The problem I have on hand is basically the same every growing website faces: decreasing mean resource usage per page request.

Now – this is my plan A to keep up with the website’s growth without a lot of hardware upgrades. There is a plan B, but I will keep that to a later post.

Usually, the time consumed by executing the SQL statements is more than 80% of the whole script time. Reducing this time will significantly reduce the resource usage. CPU is not the bottle neck, usually SQL queries result in a significant disk access especially if there’s a lot of inserts/updates/deletes.

The easiest and best way I found is to use PHP’s serialize to store the SQL results. This requires that we fetch all the rows we’re dealing with. So the sequence of operations is to check the cache file, if expired or does not exist then fetch all the rows from the result set, serialize in one variable, write it to the cache file.

  1. <?
  2. require_once "utils.php";
  4. $cache_prefix=‘/tmp/clkercache/’;
  6. function cache_fetch($filename, $expiration=3600)
  7. {
  8.   global $cache_prefix;
  9.   $filename=$cache_prefix.$filename;
  11.   if (!file_exists($filename)) return false;
  12.   if (time(0)-filemtime($filename)>$expiration) return false;
  15.   return $data;
  16. }
  18. function cache_store($filename,$data)
  19. {
  20.   global $cache_prefix;
  21.   $filename=$cache_prefix.$filename;
  23.   @mk_dir($filename);
  24.   return file_put_contents($filename,base64_encode(serialize($data)));
  25. }
  26. ?>

So the way to use the caching functions would be something similar to this:

  1. <?
  2. if ( !($pictures=cache_fetch("mypictures.cache")) ){
  3.   $res=query("select pictureid from pictures order by rating15days desc limit 100");
  4.   while ($row=fetch_array($res))
  5.     $pictures[]=$row;
  7.   cache_store("mypictures.cache",$pictures);
  8. }
  9. ?>

So how to do that if you have multiple queries? The way I store my cache files is by giving them suffix or prefix names depending on what is being stores. For example a file that store the pictures that are related to a picture whose id is 1500 being displayed, will be called related-1500.cache. For more complicated cases like searches, I would use the md5sum of the request URI.

The results were good. Using apache bench I tested the homepage. Before any caching it used to support 3 pages per second. After caching the result was 35. I think in my current situation with my current traffic, this is a very good result.

One might think so what does it take to generate pages at a much higher rate – say 300, 400 or even a 1000 or more? Well, that’s my plan B, and I’ll discuss that in more detail in another post. This plan B will generate that much pages per second.

Technorati Tags: , , , , , ,

Tags: , , , , , ,

One Response to “Caching SQL results with PHP”

  1. Clker.com - weblog » Blog Archive » Caching SQL with PHP Says:

    [...] art cleanup What are the candidates web serv… Firefox 3 this Tuesday Caching SQL results with PHP Gatner “Windows is collapsing” Multiple SVG uploads Creating a tar gz on the fly [...]

Leave a Reply

Fatal error: Call to undefined function akismet_counter() in /home/webs/www.clker.com/blog/wp-content/themes/mibrahim/footer.php on line 27