Rails memcached is not very easy to introduce to a large rails installation. Memcached also chews up a lot of memory on the box and overall cached model does not work the way I needed it to. Basically, I have just a “few� queries that I needed to cache because pagination sucks just that bad in rails.
So, I built my own cache, similar to how I build them in PHP except I am not using disk cache, I am using MySQL itself to cache it’s own results

First, we need a table to hold all this info (note the ‘blob’ field)

   1  
   2  CREATE TABLE `cacheditems` (
   3  `id` int(11) NOT NULL auto_increment,
   4  `cachekey` varchar(255) default NULL,
   5  `created` datetime default NULL,
   6  `expires` datetime default NULL,
   7  `content` longblob,
   8  `cachehit` int(11) NOT NULL,
   9  PRIMARY KEY  (`id`),
  10  KEY `cacheditems_cachekey_index` (`cachekey`),
  11  KEY `cacheditems_created_index` (`created`),
  12  KEY `cacheditems_expires_index` (`expires`)
  13  )

Then we create a model called “cacheditem� which has the following functions

   1  
   2  require 'digest/sha1'
   3  class Cacheditem < ActiveRecord::Base
   4  
   5  def self.checkfor(sql)
   6  key = Digest::MD5.hexdigest(Marshal.dump(sql))
   7  logger.info "%%% checking for key #{key}"
   8  #logger.info "%%% checking by sql #{sql[0]}"
   9  Cacheditem.find( :first, :conditions => [ “cachekey = ? AND expires > NOW()�, key] )
  10  end
  11  
  12  def self.getcached(sql)
  13  key = Digest::MD5.hexdigest(Marshal.dump(sql))
  14  logger.info “%%% getting by key #{key}�
  15  #logger.info “%%% getting by sql #{sql[0]}�
  16  getc = Cacheditem.find( :first, :conditions => [ “cachekey = ?�, key] )
  17  hitcount = getc.cachehit + 1
  18  Cacheditem.update(getc.id, {:cachehit => hitcount})
  19  Cacheditem.delete_all “expires < NOW()"  # cleaner
  20  return Marshal.load( getc.content )
  21  end
  22  
  23  def self.storeresult(sql, result)
  24  key = Digest::MD5.hexdigest(Marshal.dump(sql))
  25  logger.info "%%% storing by key #{key}"
  26  content = Marshal.dump(result)
  27  logger.level = (4) # this stops display in logs of the marshal data
  28  ci = new()
  29  ci.cachekey    = key
  30  ci.created       = Time.now()
  31  ci.expires        = 30.minutes.from_now() # change as needed
  32  ci.content        = content
  33  ci.cachehit       = 0
  34  ci.save
  35  return  result
  36  end
  37  
  38  end

Then, in application.rb I added the following function

   1  
   2  def find_by_sql_cache(sql)
   3  iscached = Cacheditem.checkfor(sql)
   4  if iscached
   5  Cacheditem.getcached(sql)
   6  else
   7  result = connection.select_all(sanitize_sql(sql), "#{name} Load").collect! { |record| instantiate(record) }
   8  Cacheditem.storeresult(sql, result)
   9  end
  10  end

just throw “_cache� after any “find_by_sql� statement you have a need to cache and there you are.

This works very fast, very well, and doesn’t hog your memory. It cleans up after itself in the database, and perhaps it does that too much.. It would be easy to add in a standard garbage collection function which runs on a random but I felt this gave me much better stats of the actual thirty-minute cache…
If you use zabbix for monitoring your network, you can have fun graphs of cache statistics by adding the following to your zabbix_agentd.conf

   1  
   2  UserParameter=mysql.totalcache,mysql –batch –skip-column-names -D {YOUR_DATABASE} -u{YOUR_USERNAME} -p{YOUR_PASSWORD} -e “SELECT count( * ) AS total, SUM( cachehit ) AS amount from cacheditems;� | cut -f1
   3  UserParameter=mysql.cachehits,mysql –batch –skip-column-names -D {YOUR_DATABASE} -u{YOUR_USERNAME} -p{YOUR_PASSWORD} -e “SELECT count( * ) AS total, SUM( cachehit ) AS amount from cacheditems;� | cut -f2