Hungry Machine Blog

Problem

With high volume Rails applications, entities with unique constraints are expensive and error prone to create/update. ActsAsInsertOrUpdate helps solve that problem (if you're using MySQL), by leveraging the "INSERT ... ON DUPLICATE KEY UPDATE" functionality.

Scenario

Lets say you have a Person, and Entity, and a Rating. Each user can rate each entity only once, and if they re-rate the entity, it should update the value.

class Entity < ActiveRecord::Base
  has_many :ratings
end

class Person < ActiveRecord::Base
 has_many :ratings
end
  
class Rating < ActiveRecord::Base
 belongs_to :Person
 belongs_to :Entity
end  

Here is the table that back's Rating. Notice the Unique Key constraint on (entity_id, person_id).

CREATE TABLE `ratings` (
  `id` int(11) NOT NULL auto_increment,
  `rating` tinyint(4) default '0',
  `person_id` int(11) default NULL,
  `entity_id` int(11) default NULL,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_ratings_on_entity_id_and_person_id` (`entity_id`,`person_id`),
)

Previously, the logic would be something like:

• 1) Check if a rating exists for the User + Entity
• 2) If so, update
• 3) If not, insert
• 4) rescue the insert in case there is a unqiue constraint error
• 5) retrieve the record (and/or update with the new rating)

If the table is MyISAM, Steps 1-5 aren't transactionally safe. If you're using InnoDB, and experience heavy volumes of traffic, you're prone to Deadlock's. This is even more of a concern is the unique entity is shared across multiple users, as seen with a recent client of ours.

Solution:

class Rating < ActiveRecord::Base
 belongs_to :Person
 belongs_to :Entity
 acts_as_insert_or_update :field_to_update => "rating"
end  

Now Steps 1-5 above become, just one. Rating.create(..)

In the background, ActsAsInsertOrUpdate overwrites the implementation of ActionRecord:Base#create, to leverage an often unsed feature of MySQL called INSERT ... ON DUPLICATE KEY UPDATE. As configured above, if a duplicate record is found for the unique constraint, the rating field will be updated with the new value.

Caution

This is a brute force hack on ActiveRecord::Base#create. Use at your own risk.

Code

Waiting for a rubyforge account. Will post more info soon.

CREATE FUNCTION JSON(`json` TEXT, `search_key` VARCHAR(255)) RETURNS TEXT DETERMINISTIC BEGIN

  DECLARE i INT DEFAULT 1;
  DECLARE json_length INT DEFAULT LENGTH(json);
  DECLARE state ENUM('reading_key','done_reading_key','reading_string', 'reading_array');
  DECLARE tmp_key TEXT;
  DECLARE tmp_value TEXT;
  DECLARE current_char VARCHAR(1);

  WHILE i <= json_length DO
    SET current_char = SUBSTRING(json,i,1);

    IF state = 'reading_key' THEN
      IF current_char = '"' THEN
        SET state = 'done_reading_key';
      ELSE
        SET tmp_key = CONCAT(tmp_key, current_char);
      END IF;
    ELSEIF state = 'done_reading_key' THEN
      IF current_char = '"' THEN
        SET state = 'reading_string';
      ELSEIF current_char = '[' THEN
        SET state = 'reading_array';
      END IF;
    ELSEIF state = 'reading_string' OR state = 'reading_array' THEN
      IF current_char = '\\' THEN
        SET i = i + 1;
        SET tmp_value = CONCAT(tmp_value, SUBSTRING(json,i,1));
      ELSEIF (state = 'reading_string' AND current_char = '"') OR (state = 'reading_array' AND current_char = ']') THEN
        IF search_key = tmp_key THEN
          RETURN tmp_value;
        ELSE
          SET state = NULL;
        END IF;
      ELSE
        SET tmp_value = CONCAT(tmp_value, current_char);
      END IF;
    ELSE 
      IF current_char='"' THEN
        SET state = 'reading_key';
        SET tmp_key = '';
        SET tmp_value = '';
      END IF;
    END IF;

    SET i = i + 1;
  END WHILE;

  RETURN NULL;
END

Examples

Here's a few examples of how it can be used:

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key1');
# returns 'val"ue1'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key2');
# returns 'value2'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key3');
# returns '"array1","array2"'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key4');
# returns 'value4'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key5');
# returns NULL

Notes:

If you're trying to run this in the MySQL console, you'll need to set the DELIMITER to be something other than a semi-colon. Before executing the above, run "DELIMITER $$" and after executing it, run "$$" and then "DELIMITER ;" to set your delimiter back to semi-colon.

If you're trying to run this in a Rails migration, don't forget to escape the back-slashes (i.e. '\\' should become '\\\\')

Although this handles several simple use cases of extracting JSON-encoded data, it is by no means comprehensive. There are many JSON-encoded structures that this will not work on. This will not work correctly with nested arrays, or with named hashes.

The performance of this is pretty slow. A better approach would be to create a UDF that plugs into MySQL. Here's a UDF to encode JSON data, but not decode: http://www.mysqludf.org/lib_mysqludf_json/index.php

Faster Implementation:

CREATE FUNCTION JSON_FAST(`json` TEXT, `search_key` VARCHAR(255)) RETURNS TEXT DETERMINISTIC BEGIN
  IF INSTR(json, CONCAT('"', search_key, '":"')) THEN
    RETURN SUBSTRING_INDEX(SUBSTRING(json, INSTR(json, CONCAT('"', search_key, '":"')) +
           LENGTH(search_key) + 4), '"', 1);
  ELSEIF INSTR(json, CONCAT('"', search_key, '": "')) THEN
    RETURN SUBSTRING_INDEX(SUBSTRING(json, INSTR(json, CONCAT('"', search_key, '": "')) +
           LENGTH(search_key) + 5), '"', 1);
  ELSE
    RETURN NULL;
  END IF;
END

SELECT JSON('{"key":"value \"plus quotes\""}', 'key');
# returns 'value "plus quotes"'
SELECT JSON_FAST('{"key":"value \"plus quotes\""}', 'key');
# returns 'value \'

SELECT JSON('{"key":["value1","value2"]}', 'key');
# returns '"value1","value2"'
SELECT JSON_FAST('{"key":["value1","value2"]}', 'key');
# returns NULL

sudo gem install curb

require 'rubygems'
require 'curb'
require "net/http"
require 'benchmark'

iterations = 40
Benchmark.bm do |x|
  x.report("curb") do
    iterations.times do
      c = Curl::Easy.perform("http://www.google.com")
      #puts c.body_str
    end
  end
  x.report("net/http")  do
    iterations.times do
      http = Net::HTTP.start("www.google.com")
      req = Net::HTTP::Get.new("/")
      res = http.request(req)
      #puts res.body
    end
  end
end

             user     system      total        real
curb      0.010000   0.030000   0.040000 (  4.019197)
net/http  0.140000   0.110000   0.250000 (  4.155106)

One of the challenges with writing a Facebook or Bebo application is staying within a limit it gives you to respond with data before it shows the Application Did Not Respond page to a user. Having a content reach application calling external APIs, like Amazon or YouTube, with response times beyond your control, forces you to keep such calls short to allow extra time for processing. We usually wrap them in aggressive timeouts with a retry. As an example is this code from the Ruby Amazon E-Commerce REST Service API gem rewritten to limit a single call attempt to two seconds with one more retry.

module Amazon  
  class Ecs

    def self.send_request(opts)
      request_url = prepare_url(opts)

      res = Net::HTTP.get_response(URI::parse(request_url))
      unless res.kind_of? Net::HTTPSuccess
        raise Amazon::RequestError, "HTTP Response: #{res.code} #{res.message}"
      end
      Response.new(res.body)
    end

  end
end

module Amazon  
  class Ecs

    class EmptyResponse
      def items; []; end
      def total_pages; 0; end
    end

    def self.send_request(opts)

      res = timed_try(request_url, 2) do |url|

        uri = URI::parse(url)
        req = Net::HTTP.new(uri.host, uri.port)

        # Agressive timeouts
        req.open_timeout = 1
        req.read_timeout = 2

        req.start { |http| http.request_get(url) }

      end

      res.kind_of?(Net::HTTPSuccess) ? Response.new(res.body) : EmptyResponse.new

    end

private

     def timed_try(url, attempts, &block)

       attempt = 1
       begin
         block.call(url)
       rescue Timeout::Error
         if attempt >= attempts
           RAILS_DEFAULT_LOGGER.warn "[amazon_api] gave up after attempt ##{ attempt } to get data from #{ url }"
           nil
         else
           RAILS_DEFAULT_LOGGER.warn "[amazon_api] attempt ##{ attempt } timed out on getting data from #{ url }"
           attempt += 1
           retry
         end
       end

     end

  end
end