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 with PHP

I wrote an blog entry about caching sql with php before, since then I re-wrote the php functions that I’ve been using. The main difference betweek the older functions, and what I will describe here is storing in memory versus the disk.

The old routines stored the results on disk and in doing so I assumed that I will not create a mess of files. However, about two months ago I found that my server is almost running out of disk space. By testing, I found that more than 500GBs are being used by temporary and small files thrown in the tmp directory, all of which are used to store SQL queries.

I tried to delete those files, and it didn’t work since the files were too many for the rm command. I had to run find, pipe the result to awk and write an rm command for every file. I also had to nice the process as I didn’t want to block apache or the database servers running. Finally, after approximately 60 hours the delete process was done.

I had to change the way the sql results are being stored to avoid future complications and to save the disk space. I found a PHP module called APC, which remains running with apache and can store PHP data in a way similary to assocative arrays.

After testing, I found that around 200-300 Megs of ram is sufficient to generate more than 99% hit ratio, which is excellent. What remained is how to store the data.

I had to insert a set of functions that perform update, delete and insert. Those functions will invalidate the cache of the corresponding rows. In my older website versions, I used to connect to the database for every user hit. Now, I do a lazy check in a function called dbh() which is not listed here. In some instances, if the same user keeps on browsing the website and reads pages whose rows are already cached, the website will respond without actually running any queries against the database, and it can run toally 100% out of memory. I found that in some cases those simple routines can lead to speedups of approximately 30x. Only if the user requests a row which is not cached, the code will then query the database (or update), cache the results and return it.

The function cache_query_row takes in the table name, and the condition that will be used to query. The condition is an associative array containing the field names in the keys, and the required values as data. It will then check the cache, if that field is not in the cache, it will query the database and store the result. The function expects to find at most one row. If more than one row exists, the first is returned and stored.

In case there was more than one row in the database, then start by first doing a query for the keys, and calling the cache storage functions to store the row keys, then fetch every single row using cache_query_row.

  1. /* This code is released under the latest version of the GNU Public License.
  2.  * It is provided AS IS in the hope it will be useful, without any garantees or waranties
  3.  * of any type. If you do not have a copy of the license, you may download one from:
  4.  * http://www.gnu.org/copyleft/gpl.html .
  5.  *
  6.  * NOTE: Some parts of the code references other functions in other libraries that I built,
  7.  * specifically the function query_row, is assumed to return the first row of multiple
  8.  * matches in a database query.
  9. **/
  10. $cache_prefix=‘/tmp/clkercache/’;
  11.  
  12. $cacheenabled=1;
  13.  
  14. function makewhere($array)
  15. {
  16.   $where=;
  17.  
  18.   foreach($array as $key => $value){
  19.     if (strlen($where)) $where.=‘ and ‘;
  20.     $where.=$key."=’".pg_escape_string($value)."’";
  21.   }
  22.  
  23.   return $where;
  24. }
  25.  
  26. function cache_fetch($filename, $expiration=3600) // One hour cache
  27. {
  28. global $cache_prefix, $cacheenabled;
  29. $filename=$cache_prefix.$filename;
  30.  
  31. if (!$cacheenabled) return false;
  32.  
  33. // See if it exists in APC
  34. $content=apc_fetch($filename);
  35.  
  36. if (!$content) return false;
  37.  
  38. $data=unserialize(base64_decode($content));
  39. return $data;
  40. }
  41.  
  42. function cache_store($filename,$data)
  43. {
  44. global $cache_prefix;
  45. $filename=$cache_prefix.$filename;
  46.  
  47. $coded=base64_encode(serialize($data));
  48. apc_add($filename,$coded,3600); // One hour
  49. }
  50.  
  51. function cache_query_row($tablename, $condition)
  52. {
  53. $coded=serialize($condition);
  54. $row=cache_fetch("clker-$tablename-$coded");
  55.  
  56. if (!$row){
  57. $row=query_row("select * from $tablename where ".makewhere($condition));
  58. if ($row!=FALSE) cache_store("clker-$tablename-$coded",$row);
  59. }
  60.  
  61. return $row;
  62. }
  63.  
  64. function cache_delete($filename)
  65. {
  66. global $cache_prefix;
  67. $filename=$cache_prefix.$filename;
  68.  
  69. apc_delete($filename);
  70. }
  71.  
  72. function cache_update_row($tablename, $condition, $row)
  73. {
  74. global $dbh;
  75. $coded=serialize($condition);
  76.  
  77. // Force requery next time
  78. cache_delete("clker-$tablename-$coded");
  79.  
  80. // Update the database
  81. dbcon(); // Lazy test to connect to the database if needed
  82.  
  83. $res=pg_update( $dbh,
  84. $tablename,
  85. $row,
  86. $condition);
  87.  
  88. return $res;
  89. }
  90.  
  91. function cache_insert_row($tablename, $row)
  92. {
  93. global $dbh;
  94. $coded=serialize($condition);
  95.  
  96. // Update the cache
  97. cache_store("clker-$tablename-$coded",$row);
  98.  
  99. // Update the database
  100. dbcon();
  101. $oldsqltime=$sqltime;
  102. $sqltime-=getmicrotime();
  103. $res=pg_insert( $dbh,
  104. $tablename,
  105. $row);
  106.  
  107. return $res;
  108. }
  109.  
  110. function cache_delete_row($tablename, $condition)
  111. {
  112. global $dbh;
  113. $coded=serialize($condition);
  114.  
  115. // Update the cache
  116. cache_delete("clker-$tablename-$coded");
  117.  
  118. // Update the database
  119. dbcon();
  120. $oldsqltime=$sqltime;
  121. $sqltime-=getmicrotime();
  122. $res=pg_delete( $dbh,
  123. $tablename,
  124. $condition);
  125.  
  126. return $res;
  127. }

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