There’s a problem with nested query I previously mentioned: query time is unstable. The worst case is at least as slow as multiple self-join method – when the first few search key result is too big.
Tags again, denormalized
Reply
Category Archives: software
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 Update
I haven’t been able to work on Moebooru for most of this month but I got to work on it this weekend. At ~30 changes, it went from -alpha to -beta.2 after receiving good amount of tests.
Summary of changes (more or less):
- Most instances of RAILS_ENV and RAILS_ROOT have been replaced with newer Rails.env/Rails.root
- Better Javascript handling in Development mode by moving cached Javascript from application.js to cached/all.js
- Removed AssetCache modification library
- Replaced HTML5Sanitizer’s html5sanitize with Rails’ built-in sanitize (because the former doesn’t work in 1.9)
- Fixed Note Editor’s “History” button
- Fixed Comment page’s pagination
- Fixed Tag Relation search function
- Updated “hack” for UTF-8 handling in Rails 2.3 with Ruby 1.9
- Merged updates from moe branch (the one currently used in yande.re)
- Reverted change which put class definition in <html> tag as it currently required by Javascript
Searching Tags in (3NF) Database
This afternoon I experimented with tag searching in yande.re database. Current implementation doesn’t scale beyond 6 tags due to parsing requirement. You can argue about binding variable all day but in the end it can be abused easily if the limit is lifted.
Moebooru rebased
Since I forgot to branch the original source, the branching looked awesomely crappy. Therefore I decided to rebase entire thing to ease up keeping track with Moebooru “mainline”. All my commits are now in branch “default”. If you didn’t do any change, backout up to revision 9174b6b5b02d and then pull again. And then, don’t ever touch moe branch again anymore.
Firefox Memory Usage
It’s still quite slow even on my system. I should switch to Chrome again one of these days. I don’t even use Firefox anymore apart of on this specific system. Oh and Flash also crashes every other minutes in this browser.
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 ]
bcrypt in Debian
As much as Drepper want to pretend bcrypt is wrong solution, it actually gives one benefit: ease of switch to Linux. Some systems use bcrypt by default or configurable to use it. On other case, there might be time where you need system’s (or applications using system’s) crypt to handle bcrypt passwords from external system (usually web applications).
It’s quite difficult to enable bcrypt support in RHEL based distro as there is no libxcrypt and pam_unix2 packages available. Thankfully it’s available in Debian (and derivatives) in package libpam-unix2.
The README.Debian says to modify files in /etc/pam.d but if I remember it correctly, it confused apt PAM handling system or whatever. Fast forward few weeks, I discovered a better way to use it by creating PAM configuration in /usr/share/pam-configs. Since it’s mostly equivalent to normal pam_unix, I just copy and modify the file using this (long-ass) oneliner sed:
sed -e 's/pam_unix\.so/pam_unix2.so/g;s/^Name: Unix authentication$/Name: Unix2 authentication/;s/pam_unix2\.so obscure sha512/pam_unix2.so obscure blowfish rounds=8/;s/ nullok_secure//' /usr/share/pam-configs/unix > /usr/share/pam-configs/unix2
Then execute pam-auth-update, select Unix2 authentication and deselect Unix authentication. Don’t forget to update passwords for all other users as well or they won’t be able to login since pam_unix2 doesn’t recognize sha based hashes.
Actually, change all other users password to use md5 first before replacing the PAM with pam_unix2.
Update 2012-04-01: Removed nullok_secure since it isn’t supported.
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.
Repositories
I’ve enabled Danbooru repository sync to Bitbucket again as I’m planning to work on it again – mainly upgrading to Rails 3 and reducing insanity required for installation. And perhaps import the danbooru-imouto changes back.
As usual, the WordPress mirror is still going.