analyze_logs.rb

require 'rubygems'
require 'activerecord'

if false
  require 'ruby-debug'
  Debugger.start
  Debugger.settings[:autoeval] = true if Debugger.respond_to?(:settings)
end

ActiveRecord::Base.establish_connection(:adapter => "sqlite3", :dbfile => 'request_times.db')

ActiveRecord::Schema.define do
  create_table :request_paths, :force => true do |t|
    t.string :path
    t.integer :request_count
    t.float :mean_processing_time
    t.float :mean_rendering_time
    t.float :min_hourly_mean_processing_time
    t.float :max_hourly_mean_processing_time
    t.float :min_hourly_mean_rendering_time
    t.float :max_hourly_mean_rendering_time
    t.float :hourly_mean_processing_time_range
    t.float :hourly_mean_rendering_time_range
  end
  add_index :request_paths, :path, :unique => true

  create_table :request_hours, :force => true do |t|
    t.integer :hour
    t.integer :request_path_id
    t.float :mean_processing_time
    t.float :mean_rendering_time
    t.integer :request_count
  end
  add_index :request_hours, [:hour, :request_path_id], :unique => true

  create_table :requests, :force => true do |t|
    t.string :url
    t.integer :request_path_id
    t.datetime :requested_at
    t.float :processing_time
    t.float :rendering_time
    t.integer :hour
  end
end

class RequestPath < ActiveRecord::Base
  has_many :requests

  def self.[](full_url)
     path = full_url[/^([^\?]+)/, 1]
     record = self.find_or_create_by_path(path)
  end
end

class Request < ActiveRecord::Base
  belongs_to :request_path

  before_create :set_stuff

  def set_stuff
    self.request_path = RequestPath[self.url]
    self.hour = self.requested_at.hour
    true
  end  
end

class RequestHour < ActiveRecord::Base
  belongs_to :request_path
end

# http://rubular.com/r/8YnHaXZgtT
EXTRACTOR = /^\[([^\]]+)\].*Completed in ([\d\.]+)(?:.*Rendering: ([\d\.]+))?.*\[([^\]]+)\]$/

i = 0
File.open('20100729-wfe-render') do |file|
  file.each_line do |line|
    i += 1
    break if i == 1000
 
    raise "Did not match: #{line}" unless line =~ EXTRACTOR

    Request.create!(
      :requested_at => DateTime.parse($1),
      :processing_time => $2,
      :rendering_time => $3,
      :url => $4
    )
  end
end

ActiveRecord::Base.connection.execute(<<SQL)
         INSERT INTO request_hours(hour, request_path_id, mean_processing_time, mean_rendering_time, request_count) 
         SELECT requests.hour, requests.request_path_id, AVG(requests.processing_time), AVG(requests.rendering_time), COUNT(*)
         FROM requests GROUP BY requests.request_path_id, requests.hour; 
SQL

ActiveRecord::Base.connection.execute(<<SQL)
 UPDATE request_paths SET request_count = (SELECT COUNT(*) FROM requests WHERE requests.request_path_id = request_paths.id), 
   mean_processing_time = (SELECT AVG(processing_time) FROM requests WHERE requests.request_path_id = request_paths.id),
   mean_rendering_time = (SELECT AVG(rendering_time) FROM requests WHERE requests.request_path_id = request_paths.id);
SQL

ActiveRecord::Base.connection.execute(<<SQL)
  UPDATE request_paths SET 
    max_hourly_mean_processing_time = (SELECT MAX(mean_processing_time) FROM request_hours WHERE request_hours.request_path_id = request_paths.id),
    min_hourly_mean_processing_time = (SELECT MIN(mean_processing_time) FROM request_hours WHERE request_hours.request_path_id = request_paths.id),
    max_hourly_mean_rendering_time = (SELECT MAX(mean_rendering_time) FROM request_hours WHERE request_hours.request_path_id = request_paths.id),
    min_hourly_mean_rendering_time = (SELECT MIN(mean_rendering_time) FROM request_hours WHERE request_hours.request_path_id = request_paths.id),
    hourly_mean_rendering_time_range = (SELECT MAX(mean_rendering_time) - MIN(mean_rendering_time) FROM request_hours WHERE request_hours.request_path_id = request_paths.id),
    hourly_mean_processing_time_range = (SELECT MAX(mean_processing_time) - MIN(mean_processing_time) FROM request_hours WHERE request_hours.request_path_id = request_paths.id);
SQL