View Counts in MySql

1_justification.md

We store views in a single MySQL row, indexed by the redis key. Without redis in front of the DB this would cause lock contention, but redis keeps us reading/writing to the DB on the first, 10th, 25, and mod 100th read.

Without redis backing, using a single row to store counts would cause lock contention.

Using a row per view would result in slow reads on count(*) and in a write-heavy situation will cause the re-index to slow/stop replication because indexes are single-threaded.

count.rb

 module Count
  # Public: this is the basis for our self-healing counter class.
  # We want to be sure that if the redis store were to collapse, we
  # will pull from the DB and rebuild the store.  Our system builds in a threshold for failure
  # determined by :interval.  In the default state, we'll save to the DB every
  # 10, 25, and multiple of 100.  This 'fans out' the redis storage on write,
  # saves DB calls, and prevents race conditions for locked DB count rows.
  class CountBase
    attr_reader :interval

    def initialize
      @interval = [10, 25, 100]
    end

    # Public: acts like a redis SET command, but also writes to the DB
    # on the interval specified in :interval
    # noun - eg: pen
    # verb - eg: count
    # identifier - eg: slug_hash
    # user_id - (optional) if user-specific, the user_id
    def store(noun, verb, identifier, user_id = 0)
      @user_id    = user_id
      @noun       = noun
      @verb       = verb
      @identifier = identifier

      redis_incr do |count|
        store_db(count)
      end
    end

    # Public: acts like a redis GET, but also tries to pull from the DB
    # if the value of $redis_store.get is nil.  This allows the DB to self-heal
    # on read or write.
    def retrieve(noun, verb, identifier, user_id = 0)
      @user_id    = user_id
      @noun       = noun
      @verb       = verb
      @identifier = identifier

      redis_get do |count|
        if count.nil?
          rslt = first_get
        else
          rslt = count
        end
        # we cast becasue a redis GET always returns a string
        rslt.to_i
      end
    end

    private

    def store_db(count)
      if count == 1
        first_set
      else
        subsequent_sets(count)
      end
    end

    #####################
    # Redis Stuff
    #####################
    def redis_get
      yield $redis_store.get(redis_key)
    end

    def redis_incr
      yield $redis_store.incr(redis_key)
    end

    def redis_key
      @redis_key ||= "#{@user_id}-#{@noun}-#{@verb}-#{@identifier}"
    end

    ###################
    # DB Stuff
     ###################

    # Private: the first time a set is called for a key, we have to find out if we must self heal
    # The first set you make will always return 1 if nothing is in the DB.
    def first_set
      count = Counter.find_by_key(redis_key)
      if count
        $redis_store.set(redis_key, count.key_count)
        count.key_count
      else
        1
      end
    end

    # Private: the first time get is called for a key, we must self heal
    # The first set you make will always return 0 if nothing is in the DB.
    def first_get
      count = Counter.find_by_key(redis_key)
      if count
        $redis_store.set(redis_key, count.key_count)
        count.key_count
      else
        $redis_store.set(redis_key, 0)
        0
      end
    end

    # Private: sets after the first check the interval.
    # count - this is the current count from redis.  The count is saved if it is in
    # the interval or is a multiple of the last item in the interval array
    def subsequent_sets(count)
      if @interval.include?(count)
        count = save_count(count)
      elsif count % @interval[-1] == 0
        count = save_count(count)
      end
      count
    end

    # this is the getter/setter combo for the counter
    def save_count(count)
      # has to happen in a transaction
      User.transaction do
        counter = Counter.find_or_initialize_by_key(redis_key)
        counter.update_attributes!(key_count: count, user_id: @user_id)
        counter.key_count
      end
    end
  end
end