Cobalt Edge

 
Tags


Filed under

Performance

 
February 4, 2009

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]

February 27, 2007

Benchmarking Mongrel, Apache, Rails, etc.

I'm doing some benchmarking of a Rails based web app (technically, a web service) to try to establish some baselines and to use in assessing build outs/hardware deployment, etc. To start off, I wanted to establish a baseline by using pure static content. The performance I got didn't seem quite right to me. Below is a message I sent to the mongrel-users mailing list. I figured this was a good blog item, and would like to solicit help on this...

I'm trying to do some initial benchmarking of our setup, mainly just to establish baselines. I'm essentially using the process Zed outlines in a previous message:
http://rubyforge.org/pipermail/mongrel-users/2006-May/000200.html

What I'm running into is that Mongrel appears only half as fast as Apache when serving a small static HTML file. If I then add in Apache with mod_proxy_balancer, going to a single Mongrel, it drops down to nearly about a third of what pure static Apache will do. This seems bogus to me, and I suspect I have either some configuration problem, or something. My understanding from what I've read is that Mongrel should be fairly close to Apache when serving static content (at least not only 50% as fast). Is that right as a generalization?

Here's some info to back this up.
- Server: HP DL360 dual 3.0GHz Xeons, 4GB RAM, 10k RPM SCSI disks
- OS: Fedora Core 6, up to date as of a few days ago
- Apache 2.2.3-5, with mod_proxy_balancer
- Mongrel 1.0.1, mongrel_cluster 0.2.1
- serving a static HTML file from the Rails app's public directory
- Testing using an identical server, that sits above this one in the rack, connected to a switch (so it's one hop)
- Using httperf for testing, with the following command:
httperf --server lab05 --port 80 --uri /mongrel_alive.html --num-conns 10000
The number of connections I vary to be near the 10 second mark...

Here's the results:

- Just Apache, num-conns=15000, ~1400 req/sec
- Direct to Mongrel on port 5000, num-conns=8000, ~740 req/sec
- Apache mod_proxy_balancer to a single Mongrel, num-conns=5000, ~475 req/sec

So, I'm incurring a massive penalty for the balancer/cluster setup. In production we will be using hardware load balancers, but even still, my understanding was that instead of a drop from 1400 to 740, it should be somewhat closer (say at least over 1000).

What would folks suggest, or what comments do you have?

Shortly after that, I decided to throw Pen and Nginx in the mix just as a random check on this. I haven't used them before so relied on what I found on the net, etc. for config, thus I could potentially get better results, but they yielded:
  • Nginx with one Mongrel: ~612 req/sec
  • Pen with one Mongrel: ~670 req/sec
I can see Apache being slower than these, as it's a much bigger and more complex app. So the question remains, should straight Mongrel be half as fast as Apache? And, should the load balancers affect the performance that much (although Pen is having only a 10% impact here).

Loading mentions Retweet
Filed under  //   Apache   LoadBalancing   Mongrel   Performance   Rails   Testing  

Comments [0]