Mysql Insert Delayed / MyISAM Migrations
For reasons I wont get into, I'm logging page hits to the database in doodlekit. The performance impact really hasn't been that bad, but I think it will start to show over time. I've been looking for some sort of fire-and-forget function for a long time. I thought about threads, message queues, or even a Ruby ESB. But I finally found a much simpler option.
Mysql has a DELAYED modifier for the INSERT function. Basically the client just sends the statement and returns right away. The statement goes into a queue, and will insert whenever Mysql decides to. If there are many statements at the same time, it will batch insert them. Obviously you would only want to use this in very specific circumstances. It's perfect for me since I don't rely on the record being there immediately.
It's very simple...
INSERT DELAYED INTO requests (ip, path, .....) VALUES ('127.0.0.1', '/home', ....)
I couldn't find any plugins to do this, and of course I'm to lazy to write my own. I just did a raw sql execute with the proper Sql Injection protection.
One important thing to mention is that the DELAYED modifier requires the MyISAM table engine, and rails uses InnoDB by default. You'll have to convert the table...
execute("ALTER TABLE web_requests ENGINE=MyISAM")
And to top it all off, by default the type of Mysql storage engine you're using wont be copied to your test database when it does db:test:prepare. When you run a unit/functional test, rails will export your development database into the schema.rb file. It doesn't have the capacity to handle special options like engine types, so that's all lost. To get around this, simply use the sql schema export type.
Put this in environment.rb
config.active_record.schema_format = :sql
I did some benchmarking with INSERT DELAYED, and when inserting 1000 to 100,000 records, there's a 3 to 4 fold speed increase. Pretty snappy.