slow way to aggregate downloads per version in gemcutter :(

README

There's got to be a faster way. This is with a production db dump from a few days ago.

% time rake gemcutter:migrate:downloads
> gemcutter (0.5.0) 131783
>> 0.5.0 19314
>> 15
>> 0.5.0.pre.2 64
>> 15
>> 0.5.0.pre 45
>> 14
>> 0.4.1 17712
>> 27
>> 0.4.0 3337
>> 25
>> 0.4.0.pre 47
>> 18
>> 0.3.0 61850
>> 75
>> 0.3.0.pre 103
>> 43
>> 0.2.1 11930
>> 96
>> 0.2.0 2888
>> 72
>> 0.1.8 9030
>> 83
>> 0.1.7 3058
>> 71
>> 0.1.6 1221
>> 112
>> 0.1.5 191
>> 61
>> 0.1.4 77
>> 56
>> 0.1.3 57
>> 44
>> 0.1.2 64
>> 48
>> 0.1.1 93
>> 56
>> 0.1.0 95
>> 56
>> 0.0.9 62
>> 48
>> 0.0.8 58
>> 45
>> 0.0.7 52
>> 42
>> 0.0.6 45
>> 38
>> 0.0.5 40
>> 34
>> 0.0.4 42
>> 38
>> 0.0.3 41
>> 35
>> 0.0.2 44
>> 35
>> 0.0.1 45
>> 39
rake gemcutter:downloads:migrate  3.50s user 1.21s system 11% cpu 39.933 total

downloads.rb

namespace "gemcutter:downloads" do
  desc "Migrate from downloads table to redis"
  task :migrate => :environment do
    RAILS_DEFAULT_LOGGER = Logger.new(STDOUT)
    $redis.flushdb

    class Dl < ActiveRecord::Base
      set_table_name "downloads"
    end

    rubygems = Rubygem.find_all_by_name("gemcutter")
    #rubygems = Rubygem.all
    rubygems.each do |rubygem|
      print "> #{rubygem} "
      puts $redis[Download.key(rubygem)] = rubygem['downloads']

      rubygem.versions.each do |version|
        print ">> #{version} "

        puts $redis[Download.key(version)] = version['downloads_count']

        downloads = Dl.count(:group  => "version_id, date(created_at)",
                             :having => "version_id = #{version.id}",
                             :select => "version_id")

                             puts ">> #{downloads.size}"
        downloads.each do |date, dls|
          $redis.hset Download.history_key(version), date, dls
        end
      end
    end
  end
end

gistfile3.txt

  Rubygem Load (430.8ms)   SELECT * FROM "rubygems" WHERE ("rubygems"."name" = E'gemcutter') 
  Version Load (681.7ms)   SELECT * FROM "versions" WHERE ("versions"."platform" = E'ruby') AND ((("versions"."latest" = 't') AND ("versions".rubygem_id = 18555)) AND ("versions".rubygem_id = 18555)) ORDER BY position LIMIT 1
  Rubygem Load (14.2ms)   SELECT * FROM "rubygems" WHERE ("rubygems"."id" = 18555) 
  Version Load (1.6ms)   SELECT * FROM "versions" WHERE ("versions".rubygem_id = 18555) ORDER BY position
  SQL (4883.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 127328 
  SQL (222.9ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 126602
  SQL (174.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 126413 
  SQL (2721.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 125391
  SQL (483.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 125180 
  SQL (134.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 123517
  SQL (12154.9ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 118142 
  SQL (179.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 117952
  SQL (3523.4ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 115175 
  SQL (734.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 114823
  SQL (2335.5ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 112770 
  SQL (3348.9ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 111886
  SQL (2886.9ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 107021 
  SQL (236.6ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 106006
  SQL (381.6ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 104706 
  SQL (161.4ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 100047
  SQL (242.6ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99829 
  SQL (134.4ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99812
  SQL (77.8ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99811 
  SQL (119.0ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99446
  SQL (67.1ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99343 
  SQL (13.6ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99342
  SQL (314.5ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99220 
  SQL (222.4ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 95576
  SQL (223.0ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88211 
  SQL (118.8ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88212
  SQL (222.1ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88213 
  SQL (152.4ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88214

optimize?

create index "index_downloads_on_created_at_and_date" on downloads (version_id, date(created_at));
vaccuum analyze downloads

rake gemcutter:downloads:migrate  3.47s user 1.22s system 9% cpu 49.552 total

  SQL (0.3ms)   SET client_min_messages TO 'panic'
  SQL (0.3ms)   SET client_min_messages TO 'notice'
  Rubygem Load (83.5ms)   SELECT * FROM "rubygems" WHERE ("rubygems"."name" = E'gemcutter') 
  Version Load (167.1ms)   SELECT * FROM "versions" WHERE ("versions"."platform" = E'ruby') AND ((("versions"."latest" = 't') AND ("versions".rubygem_id = 18555)) AND ("versions".rubygem_id = 18555)) ORDER BY position LIMIT 1
  Rubygem Load (13.1ms)   SELECT * FROM "rubygems" WHERE ("rubygems"."id" = 18555) 
  Version Load (1.4ms)   SELECT * FROM "versions" WHERE ("versions".rubygem_id = 18555) ORDER BY position
  SQL (4268.3ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 127328 
  SQL (111.8ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 126602
  SQL (30.9ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 126413 
  SQL (4307.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 125391
  SQL (355.8ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 125180 
  SQL (115.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 123517
  SQL (17855.1ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 118142 
  SQL (266.6ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 117952
  SQL (4014.9ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 115175 
  SQL (767.8ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 114823
  SQL (3346.6ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 112770 
  SQL (836.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 111886
  SQL (773.3ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 107021 
  SQL (88.6ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 106006
  SQL (94.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 104706 
  SQL (33.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 100047
  SQL (120.5ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99829 
  SQL (189.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99812
  SQL (109.4ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99811 
  SQL (136.8ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99446
  SQL (72.3ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99343 
  SQL (57.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99342
  SQL (285.2ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 99220 
  SQL (288.7ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 95576
  SQL (233.8ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88211 
  SQL (201.1ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88212
  SQL (228.0ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88213 
  SQL (178.0ms)   SELECT count("downloads".version_id) AS count_version_id, version_id, date(created_at) AS version_id_date_created_at FROM "downloads" GROUP BY version_id, date(created_at) HAVING version_id = 88214