Cobalt Edge

 
Filed under

MySQL

 

MySQL Performance Issues and acts_as_versioned

Recently we ran into an interesting performance issue with MySQL. We have an automated process we run at night a few nights a week that does data harvesting for hotel rates and such. This data is versioned so that we can look at historical values. However, this script had begun to really crawl. Originally it took a couple hours to run. But it had gotten to the point where it could take almost a day.

I tracked this down to being a SQL MAX call used by acts_as_versioned to determine the next version for one of these records. The problem is that it had to sift through nearly 10 million records. In testing this on my local machine, just one of these SQL queries could take 45 seconds! Think about doing this across oh say 100,000 hotels, ya, not good.

The good folks at GitHub ran into this same thing (with a table of 36M records) on nearly the same day. Their approach is similar to the approach I'll be taking on another table (which isn't currently affecting us this way, but will have different benefits), which was to split it into two tables, one with older data. I could have done this, and would have, but the reality was that we simply didn't need to keep these versions, as we weren't using the data. So, luckily, I was able to just no longer version this particular model, and throw out that table. After doing that, I ran the script and it took just over an hour. Yea!

So, this is something to note if you use acts_as_versioned with models that have frequent changes and a decent number of those models to begin with (think multipliers). One of the things I'll be looking into in the future is whether that MAX needs to get done, or whether acts_as_versioned can be smarter about how it does it. On first glance you'd think you could just use the version number on the original model itself, but that number isn't guaranteed to be the latest number, since you can rollback versions and so on.

Loading mentions Retweet
Filed under  //   MySQL   Performance   Rails   RubyOnRails  

Comments [0]

New Tools

I've been using a few new tools lately, and also got rid of one I've used a long time. First up, now that I'm running Leopard on my Macs, I've found I just don't need or want Path Finder. Path Finder has been great, I've used it for a few years now. But, with the new features in Finder, and the niggling issues I've had with Path Finder, it was finally time to end my use. The Finder's new sidebar, stacks, quick look, and the fact that you can show the directory path at the bottom of Finder windows (this was a big-little feature in Path Finder for me), brought me back to using it.

Update (12 Nov 2007): A new version of Path Finder has been released that integrates MUCH better with Leopard, including supporting Quick View, and having an "Open in Finder" replacement that works completely. I'll have to see if I go back again to using Path Finder...

Some new tools, both web and Mac that I've been using a fair bit lately include:

Mind Meister


I haven't used mind mapping tools much in the past. I've tried various ones several times, but they either were too cumbersome, too slow, or just didn't seem useful. I found out about MindMeister from someone on Twitter, and have a couple mind maps running on it now. I'm also collaborating on one of them with others. MM is fairly preferment, quite easy to use, nice to look at, and the collaboration bit is super nice. I'd like to see them add a way to insert a URL/link, where that link could be clicked on, but that's about the only issue I have so far. Interesting to note, if you get the Premium account (a measly $4/month!), they have offline editing. They are using Google Gears for this. Another intriguing bit is that they have an API. I haven't looked into this yet, or haven't thought about how I'd use it, but I always like to see services that have this as an option.

Mars Edit 2


The 2.0 version, now produced by Red Sweater Software is quite nice. In days of old, I'd preferred ecto, but this new version is simple, effective, fast, and quite nicely, has great Flickr integration. I've been using Flickr quite a bit, especially with my other blog, and often put multiple photos in a post. Mars Edit makes this trivial.

Navicat


Navicat is a GUI database tool. I used to use CocoaMySQL, and YourSQL and such on Mac, but YourSQL doesn't seem to work these days, and CocoaMySQL seems out of date, and I think wasn't under development anymore(?). Navicat, while a commercial product, has been rock solid, and I've found to be quite useful. Admittedly, I use it a lot of simple browsing, simple queries and value changes, etc., but the dependability and quality of it have kept it in my tool chest (and got me to buy it). One other nice thing here is that while it has a real Mac UI, it is a tool also available on Linux and Windows.

Acorn


Acorn is a slick, simple new image editor by Gus Mueller of Flying Meat Software (likely better known for VoodooPad). It's extremely fast to load up, and I'm finding it's my top pick to do things like saving images in another format (if they aren't in my Lightroom library - otherwise I use Lightroom), make minor tweaks, crops, etc. The speed is one of the best things - it launches super fast, and is very fast to use, so it's an excellent tool for quick work. Interesting note: you can write plugins in Python or Objective-C.

Pack Rat


I use 37 Signals Backpack extensively. It's my GTD system, and I keep tons of notes and information I need in it. Backpack's web interface is somewhat slow, especially if you switch between various pages a lot (and don't want to keep said pages in lots of browser tabs). I have a half completed AIR app to be my Backpack client, but Pack Rat seems to be the ticket now. It has synchronization, offline editing, and so on. I am sold, and have essentially not used my browser for Backpack since.

Panic Sans font


Last, but certainly not least, is the Panic Sans font. This is a fixed width font, great for coding. I've been using the Bitstream Vera Sans Mono font for quite some time, but Duncan's recent post about this discovered a few new ones. Panic Sans is actually a font found inside the Coda app's package. It's very similar to Bitstream Vera Sans Mono, but slightly nicer. Duncan's post has good info in that particular post, but also see some of his followup posts on the subject.

What's new in your tool chest?

Loading mentions Retweet
Filed under  //   Apple   Backpack   Flickr   fonts   Mac   mind mapping   MySQL  

Comments [0]

Installing Ruby MySQL Gem with MacPorts MySQL

Blogging this more for my own record, but maybe others will find it useful... Tonight I was having a hard time getting the MySQL Ruby Gem installed on a new MacBook Pro. I have installed Ruby, Rails, RubyGems, MySQL, etc. via MacPorts (or via the Ruby that was installed via MacPorts). Anyway, this is the command that finally got it to work:

sudo gem install mysql -- --with-mysql-include=/opt/local/include/mysql5 --with-mysql-lib=/opt/local/lib/mysql5 --with-mysql-config=/opt/local/lib/mysql5/bin/mysql_config

Update: as I mention in my comment below (updating here in case folks don't read the comments), when doing this on Leopard/MacOS X 10.5, I needed to change it to:

sudo env ARCHFLAGS="-arch i386" gem install mysql -- --with-mysql-include=/opt/local/include/mysql5 --with-mysql-lib=/opt/local/lib/mysql5 --with-mysql-config=/opt/local/lib/mysql5/bin/mysql_config

Loading mentions Retweet
Filed under  //   MySQL   Rails   Ruby   RubyOnRails  

Comments [0]