Optimize all the queries!

While digging more into the code called “Moebooru” which was forked from “Danbooru”, I noticed this:

def self.included(m)
  m.extend(ClassMethods)
  m.after_create :increment_count
  m.after_destroy :decrement_count
end

def increment_count
  connection.execute("update table_data set row_count = row_count + 1 where name = 'users'")
end

def decrement_count
  connection.execute("update table_data set row_count = row_count - 1 where name = 'users'")
end

Counting takes ages, right. Except it is not. I’ve done this, yes, but on a table with 10+ millions of data (this one has ~400k in mainline danbooru), with multiple data inserted (this one got, uh, one every other week?) and queried every second (see below), and with the required count method not a simple select count(1) on some_table (which is what the example above used for).

The best part? It’s only used once, when user registers:

def set_role
  if User.fast_count == 0
    self.level = CONFIG["user_levels"]["Admin"]
  elsif CONFIG["enable_account_email_activation"]
    self.level = CONFIG["user_levels"]["Unactivated"]
  else
    self.level = CONFIG["starting_level"]
  end

  self.last_logged_in_at = Time.now
end

moebooru again

Last week I posted about my random project which involves modernizing moebooru without doing complete rewrite (see this for yet another complete rewrite attempt).

Let’s revisit the plan:

  • Upgrade to Ruby 1.9: done, need testing.
  • Update all plugins: mostly done, can use some trimming.
  • Update anything deprecated: nope
  • Migrate to Bundler: done, not sure how to test.
  • Use RMagick instead of custom ruby-gd plugin: nope
  • Use RMagick instead of calling jhead binary: nope
  • And more!: I hope you didn’t expect me to do more while there are incomplete items above.

Sure looks good. Need more testing though. There’s also one part which I totally had no idea why should be changed when upgrading to 1.9. Just grep for FIXME to see which it is and hopefully fix it up for me (or explain what it does).

As usual, having completed the work for today, live demo is up and open for everyone to break (…if there’s anyone, that is).

[ Live Demo | Repository ]

An Attempt to Update moebooru Engine

If you didn’t know, the current moebooru running on oreno.imouto is using ancient version of many things. It also uses a custom lighty module (mod_zipfile) which doesn’t seem to be available anywhere.

I’ve updated it with latest Rails 2.x and made it compatible with nginx. Mostly. You can see it running here.

The plans:

  • Upgrade to Ruby 1.9.
  • Update all plugins.
  • Update anything deprecated.
  • Migrate to Bundler.
  • Use RMagick instead of custom ruby-gd plugin.
  • Use RMagick instead of calling jhead binary.
  • And more!

We’ll see if I can actually finish this one. Grab the source here. Yeah, I’m using Mercurial for a Rails project.

danbooru for windows

😆

This morning I randomly decided to try installing danbooru on Windows (7-x64). And indeed it works. Mostly.

Using mongrel and nginx since unicorn is not available on Windows.

Few things to note:

* system_timer is missing since I can’t seem to install it. Probably need to try ruby19 or ruby18-1.8.6-p27 (currently using ruby18-1.8.6-p383)
* manual initial database initialization since the script I made is specifically for *nix systems
* compiling danbooru_image_resizer is… *fun*
* for whatever reason I can’t install mongrel_service
* apparently there’s something missing. Or broken. Or both – there’s message “The system cannot find the path specified.” every time I start mongrel and do migrate

…anyone interested trying this on production server? 😛

_Last update 2011-07-18 21:01: formatting, also added rewrite rule for nginx to cope with uploaded media path change_